Python:How to count specific values in specific columns in a dataframe

user_10391050 0
Viewed 10901

I have a particular csv for eg:

col1 col2 col3 col4

a    1    2     3

b    1    2     1 

c    1    1     3

d    3    1     2

I want to count number of a particular value for eg. 1 in col2, col3 and col4

I am using the following code using pandas

import pandas as pd

fname = input('Enter the filename:')
df = pd.read_csv (fname, header='infer')
one = df.iloc[:,1:4].value_counts(normalize=False).loc[1]

It is showing error but when I am doing the same for a particular defined column the code is running properly

import pandas as pd
fname = input('Enter the filename:')
df = pd.read_csv (fname, header='infer')
one = df[col1].value_counts(normalize=False).loc[1]

I want the following output

col2 3

col3 2

col4 1

Any help or tips would be greatly appreciated! Thank you in advance. :)

pandas dataframe count python
2 Answers

Use eq with desired value i.e. 1 and then sum as:

df1[['col2', 'col3', 'col4']].eq(1).sum()

col2    3
col3    2
col4    1
dtype: int64
user_83208610

I reached this question when searching for a way to check how many values are actually higher/lower than zero, on the columns 'Buys' and 'Sells', on the following DataFrame (named "trade_track"):

Ticker  Pre-trade  Buys  Sells  Net Exposure Ch.  Post-trade
CX        10126.0   0.0 -964.0         -0.095200      9162.0
OI         3311.0   0.0  -24.0         -0.007249      3287.0
THO         748.0   0.0  -33.0         -0.044118       715.0
WRK        1002.0   0.0  -43.0         -0.042914       959.0
TAP         646.0   0.0   -4.0         -0.006192       642.0
TRN        1987.0   0.0  -93.0         -0.046804      1894.0
SJM         312.0   6.0    0.0          0.019231       318.0
WW         1100.0   0.0  -22.0         -0.020000      1078.0
FAST       -655.0  13.0    0.0         -0.019847      -642.0
CSX        -301.0   6.0    0.0         -0.019934      -295.0
ODFL       -123.0   0.0    0.0         -0.000000      -123.0
HELE       -130.0   0.0    0.0         -0.000000      -130.0
SBUX       -203.0   0.0    0.0         -0.000000      -203.0
WM         -166.0   0.0    0.0         -0.000000      -166.0
HD          -90.0   2.0    0.0         -0.022222       -88.0
VMC        -141.0   0.0    0.0         -0.000000      -141.0
CTAS        -76.0   2.0    0.0         -0.026316       -74.0
ORLY        -53.0   0.0    0.0         -0.000000       -53.0

Here is a simple code that worked:

(i) to find all numbers above zero on column 'Buys':

((trade_track['Buys'])>0).sum()

(ii) to find all zeros on column 'Buys':

((trade_track['Buys'])==0).sum()
user_112946670
Related