How to pivot a DatetimeIndex Series on Pandas

Question

I have a Pandas Series of hourly data for a whole year, where the index is the DatetimeIndex, and a column with one value for each hour in the year.

I want to create a Dataframe so that each row represents the day in the year (1-365) and each column represents the hour in the day (0-23). The shape of the DataFrame should therefore have 365 rows and 24 columns.

I was hoping that this would work, but somehow the values do not get filled in:

df = pd.DataFrame(prices,index=prices.index.dayofyear.unique(),columns=prices.index.hour.unique())

Example of Series for first two days:

Output I'm getting:

I also didn't manage to make it work with this:

df = pd.pivot(data=prices,index=prices.index.dayofyear.unique(),columns=prices.index.hour.unique())

Thanks in advance!

Answer 1

Example

i make minimal and reproducible example to solve problem

date1 = pd.date_range('2020-01-01 00:00:00', periods=3, freq='H')
date2 = pd.date_range('2020-01-02 00:00:00', periods=3, freq='H')
s = pd.Series([10, 20, 30, 40, 50, 60], index=date1.append(date2))

s

2020-01-01 00:00:00    10
2020-01-01 01:00:00    20
2020-01-01 02:00:00    30
2020-01-02 00:00:00    40
2020-01-02 01:00:00    50
2020-01-02 02:00:00    60
dtype: int64

Code

s.groupby([s.index.date, s.index.hour]).sum().unstack()

output:

            0   1   2
2020-01-01  10  20  30
2020-01-02  40  50  60