Iterate and assign weights based on two columns (python)

Question

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
sff1 = pd.read_excel (r'C:\Users\RajashekarR\Downloads\Test_CodeR.xlsx')

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')

Answer 1

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

sff1 = pd.read_csv('Weights - Sheet1.csv')

# 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)