CODEWITHSUNDEEP
pythonpandas
Cybernetic
Cybernetic

Reputation: 13334

Pivot table with duplicate indexes and without an aggregation function in Pandas

I have a dataframe:

    device_id   timestamp             metric_id    value
0   device_1    2020-12-04 05:15:00   cpu_5min      116
1   device_1    2020-12-04 05:30:00   cpu_5min      213
2   device_1    2020-12-04 05:35:00   cpu_5min      427
3   device_1    2020-12-04 05:15:00   vol_max       734
4   device_1    2020-12-04 05:30:00   vol_max       325
5   device_1    2020-12-04 05:35:00   vol_max       668
6   device_2    2020-12-04 05:15:00   cpu_5min      540
7   device_2    2020-12-04 05:30:00   cpu_5min      127
8   device_2    2020-12-04 05:35:00   cpu_5min      654

I need to pivot this table to look like this:

    device_id   timestamp              cpu_5min   vol_max
0   device_1    2020-12-04 05:15:00      116        734
1   device_1    2020-12-04 05:30:00      213        325
2   device_1    2020-12-04 05:35:00      427        668
3   device_2    2020-12-04 05:15:00      540        NA
4   device_2    2020-12-04 05:30:00      127        NA
5   device_2    2020-12-04 05:35:00      654        NA

So all unique metric_id get pivoted across as new headers, with their respective values. If no value is present an NA is placed there.

I have looked at the other pivot answers on SO but they all seem to assume an aggregation function and/or non-duplicate indexes.

Upvotes: 1

Views: 626

Answers (1)

wwnde
wwnde

Reputation: 26676

Please Try

pd.pivot_table(df, index=['device_id','timestamp'], columns=['metric_id']).droplevel(0, axis=1).reset_index()

Upvotes: 1

Related Questions