Python Pandas forward fill missing data in specific time range

Question

I have a pandas dataframe that looks like this:

As you can see - in the datetime index, there are certain minutes missing. For example, in the screenshot, minutes 9:16:00 - 9:19:00 are missing between the first & second row. I want to forward fill the data from the previous minute to all the missing minutes.

Now, we reach the part where it gets complicated - and the part I need help with. I need to only forward fill minutes between 09:15:00 and 15:30:00 on each date. And, for any row that is forward filled, the column Volume should have a value of 0

To help you explore the data, I've exported the first few rows to a json object (I think the datetime index got converted to milliseconds)

    {
  "1580464080000": {
    "expiry": "4/30/2020",
    "close": 12157.3,
    "high": 12157.3,
    "volume": 0,

    "open": 12157.3,
    "low": 12157.3,
    "timezone": "+05:30"
  },
  "1580463120000": {
    "expiry": "4/30/2020",
    "close": 12200.3,
    "high": 12200.3,
    "volume": 0,
    "open": 12200.3,
    "low": 12200.3,
    "timezone": "+05:30"
  },
  "1580464260000": {
    "expiry": "4/30/2020",
    "close": 12150.0,
    "high": 12150.0,

    "volume": 0,
    "open": 12150.0,
    "low": 12150.0,
    "timezone": "+05:30"
  },
  "1580462400000": {
    "expiry": "4/30/2020",
    "close": 12174.0,
    "high": 12174.0,
    "volume": 0,
    "open": 12174.0,
    "low": 12174.0,
    "timezone": "+05:30"
  },
  "1580462820000": {
    "expiry": "4/30/2020",
    "close": 12193.7,
    "high": 12193.7,
    "volume": 0,
    "open": 12193.7,
    "low": 12193.7,
    "timezone": "+05:30"
  },
  "1580462100000": {
    "expiry": "4/30/2020",
    "close": 12180.0,
    "high": 12180.0,
    "volume": 0,
    "open": 12180.0,
    "low": 12180.0,
    "timezone": "+05:30"
  },
  "1580464440000": {
    "expiry": "4/30/2020",
    "close": 12160.45,
    "high": 12160.45,
    "volume": 0,
    "open": 12160.45,
    "low": 12160.45,
    "timezone": "+05:30"
  }
}

user_9576116 · Answer

I suggest you use the pandas resample method. It resamples the data frame to a specified format. The steps are:

Resample using pandas resample method. '1T' is for minutes. You can see other frequencies here: https://pandas.pydata.org/pandas-docs/stable/user_guide/timeseries.html#timeseries-offset-aliases
Then remove times not need, i.e. outside 9:15 to 15:30 using between_time.
Afterwards fill in NA for 'volume' with 0 and forwardfill the remaining columns.
Fill forward the remaining columns

Here is a sample code:

# Get unique dates from the data frame
df['Date'] = df.index.date
sample_days = df['Date'].unique()

# Resample to 1 minute and keep only the original dates
df = df.resample('1t').last()
df = df.loc[df['Date'].isin(sample_days)]

# Remove non open hours
df = df.between_time('09:15', '15:30')

# Fill 0 in Na for volume
df['volume'] = df['volume'].fillna(0)

# Forward fill the remaining columns (notice, as NAs in volume are removed, it does effect this column)
df = df.fillna(method='ffill')

Python Pandas forward fill missing data in specific time range

1 Answers