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
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 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)) 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.