Pandas: transform a dbf Table into a dataframe

Question

I want to read a dbf file of an ArcGIS shapefile and dump it into a pandas dataframe. I am currently using the dbf package.

I have apparently been able to load the dbf file as a Table, but have not been able to figure out how to parse it and turn it into a pandas dataframe. What is the way to do it?

This is where I am stuck at:

import dbf
thisTable = dbf.Table('C:\\Users\\myfolder\\project\\myfile.dbf')
thisTable.open(mode='read-only')

Python returns this statement as output, which I frankly don't know what to make of:

dbf.ver_2.Table('C:\\Users\\myfolder\\project\\myfile.dbf', status='read-only')

EDIT

Sample of my original dbf:

FID   Shape    E              N
0     Point    90089.518711   -201738.245555
1     Point    93961.324059   -200676.766517
2     Point    97836.321204   -199614.270439
...   ...      ...            ...

@CF84, you may want to read [this article](http://joelmccune.com/arcgis-to-pandas-data-frame/) — MaxU - stand with Ukraine, Jan 27 '17 at 17:08
@CF84, if you have to deal with DBF files you can also do the following: read .DBF using `dbf` module export it to CSV (`.export()` method) and then parse this CSV in Pandas. If your DBF file is not huge you can use `io.StringIO` buffer instead of writing this CSV to disk... — MaxU - stand with Ukraine, Jan 27 '17 at 17:13
The output statement you are seeing is due to `dbf` returning the table on `open()` -- this is done to facilitate getting a ready-to-use table with one line: `my_table = dbf.Table('somedata.dbf').open()` — Ethan Furman, Jan 27 '17 at 18:04

score 56 · Accepted Answer · edited Dec 25 '22 at 06:52

56

You should have a look at simpledbf:

In [2]: import pandas as pd

In [3]: from simpledbf import Dbf5

In [4]: dbf = Dbf5('test.dbf')

In [5]: df = dbf.to_dataframe()

This works for me with a little sample .dbf file.

edited Dec 25 '22 at 06:52

starball

20,030
7
43
238

answered Jan 27 '17 at 16:44

Fabio Lamanna

20,504
24
90
122

1

very neat answer! – MaxU - stand with Ukraine Jan 27 '17 at 16:45
@FabioLamanna Why is it that the `FID` field is not read into the `df`? So in essence, all I see are just the `E` and `N` fields. How to make sure *all* fields are read? – FaCoffee Jan 27 '17 at 16:50
@CF84 maybe it is considered as the index of the DataFrame? – Fabio Lamanna Jan 27 '17 at 17:42
Well not sure, you know? As silly as it is, there is also a *Shape* field which somehow gets lost in the process... – FaCoffee Jan 27 '17 at 18:48
1

Nice answer, is it possible to do it the other way around? That is, from `dataframe` to `dbf` file? – DarkCygnus Jul 03 '17 at 20:41
1

@DarkCygnus have you figured out how to convert a dataframe into dbf? – FaCoffee Jul 05 '18 at 03:46
worked with python 3. Just need to do 'pip3 install simpledbf' – Rahal Kanishka Mar 21 '21 at 18:31

score 13 · Answer 2 · edited May 30 '19 at 19:25

13

As mmann1123 stated, you can use geopandas in order to read your dbf file. The Geopandas reads it even though it may or may not have geospatial data.

Assuming your data is only tabular data (no geographical coordinate on it), and you wish to read it and convert to a format which pandas library can read, I would suggest using geopandas.

Here is an example:

import geopandas as gpd

My_file_path_name = r'C:\Users\...file_dbf.dbf'

Table = gpd.read_file(Filename)

import pandas as pd
Pandas_Table = pd.DataFrame(Table)

Keys = list(Table.keys())
Keys.remove('ID_1','ID_2') # removing ID attributes from the Table keys list
Keys.remove('Date') # eventually you have date attribute which you wanna preserve.

DS = pd.melt(Pandas_Table, 
             id_vars =['ID_1','ID_2'], # accepts multiple filter/ID values 
             var_name='class_fito', # Name of the variable which will aggregate all columns from the Table into the Dataframe
             value_name ='biomass (mg.L-1)' , # name of the variable in Dataframe
             value_vars= Keys # parameter that defines which attributes from the Table are a summary of the DataFrame)

# checking your DataFrame:

type(DS)   # should appear something like: pandas.core.frame.DataFrame

edited May 30 '19 at 19:25

marc_s

732,580
175
1,330
1,459

answered Aug 03 '18 at 19:41

Philipe Riskalla Leal

954
1
10
28

You don't have to load `pd.DataFrame(Table)` to get a dataframe from `Table`... `Table` is already a pandas dataframe – zelusp Oct 02 '19 at 13:46
1

this works well, but geopandas will automatically want to add a `geometry` column which will later be a problem, for a dbf that doesn't come from a shapefile you will want to drop it. `gpd.read_file(filename).drop("geometry",axis=1)` – rick debbout May 24 '20 at 02:11
only works for me if the dbf file comes with his .shp. A single .dbf give IndexError (geopandas 0.6.1) – nantodevison Jun 06 '20 at 20:49
1

This is a great answer; the one marked above was turning 0 values to NAN on my implementation, didn't know that geoapandas was able to read the file (with it all values kept consistent). Thank you – arbermejo Feb 03 '23 at 10:42

score 4 · Answer 3 · answered Mar 08 '18 at 15:54

4

You might want to look at geopandas. It will allow you to do most important GIS operations

http://geopandas.org/data_structures.html

answered Mar 08 '18 at 15:54

mmann1123

5,031
7
41
49

Dobedani · Answer 4 · 2018-06-21T18:27:11.417

Performance can be an issue. I tested a few of the libraries suggested above and elsewhere. For my test, I used a small dbf file of 17 columns and 23 records (7 kb).

Package simpledbf has a straightforward method to_dataframe(). And the practical aspect of the DBF table object of dbfread is the possibility to just iterate over it by adding it as an argument to Python's builtin function iter(), of which the result can be used to directly initialise a dataframe. In the case of pysal, I used the function dbf2DF as decribed here. The data from the other libraries I added to the dataframe by using the method shown above. However, only after retrieving the field names so that I could initialise the dataframe with the right column names first: from the fieldNames, _meta.keys and by means of the function ListFields respectively.

Probably adding records 1 by 1 is not the fastest way to obtain a filled dataframe, meaning that tests with dbfpy, dbf and arcpy would result in more favourable figures when a smarter way would be chosen to add the data to the dataframe. All the same, I hope the following table - with times in seconds - is useful:

simpledbf   0.0030
dbfread     0.0060
dbfpy       0.0140
pysal       0.0160
dbf         0.0210
arcpy       2.7770

Dobedani · Answer 5 · 2018-06-19T11:52:30.293

How about using dbfpy? Here's an example that shows how to load a dbf with 3 columns into a dataframe:

from dbfpy import dbf
import pandas as pd

df = pd.DataFrame(columns=('tileno', 'grid_code', 'area'))
db = dbf.Dbf('test.dbf')
for rec in db:
    data = []
    for i in range(len(rec.fieldData)):
        data.append(rec[i])
    df.loc[len(df.index)] = data
db.close()

If necessary, you could find out the column names from db.fieldNames.

score 2 · Answer 6 · edited Jan 09 '22 at 20:57

2

This worked for me:

import geopandas as gpd

df = gpd.read_file('some_file.dbf').drop("geometry",axis=1)

edited Jan 09 '22 at 20:57

Flair

2,609
1
29
41

answered Jan 05 '22 at 19:51

r_a_d_u

31
3

score 1 · Answer 7 · answered May 03 '21 at 07:11

1

I used 'dbf' found on PyPi version 0.99.1 which works great.

import dbf
import pandas as pd

table = dbf.Table(filename=filepath)
table.open(dbf.READ_ONLY)
df = pd.DataFrame(table)
table.close()

print(df)

answered May 03 '21 at 07:11

JohanV

31
2

score 1 · Answer 8 · edited Jul 05 '22 at 18:03

1

How to Load content of a DBF file into a Pandas data frame.

The iter() is required because Pandas doesn't detect that the DBF object is iterable.

#import
from dbfread import DBF
import pandas as pd


dbf = DBF('people.dbf')
dataResult = pd.DataFrame(iter(dbf))

print(dataResult)

edited Jul 05 '22 at 18:03

cottontail

10,268
18
50
51

answered Jul 05 '22 at 16:01

timimi

53
1
9

Pandas: transform a dbf Table into a dataframe

8 Answers8

Linked