How can I iterate over Pandas pivot table? (A multi-index dataframe?)

I have a pivot table I want to iterate over, to store in a database.

age weekly_income category_weekly_income category_age High income Middle aged 45.527721 15015.463667 Old 70.041456 14998.104486 Young 14.995210 15003.750822 Low income Middle aged 45.548155 1497.228548 Old 70.049987 1505.655319 Young 15.013538 1501.718198 Middle income Middle aged 45.516583 6514.830294 Old 69.977657 6494.626962 Young 15.020688 6487.661554

I've played with reshape, melt, various for loops, syntax stabs in the dark, chains of stacks, unstacks, reset_indexes, etc.. The closest I have got is the syntax:


With this I can pull individual value cells, however I then can't get the value of the indexes.


You don't need to iterate the dataframe, Pandas has already provided a method to convert dataframe to sql by DataFrame.to_sql(...).

Alternatively, if you want to manually insert data into database, you can use Pandas' to_csv(), for example:

I have a df like this:

df A B first second bar one 0.826425 -1.126757 two 0.682297 0.875014 baz one -1.714757 -0.436622 two -0.366858 0.341702 foo one -1.068390 -1.074582 two 0.863934 0.043367 qux one -0.510881 0.215230 two 0.760373 0.274389 <hr> # set header=False, and index=True to get the MultiIndex from pivot print df.to_csv(header=False, index=True) bar,one,0.8264252111679552,-1.1267570930327846 bar,two,0.6822970851678805,0.8750144682657339 baz,one,-1.7147570530422946,-0.43662238320911956 baz,two,-0.3668584476904599,0.341701643567155 foo,one,-1.068390451744478,-1.0745823278191735 foo,two,0.8639343368644695,0.043366628502542914 qux,one,-0.5108806384876237,0.21522973766619563 qux,two,0.7603733646419842,0.2743886250125428

This will provide you a nice comma-delimited format which will be easily be used in sql execute query, something like:

data = [] for line in df.to_csv(header=False, index=True).split('\n'): if line: data.append(tuple(line.split(','))) data [('bar', 'one', '0.8264252111679552', '-1.1267570930327846'), ('bar', 'two', '0.6822970851678805', '0.8750144682657339'), ('baz', 'one', '-1.7147570530422946', '-0.43662238320911956'), ('baz', 'two', '-0.3668584476904599', '0.341701643567155'), ('foo', 'one', '-1.068390451744478', '-1.0745823278191735'), ('foo', 'two', '0.8639343368644695', '0.043366628502542914'), ('qux', 'one', '-0.5108806384876237', '0.21522973766619563'), ('qux', 'two', '0.7603733646419842', '0.2743886250125428')]

Then it's only the matter of doing an executemany:

... stmt = "INSERT INTO table (first, second, A, B) VALUES (%s, %s, %s, %s)" cursor.executemany(stmt, data) ...

Hope this helps.


  • How to measure the time of a Maya script?
  • SQL to select records for a specific date given created time and modified time
  • Does Maya Mel have a good API to a IDE? [closed]
  • Why is image stored different than the one imshowed?
  • Store a numpy.ndarray as an image, and then save the pixel values
  • How to disable or enable the chart visibility in BIRT
  • How to create a Pivot table when fields are on horizontal and vertical axes
  • Ending the first successful thread in gcc C Linux
  • 2d form for data entry in Access
  • How to show column at Crosstab even the data is absent
  • Does GCHandle.Alloc do anything beyond keeping a reference to an object?
  • How to check for phone network on Android devices
  • Multiply all columns of one matrix by another matrix with bsxfun
  • Extracting rows from df based on multiple conditions in R
  • Expand two large data files and apply using data.table?
  • How to deal with concurrency issues brought by NSStream run loop scheduling using GCD?
  • what is for…in statement in javascript
  • Aligning a Stack pointer 8 byte from 4 byte in ARM assembly
  • How to find the number of equal elements in javascript array
  • Socket.io on socket disconnect
  • PHP Order array based on elements dependency
  • Setting Unknown Array Boundaries and Loop
  • SCSS Language Injection in Pycharm
  • Configure WAF Application Gateway in front of App Services [closed]
  • Pytest/Allure - How to generate testcase description?
  • is this code truly private? (python)
  • Fraction length
  • mapping between two ontologies
  • C Program Compiled with Cygwin and Called from Python Hangs
  • Cannot invoke my method on the array type int[]
  • How do you create a Fuseki SPARQL server using the Apache Jena Java API?
  • Lua: Line breaks in strings
  • pandas computation in each group
  • Excel VBA How to populate a multi-dimensional (3d) array with values from multiple excel ranges?
  • Iron Router: How do I send data to the layout?
  • Primefaces ManyCheckbox inside ui:repeat calls setter method only for last loop
  • jQuery: add elements until a particular height is reached
  • Record samples being played with OpenAL
  • Using jQuery closest() method with class selector
  • How do I rollback to a specific git commit