pandas - transpose matrix in python

Question

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.

Answer 1

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