I want to extract excel columns (NOT rows) into python arrays of array. It has to be arrays, not dictionaries.
The excel file looks like this:
A B C
1 123 534 576
2 456 745 345
3 234 765 285
I want to bring this into python in the following format:
[[123,534,576],[456,745,345],[234,765,285]]
How would I do this? Thank you
import xlrd
book = xlrd.open_workbook('your.xlsx')
sheet = book.sheet_by_name('example')
data = [[sheet.cell_value(r, c) for c in range(sheet.ncols)] for r in range(sheet.nrows)]
# Profit !
print(data)
data. Thanks.If you're following the above comments and look into the xlrd package, can you try this and see if it works?
(based on what I found here: http://www.youlikeprogramming.com/2012/03/examples-reading-excel-xls-documents-using-pythons-xlrd/)
import xlrd
workbook = xlrd.open_workbook('my_workbook.xls')
worksheet = workbook.sheet_by_name('Sheet1')
num_rows = worksheet.nrows - 1
curr_row = 0
#creates an array to store all the rows
row_array = []
while curr_row < num_rows:
row = worksheet.row(curr_row)
row_array += row
curr_row += 1
print(row_array)
Use xlrd to load the data row-wise, then use zip to transpose it.
>>>
>>> a = [[1,2,3],[4,5,6],[7,8,9]]
>>> zip(*a)
[(1, 4, 7), (2, 5, 8), (3, 6, 9)]
>>>
Use xlrd to load the data row-wise, use it to create a numpy array, then transpose it.
>>> import numpy
>>> a = [[1,2,3],[4,5,6],[7,8,9]]
>>> z = numpy.array(a)
>>> z
array([[1, 2, 3],
[4, 5, 6],
[7, 8, 9]])
>>> z.transpose()
array([[1, 4, 7],
[2, 5, 8],
[3, 6, 9]])
>>>
I figured it out.
import csv
cr = csv.reader(open("temp.csv","rb"))
arr = range(100) # adjust to needed
x = 0
for row in cr:
arr[x] = row
x += 1
print(arr[:22]) # adjust to needed
import csv
array = []
with open(* insert file directory here*) as fin:
reader = csv.reader(fin)
rows = [row for row in reader]
for row in rows:
j = 0
arr = []
for i = 0 < 3:
arr[i] = row[i]
array[j] = arr
j = j + 1
import csv
csv_rows = csv.reader(open("temp.csv","r"))
result_array = []
for row_index, row in enumerate(csv_rows):
if row_index != 0: #to neglect column names row
result_array.append(row)
print(result_array)
xlrdpackagexlrdto pull the columns?[[123, 456, 234], [534, 745, 765], [576, 345, 285]]- either way, usingxlrdit'srow_valuesorcol_values- the documentation is fairly simple to follow...