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?