Best platform to work with when having millions of rows in dataframe

Question

I have table with around 20 features and millions of observations (rows).

I need to create model base on this table, however, as it is huge, training models like random forest or XGB takes forever. I'm working mainly with scikit-learn and the XGBoost packages on Jupyter lab server, using python, and i'm struggling with this when the dataframes are very large. Also it is important to mention that I have windows (not Linux).

My question is for people with more experience than I have: what way do you deal with huge dataframes? are there any better packages or platforms to work with when the data is so big?

Answer 1

A million observations of 20 features should be very manageable on a laptop, if a little slow. Cloud computing for very large datasets is staggeringly expensive and offers little or no benefit unless and until you have good parallelization in place. I would recommend keeping that option as your last resort.

For the initial data exploration and experimentation, I suggest you sample your data. Spending a few minutes googling "data sampling" will save you a lot of time and effort later. Only when you are getting reasonable results with your samples should you consider apply your methods to the larger dataset.

Also give some serious thought to dimensionality reduction, methods like PCA can be very helpful here. If you haven't already done so, a correlation analysis of your features might help you eliminate the less useful ones.

Answer 2

There are 2 things you can do here:

1.) Use libraries like Dask to speed up your data preprocessing. Here is the link

2.) Use cloud computing services like Azure, AWS or GCP. I am not aware of other two but I have worked on Azure and it provides a lot of options for implementing a data science solution. You get options like Auto-ML, Azure Designer, Python ML SDK etc.

So it depends on you. If your limitation is your compute setup, using Dask will be of little help and you should go for cloud services. But if that is not the case, go for Dask.

Answer 3

Parallelize your analyses on a single (multi-cpu) machine with e.g. pandarallel or the like or go for broke with scala/spark/hadoop if the problem wont fit on a single machine.

Answer 4

This is a VERY good question. I will break it into 2 parts:

Reading and pre-processing:

At that scale, libraries like Pandas are usually not a good bet. A simple pd.read_csv could result in an out-of-memory error. Options are:

1. Datatables: https://github.com/h2oai/datatable - Can read and process large datasets quickly and efficiently

2. DASK(https://dask.org/) - A framework to scale pandas workflows natively using a parallel processing. If you have ever used Spark, this will be very easy to get used to as it is similar conceptually

3. NVIDIA's Rapids framework https://rapids.ai/ - if you are using GPUs is another good option. The best part is it has equivalent of nearly all Scikit methods which work for big-data. This could be something you are looking for

Lastly, it is preferable to convert the dataset into a format which is faster to process. There are various formats in which datasets can be stored. However note that not all libraries support all formats. Some good formats are - feather, hdf5, parquet, jay, pickle. The good news is that Pandas supports all these formats - https://pandas.pydata.org/pandas-docs/stable/user_guide/io.html

MODELLING

Now coming to the second part i.e the actual processing of the model. There is no easy solution here as this depends on the specific scenario at hand. If you are able to sample a sub-set of data effectively by clustering or other methods (based on what you learnt during the EDA step when working with full data...) then nothing like it. If you must go with full data, then your options could be a library like Rapids which as I mentioned supports a lot of models which Scikit supports at a greater scale.

There are also smaller actions which you could take which could help:

• Revisit all datatypes and convert to the ones that occupy lesser space (e.g. float64 → float32, int64 → int8 etc but do so carefully). Check for 'object' datatypes which is not memory efficient and again convert to something lighter (of course this depends on what the object is holding). Though this sounds trivial, believe me in practice, THIS WORKS

• Use vectorizer operations in your processing. Dont use loops. Most of the time they are not needed. Vectorizers are blazingly fast as they operate on underlying C code - https://realpython.com/numpy-array-programming/

• Watch the memory sucking operations carefully. You could use a simple code like this

`

mem_details = []

def memory_ckpt():
    mem_details.append(psutil.virtual_memory()[3])
    mem_used_step = mem_details[-1] - mem_details[-2] if len(mem_details) > 1 else 0
    mem_used_total = mem_details[-1] - mem_details[0] if len(mem_details) > 1 else 0

    if mem_used_step > 50000000:
        print('Mem Warning, High memory usage step:', round(mem_used_step/1073741824, 2), ' GB\n')
    elif mem_used_step < -50000000:
        print('Mem Note, High memory release step:', round(mem_used_step/1073741824, 2), ' GB\n')
        
    if mem_used_total > 6000000000:
        print('Mem Warning, High memory usage cumulatively by the code in the kernel:', round(mem_used_total/1073741824,2), ' GB\n')
        print('Total Memory used at start of kernel before line 1:', round(mem_details[0]/1073741824,2), ' GB\n')
        print('Total Memory used as of this step:', round(mem_details[-1]/1073741824,2), ' GB\n')

`

• Call the above memory_ckpt() before and after every major processing step to see where you are using memory which will help you design your code better (remember to adjust the memory checkpoints in the function in-line with your machine memory)

• As a last resort, you may need to move to a big data platform - XGBoost4j on Scala-Spark, XGBoost with H2O.ai, Amazon SageMaker etc