CODEWITHSUNDEEP
pythonpandasdataframedatetime
sowhatnowhuh
sowhatnowhuh

Reputation: 27

Pandas - create new columns from datetime index

I have the following series of hourly data:

timestamp
2021-01-04 00:00:00   32896.2200
2021-01-04 01:00:00   33229.4000
2021-01-04 02:00:00   33508.7800
2021-01-04 03:00:00   33611.6000
2021-01-04 04:00:00   33345.2500
                         ...    
2021-04-23 19:00:00   50963.6000
2021-04-23 20:00:00   50563.0000
2021-04-23 21:00:00   50361.3700
2021-04-23 22:00:00   50524.4600
2021-04-23 23:00:00   51187.2700

I want to create a dataframe which would have each week as a separate column with 168 (24 hours * 7 days a week) rows. What would be the most optimal way to do it?

Upvotes: 0

Views: 286

Answers (1)

RJ Adriaansen
RJ Adriaansen

Reputation: 9619

I suggest creating an extra column week with dt.isocalendar().week, and then creating a pivot table with weeks as columns. The only issue is how to handle the timestamps, as I can imagine you'd like to keep them as index. A solution would be to split the timestamps by dt.dayofweek and dt.hour and to use these as indexes:

import pandas as pd
data = [ { "timestamp": "2021-01-04 00:00:00", "value": 32896.22 }, { "timestamp": "2021-01-04 01:00:00", "value": 33229.4 }, { "timestamp": "2021-01-04 02:00:00", "value": 33508.78 }, { "timestamp": "2021-01-04 03:00:00", "value": 33611.6 }, { "timestamp": "2021-01-04 04:00:00", "value": 33345.25 }, { "timestamp": "2021-04-23 19:00:00", "value": 50963.6 }, { "timestamp": "2021-04-23 20:00:00", "value": 50563 }, { "timestamp": "2021-04-23 21:00:00", "value": 50361.37 }, { "timestamp": "2021-04-23 22:00:00", "value": 50524.46 }, { "timestamp": "2021-04-23 23:00:00", "value": 51187.27 } ]
df= pd.DataFrame(data)
df['timestamp'] = pd.to_datetime(df['timestamp'])
df['week'] = df['timestamp'].dt.isocalendar().week
df["day_of_week"] = df["timestamp"].dt.dayofweek
df["hour"] = df["timestamp"].dt.hour
final_df = pd.pivot_table(df, values='value', index=['day_of_week', 'hour'], columns=['week'])

This will result in:

1 16
(0, 0) 32896.2 nan
(0, 1) 33229.4 nan
(0, 2) 33508.8 nan
(0, 3) 33611.6 nan
(0, 4) 33345.2 nan
(4, 19) nan 50963.6
(4, 20) nan 50563
(4, 21) nan 50361.4
(4, 22) nan 50524.5
(4, 23) nan 51187.3

Upvotes: 1

Related Questions