Changing dataframe values as columns - columns - as index

Question

meet the problem of changing values and columns names,

for example i have such Pandas dataframe:

       Value  2013  2014 Company Name
1  Employees    10    12     Aviators
2  Computers    20     2     Aviators
3  Employees     4    13       Mouses
4  Computers     1     8       Mouses
5   Monitors     5     1       Mouses

and i need to make values as colums and years as values or indexes (values better) smth like this:

      Employees  Computers  Monitors Company name
2013         10         20       NaN     Aviators
2014         12          2       NaN     Aviators
2013          4          1       5.0       Mouses
2014         13          8       1.0       Mouses

So i need only change years and values, company Name column should be placed like on the sample

What's the easiest way to do it?

Erfan · Accepted Answer · 2019-10-30 12:50:04Z

2

Using pivot_table with stack:

df = df.pivot_table(index=['Company Name'], 
                    columns='Value',
                    aggfunc='sum').stack(level=0).reset_index(level=0)

Value Company Name  Computers  Employees  Monitors
2013      Aviators       20.0       10.0       NaN
2014      Aviators        2.0       12.0       NaN
2013        Mouses        1.0        4.0       5.0
2014        Mouses        8.0       13.0       1.0

To remove the column index name, use rename_axis:

df = df.rename_axis(None, axis='columns')

     Company Name  Computers  Employees  Monitors
2013     Aviators       20.0       10.0       NaN
2014     Aviators        2.0       12.0       NaN
2013       Mouses        1.0        4.0       5.0
2014       Mouses        8.0       13.0       1.0

edited Oct 30, 2019 at 12:50

answered Oct 30, 2019 at 12:36

Erfan

43.4k10 gold badges75 silver badges86 bronze badges

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

10 Comments

George Over a year ago

It gaves me DataError: No numeric types to aggregate

Erfan Over a year ago

Edited my answer with aggfunc='sum', can you try that @George

Erfan Over a year ago

Let's see if this works first, he might have datetime columns instead. @jezrael

George Over a year ago

With aggfunc it works, but thats the difference between sum and first?

Erfan Over a year ago

Yes, will do later @jezrael

|

Collectives™ on Stack Overflow

Changing dataframe values as columns - columns - as index

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related