CODEWITHSUNDEEP
pythonpandas
ketan
ketan

Reputation: 2904

How to count particular column values in python pandas?

I'm having dataframe like below:

df1_data = {'sym' :{0:'AAA',1:'BBB',2:'CCC',3:'AAA',4:'CCC',5:'DDD',6:'EEE',7:'EEE',8:'FFF'},
        'identity' :{0:'AD',1:'AD',2:'AU',3:'AU',4:'AU',5:'AZ',6:'AU',7:'AZ',8:'AZ'}}

I want to check for sym column in my dataframe. My intension is to generate two different files, one containing same two columns in different order and second file contains sym,sym_count,AD_count,AU_count,neglected_count columns.

Edit 1 -

I want to avoid identity other than (AD & AU). In both output file I don't want result of AD & AU identity. neglected_count column is optional.

Expected Result-

result.csv

sym,identity
AAA,AD
AAA,AU
BBB,AD
CCC,AU
CCC,AU
EEE,AU

result_count.csv

sym,sym_count,AD_count,AU_count,neglected_count
AAA,2,1,1,0
BBB,1,1,0,0
CCC,2,0,2,0
EEE,2,0,1,1

How I can perform such type of calculation in python pandas?

Upvotes: 2

Views: 233

Answers (1)

jezrael
jezrael

Reputation: 862581

I think you need crosstab with insert for add sum column to first position and add_suffix to column names.

Last write to_csv.

df1_data = {'sym' :{0:'AAA',1:'BBB',2:'CCC',3:'AAA',4:'CCC',5:'DDD',6:'EEE',7:'EEE',8:'FFF'},
        'identity' :{0:'AD',1:'AD',2:'AU',3:'AU',4:'AU',5:'AZ',6:'AU',7:'AZ',8:'AZ'}}
df = pd.DataFrame(df1_data, columns=['sym','identity'])
print (df)
   sym identity
0  AAA       AD
1  BBB       AD
2  CCC       AU
3  AAA       AU
4  CCC       AU
5  DDD       AZ
6  EEE       AU
7  EEE       AZ
8  FFF       AZ

#write to csv
df.to_csv('result.csv', index=False)

#need vals only in identity
vals = ['AD','AU']

#replace another values to neglected
neglected = df.loc[~df.identity.isin(vals), 'identity'].unique().tolist()
neglected = {x:'neglected' for x in neglected}
print (neglected)
{'AZ': 'neglected'}

df.identity = df.identity.replace(neglected)

df1 = pd.crosstab(df['sym'], df['identity'])
df1.insert(0, 'sym', df1.sum(axis=1))
df2 = df1.add_suffix('_count').reset_index()
#find all rows where is 0 in columns with vals
mask = ~df2.filter(regex='|'.join(vals)).eq(0).all(axis=1)
print (mask)
0     True
1     True
2     True
3    False
4     True
5    False
dtype: bool

#boolean indexing
df2 = df2[mask]
print (df2)
identity  sym  sym_count  AD_count  AU_count  neglected_count
0         AAA          2         1         1                0
1         BBB          1         1         0                0
2         CCC          2         0         2                0
4         EEE          2         0         1                1

df2.to_csv('result_count.csv', index=False)

Upvotes: 2

Related Questions