Convert data from an excel file into a python dictionary

Question

I'm trying to convert data from an excel file to a python dictionary. My excel file has has two columns and many rows.

Name    Age
Steve   11
Mike    10
John    11

How do I go about adding this into a dictionary with Age as the key and name as the value? Also, if many names have the same age, they should all be in an array. For example:

{'11':['Steve','John'],'10':['Mike']}

What I've written so far:

import xlsxwriter
import openpyxl

wb = openpyxl.load_workbook('demo.xlsx')
sheet = wb.get_sheet_by_name('Sheet1')

#print sheet.cell(row=2, column=2).value


age_and_names = {}

for i in range(1,11):

    age = sheet.cell(row=i, column=2).value
    name = sheet.cell(row=i, column=1).value  

#Problem seems to be in this general area
    if not age in age_and_names:
        age_and_names[age]=[]

        age_and_names[age].append(name)    

print age_and_names

What should I have done for the desired output? I'm very new to python. All help will be appreciated. Thank You.

Answer 1

Just a simple indentation error and your code is incorrect

#Problem seems to be in this general area
    if not age in age_and_names:
        age_and_names[age]=[]
        age_and_names[age].append(name)    

should be

#Problem seems to be in this general area
    if not age in age_and_names:
        age_and_names[age]=[]

    age_and_names[age].append(name)    

Otherwise you destroy previous data from age_and_names[age].

You should consider using collections.defaultdict instead to avoid testing if key exists:

Declare like this

from collections import defaultdict

age_and_names = defaultdict(list)

Use like this:

age_and_names[12].append("Mike")

If the dict has no key 12, it will invoke the list method and will create an empty list for you. No need to test if key exists in the first place.

Answer 2

For this case, use collections.defaultdict instead of a plain dictionary {}); collections.defaultdict takes a factory function that is used to construct values for new keys. Use list to construct an empty list for each key:

import collections
age_and_names = collections.defaultdict(list)

...
     age_and_names[age].append(name)

No ifs are needed.