Search code examples
python-3.xpandasordereddictionary

How to conver excel contents into python dictionary

I have an excel spreadsheet with 1st column = IP subnet & the 2nd column = Firewall name, trying to use this data to create a dictionary. e.g. excel format -

10.1.1.0/24 ASA_01

10.2.2.0/24 ASA_02

10.3.3.0/24 ASA_03

I am using pandas module to achieve this, however, the dictionary format doesn't look correct.

The required output should be something like this - {'10.1.1.0/24' : 'ASA_01', '10.2.2.0/24': 'ASA_02', '10.3.3.0/24': 'ASA_03'}

However, the output I am getting is this - {'ASA_01': {'10.2.2.0/24': 'ASA_02', '10.3.3.0/24': 'ASA_03'}}

Here is the code that I am using-

fw = pandas.read_excel(host_file, index_col=0).to_dict()

print(fw)

Solution

  • There is no first row with columns names, so add parameter header=None and then select second column called 1 for Series:

    fw = pandas.read_excel(host_file, index_col=0, header=None)[1].to_dict()