Python – Save DataFrame as DBF with simpledbf in Python

attribute-joinsdbfpandaspython

I am doing a number of joins outside of ArcGIS as pandas seems to outperform it. I read in the dbf file with simpledbf, convert it to a dataframe, then do the join:

import pandas as pd
from simpledbf import Dbf5

#Source of the join
data_source=pd.read_excel('C:\\Users\\myfolder\\mysource.xlsx')

#Target of the join
dbf = Dbf5('C:\\Users\\myfolder\\mydbf.dbf')
d_b_f = dbf.to_dataframe()

#Join
d_b_f_2=pd.merge(d_b_f, data_source, on='COLUMN', how='left')

How can I save this dataframe as a dbf and overwrite the existing dbf?

I expected something along these lines to work:

final_dbf = Dbf5('C:\\Users\\myfolder\\finaldbf.dbf')
final_dbf = d_b_f_2.to_dbf()

but an error pops up saying that 'C:\Users\myfolder\finaldbf.dbf' does not exist. What do you suggest as a workaround? I wouldn't want to look into other packages, as I am sure there must be a way of doing this within simpledbf itself.

Best Answer

1) simpledbf cannot export dbf format as output (only CSV, SQL,pandas DataFrame, HDF5 Table)
2) Pandas DataFrames don't have a .to_dbf method

Simply use PySAL(dbf) and DBF files and the pandas DataFrame with the functions df2dbf(convert a pandas.DataFrame into a dbf), dbf2df(read a dbf file as a pandas.DataFrame) and appendcol2dbf(append a column and the associated data to a DBF) in dataIO.py) that you can adapt

import numpy as np
import pysal as ps
import pandas as pd
# adaptation of the function `df2dbf` to write your resulting dbf file
type2spec = {int: ('N', 20, 0),
             np.int64: ('N', 20, 0),
             float: ('N', 36, 15),
             np.float64: ('N', 36, 15),
             str: ('C', 14, 0),
             unicode: ('C', 14, 0)}
types = [type(d_b_f_2[i].iloc[0]) for i in d_b_f_2.columns]
specs = [type2spec[t] for t in types]
with ps.open("finaldbf.dbf", 'w') as db:
    db.header = list(df.columns)
    db.field_spec = specs
    for i, row in d_b_f_2.T.iteritems():
        db.write(row)

Related Solutions

Python – Saving .dbf as .xls Using ArcGIS Desktop

Like whuber says, you have to write out the headers explicitly. I loaded up dbfpy and xlwt in a virtualenv and ran this:

from xlwt import Workbook, easyxf
import dbfpy.dbf
from time import time

def test1():
    dbf = dbfpy.dbf.Dbf("pipelines.dbf", readOnly = True)

    header_style = easyxf('font: name Arial, bold True, height 200;')

    book = Workbook()
    sheet1 = book.add_sheet('Sheet 1')

    for (i, name) in enumerate(dbf.fieldNames):
        sheet1.write(0, i, name, header_style)

    for (i, thecol) in enumerate(dbf.fieldDefs):
        name, thetype, thelen, thedec = str(thecol).split()
        colwidth = max(len(name), int(thelen))
        sheet1.col(i).width = colwidth * 310

    for row in range(1,len(dbf)):
        for col in range(len(dbf.fieldNames)):
            sheet1.row(row).write(col, dbf[row][col])

    book.save("pipelines-xls.xls")

if __name__ == "__main__":
    start = time()
    test1()
    end = time()
    print end - start

This gives me headers in my xls:

enter image description here

Python GeoJSON – Add Elevation, Name, and Description in GeoJSON When Converting DataFrame

Using what i learned from the answer of @gene this solution avoids the use of iterrows because iterrows have performance issues.

Valid for Python 3.X

import pandas as pd
import geojson

def data2geojson(df):
    features = []
    insert_features = lambda X: features.append(
            geojson.Feature(geometry=geojson.Point((X["long"],
                                                    X["lat"],
                                                    X["elev"])),
                            properties=dict(name=X["name"],
                                            description=X["description"])))
    df.apply(insert_features, axis=1)
    with open('map1.geojson', 'w', encoding='utf8') as fp:
        geojson.dump(geojson.FeatureCollection(features), fp, sort_keys=True, ensure_ascii=False)

col = ['lat','long','elev','name','description']
data = [[-29.9953,-70.5867,760,'A','Place ñ'],
        [-30.1217,-70.4933,1250,'B','Place b'],
        [-30.0953,-70.5008,1185,'C','Place c']]

df = pd.DataFrame(data, columns=col)

data2geojson(df)

Valid for Python 2.X

import pandas as pd
import geojson

def data2geojson(df):
    features = []
    df.apply(lambda X: features.append( 
            geojson.Feature(geometry=geojson.Point((X["long"], 
                                                    X["lat"], 
                                                    X["elev"])), 
                properties=dict(name=X["name"], 
                                description=unicode(X["description"].decode('utf8'))))
                                    )
            , axis=1)
    with open('map.geojson', 'w') as fp:
        geojson.dump(geojson.FeatureCollection(features), fp, sort_keys=True)

col = ['lat','long','elev','name','description']
data = [[-29.9953,-70.5867,760,'A','Place a'],
        [-30.1217,-70.4933,1250,'B','Place b'],
        [-30.0953,-70.5008,1185,'C','Place c']]

df = pd.DataFrame(data, columns=col)

data2geojson(df)

Best Answer

Related Solutions

Python – Saving *.dbf as *.xls Using ArcGIS Desktop

Python GeoJSON – Add Elevation, Name, and Description in GeoJSON When Converting DataFrame

Related Question

Python – Saving .dbf as .xls Using ArcGIS Desktop