5 Best Ways to Split a CSV into Multiple Files Using Python Pandas

by

💡 Problem Formulation: You have a CSV file with a large dataset and you need to distribute its content into multiple smaller CSV files based on specific criteria. For instance, you might want to segment your data by categories, dates, or ID ranges and create separate CSV files for each segment. Doing this manually can be time-consuming and error-prone, so utilizing Python’s Pandas library can streamline and automate this task efficiently.

Method 1: Split by Number of Rows

Splitting a CSV file into multiple smaller files with a specific number of rows is valuable when dealing with large datasets that need to be chunked for processing. Pandas makes this relatively straightforward by enabling you to iterate over the DataFrame in chunks. The DataFrame object can be divided using the nrows parameter to define the number of rows per chunk and then each chunk can be saved as a separate CSV file.

Here’s an example:

import pandas as pd

chunk_size = 100
batch_no = 1
for chunk in pd.read_csv('large_dataset.csv', chunksize=chunk_size):
    chunk.to_csv('chunk_{}.csv'.format(batch_no), index=False)
    batch_no += 1

Output:

  • chunk_1.csv
  • chunk_2.csv

This loop reads the large CSV file in chunks of 100 rows each and writes each chunk to a new CSV file named ‘chunk_x.csv’, where ‘x’ is the batch number starting from 1. This method is ideal for very large datasets where loading the entire dataset into memory at once is not feasible.

Method 2: Split by Unique Column Values

When the dataset has a categorical variable and each category warrants its own CSV file, Pandas allows you to group by unique column values. Using the groupby method, the DataFrame is segmented into groups based on unique entries in a specified column, then each group can be saved into a separate file.

Here’s an example:

import pandas as pd

df = pd.read_csv('sales_data.csv')
for category, group in df.groupby('category'):
    group.to_csv('{}.csv'.format(category), index=False)

Output:

  • electronics.csv
  • apparel.csv

This code reads sales data, groups the records by the ‘category’ column, and writes each group to its own CSV file named after the category value. It’s a practical approach to segregating data based on distinct categories or features.

Method 3: Split by Condition

There might be scenarios where you need to split your dataset based on a certain condition, such as data before and after a specific date. Pandas allows you to filter your DataFrame based on custom conditions using boolean indexing, then save the resulting subsets into separate CSV files.

Here’s an example:

import pandas as pd

df = pd.read_csv('events_data.csv')
before_date = df[df['date'] < '2021-01-01']
after_date = df[df['date'] >= '2021-01-01']
before_date.to_csv('events_2020.csv', index=False)
after_date.to_csv('events_2021.csv', index=False)

Output:

  • events_2020.csv
  • events_2021.csv

This example splits the event data into two CSV files based on whether the events occurred before or during and after the year 2021. It demonstrates how to filter and export data that meets specific criteria to separate files, facilitating data organization and analysis.

Method 4: Split by Range of Entries

For ordered datasets, you might want to split your data based on ranges of entries, such as ID ranges or alphabetically sorted names. Pandas provides functionality for slicing the DataFrame accordingly and saving each slice into different files.

Here’s an example:

import pandas as pd

df = pd.read_csv('customer_data.csv')
df_sorted = df.sort_values(by='customer_id')
range_size = 1000
for start in range(0, len(df_sorted), range_size):
    df_slice = df_sorted.iloc[start:start+range_size]
    df_slice.to_csv('customers_{}-{}.csv'.format(start+1, start+range_size), index=False)

Output:

  • customers_1-1000.csv
  • customers_1001-2000.csv

This code slices customer data by ranges of customer IDs, sorting the data first and then segmenting it into files each containing a range of 1000 IDs. It is particularly useful when handling datasets that are naturally ordered or when order-based processing is required.

Bonus One-Liner Method 5: Random Sampling

If you need a random sample of your data for each CSV file, such as for creating different datasets for machine learning model training and validation, Pandas allows you to randomize and then export the data easily.

Here’s an example:

import pandas as pd

df = pd.read_csv('full_dataset.csv')
df.sample(frac=0.1).to_csv('sampled_data.csv', index=False)

Output:

  • sampled_data.csv

This one-liner takes a random sample comprising 10% of the original dataset and saves it into a new CSV file. It’s a straightforward and efficient method for creating sample datasets for various purposes, like testing or model validation.

Summary/Discussion

In conclusion, Python Pandas provides multiple ways to split a CSV file into multiple smaller files, each suitable for different scenarios. Here’s a quick summary:

  • Method 1: Split by Number of Rows. Good for processing large datasets in manageable chunks. May not consider logical data grouping.
  • Method 2: Split by Unique Column Values. Ideal for categorically segmented data. Depends on having a meaningful categorical variable.
  • Method 3: Split by Condition. Useful for creating datasets based on specific criteria. Requires clear conditional logic for segmentation.
  • Method 4: Split by Range of Entries. Best for ordered data that needs to be processed sequentially. Setup can be complex if data isn’t naturally ordered.
  • Bonus Method 5: Random Sampling. Simple way to create sample datasets for experimentation. May not be suitable for all types of data analysis.