1

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.

Improve this question

2 Answers 2

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

This code works, but it has weird characters all around the keys and values: {1L: [u'dmvyc'], 4L: [u'aorbe', u'ebphb', u'nprrj'], 5L: [u'fgyfg'], 6L: [u'ralno'], 7L: [u'zaysd', u'wmklg', u'gsdhy', u'cqiki']}
that's the representation of the dict, don't worry if you print each element you won't get that. (u is for unicode and L is for long integer)
No even if I print print age_and_names[6] it prints [u'ralno']
@Di437 That's because you are printing the list. If you want just an element in the list you have to index inside the list: print age_and_names[6][0] Also, you should probably be using Python 3.
1

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.