how to transpose a table by a column with group by pandas

I have a table that I need to transpose, and it looks like this:


| User ID| Week | clicks | days|
|------- |------| -------|-----|
| x      | 1    | 1      | 1   | 
| x      | 2    | 2      | 3   |
| y      | 1    | 3      | 2   | 
| y      | 2    | 3      | 2   |
| y      | 3    | 4      | 3   | 
  

The output I need looks like this:

| User ID| clicks week 1| clicks week 2| clicks week 3| days week 1| days week 2| days week 3|
|--------|--------------| -------------|------------- |------------| -----------|------------|
| x      | 1            | 2            | 0            | 1          | 3          | 0          |
| y      | 3            | 3            | 4            | 2          | 2          | 3          |

For each user I need a single row and for each category I need a column for each week (if there’s no activity for user in a week, write 0).

Any idea how to do it?

Thanks!

>Solution :

You can use .pivot() with .fillna():

df.pivot(index="User ID", columns="Week", values=["clicks", "days"]).fillna(0)

This outputs:

          clicks                    days
Week       1        2        3     1     2     3
User ID
x        1        2              0  1     3        0
y        3        3        4        2     2     3

Leave a Reply