How to assign the average of a grouped dataframe to a column of another dataframe in python

user_11051594 0
Viewed 39

I have an original dataframe like the example below

data_original = {'website': ['a', 'b'], 'unit': ['finance', 'business']}
df_original = pd.DataFrame(data_original)

And it gave rise to the dataframe:

enter image description here

I have another dataframe, df with the following:

data = {'date': ['1/1/2021', '1/2/2021', '1/3/2021', '1/4/2021', '1/5/2021', '1/1/2021', '1/2/2021', '1/3/2021', '1/4/2021',
             '1/5/2021'], 'website': ['a', 'a', 'a', 'a', 'a', 'b', 'b', 'b', 'b', 'b'], 'amount_views': [23, 17, 10, 25, 2, 12, 7, 5, 17, 2]}

df = pd.DataFrame(data)

This gave rise to the following:

enter image description here

I want to get the average views of each website in the second dataframe and add it as a column in the original dataframe.

I already did this:

df_new = df.groupby(['website']).mean()
df_original['average_views'] = df_new

And I got this instead

enter image description here

How do I just get the average and add it to the original dataframe?

pandas dataframe python
2 Answers

You could use a groupby as you started doing, and merge the result back to your original dataframe:

pd.merge(df_original,
         df.groupby('website')['amount_views'].mean().to_frame('average_views'),
         left_on='website',right_index=True,how='left')

prints:

  website      unit  average_views
0       a   finance           15.4
1       b  business            8.6
user_91673820

You can also try:

df_new = df.groupby(['website']).mean()
df_original = df_original.set_index('website')
df_original['amount_views'] = df_new['amount_views']
print(df_original)

Your error is in this line:

df_original['average_views'] = df_new

The second line joins with indices. Since there are no indices in df_original which match indices in df_new, hence you got Nan.

You can either have common indices in both dataframes as I did above or match the website with website. I think mine is easier.

If the website as index bothers you, do this:

df_original = df_original.reset_index()

This will give back your original dataframe.

  website      unit  amount_views
0       a   finance          15.4
1       b  business           8.6
user_141861160
Related