Groupby a column in Dataframe and create another dataframe with grouped data

Question

I have a dataframe like the following:

data:

    items   status
0   jet     fail
1   car     fail
2   car     pass
3   bike    fail
4   car     fail
5   jet     fail
6   bike    pass
7   jet     fail
8   jet     fail
9   bike    pass

I want to group the data by items and create a new dataframe with the counts of each value. Expected output:

df:
  unique  count  pass  fail
0    jet      4     0     4
1    car      3     1     2
2   bike      3     2     1

Now one method would be to get a list of unique items and loop on it to find the count, pass and fail and then combine these lists to a dataframe But how can I do that efficiently ?

jezrael · Accepted Answer · 2020-08-31 12:06:32Z

3

Use crosstab with DataFrame.rename_axis for new index name, then add new column for 0 position by DataFrame.insert and last convert index to column by DataFrame.reset_index:

df = pd.crosstab(df['items'], df['status']).rename_axis(columns=None, index='unique')
df.insert(0, 'count', df.sum(axis=1))
df = df.reset_index()
print (df)
  unique  count  fail  pass
0   bike      3     1     2
1    car      3     2     1
2    jet      4     4     0

If count should be last column is possible use margin parameter and remove last row:

df = (pd.crosstab(df['items'], df['status'], 
                  margins=True, 
                  margins_name='count')
       .rename_axis(columns=None, index='unique')
       .iloc[:-1]
       .reset_index())
print (df)
  unique  fail  pass  count
0   bike     1     2      3
1    car     2     1      3
2    jet     4     0      4

edited Aug 31, 2020 at 12:06

answered Aug 31, 2020 at 11:59

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

sammywemmy · Accepted Answer · 2020-08-31 12:08:48Z

0

You could get the values separately and combine with pd.concat :

A = df.groupby("items").size().rename("count")
A
items
bike    3
car     3
jet     4
Name: count, dtype: int64

B = (
    df.groupby(["items", "status"])
    .size()
    .unstack(fill_value=0)
    .rename_axis(columns=None)
)
B
      fail  pass
items       
bike    1   2
car     2   1
jet     4   0


pd.concat((A, B), axis=1).reset_index()

   items    count   fail    pass
0   bike    3        1      2
1   car     3        2      1
2   jet     4        4      0

answered Aug 31, 2020 at 12:08

sammywemmy

28.9k4 gold badges21 silver badges35 bronze badges

Collectives™ on Stack Overflow

Groupby a column in Dataframe and create another dataframe with grouped data

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related