How to make a column count by name and total amount of dataframe pandas?-CodePudding

I have the following dataframe in pandas:

import pandas as pd

df = pd.DataFrame({'Sensor': ['strain', 'water', 'water', 'ultrassonic', 'strain'], 
           'Total': [1,10,20,5,9],
           'Columns_3': ['A', 'B', 'C', 'D', 'E']})

print(df)
Sensor     Total    Columns_3
strain      1      A
water       10     B
water       20     C
ultrassonic 5      D
strain      9      E

I'd like to do a count of the total amount of different sensors. So I tried to use groupby() as follows:

df_result = df.groupby(['Sensor', 'Total'])['Total'].sum()

The output of my code is:

  Sensor       Total
  strain       1         1
               9         9
  ultrassonic  5         5
  water        10       10
               20       20
  Name: Total, dtype: int64

However, I would like the output to be a dataframe (df_result) as follows:

print(df_result)

Sensor  Total_final
strain      10     
water       30        
ultrassonic 5

CodePudding user response：

you don't need total as part of the group by

df.groupby(['Sensor'])['Total'].sum()

Sensor
strain         10
ultrassonic     5
water          30
Name: Total, dtype: int64

CodePudding user response：

You can just groupby with Sensor only

 df_result = df.groupby(['Sensor'])['Total'].sum().reset_index()