Using Python to Convert Geo Coordinate to Location Address

One of my doctor friend asked me to help him to turn geo coordinates data in .xlsx file into location addresses and extract detailed city and district data for his further research and I spent some time to get it done. For my own future references and others possible needs, I draft this article.

Task Points

  1. Read data from .xlsx file
  2. Employ an Internet API service to do the translation from coordinates to address
  3. Extract city and district data from API result
  4. Write result to .xlsx file


Setup Python Programming Environment

Code Talks

## create a virtual environment to avoid complex issues
python3 -m venv __env37
source __env37/bin/activate

## Install necessary packages
pip install -i pandas ## for Bulk manipulation instead of for loop
pip install xlrd ## for Excel reading
pip install requests ## for Internet API request

Get Baidu Map API Service

To turn geo coordinates into address need adopt Baidu Map API Service. Register a key for calling Baidu Map API as following screenshot shows.

When the API key is successfully granted, it is necessary to call the API,103&ak={YOUR_KEY}&output=json to know the structure of API result like following screenshot shows.

Get Task Done with Codes

Again, let the code talks.

## Import packages needed
import pandas as pd
import requests

## Read data from .xlsx file (`xlrd` is employed internally)
df = pd.read_excel(open('data/station.xlsx','rb'), sheet_name='data')

## Employ an Internet API service to do the translation from coordinates to address
def getAddressFromCoordinate(lat, lng):
    latitude: horizontal circle
    longitude: verticle circle
    items = {'location': str(lat) + ',' + str(lng), 'ak': 'QCRdDYAuLfd0gBauqWL3rEOSMiclscdX', 'output': 'json'}
    res = requests.get('', params=items)
    result = res.json()

    return result

df['bdResult'] = df.apply(lambda row: getAddressFromCoordinate(row['纬度(度.分)'], row['经度(度.分)']), axis=1)

## Extract city and district data from API result
df['city'] = df['bdResult'].apply(lambda x: x['result']['addressComponent']['city'] if 'result' in x else None)
df['district'] = df['bdResult'].apply(lambda x: x['result']['addressComponent']['district'] if 'result' in x else None)

## Export data into `.xlsx` file
writer = pd.ExcelWriter('data/station_with_address.xlsx')


An alternative way to read .xlsx file is as following code talks.

pip install openpyxl
from openpyxl import load_workbook

wb = load_workbook('data/station.xlsx')
# print(wb.get_sheet_names())

sheet = wb['data'] # sheet =

* cached version, generated at 2018-12-05 07:00:25 UTC.

Subscribe by RSS