Pandas - fill datafram with date range

user_5649057 0
Viewed 215

Assuming this is my df:

  Name1    Name2       date 
1 John     Jay   2015-01-01 06:01:00
2 Sara     Debra 2015-01-01 06:05:00
3 Ben      Beth  2015-01-01 06:09:00

I want to fill in the df where each row should be duplicated and incremented by 1 minute until it reaches to the next line, so the output should be: 

  Name1    Name2       date 
1 John     Jay   2015-01-01 06:01:00
1 John     Jay   2015-01-01 06:02:00
1 John     Jay   2015-01-01 06:03:00
1 John     Jay   2015-01-01 06:04:00
2 Sara     Debra 2015-01-01 06:05:00
2 Sara     Debra 2015-01-01 06:06:00
3 Ben      Beth  2015-01-01 06:07:00

I looked into date_range, but I didn't find a proper way to do this without an ugly and inefficient for loop.

Any help would be appreciated!

pandas datetime python
2 Answers

If all datetimes are unique you can use DataFrame.asfreq:

df['date'] = pd.to_datetime(df['date'])

df1 = (df.set_index('date')
         .asfreq('Min', method='ffill')
         .reset_index().reindex(df.columns, axis=1))

For me solution with DataFrame.resample working with DatetimeIndex and Resampler.ffill:

df1 = df.set_index('date').resample('1Min').ffill().reset_index().reindex(df.columns, axis=1)

print (df1)
  Name1  Name2                date
0  John    Jay 2015-01-01 06:01:00
1  John    Jay 2015-01-01 06:02:00
2  John    Jay 2015-01-01 06:03:00
3  John    Jay 2015-01-01 06:04:00
4  Sara  Debra 2015-01-01 06:05:00
5  Sara  Debra 2015-01-01 06:06:00
6  Sara  Debra 2015-01-01 06:07:00
7  Sara  Debra 2015-01-01 06:08:00
8   Ben   Beth 2015-01-01 06:09:00
user_29010020

Try this:

df.resample('60S', on='date').pad()

It's a simple case of upsampling followed by pad() to fill forward the missing values.

John_Zwinck_43230
Related