CODEWITHSUNDEEP
pythonpandas
James Flash
James Flash

Reputation: 548

Pandas: time column addition and repeating all rows for a month

I'd like to change my dataframe adding time intervals for every hour during a month

Original df

  money food
0   1   2
1   4   5
2   5   7

Output:

  money food time 
0   1   2    2020-01-01 00:00:00
1   1   2    2020-01-01 00:01:00
2   1   2    2020-01-01 00:02:00
...
2230    5   7    2020-01-31 00:22:00
2231    5   7    2020-01-31 00:23:00

where 2231 = out_rows_number-1 = month_days_number*hours_per_day*orig_rows_number - 1

What is the proper way to perform it?

Upvotes: 1

Views: 238

Answers (1)

jezrael
jezrael

Reputation: 863146

Use cross join by DataFrame.merge and new DataFrame with all hours per month created by date_range:

df1 = pd.DataFrame({'a':1, 
                    'time':pd.date_range('2020-01-01', '2020-01-31 23:00:00', freq='h')})
df = df.assign(a=1).merge(df1, on='a', how='outer').drop('a', axis=1)
print (df)
      money  food                time
0         1     2 2020-01-01 00:00:00
1         1     2 2020-01-01 01:00:00
2         1     2 2020-01-01 02:00:00
3         1     2 2020-01-01 03:00:00
4         1     2 2020-01-01 04:00:00
    ...   ...                 ...
2227      5     7 2020-01-31 19:00:00
2228      5     7 2020-01-31 20:00:00
2229      5     7 2020-01-31 21:00:00
2230      5     7 2020-01-31 22:00:00
2231      5     7 2020-01-31 23:00:00

[2232 rows x 3 columns]

Upvotes: 2

Related Questions