CODEWITHSUNDEEP
pythonpython-3.xpandasdataframe
ah bon
ah bon

Reputation: 10051

Save geocoding results from address to longitude and latitude to original dataframe in Python

Given a small dataset df as follows:

   id             name address
0   1        ABC tower  北京市朝阳区
1   2          AC park  北京市海淀区
2   3      ZR hospital  上海市黄浦区
3   4  Fengtai library     NaN
4   5     Square Point  上海市虹口区

I would like to obtain longitude and latidude for address column and append them to orginal dataframe. Please note there are NaNs in address column.

The code below gives me a table with addresses, longitude and latitude, but it ignores the NaN address rows, also the code should be improved:

import pandas as pd
import requests
import json

df = df[df['address'].notna()]

res = []

for addre in df['address']:
    url = "http://restapi.amap.com/v3/geocode/geo?key=f057101329c0200f170be166d9b023a1&address=" + addre
    dat = {
            'count': "1",
            }
    r = requests.post(url, data = json.dumps(dat))
    s = r.json()
    infos = s['geocodes']
    for j in range(0, 10000):
        # print(j)
        try:
            more_infos = infos[j]
            # print(more_infos)
        except:
            continue
        try:
            data = more_infos['location']
            # print(data)
        except:
            continue
        try:
            lon_lat = data.split(',')
            lon = float(lon_lat[0])
            lat = float(lon_lat[1])
        except:
            continue
        res.append([addre, lon, lat])
    result = pd.DataFrame(res)
    result.columns = ['address', 'longitude', 'latitude']
    print(result)
    result.to_excel('result.xlsx', index = False)

Out:

 address   longitude   latitude
0  北京市朝阳区  116.601144  39.948574
1  北京市海淀区  116.329519  39.972134
2  上海市黄浦区  121.469240  31.229860
3  上海市虹口区  121.505133  31.264600

But how could I get the final result as follows? Thanks for your kind help at advance.

   id             name address   longitude   latitude
0   1        ABC tower  北京市朝阳区  116.601144  39.948574
1   2          AC park  北京市海淀区  116.329519  39.972134
2   3      ZR hospital  上海市黄浦区  121.469240  31.229860
3   4  Fengtai library     NaN         NaN        NaN
4   5     Square Point  上海市虹口区  121.505133  31.264600

Upvotes: 0

Views: 93

Answers (1)

Ferris
Ferris

Reputation: 5601

use pd.merge, as result is the longitude & latitude dataframe.

dfn = pd.merge(df, result, on='address', how='left')

or

for _, row in df.iterrows():
    _id = row['id']
    name = row['name']
    addre = row['address']
    
    if pd.isna(row['address']):
        res.append([_id, name, addre, None, None])
        continue

    ###### same code  ######
    url = '...'
    # ...
    ###### same code  ######
        res.append([_id, name, addre, lon, lat])
    result = pd.DataFrame(res)
    result.columns = ['id', 'name', 'address', 'longitude', 'latitude']
    print(result)
    result.to_excel('result.xlsx', index = False)

Upvotes: 1

Related Questions