Iterate and assign weights based on two columns (python)

FI_name ISN Sector Industry
REC INE02 PS FS
HDB INE03 PR FS
ABC INE04 PR FS
RHC INE05 PR CO
ZHE INE06 PR FS
HSE INE07 PR FS
ZAK INE08 PS MT
HGB INE09 PR FS
YUJ INE10 PR MT
WSD INE11 PS FS
REC INE12 PS FS
HDB INE13 PR FS
ABC INE14 PR FS
RHC INE15 PR CO
ZHE INE16 PR FS
HSE INE17 PR FS
ZAK INE18 PS MT
HGB INE19 PR FS
YUJ INE20 PR MT
WSD INE21 PS FS

All the unique ISN should be assigned an equal weight (totals 100) but with the following exceptions.

1. Each unique Industry which has sector type "PR" is capped at 25%
2. So any ISN with sector 'PR' for their entire Industry should not cross the 25% limit.
3. If any industry has breached the 25% limit (i.e., if total number of ISNs in any industry is more than 5) then all those ISNs in that particular industry should be adjusted between the 25%
4. No limit for ISNs with sector == 'PS' (irrespective of the Industry)

the expected weights should be like this....

FI_name ISN Sector Industry Weights
REC INE02 PS FS 7.5%
HDB INE03 PR FS 2.5%
ABC INE04 PR FS 2.5%
RHC INE05 PR CO 7.5%
ZHE INE06 PR FS 2.5%
HSE INE07 PR FS 2.5%
ZAK INE08 PS MT 7.5%
HGB INE09 PR FS 2.5%
YUJ INE10 PR MT 7.5%
WSD INE11 PS FS 7.5%
REC INE12 PS FS 7.5%
HDB INE13 PR FS 2.5%
ABC INE14 PR FS 2.5%
RHC INE15 PR CO 7.5%
ZHE INE16 PR FS 2.5%
HSE INE17 PR FS 2.5%
ZAK INE18 PS MT 7.5%
HGB INE19 PR FS 2.5%
YUJ INE20 PR MT 7.5%
WSD INE21 PS FS 7.5%

There are total 10 ISNs with Sector == 'PR' and Industry == 'FS', so all these ISNs are assigned an equal weight of 2.5% (25%/10) Since industries apart from FS (and sector 'PR') do not breach the limit of 25%, so 7.5% (75%/10) has been assigned for the rest.

This is the current code but I believe there's a better approach. Is there any other approach to tackle the above condition? any shorter method?

# Sector weight identification
import pandas as pd

swi = sff1.loc[sff1['Sector'] != "PS"]
swi_pivot = swi.pivot_table(values=['ISN'], index = 'Industry', aggfunc= ['count'])
swi_pivot.columns = ['count',]
swi = pd.merge(swi, swi_pivot, how='inner', on='Industry')
swi2 = pd.merge(sff1, swi, how='left', left_on=['ISN', 'FI_name', 'Sector', 'Industry'], right_on=['ISN', 'FI_name', 'Sector', 'Industry'])

# Sector weight allocation
o = 20
l = 0.25
n = o*l
c= swi2['count']
n1 = c[c > n].count()
n2 = o-n1
swi3 = swi2.loc[swi2['count'] > n]
swi3['Weights'] = l/swi2['count']
s_sum = swi3['Weights'].sum()
l1 = 1-s_sum
swif = pd.merge(swi2, swi3, how='left', left_on=['ISN', 'FI_name', 'Sector', 'Industry', 'count'], right_on=['ISN', 'FI_name', 'Sector', 'Industry', 'count'])
swif = swif.set_index('ISN')
swi4 = swif[swif['Weights'].isna()]
swi4['Weights'] = l1/n2
swif = swif.reindex(columns=swif.columns.union(swi4.columns))
swif.update(swi4)
swif.reset_index(inplace=True)
final = swif.drop(['count'], axis=1)
final.to_excel('Test_CodeR_Final.xlsx')

• Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.
– Community Bot
Jul 19, 2022 at 12:11

Your use of pivot_table, reindex and merge is in all cases unnecessary. I also find that you hold onto a lot of temporary values, none very well-named, when you can just reassign to older variables and allow Python to do cleanup based on dropped references.

Rather than c[c > n].count(), you can call .sum() directly on the boolean predicate.

There's probably more simplification possible, but the results from this pass match yours.

import pandas as pd

# Sector weight allocation
o = len(sff1)
l = 0.25
n = o*l

is_not_ps = sff1.Sector != "PS"
sff1.loc[is_not_ps, 'count'] = (
sff1[is_not_ps]
.groupby('Industry')
.ISN.transform('count')
)

n1 = (sff1['count'] > n).sum()
n2 = o - n1

swi3 = sff1[sff1['count'] > n]
swi3['Weights'] = l/sff1['count']
l1 = 1 - swi3.Weights.sum()
sff1['Weights'] = swi3.Weights
sff1 = sff1.set_index('ISN')
swi4 = sff1[sff1.Weights.isna()]
swi4['Weights'] = l1 / n2
sff1.update(swi4)
final = sff1.drop(['count'], axis=1)