Reading Specific CSV Files by Year Using Python

Introduction

In this article, we will explore how to read specific CSV files from a folder based on their name satisfying certain conditions. We will use Python as our programming language of choice and leverage its built-in libraries for data manipulation.

Background

The question presented here involves dealing with a large number of CSV files in a folder, each named after a specific year (e.g., 2022-month.csv). The goal is to read all the files corresponding to a particular year or a range of years into a single pandas DataFrame. To accomplish this task efficiently, we need to consider data structures and libraries that can handle large datasets.

Data Structures for Large Datasets

When dealing with massive amounts of data, it’s essential to use data structures that are optimized for performance. In Python, the following libraries are commonly used for handling large datasets:

pandas: The pandas library is ideal for data manipulation and analysis tasks. It provides data structures like DataFrames and Series, which are suitable for most data science applications.
HDF5: HDF5 (Hierarchical Data Format 5) is a binary format that stores data in a hierarchical structure. It’s particularly well-suited for numerical data and can be used to store large datasets efficiently.

Using pandas for Data Reading

To read specific CSV files based on their name, we will use the pandas library. Here’s an example code snippet:

import pandas as pd
import os

# Initialize an empty dictionary to store the DataFrames
df_dict = {}

def load_csv(year, directory):
    """
    Loads CSV files from a specified directory and returns a DataFrame.
    
    Parameters:
    year (int): The target year for which to load CSV files.
    directory (str): The path to the directory containing the CSV files.
    
    Returns:
    pd.DataFrame: A DataFrame containing the data from all CSV files corresponding to the target year.
    """
    # Create a file list of all CSV files in the specified directory
    file_list = [x for x in os.listdir(path=directory) if x.endswith('.csv')]
    
    # Initialize an empty list to store DataFrames corresponding to the target year
    dfs_year = []
    
    # Iterate over each file name
    for i in file_list:
        # Split the file name to extract the year and month
        y, m = i.split('.')[0].split('-')
        
        # Check if the file name corresponds to the target year
        if int(y) == year:
            # Read the CSV file using pandas and add it to the list of DataFrames
            dfs_year.append(pd.read_csv(directory + i))
    
    # Concatenate all DataFrames corresponding to the target year into a single DataFrame
    df_year = pd.concat(dfs_year, axis=0)
    
    return df_year

# Example usage:
directory_path = 'path_to_your_directory'
year_range = (2022, 2023)

for year in range(year_range[0], year_range[1] + 1):
    df = load_csv(year, directory_path)
    # Perform operations on the DataFrame

In this code snippet, we define a function load_csv that takes a target year and directory path as input. It loads all CSV files from the specified directory and filters those corresponding to the target year using their names. The filtered DataFrames are then concatenated into a single DataFrame for further analysis.

Handling Large Datasets with HDF5

When dealing with extremely large datasets, storing them in memory can be impractical. In such cases, it’s essential to consider alternative data formats like HDF5 that can handle massive amounts of numerical data efficiently.

The pandas library provides support for HDF5 files through the pd.HDFStore class. This allows you to store DataFrames in an HDF5 file and retrieve them as needed.

Here’s an example code snippet demonstrating how to use HDF5 with pandas:

import pandas as pd
import os

# Create an empty HDF5 file
file_path = 'path_to_your_file.h5'
store = pd.HDFStore(file_path, mode='w')

# Load a sample dataset and store it in the HDF5 file
data = {'column1': [1, 2, 3], 'column2': ['a', 'b', 'c']}
df = pd.DataFrame(data)
store.put('/dataset', df)

# Retrieve the stored DataFrame from the HDF5 file
stored_df = store['/dataset']

In this code snippet, we create an empty HDF5 file using the pd.HDFStore class and store a sample dataset in it. We can then retrieve the stored DataFrame by accessing the /dataset key.

Alternatives to pandas: Dask and Vaex

When dealing with extremely large datasets, using libraries like pandas can be memory-intensive. In such cases, alternatives like Dask and Vaex are worth exploring.

Dask: The Dask library is designed for parallel computing and provides a high-level API for handling large datasets. It allows you to perform operations on DataFrames in parallel and scale up your computations by adding more workers.
Vaex: The Vaex library is another popular alternative to pandas that’s optimized for speed and memory efficiency. It provides a high-performance API for working with arrays and DataFrames, making it suitable for large-scale data analysis tasks.

Here’s an example code snippet demonstrating how to use Dask for parallel computing:

import dask.dataframe as dd

# Load the dataset into a Dask DataFrame
df = dd.read_csv('path_to_your_file.csv')

# Perform operations on the Dask DataFrame in parallel
result_df = df.groupby('column1').mean().compute()

In this code snippet, we load a sample dataset into a Dask DataFrame and perform grouping operations on it. The compute method is used to execute the operation on the worker threads.

Conclusion

Reading specific CSV files based on their name involves filtering and concatenating DataFrames from a directory. By using libraries like pandas, HDF5, and alternatives like Dask and Vaex, you can efficiently handle large datasets and perform data analysis tasks.

When dealing with extremely large datasets, consider alternative data formats like HDF5 that can handle massive amounts of numerical data efficiently. Libraries like Dask and Vaex provide high-performance APIs for handling large datasets, making them suitable for scale-up computing tasks.

Last modified on 2023-06-03