Transposing multiple columns to 1 column in pandas DataFrame

Question

I am working with a large dataset with multiple columns. It looks similar to this:

import pandas as pd

df = pd.DataFrame(
    {'Country': ["NL", "NL", "NL", "BE", "BE", "BE"],
     "Year": [2010, 2011, 2012, 2010, 2011, 2012],
     "Monkeys": [1, 4, 5, 2, 5, 4],
     "Giraffes": [40, 50, 60, 20, 30, 35],
     "Pigs": [10, 15, 20, 30, 35, 40]
     })

Which looks like this:

  Country  Year  Monkeys  Giraffes  Pigs
0      NL  2010        1        40    10
1      NL  2011        4        50    15
2      NL  2012        5        60    20
3      BE  2010        2        20    30
4      BE  2011        5        30    35
5      BE  2012        4        35    40

I want it to look like this:

    Animals Country  2010  2011  2012
0   Monkeys      NL     1     4     5
1  Giraffes      NL    40    50    60
2      Pigs      NL    10    15    20
3   Monkeys      BE     2     5     4
4  Giraffes      BE    20    30    35
5      Pigs      BE    30    35    40

Using pivot_table and pivot methods I have been unable to get this to work. Is there anyone who can help me solve this?

jezrael · Accepted Answer · 2021-11-16 11:26:33Z

2

Use DataFrame.set_index for Multiindex and reshape by DataFrame.unstack first and then Series.unstack:

df = (df.set_index(['Country','Year'])
        .rename_axis('Animals', axis=1)
        .unstack([0,1])
        .unstack()
        .reset_index()
        .rename_axis(None, axis=1))
print (df)
    Animals Country  2010  2011  2012
0   Monkeys      BE     2     5     4
1   Monkeys      NL     1     4     5
2  Giraffes      BE    20    30    35
3  Giraffes      NL    40    50    60
4      Pigs      BE    30    35    40
5      Pigs      NL    10    15    20

edited Nov 16, 2021 at 11:26

answered Nov 16, 2021 at 10:44

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

brokkoo Over a year ago

Thanks for the answer. Just wondering, what does [0,1] refer to in the unstack method?

jezrael Over a year ago

@brokkoo 0 it is first and second levels

sammywemmy · Accepted Answer · 2021-11-16 11:55:54Z

0

A combination of melt and pivot should suffice:

(df.melt(id_vars = ['Country', 'Year'], var_name='Animals')
   .pivot(index=['Animals', 'Country'], columns='Year', values = 'value')
   .reset_index()
   .rename_axis(columns=None)
)
 
    Animals Country  2010  2011  2012
0  Giraffes      BE    20    30    35
1  Giraffes      NL    40    50    60
2   Monkeys      BE     2     5     4
3   Monkeys      NL     1     4     5
4      Pigs      BE    30    35    40
5      Pigs      NL    10    15    20

answered Nov 16, 2021 at 11:55

sammywemmy

28.9k4 gold badges21 silver badges35 bronze badges

Collectives™ on Stack Overflow

Transposing multiple columns to 1 column in pandas DataFrame

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related