python/pandas - counting unique values in a single DataFrame column and displaying counts as new columns

Question

I am starting with data of city transits with an additional column containing the mode of transportation

Orig   Dest    Type
NY     SF      Train
NY     SF      Plane
NO     NY      Plane
SE     NO      Plane
SE     NO      Train

I want to aggregate it such that each unique value in Type becomes a column with counts of that Type for each unique Orig/Dest pair

Orig  Dest  Plane  Train
NY    SF    1      1
NO    NY    1      0
SE    NO    1      1

I know some basic aggregation using pd.groupby but can only aggregate so far as to get just basic counts of the Orig/Dest pairs using:

df.groubpy(['Orig','Dest'])['Type'].count()

jezrael · Accepted Answer · 2016-08-17 09:31:43Z

2

You can use nunique and unstack. Last reset_index and rename_axis (new in pandas 0.18.0):

print (df.groupby(['Orig','Dest', 'Type'])['Type']
         .nunique()
         .unstack()
         .fillna(0)
         .astype(int)
         .reset_index()
         .rename_axis(None, axis=1))

  Orig Dest  Plane  Train
0   NO   NY      1      0
1   NY   SF      1      1
2   SE   NO      1      1

edited Aug 17, 2016 at 9:31

answered Aug 17, 2016 at 9:19

jezrael

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

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

1 Comment

Ben Romano Over a year ago

Thanks, this was exactly what I was looking for

Collectives™ on Stack Overflow

python/pandas - counting unique values in a single DataFrame column and displaying counts as new columns

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related