pandas count frequency of column value in another dataframe column

Question

I want to calculate the frequencies of values of a dataframe column in a column from another dataframe. Right now, I have the code as below:

df2["freq"] = df1[["col1"]].groupby(df2["col2"])["col1"].transform('count')

But it is giving freq of 1.0 for all the values in df2["col2"], even for those values that don't exist in df1["col1"].

df1:

            col1
0            636  
1            636  
2            801  
3            802

df2:

            col2
0            636  
1            734  
2            801  
3            803

df2 after adding freq column:

            col2    freq
0            636    1.0
1            734    1.0
2            801    1.0
3            803    1.0

What I actually want:

            col2    freq
0            636     2
1            734     0
2            801     1
3            803     0

I am new to pandas, so I am not getting what I am doing wrong. Any help is appreciated! Thanks!

I don't understand what you mean by " in a column from another dataframe". Because you can get a frequency distribution easily: your_column.value_counts() — ifly6
– ifly6, Commented Aug 21, 2019 at 11:34
@jezrael I have updated the question to include dataframes. Hopefully my question is clear now — Ankur
– Ankur, Commented Aug 21, 2019 at 11:47

jezrael · Accepted Answer · 2019-08-21 11:47:48Z

4

Use Series.map by Series created by Series.value_counts, last replace missing values to 0:

df2["freq"] = df2["col2"].map(df1["col1"].value_counts()).fillna(0).astype(int)
print (df2)
   col2  freq
0   636     2
1   734     0
2   801     1
3   803     0

answered Aug 21, 2019 at 11:47

jezrael

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

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

Collectives™ on Stack Overflow

pandas count frequency of column value in another dataframe column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related