CODEWITHSUNDEEP
python
user11343524
user11343524

Reputation: 15

What wrong with this pandas read excel problem?

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

Upvotes: 1

Views: 261

Answers (1)

Aditya Santoso
Aditya Santoso

Reputation: 1071

This looks like a classic python loop gotcha.

Consider this:

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

Outputs

9

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

0
1
2
3
4
5
6
7
8
9

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

Upvotes: 1

Related Questions