How would I take a row-wise average values for certain columns, while retaining other in my dataframe?

user_10071640 0
Viewed 112

I'm trying to figure out how to take the averages of multiple rows (for a subset of columns), grouped by a user ID, while retaining other columns.

For example I have df:

df
| user_id |    date    | value1 | value2 | value3 | zipcode |
|---------|------------|--------|--------|--------|---------|
|  13579  | 2020-05-01 |   100  |  134   |   826  |  85001  |
|  13579  | 2020-05-02 |   234  |  153   |   725  |  85001  |
|  13579  | 2020-05-03 |   241  |  186   |   926  |  85001  |
|  24681  | 2020-05-01 |   246  |  135   |   257  |  60629  |
|  24681  | 2020-05-02 |   200  |  252   |   264  |  60629  |
|  24681  | 2020-05-03 |   210  |  134   |   302  |  60629  |

Ideally I would want the output to look like:

df_avg
| user_id | value1 | value2 | value3 | zipcode |
|---------|--------|--------|--------|---------|
|  13579  |  191.7 |  157.7 |  825.6 |  85001  |
|  24681  |  218.7 |  173.7 |  274.3 |  60629  |

As you can see, the columns value1, value2, and value3 would be averaged as new rows (or would these be considered series now that they're a new df?), grouped by user_id, and still have classifier variables like zipcode.

Originally, I thought something like df_avg = df.loc[:, 'value1':'value3'].groupby('user_id').mean() would work, but after running it and getting a KeyError, its obvious that my code doesn't include the user_id to actually group by.

I'm still relatively new to Pandas, and python as a whole and I'm a bit stuck from here. Would a lambda function be best for this kind of task?

Thanks in advance!

Edit: Looking more so how to do this conceptually, not necessarily how to perform the operation on this DF specifically - this is just dummy data. I will be scaling this up to a much larger dataframe.

pandas-groupby pandas python
2 Answers

you can do it with grouby.agg and use first on the column zipcode

print (df.groupby('user_id').agg(value1=('value1', 'mean'), 
                                 value2=('value2', 'mean'), 
                                 value3=('value3', 'mean'), 
                                 zipcode=('zipcode', 'first'))
         .reset_index())
   user_id      value1      value2      value3  zipcode
0    13579  191.666667  157.666667  825.666667    85001
1    24681  218.666667  173.666667  274.333333    60629
user_92747320

We can just do 

df=df.drop('date',axis=1).groupby('user_id').mean()
user_79645270
Related