0
$\begingroup$

I'm doing a lot of work that requires operations to be performed across rows, using the data in that rows's columns on other columns in the row.

I recently had to do some processing on a 1.2 million row dataset (27 columns, 300 Mb) that required row-wise modification of ~ 300,000 rows. Some transactions were being split across multiple records and I wanted to aggregate them. This involved collapsing them upwards with lazy deletion, summing 5 columns and doing a weighted average on a 6th.

I at first attempted to use Pandas.

Pandas spent 12+ hours to go through ~ 80,000 rows (the machine I'm using at the office is trash).

I could maybe see that if I were dropping the rows as I went and it had to resettle 300 Mb of arrays in memory after each row, but it never even got around to deleting rows since that was set to happen at the end.

I've generally seen that the sentiment around Pandas seems to be "Don't iterate over a dataframe's rows."

I'm not asking for how to make it work in Pandas, the purpose of the above was to provide context as to why I believe that Pandas isn't a viable option for what I was trying to do.

I ended up going for a basic list implementation and it ran in 5-10 seconds.

In this case, the list implementation worked. Since I had the indices of the rows in the dataframe, I avoided the O(N) look-up time pitfall.

So, while Pandas is highly optimized for column-wise operations, it's evidently not really intended to perform granular row modification, en masse (Case in point, look at the big bolded warning in the iterrows documentation).

So here's the question:

Assuming you have a non-uniform row of data, with different sized columns and data types, what is the most efficient data structure/ library to perform a large number of row-wise modifications?

What about if all the columns were floats?

Improve this question
$\endgroup$
3
  • $\begingroup$ high-level tools like pandas have a lot of functionality and layers that these operations take ages, instead manipulating data at the lowest possible level can do the trick, way faster $\endgroup$
    – Nikos M.
    May 13, 2021 at 16:34
  • $\begingroup$ The sentiment of not iterating over rows in a dataframe means that you shouldn't loop over rows if not necessary as there are often ways to use a vectorized method which is much faster. Not sure if you were already applying vectorization or if your calculations can even be vectorized, but if you were looping over the rows yourself there might be a quicker way in pandas. $\endgroup$
    – Oxbowerce
    May 15, 2021 at 13:55
  • $\begingroup$ 12h+ to go trough 80.000 k rows ? it seems that there might be a problem somewhere. I agree with the comments above that a simpler tool can go faster. Personnaly I like working in numpy when rapidity is needed. However here I would start by looking the pandas implementation. $\endgroup$
    – Lucas Morin
    Jun 28 at 12:21

1 Answer 1

Reset to default
0
$\begingroup$

It depends on whether you can separate or know in advanced what rows are affected per operation.

In this instance, if indeed they involve collections then Dask Bags. For more non-structured data (as you suggest) but may be easy to run in parallel then Dask Delayed has worked for me.

Improve this answer
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.