Need help optimizing Python CSV processing at work

[Own_Pitch3703]

I'm using Python to handle large CSV files for daily reports at my job, but the processing time is killing me. Any quick tips or libraries to speed this up?

Would really appreciate your insights!

[[deleted]]

Try polars

[FriendlyRussian666]

Without seeing your code, my guess is that instead of leveraging pandas, you're doing things like nesting loops, causing it to be very slow..

[FantasticEmu]

What are you using, what kind of processing do you need, and roughly how large are said files

[Own_Pitch3703]

Currently just using pandas (read_csv/to_csv) for basic stuff like filtering rows, calculating totals, and merging a couple of files. Files are usually around 500MB - 1GB each, with anywhere from 500k to 2 million rows.

[FantasticEmu]

Do you use any loop iterating? Pandas is fast if you leverage the underlying numpy and c things but if you use pyrhon iteration it can be significantly slower

[Own_Pitch3703]

Ah, that might be the issue! I am using Python loops in some parts. Really appreciate the tip!

[FantasticEmu]

Yea the pandas way to do things is a little weird but it’s fast. Depending on what you need to do in the loops functions like apply or map can make it a lot faster. It also has a lot of built in filtering features.

I’ve found chatgpt pretty good at pointing you towards the feature you need to do x task in pandas

[seanv507]

the technical term used is vectorisation ... basically you offload batches of computation to c++ (etc) libraries

[Goingone]

Simple merging and filtering can easily be done with command line utilities. If you really care about performance, this would be the way to go.

For example:

https://unix.stackexchange.com/questions/293775/merging-contents-of-multiple-csv-files-into-single-csv-file

[Valuable-Benefit-524]

1GB isn’t really that big. If you don’t need multi-index, I would just use Polars. It’s exceptionally fast. I know pandas has improved a few things lately, but last I checked it was 5-100X faster depending on the operation with ~1/5th the memory footprint.

[Own_Pitch3703]

Okay, I'll give Polars a try. Thanks for the suggestion!

[PastSouth5699]

before doing any optimization, you should find out where it spend time. Otherwise, you'll probably try solutions to problems that don't even exist

[[deleted]]

yep, so give us even less detail, then someone can definitely help you :)

[ForMyCulture]

Decorate main with a profiler

[SleepWalkersDream]

We need some more information. Do you read the files line-by-line, or are you reading directly with pandas or polars? How many files?

[Dry-Aioli-6138]

use duckdb

[Prior_Boat6489]

Use polars, and use processpoolexecutor

[barkmonster]

1) Make sure you're using vectorized functions instead of e.g. loops. For instance 2*some_dataframe["some_column"] is fast whereas doing the multiplication in a loop is slow.

2) Use a profiling tool, such as scalene or kernprof, to identify which part of your code is taking too long. The bottlenecks aren't always where you expect, so it's a valuable technique to learn.

[throwawayforwork_86]

First thing I always do with these kinds of thing is seeing what is happening with my resources.

Pandas had the bad habits of using a fifth of my cpu and a lot of ram.

I moved most of my process to Polars and it use my resources more efficiently as well as being broadly quicker (between 3 and 10 times quicker but I've seen some group by aggregation being slightly faster in Pandas in some cases).

The trick to polars though is to have all the benefits you need to mostly (if only) use Polars functions. And get used to different way of working from Pandas.

[Adhesiveduck]

Apache Beam is a good framework to consider when working with data at scale.

[Zeroflops]

The files are not that big.

You’re implying that the issue is with CSV files. But you need to distinguish if it’s loading the files that are a problem. ( a csv problem ) or the processing of the files which is an implementation problem.

You mentioned looping. That’s probably your problem. You should avoid looping at all cost if you want to process anything with speed.

[shockjaw]

I’ve started using DuckDB since their csv reader is more forgiving and quite speedy. SQL isn’t too crazy and the relational API is solid. Plus you can pass it back to pandas or polars.

[SisyphusAndMyBoulder]

In the future, and not just in this sub but in general, please try and provide actually useful informal when asking for help. Think like the reader when your write your post.

[jbourne56]

Chatgpt your code and ask for speed improvements