pandas - transpose matrix in python-CodePudding

I'm working with transformations to table data but now I am kinda stuck in a transpose of a table that is currently a dataframe.

Here is my table:

          0        1       2
0        NA     2005    2006
1    GREECE     78.4    97.6
2  PORTUGAL    127.3   128.1

So both Greece and Portugal has values for the 2005 and 2006 year. What I need to do is to each row correspond to a Country and a singular year this way:

          0       1        2
0        NA    YEAR    VALUE
1    GREECE    2005     78.4    
2    GREECE    2006     97.6
3  PORTUGAL    2005    127.3
4  PORTUGAL    2006    128.1

What would be the best way to achive this? I am working with dataframes in python.

CodePudding user response：

Why aren't you using the index functionality in pandas? It would make much more sense if the country was the row index, and the year was the columns:

import pandas as pd

df = pd.read_clipboard() # Your df here

df = pd.DataFrame(
    df.iloc[1:, 1:].values,
    columns=df.iloc[0, 1:].values,
    index=df.iloc[1:, 0].values
)
#           2005.0  2006.0
# GREECE      78.4    97.6
# PORTUGAL   127.3   128.1

# Now you can use built-in pandas functionality:
out = df.melt(var_name="YEAR", value_name="VALUE", ignore_index=False)
#             YEAR  VALUE
# GREECE    2005.0   78.4
# PORTUGAL  2005.0  127.3
# GREECE    2006.0   97.6
# PORTUGAL  2006.0  128.1

# Or similarly:
out = df.stack().to_frame("VALUE").reset_index(names=["COUNTRY", "YEAR"])
#     COUNTRY    YEAR  VALUE
# 0    GREECE  2005.0   78.4
# 1    GREECE  2006.0   97.6
# 2  PORTUGAL  2005.0  127.3
# 3  PORTUGAL  2006.0  128.1

CodePudding user response：

have a look at pandas pd.melt command: https://pandas.pydata.org/docs/reference/api/pandas.melt.html