How to write multiple rows in column excel python?

Question

I have 'column A' contains Hotel Name, i want to write 'loc.address' for each hotel at 'column B' in excel

ex:

i use this code:

import pandas as pd
from geopy.geocoders import Nominatim
import xlrd

# Give the location of the file
loc = "C:/Users/UI UX/Desktop/test.xlsx"


# To open Workbook
wb = xlrd.open_workbook(loc)
sheet = wb.sheet_by_index(0)

sheet.cell_value(0, 0)

for i in range(sheet.nrows):
    hotel_column = (sheet.cell_value(i, 0))
    geolocator = Nominatim(user_agent="abd")
    
    loc = geolocator.geocode(hotel_column, country_codes='', language='')
    if loc is None:
        print('Cant find latitude & longitude of this place :(')
    else:
        print("latitude is :", loc.latitude, "\nlongitude is:", loc.longitude)
        print('Location Address: ' + loc.address)
        print('---------------------------------------------------')

You need to find and install some third-party module that make it possible for you read and write Excel files. — martineau, Apr 27 '21 at 09:53
I just need to loop over cells in specific column and enter data — Abdullah Md, Apr 27 '21 at 10:00
Entering data will require at least modifying some in-memory representation of the sheet regardless of whether you write the final results to a file. A module that allows both reading and writing Excel data should support doing this. — martineau, Apr 27 '21 at 10:04
can't you create a new excel file or overwrite the existing one? — Jayvee, Apr 27 '21 at 10:23
@AbdullahMd I posted an answer using openpyxl that can help you achieve the update. I noticed that you reused the variable loc that's why I hardcoded the file read and save, but you can fix that and use a variable — Jayvee, Apr 27 '21 at 11:21

Jayvee · Accepted Answer · 2021-04-27T11:29:22.670

1

openpyxl can update xlsx files

import pandas as pd
from geopy.geocoders import Nominatim
import xlrd
import openpyxl

# To open Workbook
loc = "C:/Users/UI UX/Desktop/test.xlsx"
wb = xlrd.open_workbook(loc)
sheet = wb.sheet_by_index(0)

sheet.cell_value(0, 0)

locs=[]
for i in range(sheet.nrows):
    hotel_column = (sheet.cell_value(i, 0))
    geolocator = Nominatim(user_agent="abd")
    
    loc = geolocator.geocode(hotel_column, country_codes='', language='')
    if loc is None:
        print('Cant find latitude & longitude of this place :(')
        locs.append('Cant find latitude & longitude of this place :(')
    else:
        print("latitude is :", loc.latitude, "\nlongitude is:", loc.longitude)
        print('Location Address: ' + loc.address)
        print('---------------------------------------------------')
        locs.append('Location Address: ' + loc.address)

xfile = openpyxl.load_workbook("C:/Users/UI UX/Desktop/test.xlsx")
sheet = xfile.worksheets[0]

for i,val in enumerate(locs):
    sheet.cell(row=i+1, column=2).value = val
xfile.save('C:/Users/UI UX/Desktop/test.xlsx')

edited Apr 27 '21 at 11:29

answered Apr 27 '21 at 11:17

Jayvee

10,670
3
29
40

@Jeyvee AttributeError: 'Location' object has no attribute 'append' – Abdullah Md Apr 27 '21 at 11:59
1

@AbdullahMd method append is being used only in locs which is a list created just before the reading loop, I just tried the code myself and can't reproduce the error. If you adapted this code please make sure locs is defined as list and that you are appending strings to it. – Jayvee Apr 27 '21 at 12:32
if i want to print loc.latitude for each hotel how can do that? – Abdullah Md Apr 27 '21 at 14:20
1

you can populate the list locs with "latitude is: " + str(loc.latitude) – Jayvee Apr 27 '21 at 14:26

How to write multiple rows in column excel python?

1 Answers1