Convert from Excel Spreadsheet to Point Shapefile with Python, Pandas & Fiona - Tutorial

fromExceltoShp.jpg

We have done a tutorial with a workaround procedure in Python with Pandas and Fiona of a common and but multi-step process to create point shapefiles from excel spreadsheets. The process involve some lines of code to read the excel file, define the output shapefile structure and write the point data.

Code

This is the complete Python code used in this tutorial:

#import required packages
import fiona
import pandas as pd
from collections import OrderedDict
#open a total coliform table
totalColi = pd.read_excel('../tab/totalColiforms.xls', index_col=0)
totalColi.head()
X Y CF 4
ID
CAL-10 -77.211111 -12.063611 2.0
CAL-11 -77.167111 -12.052917 1.8
CAL-12 -77.193944 -12.063750 1.8
CAL-13 -77.175306 -12.063639 2.0
CAL-21 -77.189250 -12.100019 1.8
#check the data types
totalColi.dtypes
X       float64
Y       float64
CF 4    float64
dtype: object
#exporting resulting data as geopackage

#create schema and define crs
schema = {'properties': OrderedDict([('obsName', 'str'),('CF 4', 'float')]), 'geometry': 'Point'}
crs = {'init': 'epsg:4326'}

#open output shapefile
coliShp = fiona.open('../shps/totalColiforms.shp',mode='w',schema=schema,driver='ESRI Shapefile',crs=crs)

for index, line in totalColi.iterrows():
    feature = {
    'geometry':{'type':'Point','coordinates':(line.X,line.Y)},
    'properties':OrderedDict([('obsName',index),('CF 4',line['CF 4'])])
    }
    coliShp.write(feature)
coliShp.close()

Tutorial

Input data

You can download the input data from this link.

Comment

Saul Montoya

Saul Montoya es Ingeniero Civil graduado de la Pontificia Universidad Católica del Perú en Lima con estudios de postgrado en Manejo e Ingeniería de Recursos Hídricos (Programa WAREM) de la Universidad de Stuttgart con mención en Ingeniería de Aguas Subterráneas y Hidroinformática.

 

Suscribe to our online newsletter

Subscribe for free newsletter, receive news, interesting facts and dates of our courses in water resources.