Search code examples
pythondataframetransformdataformat

How to transform a Python dataframe to a different format?

I just need help to transform a python dataframe to a different format.

My input file:

country     1990    1991    1992
Cambodia    65.4    65.7    66.2
Cameroon    63.9    63.7    64.7
Canada      98.6    99.6    99.6
Cape Verde  77.7    77      76.6

My intended output file:

Year    country     values
1990    Cambodia    65.4
1990    Cameroon    63.9
1990    Canada      98.6
1990    Cape Verde  77.7
1991    Cambodia    65.7
1991    Cameroon    63.7
1991    Canada      99.6
1991    Cape Verde  77
1992    Cambodia    66.2
1992    Cameroon    64.7
1992    Canada      99.6
1992    Cape Verde  76.6

After transformation, I want to add additional columns and perform update and then save it to a csv file. Not sure how.

Solution

  • It’s hard to read your tables without proper formatting, but have you tried using the transpose function?

    It’s like “df.T”

    Edit:

    Okay I see. What you need to do is melt and pivot your DataFrame.

    https://pandas.pydata.org/pandas-docs/stable/user_guide/reshaping.html

    Edit 2:

    Try pd.melt(df,var_name=“Year”)

    Edit 3:

    To merge two dataframes, try pd.merge(df1,df2,on=["Year","Country"]) where df2 contains the year, country, and any other columns you want.