How to pivot a vector in Python

user_5094019 0
Viewed 218

I have the following sample dataframe:

index = pd.date_range('1/1/2020', periods=8760, freq='H')
df = pd.Series(range(8760), index=index)

What I would like to do, is have a new dataframe, which for column it will have the day numbers, 1 to 31 and for rows, it will have all the months, starting the first available month from the df till the last one available. It should also have months even if they are not included in the df.

The output should look like that:

enter image description here

In the cells the result should be either average values or sum values.

For example, the first cell (01-20,1) should have the average of all the values of the first day for the first month, the last column and last cell (12-20,31) should have the average of all the values in the last day of that month, etc.

I tried to work with groupby but not any luck there.

pandas pivot-table python
1 Answers

You want pivot_table with days and months:

# your data is a series, we turn it to a dataframe
df = df.to_frame(name='values')

# create days and months
df['days'] = df.index.day
df['months'] = df.index.to_period('M')

# since your data is hourly, I just take daily mean:
df.pivot_table(columns='days', 
               index='months', 
               values='values', 
               aggfunc='mean')

You can also use groupby without converting series to frame:

(df.groupby([df.index.day, df.index.to_period('M')])
   .mean()
   .unstack(0)
)
user_42384080
Related