CODEWITHSUNDEEP
pythoncsvoptimizationpandas
NumenorForLife
NumenorForLife

Reputation: 1746

Is there a faster way to append many XLS files into a single CSV file?

After the recommendation from Jeff's Answer to check out this Google Forum, I still didn't feel satisfied on what the conclusion was regarding the appendCSV method. Below, you can see my implementation of reading many XLS files. Is there a way to significantly increase the speed of this? It currently takes over 10 minutes for around 900,000 rows.

listOfFiles = glob.glob(file_location)
frame = pd.DataFrame()


for idx, a_file in enumerate(listOfFiles):
    data = pd.read_excel(a_file, sheetname=0, skiprows=range(1,2), header=1)
    data.rename(columns={'Alphabeta':'AlphaBeta'}, inplace=True)

    frame = frame.append(data)

# Save to CSV..
frame.to_csv(output_dir, index=False, encoding='utf-8', date_format="%Y-%m-%d")

Upvotes: 1

Views: 117

Answers (2)

Harvey
Harvey

Reputation: 339

If you are using pandas, you could do this:

dfs = [pd.read_excel(path.join(dir, name), sep='\t', encoding='cp1252', error_bad_lines=False ) for name in os.listdir(dir) if name.endswith(suffix)]
df = pd.concat(dfs, axis=0, ignore_index=True)

This is screaming fast compared to other methods of getting data into pandas. Other tips:

  • You can also speed this up by specifying dtype for all columns.
  • If you are doing read_csv, use the engine='c' to speed up the import.
  • Skip rows on error

Upvotes: 0

Dmitrii Borisevich
Dmitrii Borisevich

Reputation: 105

The very first important point

Optimize only code that is required to be optimized.

If you need to convert all you files just once then you have already made a great job, congrats! If you, however, need to reuse it really often (and by really I mean that there is a source that produce your Excel files with a speed at least of 900K rows per 10 minutes and you need to parse them in real-time) then what you need to do is to analyze your profiling results.

Profiling analysis

Sorting your profile in descending order by 'cumtime', which is cumulative execution time of function including its subcalls, you will discover that out of ~2000 seconds of runtime ~800 seconds are taken by 'read_excel' method and ~1200 seconds are taken by 'to_csv' method.

If then you will sort profile by 'tottime' which is total execution time of functions themselves you will find out that top time consumers are populated with functions that are connected with reading and writing lines and conversion between formats. So, the real problem is that either libraries you use are slow, or the amount of data you are parsing is really huge.

Possible solutions

For the first reason, please keep in mind that parsing Excel lines and converting them could be a really complex task. It is hard to advice you without having an example of your input data. But there could be a real time loss just because the library you are using is for everything and it does hard work parsing rows several times when you actually do not need it, because your rows have very simple structure. In this case you may try to switch to different libraries, that does not perform complex parsing of input data, for example use xlrd for reading data from Excel. But in title you mentioned that input files are also CSVs so if this is applicable in your case then load lines with just:

line.strip().split(sep)

instead of complex Excel format parsing. And of course if your rows are simple than you can always use

','.join(list_of_rows)

to write CSV instead of using complex DataFrames at all. However, if your files contain Unicode symbols, complex fields and so on then these libraries are probably the best choice.

For the second reason - 900K rows could contain from 900K to infinite bytes, so it is really hard to understand whether your data input is really so big, without an example again. If you have really a lot of data then probably there is not too much you could do and you just have to wait. And remember that disk is actually a very slow device. Usual disks could provide you with ~100Mb/s at its best so if you are copying (because ultimately that is what you are doing) 10Gb of data then you can see that at least 3-4 minutes will be required for just physically reading raw data and writing the result. But in case if you are not using your disk bandwidth for 100% (for example if parsing one row with library that you are using takes comparable time with just reading this row from disk) you might also try to increase speed of your code by asynchronous data reading with multiprocessing map_async instead of cycle.

Upvotes: 1

Related Questions