I have two datasets that look like this:

df1:

df2:

They are EXTREMELY large datasets, to the point where pd.merge() and dd.merge() do not work, and my kernel gives me memory errors. However, I found that concatenating the two of those does not give me the memory error. My desired dataset, out2 looks like this:

I used the following code:

out2=dd.concat([df1,df2],join='outer')

but my new dataset looks like this:

How can I get my desired dataset without running into memory error issues, without using the pd.merge function?

CodePudding user response：

If performance is not critical, you could create a defaultdict of dict and use the first three values as the dict key and then add quantity and sales to the value dict. This would allow you to process the files without reading them into memory first.

from collections import defaultdict
from pathlib import Path

paths = [(Path.home() / 'file1.csv', 'Quantity'), (Path.home() / 'file2.csv', 'Sales')]
results = defaultdict(dict)

for path, value_column in paths:
    with path.open('r') as f:
        for line in f:
            parts = [s.strip() for s in line.split(',')]
            key = tuple(parts[0:-1])
            results[key][value_column] = parts[-1]

combined = pd.concat([pd.DataFrame(data=list(results.keys()), columns=['Date', 'City', 'State']), 
                      pd.DataFrame(list(results.values()))], axis=1)