How to convert a Dataframe into a pivot table

Question

I want to create a pivot table with users(rows), items(columns) and ratings (values). There are users that have rated many different items. I extract the data from a json file and when creating the pivot, I don't want to aggregate them.

import pandas as pd
with open('data.json', 'r') as file:
    lines = file.readlines()
    for line in lines:
    user = splitted[0]
    ratings = splitted[1]
    item = splitted[4]
    users.append(user)
    items.append(item)
    ratings.append(ratings)

df = pd.DataFrame({'Users': users,
                    'Items': items,
                    'Ratings': ratings})

The output of the dataframe is:

User  Item  Ratings

anna   A    2

anna   B    4

anna   C    3

chris  A    2

bob    D    1

And i want my pivot table to be like this:

       A  B  C  D

anna   2  4  3  -
chris  2  -  -  -
bob    -  -  -  1

When I run this, there is an error: "no numeric types to aggregate"

pivot = df.pivot_table(values = 'Ratings', index = 'Users', columns = 'Items')

And when running this, the error is: "index 502966764 is out of bounds for axis 0 with size 502966644"

pivot = df.pivot(values = 'Ratings', index = 'Users', columns = 'Items')

Could you give me any idea? Thank you in advance!

Check this out: https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.pivot.html — some_programmer, Jul 03 '19 at 20:05
@QuangHoang It doesn't run...There is a message error: "No numeric types to aggregate" — Christina, Jul 03 '19 at 20:10
@Junkrat I have already checked it out but i didn't find the answer — Christina, Jul 03 '19 at 20:39
With your example dataset, this gives me the correct result `df.pivot(index='User', columns='Item')`. For that error do the following first `df['Ratings'] = pd.to_numeric(df['Ratings'])` — Erfan, Jul 03 '19 at 20:43
Another way that does not require numeric data: `df.set_index(['User', 'Item']).Ratings.unstack(level=-1)`. — Quang Hoang, Jul 03 '19 at 21:04
@QuangHoang Therefore, there is no need for using df.pivot or df.pivot_table? — Christina, Jul 04 '19 at 08:31

How to convert a Dataframe into a pivot table

0 Answers0