Pandas dataframe return column header linked to data value for each row

Question

I am using pandas to read any CSV files and then insert the rows into a database. I will be doing this with SQLAlchemy.

I will not know the header names or size, so it has to be dynamic. Assume the database rules will ensure data validity.

I am trying to map the column header to each data value. See below my current dataframe:

  Example 1 Example 2 Example 3 Example 4
        Cat       Dog     Mouse     Horse
        Cow       Ant       Pig  Elephant

Here is my desired outputted list:

Example 1=Cat, Example 2=Dog, Example 3=Mouse, Example 4=Horse
Example 1=Cow, Example 2=Ant, Example 3=Pig, Example 4=Elephant

I have tried using zip and iterrows with the below code:

    for index, data in df.iterrows():
        mylist.append(data.values)

    myzip = zip(columns, mylist)

    for z in myzip:
        print(z)

but this is producing one column header per multiple values as seen below:

('Example 1', array(['Cat', 'Dog', 'Mouse', 'Horse'], dtype=object))
('Example 2', array(['Cow', 'Ant', 'Pig', 'Elephant'], dtype=object))

Any help would be greatly appreciated as not sure what function I need to use. I'm aware of to_sql but I need to create an insert statement per row. Thanks

df.to_dict(orient='records') will help you. In addition, SQLAlchemy support pandas insert. — giser_yugang
– giser_yugang, Commented Apr 10, 2019 at 8:44

rup · Accepted Answer · 2019-04-10 13:17:56Z

1

@giser_yugang hits the ideal solution. Pandas has inbuilt method DataFrame.to_dict(orient='dict') which converts the dataframe and returns a dictionary, where the key-value pair can be customized using the parameter orient.
'records' amongst the 'orient' gives the kind of result you want.

So your dataframe:

After using:

df.to_dict(orient='records')

gives:

[{'Example 1': 'Cat',
  'Example 2': 'Dog',
  'Example 3': 'Mouse',
  'Example 4': 'Horse'},
 {'Example 1': 'Cow',
  'Example 2': 'Ant',
  'Example 3': 'Pig',
  'Example 4': 'Elephant'}]

edited Apr 10, 2019 at 13:17

rup

5035 silver badges17 bronze badges

answered Apr 10, 2019 at 11:54

Yash Nag

1,26513 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Pandas dataframe return column header linked to data value for each row

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related