CODEWITHSUNDEEP
pythonpandasdatetime
Angelo
Angelo

Reputation: 655

Hour Series to Matrix in Pandas

I want to convert an hourly Pandas Series into a DataFrame as a DataFrame indexed only with the date and each hour as a column.

For example, let's say I have this Series:

YEAR = 2017
serie = pd.Series(pd.date_range(
        start=f'{YEAR}-01-01', end=f'{YEAR}-12-31 23:00:00', freq='H'))

But I want it like:

                             h01      h02      h03     h04     h05  ...
Date
2017-01-01                   data    data     data     data   data  ...

Upvotes: 1

Views: 166

Answers (1)

jezrael
jezrael

Reputation: 862481

I believe your Series is with DatetimeIndex and filled some data.

Then need DataFrame.pivot with DataFrame.assign for new columns created by DatetimeIndex.date and DatetimeIndex.strftime and Series.to_frame for one columns DataFrame:

YEAR = 2017
serie = pd.Series(np.arange(8760), pd.date_range(
        start=f'{YEAR}-01-01', end=f'{YEAR}-12-31 23:00:00', freq='H'))

df = serie.to_frame('vals').assign(date = lambda x: x.index.date,
                                   hour = lambda x: x.index.strftime('h%H'))
#print (df)


df1 = df.pivot('date','hour','vals')
#print (df1)

Another solution:

serie.index = [serie.index.date, serie.index.strftime('h%H')]
df1 = serie.unstack()

Upvotes: 2

Related Questions