Converting values of an index to columns in pandas

Question

If have a long dataframe, that features a two column multiindex and one column with values. I think this is the best way to represent data in pandas (tidy), but I'd like to convert it to a format that excel is capable of working with. The current format has too many rows. I start out with pandas in the first place, because i have about 6 GB of .csv data and excel won't do the job. A lot of it is cropped, so I hope the output is more manageable.

                                VALUE
DATE                    ID      
2005-01-01 00:00:00     222     3.8
2005-01-01 01:00:00     222     3.7
2005-01-01 02:00:00     222     3.2
2005-01-01 00:00:00     201     3.1
2005-01-01 01:00:00     201     3.7
2005-01-01 02:00:00     201     3.2
...................................

The desired format for .csv export would look as follows:

                        201     222     ....
DATE                          
2005-01-01 00:00:00     3.1     3.8     ....
2005-01-01 01:00:00     3.7     3.7     ....
2005-01-01 02:00:00     3.2     3.2     ....

So in short, I'd like to convert every index value "ID" to a seperate column.

jezrael · Accepted Answer · 2020-09-22 08:42:27Z

1

Select column VALUE and reshape by Series.unstack:

df1 = df['VALUE'].unstack()

If df is Series then use:

df1 = df.unstack()

edited Sep 22, 2020 at 8:42

answered Sep 22, 2020 at 6:55

jezrael

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

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

4 Comments

Harper Over a year ago

I'm amazed that it's that easy.

Harper Over a year ago

After trying it out, the right way to use it seems to be df1 = df.unstack('VALUE')

jezrael Over a year ago

@Harper - If df is Series, then df1 = df.unstack() should working

Harper Over a year ago

Yes, that is also an option, but df1 = df['VALUE'].unstack() does not. It returns a key error.

Collectives™ on Stack Overflow

Converting values of an index to columns in pandas

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related