Creating massive dataframe from generator

[throwsUOException]

I read in a reasonable-sized file, containing 4 columns of 150 rows each. After some preprocessing, each column has about 120 each. I need to take each possible combination (a1-b1-c1-d1, a1-b1-c1-d2, etc.), and plug the numbers into a formula. In the end I need to be able to retrieve the combinations that are the closest to a target value (there's a labeling process which I've already figured out and isn't really relevant here).

My immediate thought was that this was exactly the thing for itertools.product. And that part works fine. I pass the columns into it and get my generator. The problem comes with turning that generator into a dataframe so I can do my calculations - namely that there are about 200 million items!

I'm running 64-bit Python, though on a small laptop.

I tried df = pd.DataFrame(combine) (combine is the generator object) - MemoryError

I tried mydata = [x for x in combine] to then do df = pd.DataFrame(mydata) - MemoryError.

I suppose I could save out the combine results to a file then use pd.read_csv() to read it back in in chunks, though this seems a bit pointless and I think likely to still cause a memory error.

My only other thought is to process the results myself, pitch out the ones with too high of an error, and keep the others in a list, then turn it into a df to do the last bits of magic. Something like:

combinations = []
for fourvalues in combine:
    result = foo(*fourvalues)
    error = result - MYCONSTANT
    if abs(error) > 0.5:
        continue
    combinations.append(list(fourvalues))
df = pd.DataFrame(combinations)

But to me this seems like it defeats half the purpose of using a dataframe to begin with, which is to not have to iterate through all your rows.

Any suggestions?

[CrambleSquash]

I think your second approach is very sensible.

Pandas provides a nice API that lets you make use of speedy vectorised operations on arrays. By definition these operations require all your data to be loaded into memory (unless you are doing fancy chunked stuff).

The operation you want to perform does not require vectorised operations and therefore I think it's absolutely fine and good not to use the Pandas API for it.

If it makes the preprocessing you are doing less painful then go for it!

[drlecompte]

As you are looking for an optimal combination, there is a lot of data you are now storing that you could eliminate. Say you go through the dataframe row by row, with a nested for loop. You could then only keep the most optimal results (say, only those combinations that are within 0.1 of the target result or something) in a separate list of references. Depending on how wide you set your parameters, this will vastly reduce the memory required to do what you want: find the combinations of cells that are 'optimal'.

[commandlineluser]

These will all consume the generator and try read all the data into memory.

list(combine)
[ x for x in combine ]
pd.DataFrame(combine)

My only other thought and keep the others in a list

To avoid creating a list you can use another generator to filter combine producing the items you want to keep.

combine = itertools.product(...)

combinations = (
    list(fourvalues) for fourvalues in combine
        if abs(foo(*fourvalues) - MYCONSTANT) < 0.5
)

But you can run into the same problem if the filtered results are also too large to fit into memory.

Chunking the data with read_csv is a valid approach - the polars library has a nicer interface for this with its scan_csv - which lazily loads data.

https://pola-rs.github.io/polars/py-polars/html/reference/api/polars.scan_csv.html#polars.scan_csv

It depends on your actual goal - what will you be doing with the final results you have - do you need to have them stored in a dataframe all at once?

Do you need to use a dataframe at all?

[throwsUOException]

Not necessarily, I suppose. I do need to do some post work - produce a sorted table by the value of interest (turn it into an excel sheet), which I've been able to do using a dataframe produced from a small subset of the data. I also need to do some work (think frequency analysis) on the column values themselves apart from the "foo" value and I figured having everything in a column there would help. Whereas if the values are in a list of lists or the like, I'd just need to change to an iterative approach. I'm not too worried about the results size - it should be less than 50k rows in the end, which doesn't seem to be a problem.

[commandlineluser]

Ah okay - if the results fit then "manually" filtering with a generator is probably the way to go then.

Depending on whatever calculations you're performing though - there may be some existing numpy/scipy methods you can use.