-2

I am having *.xlsx file like this

Key   value1 value2
test1  3      5
test2  6      7
test3  6      5

need to convert this into dictionary like below

dict = {"test1":["3","5"]},{"test2":["6","7"]},{"test3":["6","5"]}

Code:

import pandas as pd
df = pd.read_excel('ExcelFile.xlsx')

As I am new to python, your help is highly appreciated.

usmanharoon
  • 173
  • 2
  • 13
  • What do you mean by "excel"? Excel is a program. Do you want to read an XLS file? Or a CSV file? Your question cannot be answered as asked. – DYZ May 29 '21 at 06:55
  • Edited the question. – usmanharoon May 29 '21 at 06:59
  • Start with `import pandas as pd` and `pd.read_excel('your/path/here.xlsx')`, then come back with an example of your data. Do you really want to have integer values as strings in your result? – Wouter May 29 '21 at 08:09

2 Answers2

2

This can also be done without pandas, the openpyxl module lets you manipulate excel files in python::

from openpyxl import load_workbook

wb = load_workbook(filename='toto.xlsx')
ws = wb.active

d = {}
for row in list(ws.rows)[1:]:
    d[row[0].value] = [str(c.value) for c in row[1:]]
joao
  • 2,220
  • 2
  • 11
  • 15
0

Zip together the last two columns, and zip the first column with that.

>>> import pandas as pd
>>> df = pd.read_excel('Excel File.xlsx')
>>> df
    Key value1  value2
0   test1   3   5
1   test2   6   7
2   test3   6   5
>>> dict(zip(t.Key, zip(t.value1, t.value2)))
{'test1': (3, 5), 'test2': (6, 7), 'test3': (6, 5)}

If you want the values to be strings and not integers, check how to change column types in Pandas

Safwan Samsudeen
  • 1,645
  • 1
  • 10
  • 25