CODEWITHSUNDEEP
pythonpandasdatetimedataframe
currentlyunknown
currentlyunknown

Reputation: 169

How to insert rows with 0 data for missing quarters into a pandas dataframe?

I have a dataframe with specific Quota values for given quarters (YYYY-Qx format), and need to visualize them with some linecharts. However, some of the quarters are missing (as there was no Quota during those quarters).

Period      Quota
2017-Q1     500
2017-Q3     600
2018-Q2     700

I want to add them (starting at 2017-Q1 until today, so 2019-Q2) to the dataframe with a default value of 0 in the Quota column. A desired output would be the following:

Period      Quota
2017-Q1     500
2017-Q2     0
2017-Q3     600
2017-Q4     0
2018-Q1     0
2018-Q2     700
2018-Q3     0
2018-Q4     0
2019-Q1     0
2019-Q2     0

I tried

df['Period'] = pd.to_datetime(df['Period']).dt.to_period('Q')

And then resampling the df with 'Q' frequency, but I must be doing something wrong, as it doesn't help with anything.

Any help would be much appreciated.

Upvotes: 1

Views: 617

Answers (2)

vrana95
vrana95

Reputation: 521

#An alternate solution based on left join 

qtr=['Q1','Q2','Q3','Q4']
finl=[]
for i in range(2017,2020):
    for j in qtr:
        finl.append((str(i)+'_'+j))

df1=pd.DataFrame({'year_qtr':finl}).reset_index(drop=True)
df1.head(2)

original_value=['2017_Q1' ,'2017_Q3' ,'2018_Q2']
df_original=pd.DataFrame({'year_qtr':original_value,
                        'value':[500,600,700]}).reset_index(drop=True)

final=pd.merge(df1,df_original,how='left',left_on=['year_qtr'], right_on =['year_qtr'])
final.fillna(0)

Output

year_qtr    value
0   2017_Q1 500.0
1   2017_Q2 0.0
2   2017_Q3 600.0
3   2017_Q4 0.0
4   2018_Q1 0.0
5   2018_Q2 700.0
6   2018_Q3 0.0
7   2018_Q4 0.0
8   2019_Q1 0.0
9   2019_Q2 0.0
10  2019_Q3 0.0
11  2019_Q4 0.0

Upvotes: 0

jezrael
jezrael

Reputation: 863611

Use:

df.index = pd.to_datetime(df['Period']).dt.to_period('Q')
end = pd.Period(pd.datetime.now(), freq='Q')

df = (df['Quota'].reindex(pd.period_range(df.index.min(), end), fill_value=0)
                 .rename_axis('Period')
                 .reset_index()
                )
df['Period'] = df['Period'].dt.strftime('%Y-Q%q')
print (df)
    Period  Quota
0  2017-Q1    500
1  2017-Q2      0
2  2017-Q3    600
3  2017-Q4      0
4  2018-Q1      0
5  2018-Q2    700
6  2018-Q3      0
7  2018-Q4      0
8  2019-Q1      0
9  2019-Q2      0

Upvotes: 1

Related Questions