Count list in columns - Pandas

user_12926741 0
Viewed 87

fellow Pytoneers or Pythonistas,

I am hoping I can get your help on this issue:

I have more than one pandas data frames that contain as in the example below:

enter image description here

I would like to create a function that will count values from a list e.g. [a,b,c] and iterate through the columns and the data frames. I would like to return the total number of items found in the columns from the list. I wrote this code, but it doesn't seem to do the trick. Could you please help? Thank you in advance for your answers. PS: As I am new to this, my karma is quite low :D

list_of_dataframes = [k,p,r]

list_of_chars = ["a","b","c"]

for dataframe in list_of_dataframes:
        for values in list_of_chars:
            for columns in dataframe:
                count = 0
                if dataframe.columns.str.contains(list_of_chars):
                    dataframe['Total']=count+1
                else dataframe['Total']
pandas dataframe python string
1 Answers

Join strings columns together and then count values by Series.str.count with joined values together and replaced missing values by empty string. Because there are some values of list with regex characters is necessary escape them by re.escape first:

In your solution:

k = pd.DataFrame({'ID':[1,2, 3], 
                   'String1':['a d d','a','s'],
                   'String2':['a','a b b','c']})
 
p = pd.DataFrame({'ID':[1,2, 3], 
                   'String1':['c','b','c'],
                   'String2':['a b','a c','d a']})
 
r = pd.DataFrame({'ID':[1,2, 3], 
                   'String1':['a','c d as','c a d'],
                   'String2':['a b c c','a b c','d a b f']})
 
list_of_dataframes = [k,p,r]

import re

list_of_chars = ["a","b","c"]
pat = '|'.join(['({})'.format(re.escape(c)) for c in list_of_chars])

for i, dataframe in enumerate(list_of_dataframes):
    dataframe['Total'] = dataframe.select_dtypes(object).fillna('').agg(''.join, 1).str.count(pat)
    list_of_dataframes[i] = dataframe
     
print (list_of_dataframes)
[   ID String1 String2  Total
0   1   a d d       a      2
1   2       a   a b b      4
2   3       s       c      1,    ID String1 String2  Total
0   1       c     a b      3
1   2       b     a c      3
2   3       c     d a      2,    ID String1  String2  Total
0   1       a  a b c c      5
1   2  c d as    a b c      5
2   3   c a d  d a b f      4]
user_29010020
Related