Count of daily activities per machine based on a lambda conditional in pandas

user_11793491 0
Viewed 57

I have the following dataset:

my_df = pd.DataFrame({'id':[1,2,3,4,5,6,7,8,9],
                      'machine':['A','A','A','B','B','A','B','B','A'],
                      'prod':['button','tack','pin','button','tack','pin','clip','clip','button'],
                      'qty':[100,50,30,70,60,15,200,180,np.nan],
                      'hours':[4,3,1,3,2,0.5,5,6,np.nan],
                      'day':[1,1,1,1,1,1,2,2,2]})
my_df['prod_rate']=my_df['qty']/my_df['hours']
my_df
    id  machine prod    qty     hours   day prod_rate
0   1   A       button  100.0   4.0     1   25.000000
1   2   A       tack    50.0    3.0     1   16.666667
2   3   A       pin     30.0    1.0     1   30.000000
3   4   B       button  70.0    3.0     1   23.333333
4   5   B       tack    60.0    2.0     1   30.000000
5   6   A       pin     15.0    0.5     1   30.000000
6   7   B       clip    200.0   5.0     2   40.000000
7   8   B       clip    180.0   6.0     2   30.000000
8   9   A       button  NaN     NaN     2   NaN

And I want to count the daily activities, except when there is a NaN (which means that the machine was paralyzed due to failure).

I tried this code:

my_df['activities']=my_df.groupby(['day','machine'])['machine']\
.transform(lambda x: x['machine'].count() if x['qty'].notna() else np.nan)

But it returns me an error: KeyError: 'qty'

This is the expected result:

id  machine prod    qty     hours   day prod_rate   activities
0   1   A   button  100.0   4.0     1   25.000000   4
1   2   A   tack    50.0    3.0     1   16.666667   4
2   3   A   pin     30.0    1.0     1   30.000000   4
3   4   B   button  70.0    3.0     1   23.333333   2
4   5   B   tack    60.0    2.0     1   30.000000   2
5   6   A   pin     15.0    0.5     1   30.000000   4
6   7   B   clip    200.0   5.0     2   40.000000   2
7   8   B   clip    180.0   6.0     2   30.000000   2
8   9   A   button  NaN     NaN     2   NaN         NaN

Please, could you help me fix my lambda expression? It will help me for this question and for other operations too.

pandas python
2 Answers

Although I prefer the solution from @steele-farnsworth, here is what OP requested. for the lambda to work

my_df['activities'] = my_df.groupby(['day','machine'])['qty']\
    .transform(lambda x: x.count() if x.notna().all() else np.nan)

print(my_df)

Prints

   id machine    prod    qty  hours  day  prod_rate  activities
0   1       A  button  100.0    4.0    1  25.000000         4.0
1   2       A    tack   50.0    3.0    1  16.666667         4.0
2   3       A     pin   30.0    1.0    1  30.000000         4.0
3   4       B  button   70.0    3.0    1  23.333333         2.0
4   5       B    tack   60.0    2.0    1  30.000000         2.0
5   6       A     pin   15.0    0.5    1  30.000000         4.0
6   7       B    clip  200.0    5.0    2  40.000000         2.0
7   8       B    clip  180.0    6.0    2  30.000000         2.0
8   9       A  button    NaN    NaN    2        NaN         NaN
user_32153760

You can do the calculation as normal, and then fill in the NaNs where they are wanted afterwards.

>>> my_df['activities'] = my_df.groupby(['day', 'machine'])['machine'].transform('count')
>>> my_df.loc[my_df['qty'].isna(), 'activities'] = np.NaN
>>> my_df

   id machine    prod    qty  hours  day  prod_rate  activities
0   1       A  button  100.0    4.0    1  25.000000         4.0
1   2       A    tack   50.0    3.0    1  16.666667         4.0
2   3       A     pin   30.0    1.0    1  30.000000         4.0
3   4       B  button   70.0    3.0    1  23.333333         2.0
4   5       B    tack   60.0    2.0    1  30.000000         2.0
5   6       A     pin   15.0    0.5    1  30.000000         4.0
6   7       B    clip  200.0    5.0    2  40.000000         2.0
7   8       B    clip  180.0    6.0    2  30.000000         2.0
8   9       A  button    NaN    NaN    2        NaN         NaN

You should avoid using lambdas as much as possible in the context of Pandas, as they are not vectorized (and will therefore run slower) and are less communicative than using existing, idiomatic Pandas methods.

user_99183450
Related