Summing unique values via groupby function

user_19647583 0
Viewed 56

I am new to python and coding. I was doing a data analysis on a data set that resembles following set up.

    EID     Hours   Block  Participant
0   5443    1.0     STG.       x
1   5443    1.0     STG.       y
2   5402    3.5     BZP.       a
3   5223    1.0     STG.       z

I want the output table as:

Block  Unique count.  Unique sum
          EID       
BZP        1            3.5
STG        2            2.0

I am trying to use following code but not getting desired output

abc = df.groupby("Block").agg({'EID':['nunique'],'Hours':lambda x: sum(range(int(x.max())))})

Please help

pandas unique group-by sum python
3 Answers

Try this :

df = df.drop_duplicates(subset=['EID']).groupby('Block').agg({'EID': 'count', 'Hours': 'sum'})

result :

       EID  Hours
Block
BZP.     1    3.5
STG.     2    2.0
user_167126660

Sample:

df = pd.DataFrame({
    "EID" : [5443, 5443, 5402, 5223],
    "Hours": [1.0, 1.0, 3.5, 1.0],
    "Block": ["STG.", "STG.", "BZP.", "STG."],
    
})

df.groupby("Block").agg({"EID": "nunique", "Hours": "sum"})

result

    EID Hours
Block       
BZP.    1   3.5
STG.    2   3.0

can you please explain what you want to do with the hours column ? how the output is 2.0 ?

I assume you want to find the max and then sum up the hours. if that is the case

df.groupby(["Block", "EID"])["Hours"].max().reset_index().groupby("Block").agg({"EID" : "nunique", "Hours": "sum"}).reset_index()

output

    Block   EID Hours
0   BZP.    1   3.5
1   STG.    2   2.0
user_12504630

This also works

df.groupby('Block').agg({'EID': pd.Series.nunique, 'Hours': pd.Series.sum})
user_192557490
Related