CODEWITHSUNDEEP
pandaspandas-groupby
theSekyi
theSekyi

Reputation: 530

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

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?

Upvotes: 1

Views: 58

Answers (1)

jezrael
jezrael

Reputation: 862641

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)

Upvotes: 1

Related Questions