two conditional variable and one numerical variable lambda function in pandas dataframe to create a new boolean column

user_17934027 0
Viewed 37

I have a Dataframe that I want to add an extra column which will be a Boolean output ('True' or 'False').

The categorical variables, i.e. column names, in the Dataframe are 'chems' and 'lot' and the continuous variable is the column name 'value'.

I want to use two of the categorical variables to assess and create the output 'True' or 'False' considering the values associated with the continuous variable 'value' and if the mean of one of the lots for a particular chemical (chems) is greater than the other. In this particular example, it would be: is the mean of lot 9849 > lot 7711?

Below is a snapshot of the pandas Dataframe relevant to the columns above and the expected output:

chems lot value Boolean Average
AMCL1 7711 -0.01737 TRUE -0.02433
AMCL1 7711 0.014506 TRUE
AMCL1 7711 -0.03482 TRUE
AMCL1 7711 -0.05299 TRUE
AMCL1 7711 -0.03097 TRUE
AMCL1 9849 0.027269 TRUE 0.04055
AMCL1 9849 0.05071 TRUE
AMCL1 9849 0.043671 TRUE
AmT 7711 0.025124 FALSE 0.032779
AmT 7711 0.026267 FALSE
AmT 7711 0.05459 FALSE
AmT 7711 0.025135 FALSE
AmT 7711 NaN FALSE
AmT 9849 -0.04318 FALSE -0.04371
AmT 9849 -0.04331 FALSE
AmT 9849 -0.04463 FALSE

note, please ignore the column average here, that's the calculation I want to use to decide the outcome for the Boolean column.

I'm nearly sure I can do this with a lambda expression but I haven't been able to capture with a lambda expression the two categorical variables for assessing the means. Currently, I have something like this:

df['Boolean'] = df['value'].apply\
    (lambda x: 'True' for chem in df['chems'] if np.mean(np.array(df[(df['lot'] == '9849')]['value'])) > np.mean(np.array(df[(df['lot'] == '7711')]['value'])) else 'False')

I can't figure out the syntax at the end for the else 'False', I'm getting a SyntaxError: invalid syntax and I'm not sure the apply lambda function is even close to doing what I want it to do.

pandas dataframe lambda python
1 Answers

Easy solution

s = df.pivot_table('value', 'chems', 'lot')
df['Boolean'] = df['chems'].isin(s.index[s[7711] < s[9849]])

Explained

Pivot the dataframe to reshape and aggregate with mean.

>>> s

lot        7711      9849
chems                    
AMCL1 -0.024329  0.040550
AmT    0.032779 -0.043707

Find the list of chems where mean of lot 9849 is greater than 7711

>>> s.index[s[7711] < s[9849]]

Index(['AMCL1'], dtype='object', name='chems')

Use isin to check whether values in chems column of df are contained in the above list

>>> df

    chems   lot     value  Boolean   Average
0   AMCL1  7711 -0.017370     True -0.024330
1   AMCL1  7711  0.014506     True       NaN
2   AMCL1  7711 -0.034820     True       NaN
3   AMCL1  7711 -0.052990     True       NaN
4   AMCL1  7711 -0.030970     True       NaN
5   AMCL1  9849  0.027269     True  0.040550
6   AMCL1  9849  0.050710     True       NaN
7   AMCL1  9849  0.043671     True       NaN
8     AmT  7711  0.025124    False  0.032779
9     AmT  7711  0.026267    False       NaN
10    AmT  7711  0.054590    False       NaN
11    AmT  7711  0.025135    False       NaN
12    AmT  7711       NaN    False       NaN
13    AmT  9849 -0.043180    False -0.043710
14    AmT  9849 -0.043310    False       NaN
15    AmT  9849 -0.044630    False       NaN
user_128331660
Related