Search code examples
pandaspandas-melt

Reshaping pandas df from corsstabulation to long format

I have a dataframe along the lines of the below:

d = {'Country': ['USA', 'France', 'Germany'], 'USA': ['NaN', 2, 5], 'France': [3, 'NaN', 7],  'Germany': [3.5, 4, 'NaN']}
df = pd.DataFrame(data=d)

    Country USA France  Germany
0   USA     NaN   3       3.5
1   France  2     NaN      4
2   Germany 5      7      NaN

I wish to reshape it to the following format:

d = {'Country': ['USA', 'USA', 'France', 'France', 'Germany', 'Germany'], 'Country2': ['France', 'Germany', 'USA', 'Germany', 'USA', 'France'],\
    'Value': [3, 3.5, 2, 4, 5, 7]}
df = pd.DataFrame(data=d)

Country     Country2    Value
0   USA     France      3.0
1   USA     Germany     3.5
2   France  USA         2.0
3   France  Germany     4.0
4   Germany USA         5.0
5   Germany France      7.0

How can I rotate/melt/pivot the df to achieve this outcome?

Solution

  • Let us do

    out = df.set_index('Country').stack().reset_index()