I've an Excel File
Arm_id DSPName DSPCode HubCode PinCode PPTL
1 JaVAS 01 AGR 282001 1,2
2 JaVAS 01 AGR 282002 3,4
3 JaVAS 01 AGR 282003 5,6
I want to save a string in the form Arm_id,DSPCode,Pincode
. This format is configurable, i.e. it might change to DSPCode,Arm_id,Pincode
. I save it in a list like:
FORMAT = ['Arm_id', 'DSPName', 'Pincode']
How do I read the content of a specific column with provided name, given that the FORMAT
is configurable?
This is what I tried. Currently I'm able to read all the content in the file
from xlrd import open_workbook
wb = open_workbook('sample.xls')
for s in wb.sheets():
#print 'Sheet:',s.name
values = []
for row in range(s.nrows):
col_value = []
for col in range(s.ncols):
value = (s.cell(row,col).value)
try : value = str(int(value))
except : pass
col_value.append(value)
values.append(col_value)
print values
My output is
[[u'Arm_id', u'DSPName', u'DSPCode', u'HubCode', u'PinCode', u'PPTL'], ['1', u'JaVAS', '1', u'AGR', '282001', u'1,2'], ['2', u'JaVAS', '1', u'AGR', '282002', u'3,4'], ['3', u'JaVAS', '1', u'AGR', '282003', u'5,6']]
Then I loop around values[0]
trying to find out the FORMAT
content in values[0]
and then getting the index of Arm_id, DSPname and Pincode
in the values[0]
and then from next loop I know the index of all the FORMAT
factors , thereby getting to know which value do I need to get .
But this is such a poor solution.
How do I get the values of a specific column with name in excel file?
The approach I took reads the header information from the first row to determine the indexes of the columns of interest.
You mentioned in the question that you also want the values output to a string. I dynamically build a format string for the output from the FORMAT column list. Rows are appended to the values string separated by a new line char.
The output column order is determined by the order of the column names in the FORMAT list.
In my code below the case of the column name in the FORMAT list is important. In the question above you've got 'Pincode' in your FORMAT list, but 'PinCode' in your excel. This wouldn't work below, it would need to be 'PinCode'.
from xlrd import open_workbook
wb = open_workbook('sample.xls')
FORMAT = ['Arm_id', 'DSPName', 'PinCode']
values = ""
for s in wb.sheets():
headerRow = s.row(0)
columnIndex = [x for y in FORMAT for x in range(len(headerRow)) if y == firstRow[x].value]
formatString = ("%s,"*len(columnIndex))[0:-1] + "\n"
for row in range(1,s.nrows):
currentRow = s.row(row)
currentRowValues = [currentRow[x].value for x in columnIndex]
values += formatString % tuple(currentRowValues)
print values
For the sample input you gave above this code outputs:
>>> 1.0,JaVAS,282001.0
2.0,JaVAS,282002.0
3.0,JaVAS,282003.0
And because I'm a python noob, props be to: this answer, this answer, this question, this question and this answer.
So the key parts are to grab the header ( col_names = s.row(0)
) and when iterating through the rows, to skip the first row which isn't needed for row in range(1, s.nrows)
- done by using range from 1 onwards (not the implicit 0). You then use zip to step through the rows holding 'name' as the header of the column.
from xlrd import open_workbook
wb = open_workbook('Book2.xls')
values = []
for s in wb.sheets():
#print 'Sheet:',s.name
for row in range(1, s.nrows):
col_names = s.row(0)
col_value = []
for name, col in zip(col_names, range(s.ncols)):
value = (s.cell(row,col).value)
try : value = str(int(value))
except : pass
col_value.append((name.value, value))
values.append(col_value)
print values
A somewhat late answer, but with pandas, it is possible to get directly a column of an excel file:
import pandas
df = pandas.read_excel('sample.xls')
#print the column names
print df.columns
#get the values for a given column
values = df['Arm_id'].values
#get a data frame with selected columns
FORMAT = ['Arm_id', 'DSPName', 'Pincode']
df_selected = df[FORMAT]
Make sure you have installed xlrd and pandas:
pip install pandas xlrd
By using pandas we can read excel easily.
import pandas as pd
from pandas import ExcelWriter
from pandas import ExcelFile
DataF=pd.read_excel("Test.xlsx",sheet_name='Sheet1')
print("Column headings:")
print(DataF.columns)
Test at :https://repl.it Reference: https://pythonspot.com/read-excel-with-pandas/
Here is the code to read an excel file and and print all the cells present in column 1 (except the first cell i.e the header):
import xlrd
file_location="C:\pythonprog\xxx.xlsv"
workbook=xlrd.open_workbook(file_location)
sheet=workbook.sheet_by_index(0)
print(sheet.cell_value(0,0))
for row in range(1,sheet.nrows):
print(sheet.cell_value(row,0))
Although I almost always just use pandas for this, my current little tool is being packaged into an executable and including pandas is overkill. So I created a version of poida's solution that resulted in a list of named tuples. His code with this change would look like this:
from xlrd import open_workbook
from collections import namedtuple
from pprint import pprint
wb = open_workbook('sample.xls')
FORMAT = ['Arm_id', 'DSPName', 'PinCode']
OneRow = namedtuple('OneRow', ' '.join(FORMAT))
all_rows = []
for s in wb.sheets():
headerRow = s.row(0)
columnIndex = [x for y in FORMAT for x in range(len(headerRow)) if y == headerRow[x].value]
for row in range(1,s.nrows):
currentRow = s.row(row)
currentRowValues = [currentRow[x].value for x in columnIndex]
all_rows.append(OneRow(*currentRowValues))
pprint(all_rows)
Source: Stackoverflow.com