PANDAS: int32 overflow? Can't bulid a pivot table

Question

I use the pd.pivot_table() method to create a user-item matrix by pivoting the user-item activity data. However, the dataframe is so large that I got complain like this:

Unstacked DataFrame is too big, causing int32 overflow

Any suggestions on solving this problem? Thanks!

r_matrix = df.pivot_table(values='rating', index='userId', columns='movieId')

Answer 1

Some Solutions:

• You can downgrade your pandas version to 0.21 which is no problem with pivot table with big size datas.
• You can set your data to dictionary format like df.groupby('EVENT_ID')['DIAGNOSIS'].apply(list).to_dict()

Answer 2

You can use groupby instead. Try this code:

reviews.groupby(['userId','movieId'])['rating'].max().unstack()

Answer 3

An integer overflow inside library code is nothing you can do much about. You have basically three options:

1. Change the input data you provide to the library so the overflow does not occur. You probably need to make the input smaller in some sense. If that does not help, you may be using the library in a wrong way or hit a bug in the library.
2. Use a different library (or none at all); it seems that the library you are using is not intended to operate on large input.
3. Modify the code of the library itself so it can handle your input. This may be hard to do, but if you submit a pull request to the library source code, many people will profit from it.

You don't provide much code, so I cannot tell what is the best solution for you.

Answer 4

If you want movieId as your columns, first sort the dataframe using movieId as the key.

Then divide (half) the dataframe such that each subset contains all the ratings for a particular movie.

subset1 = df[:n] 
subset2 = df[n:]

Now, apply to each of the subsets

matrix1 = subset1.pivot_table(values='rating', index='userId', columns='movieId')
matrix2 = subset2.pivot_table(values='rating', index='userId', columns='movieId')

Finally join matrix1 and matrix2 using,

complete_matrix = matrix1.join(matrix2)

On the other hand, if you want userId as your columns, sort the dataframe using userId as the key and repeat the above process.

***Please be sure to delete subset1, subset2, matrix1 & matrix2 after you're done or else you'll end up with Memory Error.