CODEWITHSUNDEEP
pythonpandasdataframe
user1631306
user1631306

Reputation: 4470

frequency of values in column in multiple panda data frame

I have multiple panda data frames ( more than 70), each having same columns. Let say there are only 10 rows in each data frame. I want to find the column A' value occurence in each of data frame and list it. Example:

# Import pandas library 
import pandas as pd 
  
# initialize list of lists 
data = [['tom', 10], ['nick', 15], ['juli', 14]] 
  
# Create the pandas DataFrame 
df = pd.DataFrame(data, columns = ['Name', 'Age']) 

data = [['sam', 12], ['nick', 15], ['juli', 14]] 

df2 = pd.DataFrame(data, columns = ['Name', 'Age'])

I am expecting the output as

Name  Age
 tom    1
 sam    1
nick    2
juli    2

Upvotes: 0

Views: 70

Answers (5)

Soumendra Mishra
Soumendra Mishra

Reputation: 3663

You can try this:

df = pd.concat([df1,df2])
df = df.groupby(['Name'])['Age'].count().to_frame().reset_index()
df = df.rename(columns={"Age": "Count"})
print(df)

Upvotes: 1

StupidWolf
StupidWolf

Reputation: 46938

This can work if your data frames are not costly to concat:

pd.concat([x['Name'] for x in [df,df2]]).value_counts()

nick    2
juli    2
tom     1
sam     1

Upvotes: 1

NYC Coder
NYC Coder

Reputation: 7604

You can try this:

df = pd.concat([df, df2]).groupby('Name', as_index=False).count()
df.rename(columns={'Age': 'Count'}, inplace=True)
print(df)

   Name  Count
0  juli    2
1  nick    2
2   sam    1
3   tom    1

Upvotes: 1

Hryhorii Pavlenko
Hryhorii Pavlenko

Reputation: 3910

Do you want value counts of Name column across all dataframes?

main = pd.concat([df,df2])
main["Name"].value_counts()

juli    2
nick    2
sam     1
tom     1
Name: Name, dtype: int64

Upvotes: 3

IoaTzimas
IoaTzimas

Reputation: 10624

You can do the following:

from collections import Counter

d={'df1':df1, 'df2':df2, ..., 'df70':df70}
l=[list(d[i]['Name']) for i in d]
m=sum(l, [])
result=Counter(m)

print(result)

Upvotes: 3

Related Questions