Example of using openpyxl & geopy #geopy #openpyxl

gistfile1.py

# -*- coding: utf-8 -*-
import sys
reload(sys)
sys.setdefaultencoding('utf-8')
from geopy.geocoders import Yandex
from openpyxl import load_workbook

filename = 'realty.xlsx'
wb = load_workbook(filename)
ws = wb.get_sheet_by_name('data')

geolocator = Yandex()

iterrows = ws.iter_rows()
next(iterrows)
for i,row in enumerate(iterrows):
    cell = row[2]
    location = geolocator.geocode(cell.value)
    AdministrativeArea = location.raw['metaDataProperty']['GeocoderMetaData']['AddressDetails']['Country']['AdministrativeArea']['AdministrativeAreaName']
    row[1].value = AdministrativeArea

wb.save(filename)