CODEWITHSUNDEEP
pythonpandas
lindak
lindak

Reputation: 167

Compare dataframes and output the numbers of count matches

I would like to count occurences of one dataframe in another dataframe and output the counts of matches. 

df
   group1  group2
0  orange  orange
1   apple   apple
2  banana    pear
3  banana  banana

.

fruit_df
   fruits
0  orange
1  banana

So:

 groups = ["group1", "group2"]
 matrix = pd.DataFrame()
 for group in groups:
        out = fruit_df["fruits"].isin(df[group]).astype(int)
        matrix = pd.concat([matrix, out], axis = 1)
 matrix.columns = groups
 matrix = matrix.rename(index = fruit_df["fruits"])

Results in:

matrix
        group1  group2
orange       1       1
banana       1       1

What I would like is:

 matrix
        group1  group2
 orange      1       1
 banana      2       1

Upvotes: 2

Views: 72

Answers (2)

jezrael
jezrael

Reputation: 862661

Use value_counts per columns, select by values from fruit_df['fruits'] by DataFrame.loc and if necessary replace missing values to 0 and convert to integers:

df = df.apply(pd.value_counts).loc[fruit_df['fruits']].fillna(0).astype(int)
print (df)
        group1  group2
orange       1       1
banana       2       1

Upvotes: 3

Saranya
Saranya

Reputation: 756

Here's one of the way you could try

temp_df = pd.melt(df, var_name='group', value_name='fruits')
temp_df['count'] = 1
df_count = temp_df.pivot_table(index=['fruits'], columns=['group'], values='count', aggfunc=np.sum).reset_index()
matrix = fruits_df.merge(df_count)
matrix.set_index('fruits')
print(matrix)

The result is

enter image description here

Upvotes: 3

Related Questions