Read excel file with pandas and store values into Python dict with the column names as keys-CodePudding

I have following excel:

type        name          latitude    longitude
--------------------------------------------
area        area1         50.33       4.23
building    building1     -           -

I'm using pandas to read in the excel file using following function:

def read_excel(self,sheet_name):
    df = pd.read_excel(io=self.excel_file, sheet_name=sheet_name)
    dict = df.to_dict()

I get following output:

{
  'type': { 0: 'area', 1: 'building' }, 
  'name': { 0: 'area1', 1: 'building1' }, 
  'latitude': { 0: 50.33, 1: nan }, 
  'longitude': { 0: 4.23, 1: nan }
}

I would like to have the following output:

[
    {
         'type': 'area', 
         'name': 'area1',
         'latitude': 50.33, 
         'longitude': 4.23
    }, 
    {
         'type': 'building', 
         'name': 'building1', 
         'latitude': nan, 
         'longitude': nan
    }
]

To achieve this, I have written the following function:

def read_excel(self,sheet_name):
        df = pd.read_excel(io=self.excel_file, sheet_name=sheet_name)
        dict = df.to_dict()

        objects = []

        for i in range(0,len(df.index)):
            temp = {}
            temp['type'] = dict['type'][i]
            temp['name'] = dict['name'][i]
            temp['latitude'] = dict['latitude'][i]
            temp['longitude'] = dict['longitude'][i]
            objects.append(temp)

        print(objects)

This produces the output I want. However, I would like to have a solution that is more dynamic, e.g. that I don't need create a temp dict with assigning statically column names.

Any suggestions to achieve this?

CodePudding user response：

pass orient='records' to to_json :)

always refer to the documentation as a debugging step! https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.to_dict.html

CodePudding user response：

Did you try orienting it as records:

print(df.to_dict(orient='records'))