What wrong with this pandas read excel problem?

Question

I want to read a large number of coordinates(about 14000+) from a excel file and transform them into specific address through an API from Baidu map. But the program can only read the last coordinate. Is there any problem in my code? Here is my code:

import requests
import pandas as pd
import json

df = pd.read_excel(r'C:\Users\YDC\Desktop\JW.xlsx')
fw = open(r'C:\Users\YDC\Desktop\result.txt', "w", encoding="utf-8")

for i in range(0,len(df)):
    t1=df.iloc[i]['lat']
    t2=df.iloc[i]['lng']

baiduUrl = "http://api.map.baidu.com/geocoder/v2/?ak=21q0bMSgjdDVe0gLmjClrsuyUA1mvsRx&callback=renderReverse&location=%s,%s&output=json&pois=0" % (t1, t2)
req = requests.get(baiduUrl)
content = req.text
content = content.replace("renderReverse&&renderReverse(", "")
content = content[:-1]
baiduAddr = json.loads(content)
country = baiduAddr["result"]["addressComponent"]["country"]
city = baiduAddr["result"]["addressComponent"]["city"]
province = baiduAddr["result"]["addressComponent"]["province"]

new_line = country + "|" + city + "|" + province
fw.write(new_line)
fw.write("\n")

print(new_line)

It can only print the address of the last coordinate: Czech Republic|Olomouc|Olomouc

How to get the rest of these coordinates? Here is the data in excel file

Is there not indentation issue from line 12 onwards? Otherwise t1 and t2 are undeclared — Aditya Santoso, Apr 11 '19 at 03:09
t1 and t2 refer to latitude and logitude which are defined in line 9 and 10 — user11343524, Apr 11 '19 at 03:47
Yes, but line 12 onwards are done *outside* of the loop because of the indentation. So you only execute line 12 onwards once, outside the loop, and only take the last value of t1 and t2 — Aditya Santoso, Apr 11 '19 at 03:50

score 1 · Accepted Answer · answered Apr 11 '19 at 03:53

This looks like a classic python loop gotcha.

Consider this:

for i in range(0, 10):
    foo = i
print (foo) # notice the indentation

Outputs

That's because in python, the variable scope is such that you can still reference variables that are defined inside the loop from outside the loop.

A very simple fix like such:

for i in range(0, 10):
    foo = i
    print (foo)

Gives the expected result

In your case, just make sure that line 12 onwards is indented to the right by one more level.

Related: Scoping in Python 'for' loops

Thanks for your valuable comment! I have fixed the problem. – user11343524 Apr 11 '19 at 04:41 — user11343524, Apr 11 '19 at 04:41

What wrong with this pandas read excel problem?

1 Answers1