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