How to create rows for missing years and quarters in pandas dataframe

Question

I have a dataframe in pandas like:

year quarter value
2018  3     100
2018  4     200
2019  2     300
2019  3     380
2020  1     400

In this I want years 2019-1,2019-4 are missing. I want to create and fill these values with previous row values. So that the output dataframe becomes continuous till current year-quarter which is 2020-3:

year quarter value
2018  3     100
2018  4     200
2019  1     300
2019  2     300
2019  3     380
2019  4     380
2020  1     400
2020  2     400
2020  3     400

How do you fill those missing quarters? – Bruno Mello Aug 31 '20 at 11:40 — Bruno Mello, Aug 31 '20 at 11:40
with last quarter's value – richa verma Aug 31 '20 at 11:44 — richa verma, Aug 31 '20 at 11:44

score 3 · Accepted Answer · answered Aug 31 '20 at 11:42

You can create quarter PeriodIndex by convert both columns to quarters periods and then use DataFrame.reindex by period_range, last assign back year and querter columns:

idx = pd.to_datetime(df['year'].astype(str) + 'q' + 
                     df['quarter'].astype(str)).dt.to_period('q')
per = pd.Timestamp('now').to_period('q')

df = (df.set_index(idx)
        .reindex(pd.period_range(idx.min(), per), method='ffill')
        .assign(year = lambda x: x.index.year,
                quarter = lambda x: x.index.quarter))
print (df)
        year  quarter  value
2018Q3  2018        3    100
2018Q4  2018        4    200
2019Q1  2019        1    200
2019Q2  2019        2    300
2019Q3  2019        3    380
2019Q4  2019        4    380
2020Q1  2020        1    400
2020Q2  2020        2    400
2020Q3  2020        3    400

I think the year and quarter will be created as new columns – richa verma Aug 31 '20 at 15:56 — richa verma, Aug 31 '20 at 15:56

How to create rows for missing years and quarters in pandas dataframe

1 Answers1