CODEWITHSUNDEEP
pythonlistdictionaryxlrd
Arman
Arman

Reputation: 907

Read Excel file and write the data to a dictionary

I have a Excel file with two columns like the following:

Reviews| Numbers
text1 | 1,3
text2 | 2
text3 | 1
text4 | 2
text5 | 1

I am reading the data as follows:

def read_xls(filename):
    book = xlrd.open_workbook(filename)
    sheet = book.sheet_by_index(0)
    data=[]
    for row_index in xrange(1, sheet.nrows): # skip heading row
        reviews, numbers = sheet.row_values(row_index, end_colx=2)
        data.append(reviews)

But instead of getting the columns as separate lists, I want the result in a dictionary as follows:

{1: [text1, text3, text5], 2: [text2, text4], 3: [text1]}

How do I do that?

Upvotes: 1

Views: 1414

Answers (2)

M07
M07

Reputation: 1131

It should work with the code below

def read_xls(filename):
    book = xlrd.open_workbook(filename)
    sheet = book.sheet_by_index(0)
    data = {}
    for row_index in xrange(1, sheet.nrows): # skip heading row
        reviews, numbers = sheet.row_values(row_index, end_colx=2)
        number_list = numbers.split(",")
        for number in number_list: 
            if number not in data:
                data[number] = []
            data[number].append(reviews)

Upvotes: 1

akuiper
akuiper

Reputation: 215117

You can use a defaultdict:

from collections import defaultdict
data = defaultdict(list)

for row_index in xrange(1, sheet.nrows): 
    reviews, numbers = sheet.row_values(row_index, end_colx=2)
    data[numbers].append(reviews)

Upvotes: 1

Related Questions