I have a time series dataframe with yearly temperature values for multiple cities, but for a few cities I have a set of dates missing
Dataframe Example
ID Date City PRCP TAVG TMAX TMIN
abcd1 2020-01-01 Zurich 0 -1.9 -0.9 -2.9
abcd1 2020-01-02 Zurich 9.1 8.8 12.7 4.9
abcd1 2020-01-03 Zurich 0.8 8.55 13.2 3.9
abcd1 2020-01-04 Zurich 0 4.1 10.8 -2.6
.
.
abcd9 2020-01-01 Singapore 4.1 5.9 0.3 3.1
abcd9 2020-01-04 Singapore 0.32 13.78 4.22 9
abcd9 2020-01-28 Singapore 9.42 11.32 5.34 8.33
...
Now suppose that a few dates are missing in every month, such that total count of days in Date column is 300 days. I want to add the rest of the days to dataset and assign nulls(NaN) to column TMAX,TMIN.. for such dates such that total count of days is 365.
This is what I am doing right now
df_list = []
for (columns, group) in df.groupby(['ID', 'City']):
idx = pd.MultiIndex.from_product([group['ID'].unique(),
pd.date_range(group['Date'].min().replace(day=1), end=group['Date'].max(), freq='D')],
names=['ID', 'Date'])
group = group.set_index(['ID', 'Date']).reindex(idx).reset_index()
group['City'] = group['City'].fillna(method='bfill')
df_list.append(group)
data = pd.concat(df_list, ignore_index=True)
What this does is fills dates between a start_index and an end_index(and fills NaN to PRCP, TMIN, TMAX, TAVG for those dates), but even with this I end up missing a few dates i.e. the count does not equal to 365.
e.g If the source file has data from 1st Oct to 28th Oct, the above will add any missing dates between this range but will not add 29,30,31 to dataset.
Is there a way I can impute all dates from 1 Jan to 31 Dec(year being irrelevant and ignoring leap years) for all cities in my City column.