Excel file to Python dictionary

Question

I have an Excel table where Column A is a list of emails and column B is a customer ID.

I need to create a Python dictionary and have key=email address and value = customer ID.

Desired results:

dict = {email@domain.com :'customer ID'}

My code is below:

import pandas as pd
excel = "excel_file.xlsx"
list_dict = pd.read_excel(excel, index_col=0).to_dict()
print list_dict

However the dictionary is printing like this:

{u'customer ID': {u'email@domain.com': u'customer ID}}

What am I doing wrong here?

Read https://stackoverflow.com/questions/2464959/whats-the-u-prefix-in-a-python-string — Kamal Nayan, Jul 04 '18 at 13:45
Possible duplicate of [What's the u prefix in a Python string?](https://stackoverflow.com/questions/2464959/whats-the-u-prefix-in-a-python-string) — jpp, Jul 04 '18 at 14:04
For making this question self-contained, please post a short snippet that constructs the DataFrame with 2-3 lines in-place, without having to refer to a unavailable Excel file. Your question seems to be mostly about the to-dict conversion, not the input format. (Hint: simply reproducing the output of `df.head()` returned by `pd.read_excel(...)` should be enough.) — ojdo, Jul 04 '18 at 14:09

score 1 · Answer 1 · answered Jul 04 '18 at 14:30

1

If your excel file looks like this:

Then you could do:

import pandas as pd
excel = "excel_file.xlsx"
list_dict = pd.read_excel(excel, header=None).to_dict('list')
print {k: v for k, v in zip(*list_dict.values())}

(tested on Python3, im sure it works in Python2 as well -> be careful about Unicode)

Output:

C:\projects\python\test\so_pandas_dict>python main.py
{'example@gmail.com': 'customer ID', 'example1@gmail.com': 'customer ID1'}

answered Jul 04 '18 at 14:30

minecraftplayer1234

2,127
4
27
57

Then it's a problem with your Excel file. If you have the exact same file scheme as I've shown you, then it works. You probably have more columns, or something like this. Then you could replace `zip(*list_dict.values())` with `zip(*(list_dict.values()[:2]))` if the columns you want to get are A and B – minecraftplayer1234 Jul 05 '18 at 18:43
ahh it was, however I still see the ubnicode {u'email@.com':u'link here'} – NULL.Dude Jul 06 '18 at 14:38
Its because you use Python2, not Python3 – minecraftplayer1234 Jul 06 '18 at 21:20
If that's all, you don't have any following questions, you can accept my answer – minecraftplayer1234 Jul 08 '18 at 15:06

Excel file to Python dictionary

1 Answers1