I am attempting to convert a long data frame to a wide data frame but can't seem to get anything to work the way I need it to. I've tried using transpose, melt, pivot, but can't get the syntax right. Please let me know if you need more information. Any help you could provide would be much appreciated!
Details:
Orignal Long Table:
| Emp_ID | attempt | results | name | date |
|--------|---------|---------|------|---------|
| 123 |1 |fail |John |1/21/2019|
| 123 |2 |pass |John |1/21/2019|
| 145 |1 |fail |Todd |2/04/2019|
| 145 |2 |fail |Todd |2/05/2019|
| 145 |3 |pass |Todd |2/05/2019|
| 656 |1 |pass |Sarah |3/02/2019|
| 767 |1 |pass |Jim |3/14/2019|
| 3453 |1 |fail |Rose |3/15/2019|
| 3453 |2 |pass |Rose |3/15/2019|
New Wide Table: What I need it to look like (I only included 3 records because it took FOREVER to write that out, but ideally I need all records from the original table in the new format):
| Emp_ID | attempt1_results | attempt1_name | attempt1_date | attempt2_results | attempt2_name | attempt2_ date | attempt3_results | attempt3_name | attempt3_date |
|--------|------------------|---------------|---------------|------------------|---------------|----------------|------------------|---------------|---------------|
| 123 |fail |John |1/21/2019 |pass |John |1/21/2019 | | | |
| 145 |fail |Todd |2/04/2019 |fail |Todd |2/05/2019 |pass |Todd |2/05/2019 |
| 656 |pass |Sarah |3/02/2019 | | | | | | |
Code: I've tried a few different things but as I said, I can't get them to work:
Data: df = pd.read_csv(self.results_file_path)
Melt:
df1 = df(id_vars=['Emp_ID'], value_vars=list(df.columns), var_name=None, col_level=0)
Unstack:
d1 = df.set_index(['Emp_Id', 'attempt', 'results', 'name', 'date']).unstack()
d1.columns = d1.columns.map(lambda x: '{}attempt_{}'.format(*x))
d1.reset_index(inplace=True)
Pivot
d2 = df.pivot(index='Emp_ID', columns=('attempt', 'results', 'name', 'date'))
