Python: create a dictionary using data from excel file

Question

I'm very new to Python and programming and I have an issue I can't resolve. I have an excel file with 3 columns:

Id      Object     kg
1142     Apple     17,5
1142     Banana    13,55
1142     Kiwi      52,3
1255     Apple     77,38
1255     Banana    99,42
1255     Kiwi      128,35

I want to create a dictionary that takes as key the Id and the Object and as value th kg (for instance {(1142, Apple) : 17,5, (1142, Banana) : 13,55, ...}

Is there a way to create such dictionary? What module should I import? Thanks in advance!

Have you already figured out how to get the data out of the Excel file, or do you need help with that too? — FamousJameous
– FamousJameous, Commented May 19, 2017 at 14:45
@FamousJameous I've seen that there are module like xlrd that should permit to get data from excel file, but I am so new to programming and I have difficulty on understand the documentation ... so I was asking for an help — Zeno
– Zeno, Commented May 19, 2017 at 14:49

brunormoreira · Accepted Answer · 2017-05-19 15:20:29Z

3

You could use pandas on it. I'd do:

import pandas as pd

d = pd.read_excel(r"excel_file.xlsx",index_col=[0,1]).to_dict(orient='index')
for key in d:
    d[key] = d[key].values()[0]

print d

The output is something like:

{(1255L, u'Kiwi'): 128.34999999999999, (1142L, u'Apple'): 17.5, (1255L, u'Apple'): 77.379999999999995, (1142L, u'Banana'): 13.550000000000001, (1255L, u'Banana'): 99.420000000000002, (1142L, u'Kiwi'): 52.299999999999997}

answered May 19, 2017 at 15:20

brunormoreira

2482 silver badges15 bronze badges

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

Comments

eli-bd · Accepted Answer · 2017-05-19 15:07:54Z

2

import csv
d = {}
with open('input.csv', 'rb') as csvfile:
    reader = csv.reader(csvfile, delimiter=',')
    for row in reader:  # type(row) = <list>
        d[(row[0], row[1])] = row[2]

print(d)

Notice that I set the csv delimiter to ',' and file name to 'input.csv'. Change it if you need.

When I used this as an input file:

1142,Apple,17
1143,Banana,13
1144,Kiwi,52

This was the output:

{('1143', 'Banana'): '13', ('1142', 'Apple'): '17', ('1144', 'Kiwi'): '52'}

edited May 19, 2017 at 15:07

answered May 19, 2017 at 14:58

eli-bd

1,6342 gold badges19 silver badges35 bronze badges

3 Comments

FamousJameous Over a year ago

OP said the input was an Excel file, not CSV.

Zeno Over a year ago

Thank you for the answer, but I think you misinterpreted the question. The dictionary should have the tuple (Id, Object) as key

eli-bd Over a year ago

My bad. Fixed it. Now it outputs the needed result.

KKCH · Accepted Answer · 2021-01-13 03:18:23Z

1

This is my answer

d = pd.read_excel('Name of File', engine='openpyxl')
d = d.set_index(['ID', 'Object'])["kg"].to_dict()

The output should be

{('1143', 'Banana'): '13', ('1142', 'Apple'): '17', ('1144', 'Kiwi'): '52'}

answered Jan 13, 2021 at 3:18

KKCH

7910 bronze badges

Collectives™ on Stack Overflow

Python: create a dictionary using data from excel file

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related