I am new to pandas I have a dataframe and it has three columns with values as

nTimeStamp       nMessageCode   nIOC
1544114048       12073           0
1544114048       12075           0         
1544114048       12073           1
1544114048       12075           1
1544114049       12074           0         
1544114049       12073           0
1544114049       12075           0

I want to get the sum as per the nTimeStamp based on the filter as below

SQL code

SELect nTimeStamp, SUM(nFilter) nCount
 FROM (
Select nTimeStamp case when nMessageCode in (12073,12074)
                       then 1
                       when nMessageCode = 12075 and nIOC = 0
                       then 1
                       else 0
                   end 'nFilter'   
      )
 group by nTimeStamp

expected result will be

nTimestamp       nCount
1544114048         3
1544114049         3

How do I achieve the same. Please help

1 Answers

0
jezrael On

Create boolean mask and count True values by Series.groupby with sum:

m = (df['nMessageCode'].isin((12073,12074)) | 
     (df['nMessageCode'].eq(12075) & df['nIOC'].eq(0)))

df1 = m.groupby(df['nTimeStamp']).sum().astype(int).reset_index(name='nCount')
print (df1)
   nTimeStamp  nCount
0  1544114048       3
1  1544114049       3