I'm trying to write a DataFrame that has MultiIndex columns to an MS SQL database. The index gets output as NULL. If I have just single columns, it works fine.
l1 = ['foo', 'bar']
l2 = ['a', 'b', 'c']
cols = pd.MultiIndex.from_product([l1, l2])
df = pd.DataFrame(np.random.random((3,6)), index=[1,2,3], columns=cols)
df.to_sql('test', conn, if_exists='replace')
Is this a bug or do I need to do something else to properly write the index?
You can concatenate each of the first levels of your dataframe:
l1 = ['foo', 'bar']
l2 = ['a', 'b', 'c']
cols = pd.MultiIndex.from_product([l1, l2])
df = pd.DataFrame(np.random.random((3,6)), index=[1,2,3], columns=cols)
pd.concat([df['foo'],df['bar']]).to_sql('test', conn, if_exists='replace')
This results in this table:
index a b c
-------------------- ---------------------- ---------------------- ----------------------
1 0.803555407060559 0.0185295254735488 0.702949767792433
2 0.257823384796912 0.985716269729717 0.749719964181681
3 0.909115063376081 0.236242172285058 0.932813789580215
1 0.898527697819921 0.874431627680823 0.805393798630385
2 0.97537971906356 0.319221893730643 0.584449093938984
3 0.678625747581189 0.606321574437647 0.437746301372623
If you want something closer to the SQL table example you link to, you could use merge and suffix each column:
l1 = ['foo', 'bar']
l2 = ['a', 'b', 'c']
cols = pd.MultiIndex.from_product([l1, l2])
df = pd.DataFrame(np.random.random((3,6)), index=[1,2,3], columns=cols)
pd.merge(df['foo'], df['bar'],
right_index=True, left_index=True,
suffixes=['_' + s for s in df.columns.levels[0].to_list()]
).to_sql('test', conn, if_exists='replace')
That will get you:
index a_bar b_bar c_bar a_foo b_foo c_foo
-------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ----------------------
1 0.989229457189419 0.0759829132299624 0.172846406489083 0.154227020200058 0.386003904079867 0.733402063652856
2 0.839971061213949 0.975761261358953 0.252917398323633 0.0881692963378311 0.560403977291031 0.806066332511174
3 0.914544313717528 0.921965094934119 0.821869705625485 0.337292501691803 0.125899685577926 0.527830968883373
I've just hit the same problem. Pandas now allows an index or column multiindex to be flattened with
df.columns = df.columns.to_flat_index()
Do that first, then
df.to_sql('test', conn, if_exists='replace')
The index is written and the column names are the same as your SQL output.
If you don't like the strange SQL columns names another option is to modify the pandas column names instead by joining both levels, ie.
df.columns = ['_'.join(i) for i in df.columns.to_flat_index()]
print(df.columns)
Index(['foo_a', 'foo_b', 'foo_c', 'bar_a', 'bar_b', 'bar_c'], dtype='object')