[FIXED] Suggestions to convert Quarter results to Yearly results using Pandas

Issue

So i have the Quarterly data for Disney_Plus revenue from Q1 2020 to Q4 2021.

Desired Output of Disney_Plus_Revenue should include yearly results of 2020 and 2021. In addition to this it should also have 2010 to 2019 yearly results as None/NaN.

I initially changed the column Year to Quarter and later inserted a new column Year with 2020 and 2021 values and used groupby('Year).agg('revenue':['sum'])

But when i am trying to append 2010 to 2019 yearly revenues to this it is throwing me an error: Solution i tried

Disney_plus_Revenue = pd.read_csv("Disney_plus_Revenue.csv")


Disney_plus_Revenue.rename(columns = {'Year':'Quarter'},inplace = True)


Disney_plus_Revenue.insert(0,"Year",["2020","2020","2020","2020","2021","2021","2021","2021"],True)


Disney_plus_Revenue.rename(columns = {'Revenue':'Disney_Plus_Revenue'},inplace = True)


Disney_plus_Revenue = Disney_plus_Revenue.groupby('Year').agg({'Disney_Plus_Revenue': ['sum']})    



DS_new = pd.DataFrame(np.array([["2010",None],["2011",None],["2012",None],["2013",None],["2014",None],["2015",None],["2016",None],["2017",None],["2018",None],["2019",None]]), columns=['Year','Disney_Plus_Revenue']).append(Disney_plus_Revenue, ignore_index=True)

Error -

Solution

First of all, using .agg will give you a MultiIndex DataFrame. Since you're using only one aggregation function, maybe you should group that way:

agg = Disney_plus_Revenue.groupby("Year")["Revenues"].sum()

This will give you a Series:

Year
2020    2.802
2021    5.200
Name: Revenues, dtype: float64

Then you can create another Series with None values for each years:

indexes = np.arange(2010, 2020)
values = [None for x in indexes]
new_series = pd.Series(data=values, index=indexes, name="Revenues")

And finally, concat them:

pd.concat([new_series, agg])

2010     None
2011     None
2012     None
2013     None
2014     None
2015     None
2016     None
2017     None
2018     None
2019     None
2020    2.802
2021      5.2
Name: Revenues, dtype: object

Answered By - bhml