Make my code faster - load CSVs into pandas dataframe on selected columns and merge them

user_11724813 0
Viewed 213

I have three CSV files in the folder ending by "1251". I want to iterate through the folder, pick these files, load them in chunks into pandas dataframes and merge them with selected columns.

The file of 90MB is a breeze but the script takes 15 min to add the file of 700 MB (over 3 mil lines). The whole operation takes 20 min to finish - this being unacceptable.

Is there any way to change and accelerate the procedure? I mean load CSV to pandas dataframes in chunks and merge/append/concatenate them into one file.

This works well with small files, but it needs to be faster with big csv files. I have found many questions with good ideas, but this should work - not sure why is it so slow. Any ideas how to make it faster?

import os
import sys
import struct
import fileinput
import csv
import pandas as pd



cwd = 'C:\\Users\\'
print(cwd)
directory = (cwd + '\\FINAL\\')
directory2 = (cwd + '\\FINAL\\CSV')
print(directory)
x=pd.DataFrame()
for file in os.listdir(directory):
    if file.endswith( "1251.csv"):
        fajl = os.path.splitext(file)[0]
        print(fajl)
        for chunk in pd.read_csv(directory + '\\' + fajl + ".csv", sep=",",error_bad_lines=False, encoding='latin-1',low_memory=False, chunksize=100000):

            mylist = []
            mylist.append(chunk)
            big_data = pd.concat(mylist, axis= 0)


            big_data = big_data.fillna(value='')
            selected = big_data[['SYS', 'MANDT', 'AGR_NAME', 'OBJECT', 'AUTH', 'FIELD', 'LOW', 'HIGH', 'DELETED']]

            x=x.append(selected)

            x.to_csv(directory2 + '\\' + fajl + '.csv', sep=',', index=False)
python
1 Answers

I think you have several problems with your code.

  1. Why are you reading in chunks? Can pandas not handle reading your csv's? Or was this an attempt to speed-up the code?

  2. For some reason you're re-initializing the list inside your second for-loop, and in essence this code isn't doing anything beyond appending a dataframe:

        for chunk in pd.read_csv(directory + '\\' + fajl + ".csv", sep=",",error_bad_lines=False, encoding='latin-1',low_memory=False, chunksize=100000):

        mylist = []
        mylist.append(chunk)
        big_data = pd.concat(mylist, axis= 0)


        big_data = big_data.fillna(value='')
        selected = big_data[['SYS', 'MANDT', 'AGR_NAME', 'OBJECT', 'AUTH', 'FIELD', 'LOW', 'HIGH', 'DELETED']]

        x=x.append(selected)

  3. Assuming pandas can handle your csv's (it's not clear from your post how large each csv is), I would go about this in the following way (using pd.concat for a list containing several DataFrame's is way more efficient than append):

    import csv
import pandas as pd    
cwd = 'C:\\Users\\'
print(cwd)
directory = (cwd + '\\FINAL\\')
directory2 = (cwd + '\\FINAL\\CSV')
print(directory)
my_list = []
for file in os.listdir(directory):
    if file.endswith( "1251.csv"):
        fajl = os.path.splitext(file)[0]
        print(fajl)
        curr_df = pd.read_csv(directory + '\\' + fajl + ".csv", sep=",",error_bad_lines=False, encoding='latin-1', usecols=['SYS', 'MANDT', 'AGR_NAME', 'OBJECT', 'AUTH', 'FIELD', 'LOW', 'HIGH', 'DELETED'])
        curr_df = curr_df.fillna(value='')
        my_list.append(curr_df)
x = pd.concat(my_list)
x.to_csv(directory2 + '\\' + fajl + '.csv', sep=',', index=False)

  4. Assuming you really do have to read in chunks:

    import os
import sys
import struct
import fileinput
import csv
import pandas as pd



cwd = 'C:\\Users\\'
print(cwd)
directory = (cwd + '\\FINAL\\')
directory2 = (cwd + '\\FINAL\\CSV')
print(directory)
x = []
for file in os.listdir(directory):
    if file.endswith( "1251.csv"):
        fajl = os.path.splitext(file)[0]
        print(fajl)

        for chunk in pd.read_csv(directory + '\\' + fajl + ".csv", sep=",",error_bad_lines=False, encoding='latin-1',low_memory=False, chunksize=100000):

            x.append(chunk['SYS', 'MANDT', 'AGR_NAME', 'OBJECT', 'AUTH', 'FIELD', 'LOW', 'HIGH', 'DELETED'])
big_data = pd.concat(x, axis=0)
big_data = big_data.fillna(value='')
big_data.to_csv(directory2 + '\\' + fajl + '.csv', sep=',', index=False)
user_94050220
Related