Importing coordinates from excel - Python

Question

I am new to python and i am trying to import a set of x,y coordinates from excel file into python.

Each column has data in this format - ('1050','728') to multiple rows with shape size of almost (Rows = 34, columns = 10)

How can i import this in tuple/list or data frame in - (x,y) format

coordinates data in excel form

You could parse your excel line by line and append the string values of each cell in the line to a list, or another type of object (such as dictionary). You could then parse the entire structure and convert each entry using `ast.literal_eval()`. Or you could import your entire excel into a pandas dataframe and convert each value with the aforementioned method. — BoboDarph, Feb 22 '19 at 11:40
Maybe this is what you want? https://stackoverflow.com/questions/54805903/how-to-import-entire-sheet-to-list-via-openpyxl/54806210#54806210 — N00b, Feb 22 '19 at 12:55

raphael · Accepted Answer · 2019-02-22T11:59:07.527

How about using pandas together with it's read_excel function (I've never tried to use it so far) ... alternatively, export the excel-file into a csv-file and use read_csv or numpy's loadtext

once you have the strings (str) from each cell in python, you can convert them into the x- and y values via

x, y = str.replace('(', '').replace(')','').split(',')

for example

import numpy as np
data = ['(1,2)', '(3,4)', '(5,6)', '(77,8)']
x, y = np.array([i.replace('(', '').replace(')','').split(',') for i in data], dtype=int).T

score 0 · Answer 2 · answered Feb 22 '19 at 14:07

In case you don't wanna use numpy, you can try something like this:

# python 2.7
from ast import literal_eval

import xlrd  # pip install xlrd


wb = xlrd.open_workbook('PATH/TO/FILE.xlsx')  # get Excel workbook
sheet = wb.sheet_by_name(wb.sheet_names()[0])  # get first sheet

result = []
for i in xrange(sheet.nrows):  # loop over all filled rows
    # add all non-empty values to result list
    result.extend([literal_eval(cell.value) for _, cell in enumerate(sheet.row(i)) if cell.ctype != xlrd.XL_CELL_EMPTY])

print result

Importing coordinates from excel - Python

2 Answers2