1
$\begingroup$

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?

$\endgroup$

closed as unclear what you're asking by Aditya, Sean Owen Feb 5 at 22:53

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • $\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 at 17:50
  • $\begingroup$ @Aditya I have done exactly same. The code I wrote divides it into 100000. Should I split it further? $\endgroup$ – Abhimanyu Aryan Feb 6 at 0:06
  • $\begingroup$ Added some details on how to train / test data once you have processed it. $\endgroup$ – Shamit Verma Feb 6 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 at 5:58
  • $\begingroup$ @Aditya can you be more specific. Contribute an answer. What I'm doing and what should be done. Thanks $\endgroup$ – Abhimanyu Aryan Feb 7 at 20:48
2
$\begingroup$

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

$\endgroup$
1
$\begingroup$

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.

$\endgroup$
0
$\begingroup$

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.

$\endgroup$
  • $\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$ – Abhimanyu Aryan Feb 6 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 at 0:28
  • $\begingroup$ yaa in that case kernel dies $\endgroup$ – Abhimanyu Aryan Feb 6 at 0:29
0
$\begingroup$
  • 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")

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

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