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

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)



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

• Nothing is wrong... It needs more memory and you can't provide it ... Hence OOM... Split the data into chunks and then do Feb 5, 2019 at 17:50
• @Aditya I have done exactly same. The code I wrote divides it into 100000. Should I split it further? Feb 6, 2019 at 0:06
• Added some details on how to train / test data once you have processed it. Feb 6, 2019 at 4:58
• I don't think so... Because you are still saving them all at once.. you need to load them on fly and process them Feb 7, 2019 at 5:58
• @Aditya can you be more specific. Contribute an answer. What I'm doing and what should be done. Thanks Feb 7, 2019 at 20:48

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

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.

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


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.

• I did same. Break into chunks and loop. But I still get that kernel died errors and runs out of memory. Check my code Feb 6, 2019 at 0:05
• @AbhimanyuAryan what I mean is that this line in your code: for i in range(len(list_df)): add_datepart(list_df[i], 'date') is processing each chunk one row at a time; instead it might be better to process the whole chunk at once. Possibly just add_datepart(list_df). Feb 6, 2019 at 0:28
• yaa in that case kernel dies Feb 6, 2019 at 0:29

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

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

• I'm saving data to feather format. Saving it /tmp/ and then reading that Feb 7, 2019 at 20:49