CODEWITHSUNDEEP
pythonpandasgroup-by
Nithin Nampoothiry
Nithin Nampoothiry

Reputation: 199

Mixing aggregation and group by in pandas

What I have is a data set called 'report' which has details of delivery drivers. 'Pass' means they delivered on time and 'Fail' means they didn't

Name|Outcome
A   |Pass
B   |Fail
C   |Pass
D   |Pass
A   |Fail
C   |Pass

What I want

Name|Pass|Fail|Total
A   |1   |1   |2
B   |0   |1   |1
C   |2   |0   |2
D   |1   |0   |1

I tried:

report.groupby(['Name','outcome']).agg(['count'])

but it is not giving me the required output.

Upvotes: 2

Views: 288

Answers (4)

Sociopath
Sociopath

Reputation: 13401

One way to do it using pandas.dummies and groupby :

report = pd.get_dummies(df1, columns=['outcome']).groupby(['name'], as_index=False).sum().rename(columns={"outcome_Fail":"Fail", "outcome_Pass":"Pass"})

report["Total"] = report["Pass"] + report["Fail"]

print(report)

Output:

    name Fail Pass Total
0   A     1    1    2
1   B     1    0    1
2   C     0    2    2
3   D     0    1    1

Upvotes: 0

jezrael
jezrael

Reputation: 862791

Use crosstab with margins=True and margins_name parameter:

print (pd.crosstab(df['Name'], df['Outcome'], margins=True, margins_name='Total'))
Outcome  Fail  Pass  Total
Name                      
A           1     1      2
B           1     0      1
C           0     2      2
D           0     1      1
Total       2     4      6

And then remove last row with positions by DataFrame.iloc:

df = pd.crosstab(df['Name'], df['Outcome'], margins=True, margins_name='Total').iloc[:-1]
print (df)
Outcome  Fail  Pass  Total
Name                      
A           1     1      2
B           1     0      1
C           0     2      2
D           0     1      1

Upvotes: 6

Erfan
Erfan

Reputation: 42916

This is pd.crosstab with sum over axis=1:

df = pd.crosstab(df['Name'], df['Outcome'])
df['Total'] = df[['Fail', 'Pass']].sum(axis=1)

Outcome  Fail  Pass  Total
Name                      
A           1     1      2
B           1     0      1
C           0     2      2
D           0     1      1

Or to remove the column axis name, we use rename_axis:

df = pd.crosstab(df['Name'], df['Outcome']).reset_index().rename_axis(None, axis='columns')
df['Total'] = df[['Fail', 'Pass']].sum(axis=1)

  Name  Fail  Pass  Total
0    A     1     1      2
1    B     1     0      1
2    C     0     2      2
3    D     0     1      1

Upvotes: 5

Horace
Horace

Reputation: 1054

In [1]: from io import StringIO

In [2]: df_string = '''Name|Outcome^M
   ...: A   |Pass^M
   ...: B   |Fail^M
   ...: C   |Pass^M
   ...: D   |Pass^M
   ...: A   |Fail^M
   ...: C   |Pass'''


In [3]: report = pd.read_csv(StringIO(df_string), sep='|')

In [4]: report.assign(count=1).groupby(["Name", "Outcome"])["count"].sum().unstack().assign(Total=lambda df: df.sum(axis=1))
Out[4]:
Outcome  Fail  Pass  Total
Name
A         1.0   1.0    2.0
B         1.0   NaN    1.0
C         NaN   2.0    2.0
D         NaN   1.0    1.0

Now you can fill NAs values using the fillna(0) method

Upvotes: 1

Related Questions