Pandas comparing dataframes and changing column value based on number of similar rows in another dataframe

user_13937592 0
Viewed 38

Suppose I have two dataframes:

df1:

    Person Number Type
0   Kyle   12     Male
1   Jacob  15     Male
2   Jacob  15     Male

df2: A much larger dataset with similar format except there is a count column that needs to increment based on df1

    Person Number Type    Count 
0   Kyle   12     Male    0
1   Jacob  15     Male    0
3   Sally  43     Female  0
4   Mary   15     Female  5

What I am looking to do is increase the count column based on the number of occurrences of the same person in df1

Excepted output for this example:

    Person Number  Type    Count 
0   Kyle   12     Male    1
1   Jacob  15     Male    2
3   Sally  43     Female  0
4   Mary   15     Female  5

Increase count to 1 for Kyle because there is one instance, increase count to 2 because there are two instances for Jacob. Don't change value for Sally and Mary and keep the value the same.

How do I do this? I have tried using .loc but I can't figure out how to account for two instances of the same row. Meaning that I can only get count to increase by one for Jacob even though there are two Jacobs in df1.

I have tried

df2.loc[df2['Person'].values == df1['Person'].values, 'Count'] += 1

However this does not account for duplicates.

pandas numpy python
2 Answers
df1 = df1.groupby(df.columns.tolist(), as_index=False).size().to_frame('Count').reset_index()

df1 = df1.set_index(['Person','Number','Type'])
df2 = df2.set_index(['Person','Number','Type'])

df1.add(df2, fill_value=0).reset_index()

Or

df1 = df1.groupby(df.columns.tolist(), as_index=False).size().to_frame('Count').reset_index()
df2.merge(df1, on=['Person','Number','Type'], how='left').set_index(['Person','Number','Type']).sum(axis=1).to_frame('Count').reset_index()
user_47183500

value_counts + Index alignment.

u = df2.set_index("Person")
u.assign(Count=df1["Person"].value_counts().add(u["Count"], fill_value=0))

        Number    Type  Count
Person
Kyle        12    Male    1.0
Jacob       15    Male    2.0
Sally       43  Female    0.0
Mary        15  Female    5.0
user_34832030
Related