CODEWITHSUNDEEP
pythonexcelpython-2.7dictionary
Zeno
Zeno

Reputation: 65

Python: create a dictionary using data from excel file

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!

Upvotes: 2

Views: 8564

Answers (3)

KKCH
KKCH

Reputation: 79

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'}

Upvotes: 1

brunormoreira
brunormoreira

Reputation: 248

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}

Upvotes: 3

eli-bd
eli-bd

Reputation: 1634

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'}

Upvotes: 2

Related Questions