Reading Large Datasets in Chunks with Pandas and PySpark using CSV

S3, especially when dealing with 3 GB or more of data. Using Pandas to process data in chunks is a very effective method for handling large files without overwhelming your system’s memory.

Here’s how to manage the process step-by-step:

1. Reading Large Datasets in Chunks with Pandas

If you’re dealing with CSV files stored in S3, you can read them in smaller chunks using the chunksize parameter in pandas.read_csv().

import pandas as pd

# Set the chunk size (e.g., process 500,000 rows at a time)
chunksize = 500000

# Define the S3 path to your data
s3_path = 's3://your-bucket/folder/data.csv'

# Initialize an empty list to store processed chunks
chunks = []

# Read the CSV in chunks and process each chunk
for chunk in pd.read_csv(s3_path, chunksize=chunksize):
    # Perform your cleaning operations (e.g., drop missing values and duplicates)
    chunk_cleaned = chunk.dropna().drop_duplicates()

    # Append the cleaned chunk to the list
    chunks.append(chunk_cleaned)

# Concatenate all cleaned chunks into one DataFrame
df_cleaned = pd.concat(chunks)

2. Process and Save Data in Chunks to Avoid Memory Overhead

Instead of storing all chunks in memory and then concatenating, you can process and save each chunk to S3 immediately, which avoids loading the entire dataset into memory.

for chunk in pd.read_csv(s3_path, chunksize=chunksize):
    # Clean each chunk
    chunk_cleaned = chunk.dropna().drop_duplicates()

    # Save cleaned chunk back to S3
    chunk_cleaned.to_csv('s3://your-bucket/folder/cleaned_data.csv', mode='a', header=False, index=False)

3. Why Processing in Chunks is Effective

• Memory Efficiency: Processing only a small portion of data at a time (e.g., 500,000 rows) keeps the memory usage low.
• Performance Optimization: Operations on smaller chunks are faster and more manageable.
• I/O Considerations: Saving the cleaned data after processing each chunk avoids reloading and recalculating previous chunks.

4. Working with Multiple Files

If you have multiple files stored in a folder in S3, all associated with a single table, you can loop through them and process them chunk-wise.

import boto3
import pandas as pd

# S3 setup
s3 = boto3.resource('s3')
bucket_name = 'your-bucket'
prefix = 'your-folder/'
bucket = s3.Bucket(bucket_name)

# Process each file in the S3 folder
for obj in bucket.objects.filter(Prefix=prefix):
    file_key = obj.key
    if file_key.endswith('.csv'):  # Ensure only CSV files are processed
        s3_path = f's3://{bucket_name}/{file_key}'
        
        # Process the file in chunks
        for chunk in pd.read_csv(s3_path, chunksize=chunksize):
            # Perform your data cleaning
            chunk_cleaned = chunk.dropna().drop_duplicates()

            # Save each chunk to S3
            chunk_cleaned.to_csv('s3://your-bucket/folder/cleaned_data.csv', mode='a', header=False, index=False)

5. Handling Large Parquet Files

For Parquet files, since chunksize is not natively supported, you can instead use row groups or a tool like PyArrow or Dask for efficient chunk-wise reading.

6. Optimizing with PySpark

If you’re dealing with a very large dataset or want to scale up for parallel processing, PySpark offers a more distributed approach:

from pyspark.sql import SparkSession

# Create Spark session
spark = SparkSession.builder.appName('LargeDataProcessing').getOrCreate()

# Load data from S3
df = spark.read.csv('s3://your-bucket/folder/')

# Perform cleansing in PySpark (e.g., remove duplicates and null values)
df_cleaned = df.dropDuplicates().na.drop()

# Save the cleaned data back to S3
df_cleaned.write.csv('s3://your-bucket/folder/cleaned_data/')

7. Parallel Processing for Larger Data

For further optimization, consider using parallel processing techniques with Dask or distributed PySpark. Both libraries allow you to parallelize operations across chunks of data.

8. Handling Missing and Duplicate Values

For cleaning missing (None, NaN) and duplicate values, both Pandas and PySpark offer the following functionalities:

• Pandas: dropna(), fillna(), and drop_duplicates()
• PySpark: na.drop(), na.fill(), and dropDuplicates()

Conclusion

• Processing large datasets in chunks is a highly recommended approach to avoid memory overload and improve performance.
• You can read, process, and save data chunk by chunk to optimize memory and avoid performance degradation, especially when working with large datasets stored in S3.
• PySpark is a viable alternative if the dataset becomes too large for Pandas, offering better scalability and parallelization.