CODEWITHSUNDEEP
pythonpandas
Harper
Harper

Reputation: 1223

Converting values of an index to columns in pandas

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.

Upvotes: 1

Views: 31

Answers (1)

jezrael
jezrael

Reputation: 863611

Select column VALUE and reshape by Series.unstack:

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

If df is Series then use:

df1 = df.unstack()

Upvotes: 1

Related Questions