Converting an xlsx file to a dictionary in Python pandas-CodePudding

I am trying to import a dataframe from an xlsx file to Python and then convert this dataframe to a dictionary. This is how my Excel file looks like:

  A B
1 a b
2 c d

where A and B are names of columns and 1 and 2 are names of rows.

I want to convert the data frame to a dictionary in python, using pandas. My code is pretty simple:

import pandas as pd
my_dict = pd.read_excel(‘.\inflation.xlsx’, sheet_name = ‘Sheet2’, index_col=0).to_dict()
print(my_dict)

What I want to get is:

 {‘a’:’b’, ‘c’:’d’}

But what I get is:

{‘b’:{‘c’:’d’}}

What might be the issue?

CodePudding user response：

This should work

import pandas as pd
my_dict = pd.read_excel(‘.\inflation.xlsx’, sheet_name = ‘Sheet2’,header = 0 index_col=None).to_dict('records')
print(my_dict)

CodePudding user response：

This does what is requested:

import pandas as pd

d = pd.read_excel(‘.\inflation.xlsx’, sheet_name = ‘Sheet2’,index_col=0,header=None).transpose().to_dict('records')[0]
print(d)

Output:

{'a': 'b', 'c': 'd'}

The to_dict() function takes an orient parameter which specifies how the data will be manipulated. There are other options if you have more rows.