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?

  • $\begingroup$ Nothing is wrong... It needs more memory and you can't provide it ... Hence OOM... Split the data into chunks and then do $\endgroup$
    – Aditya
    Feb 5 '19 at 17:50
  • $\begingroup$ @Aditya I have done exactly same. The code I wrote divides it into 100000. Should I split it further? $\endgroup$ Feb 6 '19 at 0:06
  • $\begingroup$ Added some details on how to train / test data once you have processed it. $\endgroup$ Feb 6 '19 at 4:58
  • $\begingroup$ I don't think so... Because you are still saving them all at once.. you need to load them on fly and process them $\endgroup$
    – Aditya
    Feb 7 '19 at 5:58
  • $\begingroup$ @Aditya can you be more specific. Contribute an answer. What I'm doing and what should be done. Thanks $\endgroup$ Feb 7 '19 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
df_all = dd.read_csv(...) # Whatever arguments you used for pd.read_csv

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.

  • $\begingroup$ I did same. Break into chunks and loop. But I still get that kernel died errors and runs out of memory. Check my code $\endgroup$ Feb 6 '19 at 0:05
  • $\begingroup$ @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). $\endgroup$
    – DGrady
    Feb 6 '19 at 0:28
  • $\begingroup$ yaa in that case kernel dies $\endgroup$ Feb 6 '19 at 0:29
  • 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")

  • $\begingroup$ I'm saving data to feather format. Saving it /tmp/ and then reading that $\endgroup$ Feb 7 '19 at 20:49

Not the answer you're looking for? Browse other questions tagged or ask your own question.