Here is a dummy example to illustrate the problem.
I am interested in upsampling to the beginning of the each year (AS) and, for every country, I want to cover the period that goes from 1995 to the year 2000.
Imagine we had the following dataset:
df = pd.DataFrame({
'year': [
'1995-01-01', '1997-01-01',
'1997-01-01', '1998-01-01', '2000-01-01',
'1996-01-01', '1999-01-01',
],
'country': [
'ES', 'ES',
'GB', 'GB', 'GB',
'DE', 'DE',
],
'members': [
100, 101,
200, 201, 202,
300, 301,
]
})
df['year']= pd.to_datetime(df['year'])
df = df.set_index(['country', 'year'])
print(df)
members
country year
ES 1995-01-01 100
1997-01-01 101
GB 1997-01-01 200
1998-01-01 201
2000-01-01 202
DE 1996-01-01 300
1999-01-01 301
As you can see, no country has data available for all the years between 1995 to 2000. Notice that some countries are also missing the year 1995 and other are missing the year 2000.
I know how to upsample the dataframe so that, for each country, it fills the years missing in between (e.g. adds the year 1996 to Spain).
def my_upsample(df):
return (
df
.reset_index('country') # upsampling multi-index wit the keyword level is not supported
.groupby('country', group_keys=False) # hence this little trick of single-indexing & grouping
# see this issue for details: https://github.com/pandas-dev/pandas/issues/28313
.resample('AS') # resample to the beggining of each year
.apply({
'country':'pad', # pad the countries
'members':'asfreq', # but leave the number of members as NaN. Irrelevant in this dummy example, but
# the desired behaviour in my real-world problem
})
)
print(my_upsample(df))
country members
year
1996-01-01 DE 300.0
1997-01-01 DE NaN
1998-01-01 DE NaN
1999-01-01 DE 301.0
1995-01-01 ES 100.0
1996-01-01 ES NaN
1997-01-01 ES 101.0
1997-01-01 GB 200.0
1998-01-01 GB 201.0
1999-01-01 GB NaN
2000-01-01 GB 202.0
But what I would like to do is make sure that all the countries cover the period from 1995 to 2000.
The desired output should look like this:
country members
year
1995-01-01 DE NaN
1996-01-01 DE 300.0
1997-01-01 DE NaN
1998-01-01 DE NaN
1999-01-01 DE 301.0
2000-01-01 DE NaN
1995-01-01 ES 100.0
1996-01-01 ES NaN
1997-01-01 ES 101.0
1998-01-01 ES NaN
1999-01-01 ES NaN
2000-01-01 ES NaN
1995-01-01 GB NaN
1996-01-01 GB NaN
1997-01-01 GB 200.0
1998-01-01 GB 201.0
1999-01-01 GB NaN
2000-01-01 GB 202.0
I could use python loops iterating over each country and adding the missing rows (see code below), but I would like to know what is the pandas way of achieving this?
for country in df.index.levels[0]:
if not (country, '1995-01-01') in df.query(f"country == @country").index:
# if this country doesn't have the year 1995 create the row with NaN as value
df.loc[(country, '1995-01-01'),:] = np.nan
if not (country, '2000-01-01') in df.query(f"country == @country").index:
# if this country doesn't have the year 2000 create the row with NaN as value
df.loc[(country, '2000-01-01'),:] = np.nan
print(df.sort_index())
members
country year
DE 1995-01-01 NaN
1996-01-01 300.0
1999-01-01 301.0
2000-01-01 NaN
ES 1995-01-01 100.0
1997-01-01 101.0
2000-01-01 NaN
GB 1995-01-01 NaN
1997-01-01 200.0
1998-01-01 201.0
2000-01-01 202.0
And then running my_upsample returns the desired output:
print(my_upsample(df.sort_index()))
country members
year
1995-01-01 DE NaN
1996-01-01 DE 300.0
1997-01-01 DE NaN
1998-01-01 DE NaN
1999-01-01 DE 301.0
2000-01-01 DE NaN
1995-01-01 ES 100.0
1996-01-01 ES NaN
1997-01-01 ES 101.0
1998-01-01 ES NaN
1999-01-01 ES NaN
2000-01-01 ES NaN
1995-01-01 GB NaN
1996-01-01 GB NaN
1997-01-01 GB 200.0
1998-01-01 GB 201.0
1999-01-01 GB NaN
2000-01-01 GB 202.0