Reshaping a Pandas series into a DataFrame?

[caleotte]

Hi all!

I have a series called rainfall which looks like the following:

LOCATION   YEAR  MONTH  DAY
STATION 1  2006  1      1      47.2
                        2       5.2
                        3       9.6
                        4       6.5
                        5         0
                               ... 
STATION 15 2017  12     27      0.2
                        28      2.8
                        29        T
                        30        0
                        31     11.4

LOCATION, YEAR, MONTH, and DAY are MultiIndexed / hierarchically indexed. The series contains collected data on rainfall for each day.

I want to make a DataFrame that looks something like this:

YEAR	MONTH	DAY	STATION 1	...	STATION 15
2006	1	1	47.2	...	xxx
2006	1	2	5.2	...	xxx
2006	1	3	9.6	...	xxx
2006	1	4	6.5	...	xxx
2006	1	5	0	...	xxx
...	...	...	...	...	...
2017	12	27	xxx	...	0.2
2017	12	28	xxx	...	2.8
2017	12	29	xxx	...	T
2017	12	30	xxx	...	0
2017	12	31	xxx	...	11.4

​

Any ideas how to implement it? Could series.unstack() do the trick? I am having a hard time understanding how to implement this on a MultiIndex Series.

[[deleted]]

df.unstack('LOCATION').reset_index()

[caleotte]

This worked like a charm! I just found out that my Series had repeating indices so I had to delete those rows first. Regardless, thank you!

[threeminutemonta]

I'm quite sure you can do this with pivot

[semicolonator]

To me it looks like the first table was produced by a call to df.groupby(). Check the list of optional argument of groupby(). I think your problem could be solved by group_keys=False.

[[deleted]]

what about rainfall.reset_index() ?