<jupyter_start><jupyter_text>Import packages and dataset handling:<jupyter_code>import numpy as np
import pandas as pd
!pip install xlrd=='1.2.0'
data_df = pd.read_excel('Data_Temp.xls', engine='xlrd')
des_template_df = pd.read_excel('Desg_Temp.xls', engine='xlrd')
data_df, des_template_df
data = des_template_df.merge(data_df, on='fsu', how='left')
df = data.drop('strm_id_y', axis=1) 
df_new = df.rename(columns = {'strm_id_x':'strm_id', 'dsi':'capdsi'})
### Introducing missing columns with default value = 1
### smallzs(3) , no_ss(4), ss(5), smalldsi(8)

df_new['no_ss'] = int(input()) 
df_new['no_sss'] = int(input()) 

df_col = ["strm_id", "fsu", "capzs", "smallzs", "no_ss", "ss", "svc", "capdsi", "smalldsi", "no_sss"]

for col in df_col:
    if col not in df_new.columns:
        df_new[col] = '1'

print(df_new)<jupyter_output><empty_output><jupyter_text>Ns calculation<jupyter_code>subset_df_new1 = df_new

## Changing datatype of two columns in df_new
df_new['strm_id'] = df_new['strm_id'].astype(str)
df_new['no_sss'] = df_new['no_sss'].astype(str)

## Asking inputs from the user
casualty_codes = str(input())
no_sss_input = int(input()) 


########################################################
### Splitting values on the basis of ', '
casualty_codes = casualty_codes.split(',')

### Filling the modified elements with svc != in list casualty_codes
casualty_codes = [('svc != ' + i) for i in casualty_codes]

### Joining the list of casualty codes into a single string
casualty_codes = ' or '.join(casualty_codes)

casualty_codes = "(" + casualty_codes + ") "

# casualty_codes = "'" + casualty_codes + "'"
print(casualty_codes, type(casualty_codes))

#######################################################

## Creating a blank list to store the dataframes
d = pd.DataFrame()


for i in range(1, no_sss_input + 1):

    df_11 = subset_df_new1.query(casualty_codes + "and ((caph" + str(i) + ">" + str(0) + "and smlh" + str(i) + ">" + str(0) + ") | (caph" + str(i) + "==" + str(0) + "and smlh" + str(i) + "==" + str(0) + "))")
    df_11 = df_11.drop('no_sss', axis=1) 

    df_11 =df_11.groupby(['strm_id']).size().to_frame('ns' + str(i) ).reset_index()
    df_11['no_sss'+ str(i)] = i
    #d = d.append(df_11)
    d= pd.concat([d, df_11], axis = 1)
  

    d['strm_id'] = d['strm_id'].astype(str)
    d['no_sss' + str(i)] = d['no_sss' +str(i)].astype(str)

    df_new['no_sss'+ str(i)] = i
    df_new['no_sss' + str(i)] = df_new['no_sss' +str(i)].astype(str)

d = d.loc[:,~d.columns.duplicated()]
print(d)<jupyter_output>7
3
(svc != 7)  <class 'str'>
       strm_id  ns1 no_sss1  ns2 no_sss2  ns3 no_sss3
0     10099 01   15       1   15       2   15       3
1     10101101    2       1    2       2    2       3
2     10101201    1       1    2       2    2       3
3     10101301    3       1    4       2    4       3
4     10102301    4       1    4       2    4       3
...        ...  ...     ...  ...     ...  ...     ...
3718  23632 12    4       1    4       2    3       3
3719  23632 13    4       1    4       2    4       3
3720  23701 01    4       1    4       2    4       3
3721  23701 02    4       1    4       2    4       3
3722  23702 01    4       1    4       2    4       3

[3723 rows x 7 columns]<jupyter_text>FSU Ns Calculation<jupyter_code>### assign no_sss = '0'
df_new['no_sss_fsu'] = 0
df_fsu =  df_new
### append the duplicate df above the original df
df_new['strm_id'] = df_new['strm_id'].astype(str)
df_new['no_sss_fsu'] = df_new['no_sss_fsu'].astype(str)

df_fsu['strm_id'] = df_fsu['strm_id'].astype(str)
df_fsu['no_sss_fsu'] = df_fsu['no_sss_fsu'].astype(str)

### Using the duplicate df calculate the FSU Ns
### FSU Multiplier
### SELECT strm_id,'0' as msss,COUNT(*) as ns FROM mult_xx GROUP BY 1,2 WHERE svc#'7' ;

subset_df_new2 = df_fsu

## Asking inputs from the user
casualty_codes = str(input())

### Splittiung values on the basis of ', '
casualty_codes = casualty_codes.split(',')

### Filling the modified elements with svc != in list casualty_codes
casualty_codes = [('svc != ' + i) for i in casualty_codes]

### Joining the list of casualty codes into a single string
casualty_codes = ' or '.join(casualty_codes)

casualty_codes = "(" + casualty_codes + ") "

# casualty_codes = "'" + casualty_codes + "'"
print(casualty_codes, type(casualty_codes))

df_12 = subset_df_new2.query(casualty_codes)

#df_11 = df_11.groupby(['strm_id']).count().reset_index()
df_12 =df_12.groupby(['strm_id']).size().to_frame('nss_fsu').reset_index()
df_12['no_sss_fsu'] = 0
df_12['strm_id'] = df_12['strm_id'].astype(str)
df_12['no_sss_fsu'] = df_12['no_sss_fsu'].astype(str)
    

print(df_12)<jupyter_output>7
(svc != 7)  <class 'str'>
       strm_id  nss_fsu no_sss_fsu
0     10099 01       15          0
1     10101101        2          0
2     10101201        2          0
3     10101301        4          0
4     10102301        4          0
...        ...      ...        ...
3718  23632 12        4          0
3719  23632 13        4          0
3720  23701 01        4          0
3721  23701 02        4          0
3722  23702 01        4          0

[3723 rows x 3 columns]<jupyter_text>Merge NSs to the appended dataframe<jupyter_code>### Merge NS0, NS1, ....., NSi
merge_fsu = pd.merge(df_new, df_12, how = "left", on = ['strm_id', 'no_sss_fsu'])
print(merge_fsu)

# merge Ns to the dataset
merged1 = pd.DataFrame()

for i in range(1, no_sss_input + 1):

    merged = pd.merge(merge_fsu, d[["strm_id", "no_sss"+str(i), "ns"+str(i)]], on = ['strm_id', 'no_sss'+str(i)], how = "left")   

    merged1= pd.concat([merged1, merged], axis = 1) 
merged1 = merged1.loc[:,~merged1.columns.duplicated()]
print(merged1)<jupyter_output>strm_id    fsu  capzs  svc  capDsi  caph1  smlh1  caph2  smlh2  caph3  \
0      10099 01  10132  43854    2       1      0      0      0      0      0   
1      10099 01  10159  43854    2       1      0      0      0      0      0   
2      10099 01  10890  43854    1       1      0      0      2      2      1   
3      10099 01  12057  43854    3       1      0      0      0      0      0   
4      10099 01  12430  43854    3       1      0      0      0      0      0   
...         ...    ...    ...  ...     ...    ...    ...    ...    ...    ...   
14511  23701 02  26270     29    1       1      7      2     11      4      0   
14512  23702 01  26263     26    1       1      3      2      0      0      0   
14513  23702 01  26266     26    1       1     17      2      0      0      4   
14514  23702 01  26269     26    1       1      4      2      0      0      6   
14515  23702 01  26272     26    5       1      3      2      0      0      2   

       ...  smalldsi  no_ss[...]<jupyter_text>Multiplier Calculation<jupyter_code>### Calculate Multi at the FSU level
df_multfsu = merged1

inhabited_codes = str(input())
### Splitting values on the basis of ', '
inhabited_codes = inhabited_codes.split(',')

### Filling the modified elements with svc == in list inhabited_codes
inhabited_codes = [('svc == ' + i) for i in inhabited_codes]

### Joining the list of casualty codes into a single string
inhabited_codes = ' or '.join(inhabited_codes)

inhabited_codes = "(" + inhabited_codes + ") "

# inhabited_codes = "'" + inhabited_codes + "'"
print(inhabited_codes, type(inhabited_codes))

### Mult_FSU
df_mltfsu = df_multfsu.query(inhabited_codes)
df_mltfsu['mult_fsu'] = ((df_mltfsu['capzs']/df_mltfsu['nss_fsu'])*100)
df_mltfsu = df_mltfsu[["strm_id", "no_sss_fsu", "fsu", "mult_fsu"]]

print(df_mltfsu)

## Adding Fsu ns and mult to the data

df_mlt0 = pd.merge(merged1, df_mltfsu, how = "left", on = ['strm_id', 'no_sss_fsu', 'fsu'])
df_mlt0
## calculate the multiplier for all inhabited survey codes and smlh>0 and ns>0
### Mult_Ns
df_multns = df_mlt0

## Creating a blank df to store the data in loops
d_ns = df_multns


for i in range(1, no_sss_input + 1):

   
    df_multnsi = df_multns.query(inhabited_codes + "and (ns" + str(i) + " >" + str(0) + " and smlh" + str(i) + " >" + str(0) + ")")
    
    df_multnsi['mult' + str(i)] = (df_multnsi['capdsi'] * (df_multnsi['capzs'] * (df_multnsi['caph'+ str(i)]/ df_multnsi['smlh'+ str(i)])* (1/df_multnsi['ns' + str(i)])*100))
    
    d_ns = d_ns.join(df_multnsi['mult' + str(i)])

print(d_ns)



## Reorder column names as per the template shared

d_ns_sort = []
d_ns1 = d_ns[['strm_id', 'fsu', 'capzs', 'smallzs', 'no_ss', 'ss', 'svc', 'capDsi', 'smalldsi', 'no_sss']]
for i in range(1, no_sss_input + 1):
    d_ns2 = ["caph" + str(i), "smlh" + str(i), "ns" + str(i), "mult" + str(i)]
    d_ns_sort = d_ns_sort + d_ns2

d_ns3 = pd.DataFrame(d_ns, columns= d_ns_sort)

d_ns4 = d_ns[['nss_fsu', 'mult_fsu']]
d_ns5 = d_ns1.join(d_ns3)  
Multiplier = d_ns5.join(d_ns4)
Multiplier = Multiplier.fillna(0)
print(Multiplier)<jupyter_output>strm_id    fsu  capzs smallzs no_ss ss  svc  capDsi smalldsi no_sss  \
0      10099 01  10132  43854       1     1  1    2       1        1      5   
1      10099 01  10159  43854       1     1  1    2       1        1      5   
2      10099 01  10890  43854       1     1  1    1       1        1      5   
3      10099 01  12057  43854       1     1  1    3       1        1      5   
4      10099 01  12430  43854       1     1  1    3       1        1      5   
...         ...    ...    ...     ...   ... ..  ...     ...      ...    ...   
14511  23701 02  26270     29       1     1  1    1       1        1      5   
14512  23702 01  26263     26       1     1  1    1       1        1      5   
14513  23702 01  26266     26       1     1  1    1       1        1      5   
14514  23702 01  26269     26       1     1  1    1       1        1      5   
14515  23702 01  26272     26       1     1  1    5       1        1      5   

       ...  caph2  smlh2  ns2      mult2  caph3  sm[...]
{'fraction_non_alphanumeric': 0.1439223697650664, 'fraction_numerical': 0.09570990806945863, 'mean_word_length': 2.1451975586251204, 'pattern_counts': {'":': 0, '<': 19, '<?xml version=': 0, '>': 25, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 39, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '447222', 'license': 'ISC', 'n_tokens_mistral': 4359, 'n_tokens_neox': 3907, 'n_words': 984, 'path': 'MoSPI_NS_&_Multiplier_Final_OnlyNo_SSS.ipynb', 'repo_name': 'trishachaudhuri/sf', 'size': '9790'}