CODEWITHSUNDEEP
pythonexcellistpandas
student17
student17

Reputation: 791

How to extract data from an excel column to a list in Python?

I'm trying to extract data from a column in excel to a list in Python. I have the following code:

#Extracting Labels
read = pd.read_excel('Test-data-results.xlsx', sheetname=0) # can also index sheet by name or fetch all sheets
labels = read['Labels'].tolist()
print(labels)

When I run this code I get a Key Error: ....

 File "pandas/_libs/index.pyx", line 140, in pandas._libs.index.IndexEngine.get_loc

 File "pandas/_libs/index.pyx", line 162, in pandas._libs.index.IndexEngine.get_loc

 File "pandas/_libs/hashtable_class_helper.pxi", line 1492, in pandas._libs.hashtable.PyObjectHashTable.get_item

 File "pandas/_libs/hashtable_class_helper.pxi", line 1500, in pandas._libs.hashtable.PyObjectHashTable.get_item

 KeyError: 'Labels'

Is this the proper way to append the data into a list? Is there a way to extract the data from just the column cell letter (column A)?

Upvotes: 1

Views: 2619

Answers (1)

jpp
jpp

Reputation: 164613

Is there a way to extract the data from just the column cell letter (column A)?

Pandas allows indexing by column names (also known as headers) or integer location. Since column A is the first column, you can use the latter option via pd.DataFrame.iloc:

read = pd.read_excel(...)
labels = read.iloc[:, 0].tolist()

If you need a more dynamic solution and have no more than 26 columns, you can use a dictionary mapping:

from string import ascii_uppercase

d = {v: k for k, v in enumerate(ascii_uppercase)}
labels = read.iloc[:, d['A']].tolist()

In general, though, it's a good idea to use the column name if you know this beforehand. Your error occurs because Pandas cannot find a column labeled 'Labels'. Try printing read.columns to see what columns are being read in by Pandas.

Upvotes: 4

Related Questions