Pandas merge dividing a value each time it's merged

user_13877952 0
Viewed 82

I have 2 dataframes from the following

df :

     Name
1    A
2    B
3    C
4    C
5    D
6    D
7    D
8    D

and df_value :

     Name   Value
1    A      50
2    B      100
3    C      200
4    D      800

I want to merge both dataframes (into df), but with the new Value being worth the df_value Value divided by the number of occurences of Name in df

Output :

     Name   Value
1    A      50
2    B      100
3    C      100
4    C      100
5    D      200
6    D      200
7    D      200
8    D      200

A appears once, has a Value of 50 in df_value, so its value is 50. Same logic for B. C appears 2 times, has a value of 200 in df_value, so its value is 200 / 2 = 100 D appears 4 times, has a value of 800 in df_value, so its value is 800 / 4 = 200

I'm pretty sure there's a really easy way to do that but I can't find it. Thanks in advance.

pandas dataframe python-3.x python
2 Answers

Use Series.map by Name column and Series from df_value and divide mapped values of Series.value_counts:

df['Value'] = (df['Name'].map(df_value.set_index('Name')['Value'])
               .div(df['Name'].map(df['Name'].value_counts())))
print (df)
  Name  Value
1    A   50.0
2    B  100.0
3    C  100.0
4    C  100.0
5    D  200.0
6    D  200.0
7    D  200.0
8    D  200.0

Another solution, thank you @sammywemmy is mapping by already divided values:

df1.assign(Value=df1.Name.map(df2.set_index("Name").Value.div(df1.Name.value_counts())))

Solution with merge is possible, also added anothe alternative for counts by GroupBy.transform:

df['Value'] = (df.merge(df_value, on='Name', how='left')['Value']
                 .div(df.groupby('Name')['Name'].transform('size')))
user_29010020

If it is important to keep existing dataframes as is and there is no restriction of using 2 lines of code:

df1 = df.merge(df_value, on='Name', how='left')
df1['Value'] = df1.groupby('Name')[['Value']].transform(lambda x: x/len(x))

Otherwise one liner solution that modifies existing 'df' a bit.

df['Value'] = df.merge(df_value, on='Name', how='left').groupby('Name')[['Value']].transform(lambda x: x/len(x))

Both give same output with different variable names:

  Name  Value
0    A   50.0
1    B  100.0
2    C  100.0
3    C  100.0
4    D  200.0
5    D  200.0
6    D  200.0
7    D  200.0
user_37869730
Related