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

Question

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!

I think you are looking for pivot – pavi2410 Jan 15 '23 at 18:07 — pavi2410, Jan 15 '23 at 18:07
Any Idea how to write the syntax? – kri Jan 15 '23 at 18:08 — kri, Jan 15 '23 at 18:08

score 1 · Accepted Answer · answered Jan 15 '23 at 18:11

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

score 1 · Answer 2 · answered Jan 15 '23 at 18:23

1

You can try pandas pivot method:

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

         clicks         days
Week    1   2   3   1   2   3
User ID                     
x       1.0 2.0 0.0 1.0 3.0 0.0
y       3.0 3.0 4.0 2.0 2.0 3.0

answered Jan 15 '23 at 18:23

Adnan Shaikh

24
3

Same answer as already given by @BrokenBenchmark – user19077881 Jan 15 '23 at 18:26

score 0 · Answer 3 · answered Jan 15 '23 at 18:29

You can do it multiple ways. no 1. You can use .pivot and assign index='User ID', columns='Week', values=['clicks', "days"]

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

no 2. You can use groupby + unstack, if aggregations is required. groupby method is used to group the data by columns, and the unstack is used to pivot the data.

df.groupby(['User ID', 'Week'], sort=False).sum().unstack()

No 3 Another solution is using set_index + unstack

df.set_index(['User ID', 'Week']).unstack()

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

3 Answers3