Appending multiple file loads in Python

Question

I am fairly new to python, been coding in it for about a year or so. My company is switching from using SAS and a Netezza database for some of our data management. In order to access certain information, I have to load .dat.gz files now using Python3 in unix. In order to access similar information I used to access using basic SQL queries, I now have to import a list of files containing similar information and sort it using python. While this is not an issue for most of the data, there is an instance where I am running into performance issues. In order to access certain data I need, I must load over 300 files to python and try to append them together. This is an issue as the program takes a long time just to load the data. I am currently using pandas read_csv using usecols option to limit the data to the three columns I need. I first used df.append(df2), but it was slow. I then changed the dataframe to list, but that still did not seem to improve performance that much. All together the data contains over 50 Million rows. While I do not expect this program to run in seconds, I would like some help to improve performance where I can.

Just to note it is important to keep the integrity of the original .dat.gz files. The files I read in very in size from 2 rows to 50k rows or more. Any help would be greatly appreciated!

I have tried df.append(df2) and concatenating lists. I also tried appending the data to a new CSV in my working directory so the data would not all be stored while the program was running. That did not improve performance either.

import pandas as pd
import sys

#function to read in directories list from agrv[1]
def import_file_list(file_name):
    file_list = []
    with open(file_name) as f:
        for row in f:
            row = row.strip()
            file_list.append(row)
    return file_list

#main loop to read in all files and append the data
def import_data(list_of_files)
    for count, item in enumerate(list_of_files):
        exposure_data = pd.read_csv(item, sep='|', usecols=['A', 'B', 'C'], compression='gzip')
            if count == 0:
                    data_dict = exposure_data.to_dict('split')
                    values_list = list(data_dict['data'])
            else:
                    temp_dict = exposure_data.to_dict('split')
                    temp_list = list(temp_dict['data'])
                    values_list = values_list + temp_list

def main():
    import_data(import_file_list(sys.argv[1]))

if __name__ == '__main__':
    main()

Expected results would be to loop through all the files I need and combine them to one dataframe, list of lists, dictionary of all the data, or whatever object is most efficient with the three columns I need to do my calculations.

Answer 1

A suggestion that may help is to make generators of loops in import_file_list and import_data.
This is more memory efficient on the intermediate steps and defers building the dataframe until the end.

def import_file_list(file_name):
    with open(file_name) as f:
        for row in f:
            yield row.strip()


def import_data_to_dfs_iter(list_of_files)
    for item in list_of_files:
        yield pd.read_csv(
            item, sep='|', usecols=['A', 'B', 'C'], compression='gzip')

def main():
    dfs_iter = import_data_to_dfs(import_file_list(sys.argv[1]))
    df = pd.concat(dfs_iter)

Answer 2

Apart from the functional improvements that were already presented to you, there are also a few non-functional facets that could be improved.

Python has an official Style Guide, often just called PEP8. This guide presents a variety of recommendations to write consistent and good looking Python code.

IMGO the points most relevant to your code would be:

1. Sort the imports. Standard library imports come first, followed by third-party libraries, and eventually local import from other Python files you have written.
2. Write proper documentation strings. The officially recommended docstring syntax is to enclose them in """triple quotes""" and place them immediately below the function definition. Apart from a unified style with most of the Python coding world, this will also make sure that Python's built-in help(...) function as well as most proper Python IDEs will easily pick it up and show it to you.
3. Use 4 spaces per indentation level. There is an overindented block in the body of the if statement in import_data.

There are a lot of tools that may help you to keep a consistent style even on a larger scale. Some of these tools would be pylint (style and static code checking), flake8, black (style check and auto-formatting), or yapf (style check and auto-formatting) to name a few. Most Python IDEs support at least some of these tools so they will mark violating pieces of code while you write them and not just afterwards.