CODEWITHSUNDEEP
pandas
diogenes
diogenes

Reputation: 2149

Pandas DF will in for Missing Months

I have a dataframe of values that are mostly (but not always) quarterly values.

I need to fill in for any missing months so it is complete.

Here i need to put it into a complete df from 2015-12 to 2021-03.

Thank you.

    id      date        amt     rate
0   15856   2015-12-31  85.09   0.0175
1   15857   2016-03-31  135.60  0.0175
2   15858   2016-06-30  135.91  0.0175
3   15859   2016-09-30  167.27  0.0175
4   15860   2016-12-31  173.32  0.0175
....
19  15875   2020-09-30  305.03  0.0175
20  15876   2020-12-31  354.09  0.0175
21  15877   2021-03-31  391.19  0.0175

Upvotes: 1

Views: 34

Answers (1)

Ynjxsjmh
Ynjxsjmh

Reputation: 30070

You can use pd.date_range() to generate a list of months end with freq='M' then reindex datetime index.

df_ = df.set_index('date').reindex(pd.date_range('2015-12', '2021-03', freq='M')).reset_index().rename(columns={'index': 'date'})

print(df_)

         date       id     amt    rate
0  2015-12-31  15856.0   85.09  0.0175
1  2016-01-31      NaN     NaN     NaN
2  2016-02-29      NaN     NaN     NaN
3  2016-03-31  15857.0  135.60  0.0175
4  2016-04-30      NaN     NaN     NaN
..        ...      ...     ...     ...
58 2020-10-31      NaN     NaN     NaN
59 2020-11-30      NaN     NaN     NaN
60 2020-12-31  15876.0  354.09  0.0175
61 2021-01-31      NaN     NaN     NaN
62 2021-02-28      NaN     NaN     NaN

To fill the NaN value, you can use df_.fillna(0).

Upvotes: 1

Related Questions