How to generation String (Append columns) based on condition

user_12944256 0
Viewed 59

I want to append string based on values in other table.

DF1:

Tech_ID  s1  s2  s3  s4  s5  s6
-------------------------------
   1      1   1   1   0   1   1
   2      1   0   1   1   1   0  
   3      1   1   0   0   1   1 

Df1 = {'Tech_ID':[1,2,3],
        'S1':[1,1,1], 'S2':[1,0,1], 'S3': [1,1,0],  
           'S4':[0,1,0],'S5':[1,1,1],'S6':[1,0,1]}

Same way Df2 is like

Df2:

Tech_ID  Std_Id  s1_Norm  s2 Norm  S3 Norm  S4_Norm  S5 Norm  S6_Norm
---------------------------------------------------------------------
   1        1       11        01       10       11       00       01
   1        7       01        11       01       11       01       10
   2        4       00        10       00       11       10       11
   3        8       10        11       01       01       11       00
   2        2       11        01       00       10       01       10
.
.
. 
   
  Df2 = {'Tech_ID':[1,1,2,3,2], Std_Id [1,7,4,8,2]
        'S1_norm':[11,01,00,10,11], 'S2_norm':[01,11,10,11,01],
         'S3_norm': [10,01,00,01,00], 'S4_norm':[11,11,11,01,10], 
          'S5_norm': [00,01,10,11,01], 'S2_norm':[01,10,11,00,10],}​

Now want to generate the string based on DF1 Values

For Tech_ID: 1

  • s1+s1_Norm+s2+s2_Norm+s3+s3_Norm+s5+s5_Norm+s6+s6_Norm Because s4=0 so not consider value of s4 & s4_Norm in string.

Same way for Tech_ID: 2

  • s1+s1_Norm+s3+s3_Norm+s4+s4_Norm+s5+s5_Norm same here s2 and s6 are 0 so s2,s2_norm,s6 and s6_norm not consider in string.

s4 and s4_norm are not taken in string calculation as s4 value is 0.

Same way any S series value 0 then value of that s bit and s_Norm bit not considered in string generation.

Expected result:

Tech_ID  Std_Id      Result
---------------------------------
  1        1      111101110100101
  1        7      101111101101110
  2        4      100100111110
  3        8      100111111100
  2        2      111100110101
.
.
.

Same way if s4= 1 and s3=0 then s3 and s3_Norm should not consider in string generation.

try to do with below code in not consider Tech_id not in it, but not get succeed.

import itertools

#(df1.astype(str).values + df2) @ df1.T.values
-------

d_ = (df1.astype(str).values + df2.iloc[:,3:]).apply(lambda row: ''.join(itertools.compress(row.tolist(), df1.loc[0].tolist())), axis=1)

df_ = pd.concat([df2.iloc[:, :2], d_], axis=1)
pandas matrix python
1 Answers

You can do it with apply/merge methods. I've added comments to explain the code.

df1['cols_to_use'] = np.NAN
df1['cols_to_use'] = df1.apply(lambda x: [col for col in df1.columns.values[1:-1] if x[col]!=0], axis=1) # it'll create the list of columns where value is not 0.
merged_df = df2.merge(df1, on = 'Tech_ID', how= 'inner') #merging dataframes so that we can use the previous result and make the equation.
merged_df['result'] = np.NaN
merged_df['result'] = merged_df.apply(
    lambda x: ''.join(
        str(x[col]) + str(x[f'{col}_Norm']) for col in x['cols_to_use']
    ),
    axis=1,
) # this is the required equation it'll add all col and col_num where value is not zero.

Will give output -

Tech_ID Std_Id s1_Norm s2_Norm s3_Norm s4_Norm s5_Norm s6_Norm s1 s2 s3 s4 s5 s6 cols_to_use result
1 1 11 01 10 11 00 01 1 1 1 0 1 1 ['s1', 's2', 's3', 's5', 's6'] 111101110100101
1 7 01 11 11 11 01 10 1 1 1 0 1 1 ['s1', 's2', 's3', 's5', 's6'] 101111111101110
2 4 00 10 11 11 10 11 1 0 1 1 1 0 ['s1', 's3', 's4', 's5'] 100111111110
2 2 11 01 10 10 01 10 1 0 1 1 1 0 ['s1', 's3', 's4', 's5'] 111110110101
3 8 10 11 01 01 11 00 1 1 0 1 1 1 ['s1', 's2', 's4', 's5', 's6'] 110111101111100
user_154380330
Related