operating on a dataset with 125,497,040 records [closed]

Question

I'm trying to run add_datepart() which converts a df column from a datetime64 to many columns in place

Year', 'Month', 'Week', 'Day', 'Dayofweek', 'Dayofyear','Ismonthend', 'Ismonthstart', 'Isquarterend', 'Isquarterstart', 'Isyearend', 'Isyearstart' . etc

I'm using is Grocery Sales dataset. Total dates i believe in that are 125497040. What should I do to run this operation?

Every time I run this piece of code. The kernel dies(out of memory which is 17.2 GB RAM)

So I tried breaking down this data frame in smaller parts and then running add_datepart but still the same result

I wrote this code

def add_datepart_large(temp_df, size):
    list_df = [temp_df[i:i+size] for i in range(0,temp_df.shape[0],size)]
    for i in range(len(list_df)): add_datepart(list_df[i], 'date')
    return pd.concat(list_df)

final_df = add_datepart_large(df_all, 100000)

If after running this code, the kernel dies. What's wrong?

Answer 1

For 127 million rows, it is better to perform data prep on DB. It will be a select + insert query and will not require whole data to be loaded in memory.

SELECT YEAR(date) AS 'year', MONTH(date) AS 'month'
 FROM data

Edit : Once you start training / validation, even then it would be better to load few batches from DB (at a time). Most frameworks support this.

Foe example : https://github.com/keras-team/keras/issues/107

Answer 2

I cannot say how to fix this is Pandas, without having to likely restructure your approach to the problem (due to the size of your data).

Have a look at the python datatable package, which is like a database running in memory and is much more performant for larger dataframes, where Pandas might start to crash. It consumes less memory and internally works like a database.

I should additionally mention Dask, which is a distributed version of Pandas that can perhaps cope better than Pandas itself with larger amounts of data.

Answer 3

Here are two suggestions:

It looks like your function calls add_datepart on each row in data frame individually, which will introduce a lot of overhead. Instead you could try splitting df_all into chunks (something like tmp_dfs = [df_all.iloc[i:i+size] for i in range(...)]), then applying add_datepart to each chunk, and then concatenating the chunks back together. That would at least create less overhead, although 130 M rows is pretty large for Pandas running on a personal machine.

Dask is a library that provides a more-or-less drop-in replacement for Pandas data frames and is designed to work with very large data sets. I haven't tested it with your data set, but it could be as simple as

import dask.dataframe as dd
df_all = dd.read_csv(...) # Whatever arguments you used for pd.read_csv
add_datepart(df_all)

Dask is actually just providing a wrapper around regular Pandas data frames, so if you ever get to a situation that Dask doesn’t handle automatically you can always fall back on map_partitions.

Answer 4

• Read your data from disk by chunk in loop

for chunk in pd.read_csv(..., chunksize=10^6)

• Apply your function to DataFrame
• Store your DataFrame with "a" mode

chunk.to_csv(..., mode="a")