CODEWITHSUNDEEP
pandaspandas-groupby
postcolonialist
postcolonialist

Reputation: 661

Groupby and Divide One Group of Rows by Another Group

I have a dataframe:

df = pd.DataFrame({ 
    'Metric': ['Total Assets', 'Total Promo', 'Total Assets', 'Total Promo'],
    'Product': ['AA', 'AA', 'BB', 'BB'],
    'Risk': ['High', 'High','Low', 'Low'],
    '202101': [ 200, 100, 400, 100], 
    '202102': [ 200, 100, 400, 100],
    '202103': [ 200, 100, 400, 100]})

I wish to groupby Product and Risk and divide rows with Total Assets with Total Promo. I would the output to be like this:

df = pd.DataFrame({ 
    'Product': ['AA', 'BB'],
    'Risk': ['High', 'Low',],
    '202101': [ 2, 4], 
    '202102': [ 2, 4],
    '202103': [ 2, 4]})

So far my approach has been to try and first melt into long form. But I can't seem to get Total Assets and Total Promo to columns to be able to divide columns

df = pd.melt(df, id_vars=['Metric', 'Product', 'Risk'], 
        value_vars = ["202101", "202102", "202103"],
        var_name='Months', value_name='Balance')

Upvotes: 3

Views: 1069

Answers (2)

sammywemmy
sammywemmy

Reputation: 28729

Since there are only two rows per grouping and they are ordered, a groupby with the relevant columns, combined with pipe should suffice:

(df.iloc[:, 1:]
   .groupby(['Product', 'Risk'])
   .pipe(lambda df: df.first()/df.last())
)

              202101  202102  202103
Product Risk                        
AA      High     2.0     2.0     2.0
BB      Low      4.0     4.0     4.0

Upvotes: 0

Nk03
Nk03

Reputation: 14949

Here's one way:

df1 = df.set_index(['Metric', 'Product', 'Risk']).stack().unstack(0)
df = (df1['Total Assets'] / df1['Total Promo']).unstack(-1).reset_index()

OUTPUT:

  Product  Risk  202101  202102  202103
0      AA  High     2.0     2.0     2.0
1      BB   Low     4.0     4.0     4.0

Upvotes: 4

Related Questions