Skip to content

Unpivot or melt with pandas and SQLAlchemy

Here’s the script to melt data with pandas and SQLAlchemy.

import pandas as pd
import sqlalchemy, urllib
params = urllib.parse.quote_plus(DB_STRING)
            Select distinct [column to pivot on], columns to unpivot
            from table;
engine = sqlalchemy.create_engine("mssql+pyodbc:///?odbc_connect=%s" % params, fast_executemany=True)
df = pd.read_sql_query(QUERY, con = engine)
results =pd.melt(df, id_vars=['column to pivot on'], value_vars=['dx1',
'dx21', 'dx22', 'dx23', 'dx24', 'dx25', 'dx26', 'dx27', 'dx28', 'dx29',
'dx41', 'dx42', 'dx43', 'dx44', 'dx45', 'dx46', 'dx47', 'dx48', 'dx49'])
results.to_sql('newTable_Unpiv', con = engine, if_exists = 'replace', index=False)
See also  Find the Median - Hackerrank Challenge - C# Solution

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.