Pivot and count simultaneously, pandas

Asked Jul 03 '19 at 13:29

Active Jul 03 '19 at 13:29

Viewed 12 times

Starting from a dataframe of the form

userId  | tag_id
----------------
12         2
34         2
44         2
43         2
12         5
44         5
43         5
12         8
43         8
34         8
34         8
...

How can I pivot this table such that each value of tag_id becomes a new column, the rows of the table are unique userIDs and the values on each tag_id column is the number of times that user appears with the corresponding tag. The end result, for the above case should be:

userId | 2 | 5 | 8 |
--------------------
12       1   1   1   
34       1   0   2
43       1   1   1
44       1   1   0

I have a rough idea on how pivoting works, but I can't figure out how to do the counts inside it.

asked Jul 03 '19 at 13:29

Qubix

4,161
7
36
73

3

`pd.crosstab(df.user_id,df.tag_id)` would do it – anky Jul 03 '19 at 13:30
1

See question 9 of the linked answer. – Erfan Jul 03 '19 at 13:31
@anky_91 that is perfect, would you mind writing this as an answer so I can accept it? Also, is there a way to avid getting a 2-level header? – Qubix Jul 03 '19 at 13:33
@Qubix `pd.crosstab(df.user_id,df.tag_id).reset_index().rename_axis(None,axis=1)` never mind, its a dupe. :) – anky Jul 03 '19 at 13:33
1

@anky_91 oh, now I finally saw it's a dupe. Anyway, thank you so much for your answer. :) – Qubix Jul 03 '19 at 13:34

Pivot and count simultaneously, pandas

0 Answers0