Python3.7 Pandas1.0.1 Dataframe - Calculate sum of column within a range and regroup as one new row?

user_7323673 0
Viewed 49

My first question on StackOverflow. Please be good to me :)

Hello, I just started a small project on data science and I wanted to ultimately create a pie chart via matplot showing the percentage of device model on the site's overall traffic (i.e. 30% iPhone, 20% iPad, 10% Mac, etc.).

useragent count
iPhone    11298
Mac        3206
iPad        627
SM-N960F    433
SM-N950F    430
...         ...
K330          1
K220          1
SM-J737P      1
SM-J737T1     1
0PFJ50        1
[1991 rows x 2 columns]

From the screenshot, there are 1,991 records. I am preparing the data for plotting and I want to only display the top 5 useragents (top 4 being the devices and the top 5 will be labeled as others and the sum of the remaining items).

The expected output is like this:

useragent count
iPhone    11298
Mac        3206
iPad        627
SM-N960F    433
Others     9000

Thank you so much!

pandas-groupby pandas dataframe python
2 Answers

Use:

#first sorting data if necessary
df1 = df.sort_values('count', ascending=False)

#then get top 4 rows
df2 = df1.head(4)
#filter column `count` for all values after 4 rows
summed = df1.loc[df1.index[4:], 'count'].sum()

#create DataFrame by another counts
df3 = pd.DataFrame({'useragent':['Other'], 'count':[summed]})

#join together
df4 = pd.concat([df2, df3], sort=False, ignore_index=True)
print (df4)
  useragent  count
0    iPhone  11298
1       Mac   3206
2      iPad    627
3  SM-N960F    433
4     Other    435

EDIT:

#filter by threshold
mask = df['count'] > 500
#filtered rows by boolean indexing
df2 = df[mask]
#inverted mask - sum by count
summed = df.loc[~mask, 'count'].sum()
#same like above
df3 = pd.DataFrame({'useragent':['Other'], 'count':[summed]})

df5 = pd.concat([df2, df3], sort=False, ignore_index=True)
print (df5)
  useragent  count
0    iPhone  11298
1       Mac   3206
2      iPad    627
3     Other    868
user_29010020

You can try something like:

# sort dataframe
df.sort_values(by=['count'], inplace=True)
# recreate the index of your rows to make sure that 0 corresponds to the one with the higher count
df.reset_index(drop=True, inplace=True)
# add your new row to your dataset
df.append({'useragent': 'Others', 'count': df.loc[5:]['count'].cumsum()}, inplace=True)
# drop the rows you don't need anymore
df.drop([5:len(df.index.values.tolist())-1], inplace=True)

I am not entirely sure though, but it's worth a try. I hope it will at list give you some ideas.

user_121531880
Related