Pandas dataframe combine rows representing the same entity

Question

I have data that contains several rows for each employee. Each row contains one attribute and its value. For example:

Worker ID	Last Name	First Name	Metric Name	Metric Value
1	Hanson	Scott	Attendance	98
1	Hanson	Scott	On time	35
2	Avery	Kara	Attendance	95
2	Avery	Kara	On time	57

I would like to combine rows based on worker id, taking metrics to their own columns like so:

Worker ID	Last Name	First Name	Attendance	On time
1	Hanson	Scott	98	35
2	Avery	Kara	95	57

I can do worker_data.pivot_table(values='Metric Value', index='Worker ID', columns=['Metric Name']), but that does not give me the first and last names as columns. What is the best Pandas way to merge these rows?

score 1 · Accepted Answer · answered Jul 08 '22 at 07:06

In your solution change index parameter by list and for avoid MultiIndex remove [] from column parameter:

df = (worker_data.pivot_table(index=['Worker ID','Last Name','First Name'], 
                              columns='Metric Name',
                              values='Metric Value')
                 .reset_index()
                 .rename_axis(None, axis=1))

Pandas dataframe combine rows representing the same entity

1 Answers1