CODEWITHSUNDEEP
pandaspandas-groupby
Danish
Danish

Reputation: 2871

GroupBy aggregation based on condition and year wise sum using pandas

I have a data frame as shown below

ID    Sector  Plot  Tenancy_Start_Date      Rental
1     SE1     A     2018-08-14              100
1     SE1     A     2019-08-18              200
2     SE1     B     2017-08-12              150
3     SE1     A     2020-02-12              300
5     SE2     A     2017-08-13              400
5     SE2     A     2019-08-12              300
6     SE2     B     2019-08-11              150
5     SE2     A     2020-01-10              300
7     SE2     B     2019-08-11              500

From the above I would like to prepare below data frame as Sector and Plot aggregation level.

Expected Output:

Sector  Plot       Total_Rental   Rental_2017  Rental_2018  Rental_2019    Rental_2020
SE1     A          600            0            100          200            300
SE1     B          150            150          0            0              0
SE2     A          1000           400          0            300            300
SE2     B          650            0            0            650            0

Upvotes: 0

Views: 42

Answers (2)

Georgina Skibinski
Georgina Skibinski

Reputation: 13387

You can do (df being your input dataframe):

#in case if it's not already a datetime:
df["Tenancy_Start_Date"]=pd.to_datetime(df["Tenancy_Start_Date"])

df2=df.pivot_table(index=["Sector", "Plot"], columns=df["Tenancy_Start_Date"].dt.year, values="Rental", aggfunc=sum).fillna(0)

df2.columns=[f"Rental_{col}" for col in df2.columns]

df2["Total_Rental"]=df2.sum(axis=1)

df2=df2.reset_index(drop=False)

Outputs:

  Sector Plot  ...  Rental_2020  Total_Rental
0    SE1    A  ...        300.0         600.0
1    SE1    B  ...          0.0         150.0
2    SE2    A  ...        300.0        1000.0
3    SE2    B  ...          0.0         650.0

Upvotes: 1

Tom
Tom

Reputation: 1063

I'd create a year column:

df['Year'] = df['Tenancy_State_date'].dt.year

then do your groupby

df['Rent_by_cats'] = df.groupby(['Sector', 'Year', 'Plot'])['Rental'].transform(sum)

then lastly move it into separate columns

yrs = df['Year'].unique().tolist()
for y in yrs:
    df['Rental_' + str(y)] = 0
    df.loc[df['Year'] == y, 'Rental_' + str(y)] = df['Rent_by_cats']

Output:

    ID Sector Plot Tenancy_Start_Date  Rental  Year  Rent_by_cats  Rental_2018  Rental_2019  Rental_2017  Rental_2020
0   1    SE1    A         2018-08-14     100  2018           100          100            0            0            0
1   1    SE1    A         2019-08-18     200  2019           200            0          200            0            0
2   2    SE1    B         2017-08-12     150  2017           150            0            0          150            0
3   3    SE1    A         2020-02-12     300  2020           300            0            0            0          300
4   5    SE2    A         2017-08-13     400  2017           400            0            0          400            0
5   5    SE2    A         2019-08-12     300  2019           300            0          300            0            0
6   6    SE2    B         2019-08-11     150  2019           650            0          650            0            0
7   5    SE2    A         2020-01-10     300  2020           300            0            0            0          300
8   7    SE2    B         2019-08-11     500  2019           650            0          650            0            0

Upvotes: 1

Related Questions