Search code examples
pythonpandasgroup-by

How can I do groubwise multiplication with two grouby terms?

I am having a DataFrame with a bunch of companies, each company is assigned to one of five groups (ESG Bottom, ESG 4, ESG 3, ESG 2 or ESG Top). enter image description here

Now I need to compute the weight of each company within its group in order to then compute their weighted group return and hence the overall group return.

I have managed to use groupby to get each groups summed market capital (MC) enter image description here and each companies individual MC. enter image description here

My question is, how can I now divide the companies' individual MC by its respective total group MC and save the resulting weight in the "Weight in Quintile PF" column?

I could somehow manage to do this with a clumsy and slow for-loop but there must be a more elegant one (or two) liner which divides the inividual company MC with the respective total group MC.

Solution

  • I figured out a way using a clumsy for loop using mask:

        for label in labels:
       mask = df_single_MC['Quintile PF']==label
       df_single_MC['Weight in Quintile PF'][mask] = df_single_MC['MC'][mask].values / quintile_MCs[label]

    It is not pretty but it solves my problem, the second part of this answer helped me.

    I still would be curious if there is a neater solution without a for loop using e.g. apply and or lambda since I still have not fully figured them out.