有没有办法在excel文件中输出包含给定城市和国家的纬度和经度的数据帧

Question

我有一个包含两列的excel文件。第一列是"；城市"；第二列是"0"；国家"；。我希望我的python代码循环遍历每一行，并找到每一行的纬度和经度。python代码创建两个新列"；纬度；以及"；经度；根据需要，但对所有值都返回None。感谢您的帮助。

import pandas as pd
from geopy.geocoders import Nominatim
geolocator = Nominatim(user_agent="Your_Name")
df = pd.read_excel("location.xlsx")
longitude = []
latitude = []
city = df["City"]
country = df["Country"]
for i in df.index:
loc = geolocator.geocode(city + ',' + country)
if loc is None:
latitude.append(None)
longitude.append(None)
else:
latitude.append(loc.latitude)
longitude.append(loc.longitude)
df["Longitude"] = longitude
df["Latitude"] = latitude
print(df)

Answer 1

使用下面给定的数据帧df：

import pandas as pd
data = {"City": ["London", "Berlin", "Madrid", "Rome", 
"Paris", "Vienna", "Bucharest", "Hamburg", 
"Budapest", "Warsaw", "Barcelona", 
"Munich", "Milan"],
"Country": ["England", "Germany", "Spain", "Italy",
"France", "Austria", "Romania", 
"Germany", "Hungary", "Poland", "Spain",
"Germany", "Italy"]
}
df = pd.DataFrame(data)

您可以使用geolocator.geocode:获取城市/国家的纬度/经度

from geopy.geocoders import Nominatim
out = (
df.assign(Geocodes= (df['City'] + ", " + df['Country']).apply(geolocator.geocode))
.assign(Latitude = lambda x: [g.latitude for g in x['Geocodes']],
Longitude= lambda x: [g.longitude for g in x['Geocodes']])
.drop(columns='Geocodes')
)

#输出：

print(out)
City  Country   Latitude  Longitude
0      London  England  51.507322  -0.127647
1      Berlin  Germany  52.517037  13.388860
2      Madrid    Spain  40.416705  -3.703582
3        Rome    Italy  41.893320  12.482932
4       Paris   France  48.858890   2.320041
5      Vienna  Austria  48.208354  16.372504
6   Bucharest  Romania  44.436141  26.102720
7     Hamburg  Germany  53.550341  10.000654
8    Budapest  Hungary  47.497994  19.040359
9      Warsaw   Poland  52.231958  21.006725
10  Barcelona    Spain  41.382894   2.177432
11     Munich  Germany  48.137108  11.575382
12      Milan    Italy  45.464194   9.189635

Answer 2

您错过了对城市和国家系列进行索引。

只需在代码中尝试loc = geolocator.geocode(city[i] + ',' + country[i])即可。它对我有用。