mercredi 20 mars 2019

I try to iterate through a pandas data frame and create a new column based on several IF conditions

I struggle to create a new column in my data frame, which would be the result of going through each row a data frame and calculating the average based on some conditions. That is how the data frame looks like

ID, 1_a, 1_b, 1_c, 2_a, 2_b, 2_c, 3_a, 3_b, 3_c 
0, 0, 145, 0.8, 0, 555, 0.7, 1, 335, 0.7
1, 1, 222, 0.9, 1, 224, 0.4, 1, 555, 0.6
3, 1, 111, 0.3, 0, 222, 0.5, 1, 999, 0.7 

I hope to have the following result:

ID, 1_a, 1_b, 1_c, 2_a, 2_b, 2_c, 3_a, 3_b, 3_c, NEW
0, 0, 145, 0.8, 0, 555, 0.7, 1, 335, 0.7, 0.7
1, 1, 222, 0.8, 1, 224, 0.4, 1, 555, 0.6, 0.6
3, 1, 111, 0.3, 0, 222, 0.5, 1, 999, 0.7, 0.5

The logic is the following.

If 1_a is 1, keep value in 1_c, if not ignore
If 2_a is 1, keep value in 2_c, if not ignore
If 3_a is 1, keep value in 3_c, if not ignore

calculate the average of the kept values for each row and store in column 'NEW'

I tried several ways, but it only works if I have only 1 row in the data frame. If I have more than 1 row, it seems to calculate the mean across the whole data frame. Additionally, I try to optimise the function as I have more 10 of these IF conditions. That is what I tried, but it does not give me the result, I am looking for:

 def test(x):
    a = x[x['1_a']==1]['1_c'].values
    b = x[x['2_a']==1]['2_c'].values
    c = x[x['3_a']==1]['3_c'].values
    xx =np.concatenate((a,b,c), axis=0)
    z = sum(xx)/len(xx)
    x['New_Prob'] = z
    return x
print(test(df))

The result is something like that:

ID, 1_a, 1_b, 1_c, 2_a, 2_b, 2_c, 3_a, 3_b, 3_c, NEW
0, 0, 145, 0.8, 0, 555, 0.7, 1, 335, 0.7, 0.6
1, 1, 222, 0.8, 1, 224, 0.4, 1, 555, 0.6, 0.6
3, 1, 111, 0.3, 0, 222, 0.5, 1, 999, 0.7, 0.6

Aucun commentaire:

Enregistrer un commentaire