0

I need some help in converting multiple columns into individual observations. Last time with your help, I tried to convert Demand columns, now I have to add more columns like Jobs and PO 12 columns of each and want to convert as three individual observations and then later calculate Future free column (Future Free = Max(Job,PO)-Demand) 

from sqlalchemy import create_engine
import pandas as pd
import matplotlib.pyplot as plt
import datetime as dt
import calendar 
from pandas.tseries.offsets import MonthEnd 
engine = create_engine('mssql+pyodbc://server/driver=SQL+Server')
con=engine.connect()
rs=con.execute("""Select StockCode, Demand00, Demand01, Demand02, 
Demand03, Demand04, Demand05, Demand06, Demand07, Demand08, Demand09, 
Demand10, Demand11 from ForecastData""")
df= pd.DataFrame(rs.fetchall())
df.columns = ["StockCode", "Demand01","Demand02", "Demand03", "Demand04", 
"Demand05", "Demand06","Demand07", "Demand08", "Demand09", "Demand10", 
"Demand11", "Demand12"]
df.set_index('StockCode')
demand_columns=[i for i in df.columns if i.startswith('Demand')]
today=pd.Timestamp.now()
month_list=[(today+pd.DateOffset(months=i)) for i in 
range(len(demand_columns))]
dic_month={col:month for col,month in zip(demand_columns,month_list)}
df.rename(columns=dic_month) 
df2=pd.DataFrame(df.rename(columns=dict(zip(demand_columns,month_list))).set_ 
index('StockCode').stack()).reset_index()
df2.columns = ['StockCode', 'Month', 'Value']
df2['Month'] = pd.to_datetime(df2['Month'], format = '%Y%m').dt.date

Previous Output

StockCode     Month     Value
ABC       2019-01-01    100
ABC       2019-02-01    80
BXY       2019-01-01    50

Desired Output

StockCode Month  Demand Job  PO FutureFree
ABC       January  100   120 0  20
ABC       February 120   80  0  0
BXY       January  50    00 60  10
fightclub
  • 3
  • 3
  • 1
    This is unreadable. Plus the dataframe you gave as `Previous Output` is an intermediate step. Please provide the source dataframe example as well. – Erfan Apr 11 '19 at 21:10
  • The source dataframe structure is `StockCode Demand00 Demand01 Demand02......... Demand11` – fightclub Apr 12 '19 at 15:16
  • Find some inspiration in https://stackoverflow.com/a/20159305/463796 how to write a good (and short!) pandas example with reproducible data. – w-m Apr 13 '19 at 13:24

0 Answers0