So I have a df with department search_term sfr and every 3 lines are identical
What I'm looking to do is:
unmelt this df , to the output at the bottom
where click_share_rank is the anchor
| | Unnamed: 0 | department | search_term | sfr | asin | clicked_item | click_share_rank | click_share | conversion_share |
|------:|-------------:|:--------------------------|-------------------------------:|:------|:-----------|-------------:|-------------------:|--------------:|-------------------:|
| 0 | 0 | Home and Garden | air fryer | 1 | B07FDJMC9Q | Product A | 1 | 0.0647 | 0.0737 |
| 1 | 1 | Home and Garden | air fryer | 1 | B07GJBBGHG | Product B | 2 | 0.0499 | 0.0569 |
| 2 | 2 | Home and Garden | air fryer | 1 | B0936FGLQS | Product C | 3 | 0.0494 | 0.0628 |
| 6 | 6 | Camera and Photo | surveillance & security cameras| 1 | B086DKSHQ4 | Product D | 1 | 0.1237 | 0.0541 |
| 7 | 7 | Camera and Photo | surveillance & security cameras| 1 | B086DL32R3 | Product E | 2 | 0.1207 | 0.0811 |
| 8 | 8 | Camera and Photo | surveillance & security cameras| 1 | B086DKGCFP | Product F | 3 | 0.1097 | 0.0405 |
Output
Notice that department search_term sfr is like pivoted, asin clicked_item click_share_rank click_share conversion_share is reshaped from long to wide
| | Unnamed: 0 | department | search_term | sfr | #1 asin | #1 clicked_item | #1 click_share_rank | #1 click_share | #1 conversion_share | #2 asin | #2 clicked_item | #2 click_share_rank | #2 click_share | #2 conversion_share || #3 asin | #3 clicked_item | #3 click_share_rank | #3 click_share | #3 conversion_share |
|------:|-------------:|:--------------------------|-------------------------------:|:------|:-----------|-------------:|-------------------:|--------------:|-------------------:|:-----------|-------------:|-------------------:|--------------:|-------------------:|:-----------|-------------:|-------------------:|--------------:|-------------------:|
| 0 | 0 | Home and Garden | air fryer | 1 | B07FDJMC9Q | Product A | 1 | 0.0647 | 0.0737 | B07GJBBGHG | Product B | 2 | 0.0499 | 0.0569 | B0936FGLQS | Product C | 3 | 0.0494 | 0.0628 |
| 1 | 1 | Camera and Photo | surveillance & security cameras| 1 | B086DKSHQ4 | Product D | 1 | 0.1237 | 0.0541 |B086DL32R3 | Product E | 2 | 0.1207 | 0.0811 |B086DKGCFP | Product F | 3 | 0.1097 | 0.0405 |
I tried pivot_table, but couldn't achieve this output, Any guide of the method to use would be highly appreciated Note: it's a 14m rows df
I have tried
pd.pivot_table(tempo, index=['department','search_term'])
This would solve the first part, however, I can't use the remaining columns as "columns" or "values" else it will get everything screwed, didn't get any other idea so far, only thought of pivot and it wasn't the correct path
Source file: github.com/BeboGhattas/temp-repo/blob/main/sourcefile.csv
Output format: github.com/BeboGhattas/temp-repo/blob/main/desired-output.csv