I am trying to figure out how to group specific tag names in a column and then to calculate the mean of the raw data that have the same time. My dataframe looks something like this except with 10000+ rows:
tag_name time raw_data
happy 5 300
8 340
angry 5 315
8 349
sad 5 400
8 480
etc.
.
.
I wish to keep the mean in the dataframe table, but I can't figure out how. I found that I can create a pivot table to calculate the mean, but I can't figure out how to single out the specific tag names I want. Below is what I have so far:
output = pd.pivot_table(data=dataset,index=['Timestamp'],columns=['Tag_Name'],values='Raw_Data',aggfunc='mean')
I am trying to get one of these outputs when I calculate the average of sad and happy:
1. optimal output:
tag_name time raw_data sad_happy_avg
happy 5 300 350
8 340 410
sad 5 400
8 480
angry 5 315
8 349
2. alright output:
tag_name happy sad avg
time
5 300 400 350
8 340 480 410
Try as follows:
Series.isin to keep only "happy" and "sad", and apply df.pivot to get the data in the correct shape.mean on axis=1:res = df[df.tag_name.isin(['happy','sad'])].pivot(index='time', columns='tag_name',
values='raw_data')
res['avg'] = res.mean(axis=1)
print(res)
tag_name happy sad avg
time
5 300 400 350.0
8 340 480 410.0
Your "optimal" output doesn't seem a very logical way to present/store this data, but you can achieve it as follows:
# assuming you have a "standard" index starting `0,1,2` etc.
df['sad_happy_avg'] = df[df.tag_name.isin(['happy','sad'])]\
.groupby('time')['raw_data'].mean().reset_index(drop=True)
print(df)
tag_name time raw_data sad_happy_avg
0 happy 5 300 350.0
1 happy 8 340 410.0
2 angry 5 315 NaN
3 angry 8 349 NaN
4 sad 5 400 NaN
5 sad 8 480 NaN
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments