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)
QUERY = ''' SET NOCOUNT ON;
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)
