Conditionally selecting rows and a groupby

user_10492123 0
Viewed 42

I'm trying to query a dataframe, it looks like this

import numpy as np
import pandas as pd

data = [['a.txt', 1, 2, 2, np.NaN, np.NaN], 
        ['b.txt', 2, np.NaN, 2, np.NaN, np.NaN], 
        ['c.txt', 3, np.NaN, 2, np.NaN, np.NaN],
        ['d.txt', 2, 1, np.NaN, 3, np.NaN]] 


df = pd.DataFrame(data, columns = ['file', 'type', 'banana', 'apple', 'orange', 'cherry'])

i'd like to be able to find out how many files contain cherries and apples, apples only and oranges and bananas. Then split them up by type.

so far I have

cher_and_apps = df.loc[(df.cherries>0) & (df.apples>0)]
oran_ban = df.loc[(df.oranges>0) & (df.banana>0)]

but im struggling for the apples only without having to write a long statement like

apples_only = df.loc[(df.cherries= np.NaN) & (df.apples > 0) etc...

I then want the output to look like

Type | Apples Only | Oranges & Bananas | Cherries & Apples
1    | 0           | 0                 | 0
2    | 1           | 1                 | 0
3    | 1           | 0                 | 0

My plan was to split the original df into the above, then do a groupby. does this sound ok?

pandas group-by python
1 Answers

Yes, it is long - first aggregate sum and then get all columns without apple for test at least one non 0 value:

df = df.groupby('type').sum()


df['Apples Only'] = (df.apple > 0) &df[df.columns.difference(['apple'])].ne(0).any(axis=1)
df['Oranges & Bananas'] = (df.orange>0) & (df.banana>0)
df['Cherries & Apples'] = (df.cherry>0) & (df.apple>0)

print (df)
      banana  apple  orange  cherry  Apples Only  Oranges & Bananas  \
type                                                                  
1        2.0    2.0     0.0     0.0         True              False   
2        1.0    2.0     3.0     0.0         True               True   
3        0.0    2.0     0.0     0.0        False              False   

      Cherries & Apples  
type                     
1                 False  
2                 False  
3                 False

Last if need filter some columns at convert True/False to 1/0:

df = df[['Apples Only','Oranges & Bananas','Cherries & Apples']].astype(int).reset_index()
print (df) 
   type  Apples Only  Oranges & Bananas  Cherries & Apples
0     1            0                  0                  0
1     2            0                  1                  0
2     3            1                  0                  0

Details:

print (df[df.columns.difference(['apple'])])
      banana  cherry  orange
type                        
1        2.0     0.0     0.0
2        1.0     0.0     3.0
3        0.0     0.0     0.0

print (df[df.columns.difference(['apple'])].eq(0))
type                        
1      False    True    True
2      False    True   False
3       True    True    True

print (df[df.columns.difference(['apple'])].eq(0).all(axis=1))
type
1    False
2    False
3     True
dtype: bool
user_29010020
Related