CODEWITHSUNDEEP
pythonpandas
zesla
zesla

Reputation: 11793

Fill a column at missing year/quarter with 0 in pandas dataframe

I have a dataframe like below. value is missing for some year_Quarter.

import pandas as pd
pd.DataFrame({'Year_Quarter':['2017_Q1', '2017_Q3', '2017_Q4',
                             '2018_Q1', '2018_Q2', '2018_Q4'],
              'Value': [12, 14, 2, 44, 5, 33]})

Year_Quarter Value
0   2017_Q1   12
1   2017_Q3   14
2   2017_Q4   2
3   2018_Q1   44
4   2018_Q2   5
5   2018_Q4   33

what I need is a dataframe that has those missing Year_Quarter filled with 0, like below:

pd.DataFrame({'Year_Quarter':['2017_Q1', '2017_Q2','2017_Q3', '2017_Q4',
                             '2018_Q1', '2018_Q2', '2018_Q3','2018_Q4'],
              'Value': [12, 0,14, 2, 44, 5, 0, 33]})


Year_Quarter Value
0   2017_Q1   12
1   2017_Q2   0
2   2017_Q3   14
3   2017_Q4   2
4   2018_Q1   44
5   2018_Q2   5
6   2018_Q3   0
7   2018_Q4   33

Does anyone know how to do that? Thanks a lot.

Upvotes: 1

Views: 532

Answers (2)

piRSquared
piRSquared

Reputation: 294248

Munge df to make Year_Quarter into periods

df = df.assign(
    Year_Quarter=
    df.Year_Quarter.map(lambda x: pd.Period(x.replace('_', ''), 'Q'))
).set_index('Year_Quarter')

Create an index that is a range of periods

idx = pd.period_range(df.index.min(), df.index.max(), freq='Q', name=df.index.name)

Then use reindex

df.reindex(idx, fill_value=0)

              Value
Year_Quarter       
2017Q1           12
2017Q2            0
2017Q3           14
2017Q4            2
2018Q1           44
2018Q2            5
2018Q3            0
2018Q4           33

Upvotes: 3

abhilb
abhilb

Reputation: 5757

Define a second dataframe with all the needed ```Year_Quarter`` values like this

df2 = pd.DataFrame({'Year_Quarter':['2017_Q1', '2017_Q2','2017_Q3', '2017_Q4',
                             '2018_Q1', '2018_Q2', '2018_Q3','2018_Q4']})

Then merge the two data frames and fill NaN values with zero

df1.merge(df2, how='outer').fillna(0)

Upvotes: 2

Related Questions