Counting number of certain values in each column using pandas and collections

user_8653226 0
Viewed 140

I have a txt file including 9 columns and 6 rows. The first 8 columns are either of these values: "1" , "2" and "3". I named these columns from "A" to "H". I named the last column: "class". The last column is a name : "HIGH". Here is the txt file (data.txt):

1,1,1,1,2,1,1,3,HIGH 
1,1,1,2,2,1,1,3,HIGH 
1,1,1,1,1,1,1,3,HIGH 
1,1,1,2,1,1,1,3,HIGH 
1,1,1,3,2,1,1,3,HIGH 
1,1,1,2,1,2,1,3,HIGH

I am trying to count the number of each value in each column and print a list that should have 3 components including the numbers of "1", "2" and "3" values in that column respectively. For example in the first column (e.g A) all values are "1". I expect to get : A : [6,0,0]. For the 8th column (e.g. H) where all values are "3", I expect to get: H : [0,0,6] or for the fourth column (e.g. D) I have two "1" , three "2" and one "3". So I expect : D : [2,3,1]. I tried to get it done using pandas and collection . Here is what I did:

import pandas as pd
from collections import Counter

df = pd.read_csv('data.txt')
df.columns = ['A','B','C','D','E','F','G','H','class']

X = df.ix[:, 0:8].values
y = df.ix[:, 8].values

deg = ['HIGH']

names = ['A','B','C','D','E','F','G','H']

for j in range(0, 8):

    freqs = Counter(X[y == deg[0], j])
    print(names[j],':',list(freqs.values()))

The output of the above code are empty lists. Here is what it returns:

A : []
B : []
C : []
D : []
E : []
F : []
G : []
H : []

How can I modify the above code to get what I want? Thanks!

pandas counter python-3.x collections
4 Answers

Use pandas.Series.value_counts

df.loc[:, :"H"].apply(pd.Series.value_counts).fillna(0).to_dict("l")

Output:

{'A': [6.0, 0.0, 0.0],
 'B': [6.0, 0.0, 0.0],
 'C': [6.0, 0.0, 0.0],
 'D': [2, 3, 1],
 'E': [3.0, 3.0, 0.0],
 'F': [5.0, 1.0, 0.0],
 'G': [6.0, 0.0, 0.0],
 'H': [0.0, 0.0, 6.0]}
user_70937410

Solution with collections is select all columns without last, convert Counter to Series, so output is DataFrame, replace missing values by DataFrame.fillna, convert values to integers and last to dictionary by DataFrame.to_dict:

from collections import Counter

d = (df.iloc[:, :-1].apply(lambda x: pd.Series(Counter(x)))
       .fillna(0)
       .astype(int)
       .to_dict("list"))
print (d)
{'A': [6, 0, 0], 'B': [6, 0, 0], 
 'C': [6, 0, 0], 'D': [1, 4, 1], 
 'E': [3, 3, 0], 'F': [5, 1, 0], 
 'G': [6, 0, 0], 'H': [0, 0, 6]}

Only pandas solution with pandas.value_counts:

d = (df.iloc[:, :-1].apply(pd.value_counts)
       .fillna(0)
       .astype(int)
       .to_dict("list"))
print (d)
{'A': [6, 0, 0], 'B': [6, 0, 0],
 'C': [6, 0, 0], 'D': [2, 3, 1], 
 'E': [3, 3, 0], 'F': [5, 1, 0], 
 'G': [6, 0, 0], 'H': [0, 0, 6]}
user_29010020

Working within python, since your end result is a dictionary:

from string import ascii_uppercase
from collections import Counter, defaultdict
from itertools import chain, product
import csv    

d = defaultdict(list)
fieldnames = ascii_uppercase[:9]
# test.csv is your file above
with open('test.csv') as csvfile:    
    reader = csv.DictReader(csvfile, fieldnames = list(fieldnames))
    reader = Counter(chain.from_iterable(row.items() for row in reader))
    for col, value in product(fieldnames, ("1","2","3")):
        if col != fieldnames[-1]:
            d[col].append(reader.get((col,value), 0))

print(d)

defaultdict(list,
            {'A': [6, 0, 0],
             'B': [6, 0, 0],
             'C': [6, 0, 0],
             'D': [2, 3, 1],
             'E': [3, 3, 0],
             'F': [5, 1, 0],
             'G': [6, 0, 0],
             'H': [0, 0, 6]})
user_71757130

Define the following function:

def cntInts(col):
    vc = col.value_counts()
    return [ vc.get(i, 0) for i in range(1,4) ]

Then apply it and print results:

for k, v in df.loc[:, 'A':'H'].apply(cntInts).iteritems():
    print(f'{k}: {v}')

For your data sample I got:

A: [6, 0, 0]
B: [6, 0, 0]
C: [6, 0, 0]
D: [2, 3, 1]
E: [3, 3, 0]
F: [5, 1, 0]
G: [6, 0, 0]
H: [0, 0, 6]

Or maybe it is enough to call just:

df.loc[:, 'A':'H'].apply(cntInts)

This time the result is a Series, which when printed yields:

A    [6, 0, 0]
B    [6, 0, 0]
C    [6, 0, 0]
D    [2, 3, 1]
E    [3, 3, 0]
F    [5, 1, 0]
G    [6, 0, 0]
H    [0, 0, 6]
dtype: object

Edit

Following your comments I suppose that there is something wrong with your data.

To trace the actual reason:

  1. Define a string variable:

     txt = '''1,1,1,1,2,1,1,3,HIGH 
 1,1,1,2,2,1,1,3,HIGH 
 1,1,1,1,1,1,1,3,HIGH 
 1,1,1,2,1,1,1,3,HIGH 
 1,1,1,3,2,1,1,3,HIGH 
 1,1,1,2,1,2,1,3,HIGH'''

  2. Run:

     import io
 df = pd.read_csv(io.StringIO(txt), names=['A','B','C','D','E','F','G','H','class'])

  3. Run my code on my data. The result should be just as expected.

  4. Then read your input file (also into df) and run my code again. Probably there is some difference between your data and mine. Especially look for any extra spaces in your input file, check also column types (after read_csv).

user_73884770
Related