Pandas Dataframe fill minute missing datetime values series for big dataset

Farid Srouji 0
Viewed 43

How do I fill the missing values of datetime in minute frequency based on the last value For example between 2009-05-27 03:36:00-05:00 and 2009-05-27 03:41:00-05:00 for all values of the dataframe:

Date High Low Close
2009-05-27 03:36:00-05:00 32.20 32.20 32.20
2009-05-27 03:41:00-05:00 32.25 32.25 32.25

Need to be filled:

Date High Low Close
2009-05-27 03:36:00-05:00 32.25 32.20 32.25
2009-05-27 03:37:00-05:00 32.30 32.25 32.30
2009-05-27 03:38:00-05:00 32.30 32.25 32.30
2009-05-27 03:39:00-05:00 32.30 32.25 32.30
2009-05-27 03:40:00-05:00 32.30 32.25 32.30
2009-05-27 03:41:00-05:00 32.30 32.25 32.30
datetime pandas python-3.x python
1 Answers

Recreating your dataframe:

df = pd.DataFrame({'Date' : [datetime(2009, 5, 27, 3, 36, 0), datetime(2009, 5, 27, 3, 41)], 'High' : [32.20, 32.25], 'Low' : [32.20, 32.25], 'Close' : [32.20, 32.25]})

Set your date as index:

df.set_index('Date', inplace=True)
  1. Create a new dataframe that whose index goes from your earliest to your latest datetime
  2. Concatenate it with your original dataframe
  3. Sort by index (date)
  4. Backfill the missing values

e

df = pd.concat([df, pd.DataFrame(index = pd.date_range(df.index.min(), df.index.max(), freq='min', inclusive='neither'), columns = df.columns)]).sort_index().bfill()

For a more generic answer:

df = pd.DataFrame({'Date' : [datetime(2009, 5, 27, 3, 36, 0), datetime(2009, 5, 27, 3, 41)], 'High' : [32.20, 32.25], 'Low' : [32.20, 32.25], 'Close' : [32.20, 32.25]})

df = df.merge(pd.DataFrame({'Date' : pd.date_range(df.Date.min(), df.Date.max(), freq='min')}), on='Date', how='outer').sort_values('Date').bfill()
Amin Selim0
Related