Back

Explore Courses Blog Tutorials Interview Questions
0 votes
2 views
in Data Science by (18.4k points)
edited by

I am trying to transform the data using pivot_table using row-wise shape to column-wise shape based on these ITEMID values:

import pandas as pd

df = pd.DataFrame({'id': [36, 36, 36, 36, 36],

               'VALUE': [86, 21, 85, 19, 87],

               'ITEMID': [220045, 220210, 220045, 220210, 220045],

               'TIME': [pd.to_datetime('2134-05-12 13:00:00'),

                             pd.to_datetime('2134-05-12 13:00:00'),

                             pd.to_datetime('2134-05-12 14:00:00'),

                             pd.to_datetime('2134-05-12 14:00:00'),

                             pd.to_datetime('2134-05-12 15:00:00')]})

if __name__ == '__main__':

    print(df.head())

    df = df.pivot_table(index=['id', 'TIME'],

                        columns='ITEMID',

                        values='VALUE', aggfunc='sum').reset_index()

    print(df)

but I am getting the below unexpected results:

|ITEMID|id |TIME                |220045  |220210  | 

|------|---|--------------------|--------|--------|

|0     |36 |2134-05-12 13:00:00 |86.0    |21.0    |

|1     |36 |2134-05-12 14:00:00 |85.0    |19.0    |

|2     |36 |2134-05-12 15:00:00 |87.0    |NaN     |

whereas the columns should be like

|id |TIME |220045 |220210 |220045 |220210|220045 |

|---|-----|-------|-------|-------|------|-------| 

I have a index like column for the ITEMID and some in the ITEMIDs are transformed into a column-wise shape. I don't know what is causing the result.

1 Answer

0 votes
by (36.8k points)

Since the ITEMID is passed as columns to the pivot_table, yorresulting dataframe has the non-null name for your columns. Try rename_axis:

(df.pivot_table(index=['id', 'TIME'],

                        columns='ITEMID',

                        values='VALUE', aggfunc='sum').reset_index()

  .rename_axis(columns=None))

Output:

   id                TIME  220045  220210

0  36 2134-05-12 13:00:00    86.0    21.0

1  36 2134-05-12 14:00:00    85.0    19.0

2  36 2134-05-12 15:00:00    87.0     NaN

If you want to know more about the Data Science then do check out the following Data Science which will help you in understanding Data Science from scratch 

Related questions

0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
0 votes
1 answer

Browse Categories

...