itertools.permutations of Pandas dataframe index uses too much memory

user_3120026 0
Viewed 465

I'm trying to make a new Dataframe based on a permutation of another Dataframe. Here is the original Dataframe. Price is the index.

df1
Price     Bid   Ask
1          .01   .05
2          .04   .08
3          .1    .15  
.           .      .
130        2.50  3.00

The second Dataframe is meant to take the index from df1 and create a Dataframe (df2) with permutations of df1 index based on 4 prices such as in the example output below.

df2
 #     price1   price2   price 3  price 4
 1       1        2         3       4
 2       1        2         3       5
 3       1        2         3       6
 ..       ..       ..        ..      ..

To achieve this, I've been using itertools.permutation, but I'm having memory issues and cannot execute the huge number of permutations. This is the code I've been using to make the permutations.

price_combos = list(x for x in itertools.permutations(df1.index, 4))
df2 = pd.DataFrame(price_combos , columns=('price1', 'price2', 'price3', 'price4'))
pandas itertools dataframe numpy python
1 Answers
  • dtypes are probably causing the memory allocation to balloon.
    • The df1.index is Int64Index
    • The best thing I found, for your scenario, is to set the dataframe index into a numpy array with an int16 dtype.
      • The numeric range of int8 is -128 to 128. Since your index is 0 to 130, int8 will not suffice.
    • Creating a price_combos variable and then a dataframe, will use twice the amount of memory, so create df2 without the intermediary step.
    • If you create the dataframe without specifying the dtype, as you're doing, the dtype will be int64
    • With the following implementation, there will be one object, df2, that will be 2,180,905,112 Bytes
      • With the original implementation, there would be two int64 objects of 8GB each, for a total of 16GB.
  • If you're using Jupyter, it has terrible memory management.
  • Perhaps increasing the the amount of virtual memory / swap file size, will give you the extra buffer needed memory. Virtual memory is Windows, swap file is Linux. It's easy to do, just Google it.
import numpy as np
import pandas a pd
from itertools import permutations

# synthetic data set and create dataframe
np.random.seed(365)
data = {'Price': list(range(1, 131)),
        'Bid': [np.random.randint(1, 10)*0.1 for _ in range(130)]}

df1 = pd.DataFrame(data)
df1['Ask'] = df1.Bid + 0.15
df1.set_index('Price', inplace=True)

# convert the index to an int16 array
values = df1.index.to_numpy(dtype='int16')

# create df2
%%time
df2 = pd.DataFrame(np.array(list(permutations(values, 4))), columns=('price1', 'price2', 'price3', 'price4')) 
>>> Wall time: 2min 45s

print(df2.info())
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 272613120 entries, 0 to 272613119
Data columns (total 4 columns):
 #   Column  Dtype
---  ------  -----
 0   price1  int16
 1   price2  int16
 2   price3  int16
 3   price4  int16
dtypes: int16(4)
memory usage: 2.0 GB

df2.head()

   price1  price2  price3  price4
0       1       2       3       4
1       1       2       3       5
2       1       2       3       6
3       1       2       3       7
4       1       2       3       8

df2.tail()

           price1  price2  price3  price4
272613115     130     129     128     123
272613116     130     129     128     124
272613117     130     129     128     125
272613118     130     129     128     126
272613119     130     129     128     127
user_77588040
Related