How to resample dates into 1 minute bars?

Question

Say I have a pandas.DataFrame like:

            val
2020-01-01   12
2020-04-15   38
2020-05-03   19

How can I create a pandas.DataFrame like:

                     val
2020-01-01 00:00:00   12
2020-01-01 00:01:00   12
...   
2020-01-01 23:58:00   12
2020-01-01 23:59:00   12
2020-04-15 00:00:00   38
2020-04-15 00:01:00   38
...   
2020-04-15 23:58:00   38
2020-04-15 23:59:00   38
2020-05-03 00:00:00   19
2020-05-03 00:01:00   19
...   
2020-05-03 23:58:00   19
2020-05-03 23:59:00   19

I have tried df.resample('1 min').asfreq() but that gives me all the minutes from the first row to the last row, including all the days that aren't in the original index.

score 0 · Answer 1 · answered Feb 04 '21 at 20:29

Recreating your sample df:

dates = [ pd.to_datetime('2020-01-01'), pd.to_datetime('2020-04-15'), pd.to_datetime('2020-05-03') ]
val = [12, 38, 19]

df = pd.DataFrame({ 'date' : dates, 'val' : val})
df = df.set_index('date')

I don't generally recommend loops, but this feels like it might be a case where it is more natural to use one. It really depends on how much data you're dealing with. It works, anyway. :)

out = pd.DataFrame()
for row in df.itertuples():
    bars = pd.date_range(row.Index, row.Index+pd.Timedelta(days=1), freq="T", closed='left')
    out = pd.concat([out, pd.DataFrame(data={'val' : row.val}, index=bars)])  

print(out)
                       val
2020-01-01 00:00:00   12
2020-01-01 00:01:00   12
2020-01-01 00:02:00   12
2020-01-01 00:03:00   12
2020-01-01 00:04:00   12
...                  ...
2020-05-03 23:55:00   19
2020-05-03 23:56:00   19
2020-05-03 23:57:00   19
2020-05-03 23:58:00   19
2020-05-03 23:59:00   19

[4320 rows x 1 columns]

How to resample dates into 1 minute bars?

1 Answers1