pandas dataframe interpolate for Nans with groupby using window of discrete days of the year

Question

The small reproducible example below sets up a dataframe that is 100 yrs in length containing some randomly generated values. It then inserts 3 100-day stretches of missing values. Using this small example, I am attempting to sort out the pandas commands that will fill in the missing days using average values for that day of the year (hence the use of .groupby) with a condition. For example, if April 12th is missing, how can the last line of code be altered such that only the 10 nearest April 12th's are used to fill in the missing value? In other words, a missing April 12th value in 1920 would be filled in using the mean April 12th values between 1915 to 1925; a missing April 12th value in 2000 would be filled in with the mean April 12th values between 1995 to 2005, etc. I tried playing around with adding a .rolling() to the lambda function in last line of script, but was unsuccessful in my attempt.

Bonus question: The example below extends from 1918 to 2018. If a value is missing on April 12th 1919, for example, it would still be nice if ten April 12ths were used to fill in the missing value even though the window couldn't be 'centered' on the missing day because of its proximity to the beginning of the time series. Is there a solution to the first question above that would be flexible enough to still use a minimum of 10 values when missing values are close to the beginning and ending of the time series?

import pandas as pd
import numpy as np
import random

# create 100 yr time series
dates = pd.date_range(start="1918-01-01", end="2018-12-31").strftime("%Y-%m-%d")
vals = [random.randrange(1, 50, 1) for i in range(len(dates))]
# Create some arbitrary gaps
vals[100:200] = vals[9962:10062] = vals[35895:35995] = [np.nan] * 100

# Create dataframe
df = pd.DataFrame(dict(                                                   
    list(                                                                 
        zip(["Date", "vals"],                                              
            [dates, vals])                                 
        )                                                                 
))  

# confirm missing vals
df.iloc[95:105]
df.iloc[35890:35900]

# set a date index (for use by groupby)
df.index = pd.DatetimeIndex(df['Date'])
df['Date'] = df.index

# Need help restricting the mean to the 10 nearest same-days-of-the-year:
df['vals'] = df.groupby([df.index.month, df.index.day])['vals'].transform(lambda x: x.fillna(x.mean()))

Rob Raymond · Accepted Answer

This answers both parts

build a DF dfr that is the calculation you want
lambda function returns a dict {year:val, ...}
make sure indexes are named in reasonable way
expand out dict with apply(pd.Series)
reshape by putting year columns back into index
merge() built DF with original DF. vals column contains NaN 0 column is value to fill
finally fillna()

# create 100 yr time series
dates = pd.date_range(start="1918-01-01", end="2018-12-31")
vals = [random.randrange(1, 50, 1) for i in range(len(dates))]
# Create some arbitrary gaps
vals[100:200] = vals[9962:10062] = vals[35895:35995] = [np.nan] * 100

# Create dataframe - simplified from question...
df = pd.DataFrame({"Date":dates,"vals":vals})  

df[df.isna().any(axis=1)]

ystart = df.Date.dt.year.min()
# generate rolling means for month/day.  bfill for when it's start of series
dfr = (df.groupby([df.Date.dt.month, df.Date.dt.day])["vals"]
 .agg(lambda s: {y+ystart:v for y,v in enumerate(s.dropna().rolling(5).mean().bfill())})
 .to_frame().rename_axis(["month","day"])
)

# expand dict into columns and reshape to by indexed by month,day,year
dfr = dfr.join(dfr.vals.apply(pd.Series)).drop(columns="vals").rename_axis("year",axis=1).stack().to_frame()

# get df index back, plus vals & fillna (column 0) can be seen alongside each other
dfm = df.merge(dfr, left_on=[df.Date.dt.month,df.Date.dt.day,df.Date.dt.year], right_index=True)

# finally what we really want to do - fill tha NaNs
df.fillna(dfm[0])

analysis

taking NaN for 11-Apr-1918, default is 22 as it's backfilled from 1921
(12+2+47+47+2)/5 == 22

dfm.query("key_0==4 & key_1==11").head(7)

	key_0	key_1	key_2	Date	vals	0
100	4	11	1918	1918-04-11 00:00:00	nan	22
465	4	11	1919	1919-04-11 00:00:00	12	22
831	4	11	1920	1920-04-11 00:00:00	2	22
1196	4	11	1921	1921-04-11 00:00:00	47	27
1561	4	11	1922	1922-04-11 00:00:00	47	36
1926	4	11	1923	1923-04-11 00:00:00	2	34.6
2292	4	11	1924	1924-04-11 00:00:00	37	29.4

pandas dataframe interpolate for Nans with groupby using window of discrete days of the year

Answers (2)

analysis

Related Questions