How to loop through the pandas dataframe columns using for loop to count the value based on the list given

Question

I would like to loop through a pandas dataframe's columns using a for loop to count the value based on the list given.

My_list =[ 'apple', 'orange', 'grapes' ]

I can do it with value_count() functions as given below to calculate frequency

df['Fruits']. value_count()

but I want to calculate using the for loop to iterate over dataframe to get count and average of the list given.

My_list =[ 'apple', 'orange', 'grapes' ] 

Df:   
    Fruits  value
    apple      10
    apple      20 
    orange      2
    grapes      5 
    grapes     10 
    grapes      3

My output should be like this.

Fruits    count    average
apple      2         15 
orange     1          2 
grapes     3          6

jezrael · Accepted Answer · 2018-01-15 12:57:25Z

2

Use:

filter by query or boolean indexing
groupby and aggregate size and mean
rename columns names by dictionary
convert index to column by reset_index

My_list = ['apple', 'orange', 'grapes'] 
df1 = (df.query("Fruits in @My_list")
         .groupby('Fruits', sort=False)['value']
         .agg(['size','mean'])
         .rename(columns={'mean':'average', 'size':'count'})
         .reset_index())

df1 = (df[df['Fruits'].isin(My_list)]
        .groupby('Fruits', sort=False)['value']
        .agg(['size','mean'])
        .rename(columns={'mean':'average', 'size':'count'})
        .reset_index())

print (df1)
   Fruits  count  average
0   apple      2       15
1  orange      1        2
2  grapes      3        6

If want use loop, it should be slowier:

L = []
for x in My_list:
    s = df.loc[df['Fruits'] == x, 'value']
    #print (s)
    L.append({'Fruits': x, 'average':s.mean(), 'count':len(s)})

df = pd.DataFrame(L, columns=['Fruits','count','average'])
print (df)
   Fruits  count  average
0   apple      2     15.0
1  orange      1      2.0
2  grapes      3      6.0

edited Jan 15, 2018 at 12:57

answered Jan 15, 2018 at 12:37

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jezrael Over a year ago

I add also loop solution, but faster/ better is use groupby + agg solution.

user3762120 Over a year ago

Great. Thanks for listing the different options

Collectives™ on Stack Overflow

How to loop through the pandas dataframe columns using for loop to count the value based on the list given

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related