CODEWITHSUNDEEP
pythonpandas
Big Secret
Big Secret

Reputation: 47

Python Pandas - Listing Specific Column Items Horizontally

Search yielded no results, so here I am! Using Python + Pandas, I am able to import a spreadsheet. Is there a way to reformat my table from looking like this:

Name    | ID  | Shirt Color
Jim     | 001 | White
Michael | 002 | Green
Michael | 002 | Blue
Pam     | 003 | Pink
Jim     | 001 | Orange
Jim     | 001 | White (notice the duplicate counted separately)

into this?

Name    | ID  | Shirt Color 1 | Shirt Color 2 | Shirt Color 3 |
Jim     | 001 |     White     |   Orange      |     White     |
Michael | 002 |     Green     |   Blue        |               |
Pam     | 003 |     Pink      |               |               |

Im really lost as to what to call upon with Pandas to make this happen. Any help would be fantastic! I appreciate you!

I was able to use nunique to get the "count" of different shirts worn but not what I'm looking for.

Upvotes: 0

Views: 88

Answers (1)

BENY
BENY

Reputation: 323226

We need create a additional key with cumcount , then it become a pivot problem

df['key']=df.groupby('Name').cumcount()+1
s=df.pivot_table(index=['Name','ID'],columns='key',values='Shirt Color',aggfunc='first').\
       add_prefix('ShirtColor_').reset_index()

key     Name  ID ShirtColor_1 ShirtColor_2 ShirtColor_3
0        Jim   1        White       Orange        White
1    Michael   2        Green         Blue          NaN
2        Pam   3         Pink          NaN          NaN

Upvotes: 1

Related Questions