I have a table that looks like this:
| user id | observation |
|---|---|
| 25 | 2 |
| 25 | 3 |
| 25 | 2 |
| 23 | 1 |
| 23 | 3 |
the desired outcome is:
| user id | observation | retention |
|---|---|---|
| 25 | 7 | 3 |
| 23 | 4 | 2 |
I want to keep the user id column with unique ids and have another column showing how many times this id has appeared in the dataset summing up the observation column values.
any help will be appreciated thanks
Use groupby() method and chain agg() method to it:
outputdf=df.groupby('user id',as_index=False).agg(observation=('observation','sum'),retention=('observation','count'))
Now if you print outputdf you will get your desired output:
user id observation retention
0 23 4 2
1 25 7 3
You have to use group by:
import pandas as pd
d = {'user id': [25,25,25,33,33], 'observation': [2,3,2,1,3]}
# get the dataframe
df = pd.DataFrame(data=d)
df_new = df.groupby('user id').agg({"sum", "count"}).reset_index()
# rename the columns as you desire
df_new.columns = ['user id', 'observation', 'retention']
df_new
Output:
