I am merging files together in 4 folders. Within those 4 folders I am merging 80 .dbf files together each of which is 35 megabytes. I am using the following code:
import os
import pandas as pd
from simpledbf import Dbf5
list1=[]
folders=r'F:\dbf_tables'
out=r'F:\merged'
if not os.path.isdir(out):
os.mkdir(out)
for folder in os.listdir(folders):
if not os.path.isdir(os.path.join(out,folder)):
os.mkdir(os.path.join(out,folder))
for f in os.listdir(os.path.join(folders,folder)):
if '.xml' not in f:
if '.cpg' not in f:
table=Dbf5(os.path.join(folders,folder,f))
df=table.to_dataframe()
list1.append(df)
dfs = reduce(lambda left,right: pd.merge(left,right,on=['POINTID'],how='outer',),list1)
dfs.to_csv(os.path.join(out,folder,'combined.csv'), index=False)
almost immediately after running the code I receive this error:
Traceback (most recent call last):
File "<ipython-input-1-77eb6fd0cda7>", line 1, in <module>
runfile('F:/python codes/prelim_codes/raster_to_point.py', wdir='F:/python codes/prelim_codes')
File "C:\Users\spotter\AppData\Local\Continuum\Anaconda_64\lib\site-packages\spyderlib\widgets\externalshell\sitecustomize.py", line 714, in runfile
execfile(filename, namespace)
File "C:\Users\spotter\AppData\Local\Continuum\Anaconda_64\lib\site-packages\spyderlib\widgets\externalshell\sitecustomize.py", line 74, in execfile
exec(compile(scripttext, filename, 'exec'), glob, loc)
File "F:/python codes/prelim_codes/raster_to_point.py", line 66, in <module>
dfs = reduce(lambda left,right: pd.merge(left,right,on=['POINTID'],how='outer',),list1)
File "F:/python codes/prelim_codes/raster_to_point.py", line 66, in <lambda>
dfs = reduce(lambda left,right: pd.merge(left,right,on=['POINTID'],how='outer',),list1)
File "C:\Users\spotter\AppData\Local\Continuum\Anaconda_64\lib\site-packages\pandas\tools\merge.py", line 39, in merge
return op.get_result()
File "C:\Users\spotter\AppData\Local\Continuum\Anaconda_64\lib\site-packages\pandas\tools\merge.py", line 217, in get_result
join_index, left_indexer, right_indexer = self._get_join_info()
File "C:\Users\spotter\AppData\Local\Continuum\Anaconda_64\lib\site-packages\pandas\tools\merge.py", line 353, in _get_join_info
sort=self.sort, how=self.how)
File "C:\Users\spotter\AppData\Local\Continuum\Anaconda_64\lib\site-packages\pandas\tools\merge.py", line 559, in _get_join_indexers
return join_func(lkey, rkey, count, **kwargs)
File "pandas\src\join.pyx", line 160, in pandas.algos.full_outer_join (pandas\algos.c:61256)
MemoryError
but only 30% of my memory is being used, which is pretty much the baseline.
EDIT:
I picked out only 2 files and tried the merge using:
merge=pd.merge(df1,df2, on=['POINTID'], how='outer')
and still get a memory error, something weird is going on.
When I run the same thing in 32-bit Anaconda I get ValueError: negative dimensions are not allowed
EDIT:
The entire problem stemmed from the solution give here: Value Error: negative dimensions are not allowed when merging
EDITED based on comment:
Try this (it's enough to use only one if statement with logical and conditions):
import os
import pandas as pd
from simpledbf import Dbf5
folders = r'F:\dbf_tables'
out = r'F:\merged'
if not os.path.isdir(out):
os.mkdir(out)
for folder in os.listdir(folders):
if not os.path.isdir(os.path.join(out, folder)):
os.mkdir(os.path.join(out, folder))
# Initialize empty dataframe by folders
dfs = pd.DataFrame(columns=['POINTID'])
for f in os.listdir(os.path.join(folders, folder)):
if ('.xml' not in f) and ('.cpg' not in f):
table = Dbf5(os.path.join(folders, folder, f))
df = table.to_dataframe()
# Merge actual dataframe to result dataframe
dfs = dfs.merge(df, on=['POINTID'], how='outer')
# Save results by folder
dfs.to_csv(os.path.join(out, folder, 'combined.csv'), index=False)