Creating a new columns from the results of groupby from another column

Question

I want to create new columns out of the unique values of one column with the count of the unique values as values assigned in the row.

df = pd.DataFrame([["a",20],["a", 10],["b", 5],["c",10],
    ["b", 10],["a", 5],["c",5],["c",5]],
columns=["alp","min"])

In [4]: df                                                                      
Out[4]: 
  alp  min
0   a   20
1   a   10
2   b    5
3   c   10
4   b   10
5   a    5
6   c    5
7   c    5

I tried using groupby to get the values I want

In [8]: df.groupby('alp')['min'].count()                                        
Out[8]: 
alp
a    3
b    2
c    3
Name: min, dtype: int64

Now, I want to create columns out of that output.

   count_a count_b count_c
0    3        2      3

Is there any function to achieve this in pandas?

jezrael · Accepted Answer · 2019-08-28 08:44:07Z

1

Remove Series name by Series.rename_axis, convert to one column DataFrame by Series.to_frame, transpose by DataFrame.T and last DataFrame.add_prefix:

df = df.groupby('alp')['min'].count().rename_axis(None).to_frame(0).T.add_prefix('count_')
print (df)

   count_a  count_b  count_c
0        3        2        3

Or create DataFrame per constructor:

s = df.groupby('alp')['min'].count()

df = pd.DataFrame([s.values], columns='count_' + s.index.values)

edited Aug 28, 2019 at 8:44

answered Aug 28, 2019 at 8:42

jezrael

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

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

2 Comments

theSekyi Over a year ago

This works! Thanks! What does rename_axis(None) do?

jezrael Over a year ago

@theSekyi - it remove alp string

Collectives™ on Stack Overflow

Creating a new columns from the results of groupby from another column

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related