Pandas unstack() and pivot(): MemoryError

user_6273503 0
Viewed 409

Problem description

I'd like to unstack or pivot a DataFrame, but it raises the numpy exception MemoryError: Unable to allocate 1.72 GiB for an array with shape (1844040704,) and data type bool. I have tried this with a DataFrame with a numerical index -> df.pivot() and with a Multiindex -> df.unstack() ]. Both show the same exception and I don't know a way around. I don't feel like I have an exceptionally large dataset with 175199 rows. I have previously used unstack on DataFrames with more than 5mio rows. The df will even become 2 x larger for the complete analysis!

I try to unstack with df_unstacked = df.unstack(level=0)

Additional info

Befor pivot / unstack, I had to add an unique index with df['row_num'] = np.arange(len(df)), because the dataset contains (wanted) duplicate index entries. Thats due to daylight saving time, where one day in octobre has 25 hours. The 2nd hour is duplicated.

I work with Jupyterlab from a virtualenv with python 3.7.

Package versions:

  • pandas==1.1.2
  • numpy==1.19.2
  • jupyterlab==2.2.8

Example data

                                    value
target_frame        row_num year         
2017-01-01 01:00:00 0       2016  10,3706
2017-01-01 01:15:00 1       2016  27,2456
2017-01-01 01:30:00 2       2016  20,4022
2017-01-01 01:45:00 3       2016  14,4911
2017-01-01 02:00:00 4       2016  14,2611
...                                   ...
2017-12-31 23:45:00 175195  2020  30,7177
2017-01-01 00:00:00 175196  2020  21,4708
2017-01-01 00:15:00 175197  2020  44,9192
2017-01-01 00:30:00 175198  2020  37,8560
2017-01-01 00:45:00 175199  2020  30,9901

[175200 rows x 1 columns]

Desired result

The index will contain duplicates. For the record, i don't care if it's an index or a regular column.

                     value
year                 2016     2017  ...  2020
target_frame                  
2017-01-01 01:00:00  10,3706  11    ...  32
2017-01-01 01:15:00  27,2456  12    ...  32
2017-01-01 01:30:00  20,4022  13    ...  541
2017-01-01 01:45:00  14,4911  51    ...  123
2017-01-01 02:00:00  14,2611  56    ...  12
...                                   ...
2017-12-31 23:45:00  30,7177  12    ...  12
2017-01-01 00:00:00  21,4708  21    ...  12
2017-01-01 00:15:00  44,9192  21    ...  13
2017-01-01 00:30:00  37,8560  21    ...  11
2017-01-01 00:45:00  30,9901  12    ...  10

[35040 rows x 5 columns]
pandas out-of-memory python-3.x python
2 Answers

I will try to help you by addressing the issue of lack of memory, and a way to deal with it.

As your data already has almost 2 billion records, and the error is related to memory, I will focus on that without taking into account the transformations themselves.

If you are using something like, df, df_pivoted, df_unstacked, etc. With each transformation you are creating a new variable, and multiplying your memory consumption. So it is important to clear the memory in the process. Even if your data don´t seems big enough to consume all your memory.

One way to solve this problem is to work on "chuncks" and save each transformation step to a file in order to clear the memory.

So the first step is to save the files, with a simple 'dataframe.to_csv ()'.

The second step is to make the transformations using parts of the data that fit in memory.

For this, there is an argument in the pandas.read_csv () function, called 'chuncksize' that transforms your import object into iteration TextFileReader.

that way, if you want to access the data information, you need to iterate over it.

iterator = pandas.read_csv('file.csv', chuncksize=32)
iterator.shape # will raise an error.

AttributeError: 'TextFileReader' object has no attribute 'shape'

the right way to do it:

for chunck in iterator:
    print (chunck.shape)

output:

(32, ncols)

That way, to deal with your problem, you can work with chuncks and use the join functions to do the analysis as you need the data.

user_139652210

I think this might be a bug in pandas or numpy. There are differnet ErrorMessages with different pandas and numpy versions (Anaconda vs. pip). I coded the transformation myself and it runs in no time.

# Get the 2017 timestamp for the side_df
side_df = pd.DataFrame ({'timestamp': next_df.loc[next_df['year'] == 2017]['target_frame']})

for year in next_df['year'].unique():
    
    side_df[year] = next_df.loc[next_df['year'] == year]['value']

display(side_df)

Results in:

                timestamp     2016     2017     2018     2019     2020
8839  2017-01-01 01:00:00  10,3706   4,4184  14,7919  30,6942  31,0594
8840  2017-01-01 01:15:00  27,2456  23,7641  31,0019  40,2778  46,8350
8841  2017-01-01 01:30:00  20,4022  14,9732  23,8531  34,4941  41,3688
8842  2017-01-01 01:45:00  14,4911   9,4986  17,0181  28,8678  37,8213
8843  2017-01-01 02:00:00  14,2611   5,1241  14,0869  24,3203  34,4150
...                   ...      ...      ...      ...      ...      ...
43874 2017-12-31 23:45:00  10,9256  15,2959  22,6000  40,1677      NaN
43875 2017-01-01 00:00:00  10,9706   4,8184  11,5150  30,9208      NaN
43876 2017-01-01 00:15:00  35,6275  25,8251  30,2893  41,5722      NaN
43877 2017-01-01 00:30:00   24,555  17,7821  24,2928  35,5510      NaN
43878 2017-01-01 00:45:00     5,61  11,7059  20,0477  31,2884      NaN

There are still some problems in the dataset (like the NaNs), but that has nothing to do with this question.

user_62735030
Related