I have a report that I am working on to show variance between two quarterly periods. I have an SQL query that I am reading into a pandas dataframe, and then pivoting.
Here is my code for that piece:
df = pd.read_sql_query(mtd_query, cnxn, params=[report_start, end_mtd, report_start, end_mtd, whse])
##(m-1)//3 + 1 Determine which Quarter each month is
## Create the "Period" column by combining the Quater and the Month
df['QUARTER'] = (df['INV_MONTH'].astype(int) - 1)//3 + 1
df['PERIOD'] = df['INV_YEAR'].astype(str) + 'Q' + df['QUARTER'].astype(int).astype(str)
df['MARGIN'] = (df['PROFIT'].astype(float) / df['SALES'].astype(float))
df = df.drop('INV_MONTH', axis=1)
df = df.drop('INV_YEAR', axis=1)
df = pd.pivot_table(df, index=['REP', 'REP_NAME', 'CUST_NO', 'CUST_NAME', 'TOTALSALES'], columns=['PERIOD'], values=['SALES', 'PROFIT', 'MARGIN'], fill_value=0)
df = df.reorder_levels([1, 0], axis=1).sort_index(axis=1, ascending=False)
df = df.sortlevel(level=0, ascending=True)
I am trying to determine the difference between the 'MARGIN' columns between 'PERIOD'. I have been unable to find any way to accomplish this. Any suggestion is appreciated.
Current Output shows:
PERIOD 2017Q4 2017Q3 2017Q2 2017Q1 2016Q4
SALES PROFIT MARGIN SALES PROFIT MARGIN SALES PROFIT MARGIN SALES PROFIT MARGIN SALES PROFIT MARGIN
REP REP_NAME CUST_NO CUST_NAME TOTALSALES
1.0 Greensboro - House 245.0 TE CONNECTIVITY CORPORATION 103361.05 0.000000 0.000000 0.000000 434.500000 69.520000 0.160000 20391.666667 3262.666667 0.160000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000
1789.0 GOOD HOUSEKEEPER 50108.47 678.508182 80.170909 0.145883 585.301429 64.180476 0.121915 718.685000 92.033125 0.130453 720.729333 97.955333 0.134821 1237.308333 88.210000 0.099450
Desired Output would look like below:
PERIOD 2017Q4 2017Q3 2017Q2 2017Q1 2016Q4
SALES PROFIT MARGIN VARIANCE SALES PROFIT MARGIN VARIANCE SALES PROFIT MARGIN VARIANCE SALES PROFIT MARGIN VARIANCE SALES PROFIT MARGIN
REP REP_NAME CUST_NO CUST_NAME TOTALSALES
1.0 Greensboro - House 245.0 TE CONNECTIVITY CORPORATION 103361.05 0.000000 0.000000 0.000000 -.16 434.500000 69.520000 0.160000 0 20391.666667 3262.666667 0.160000 .16 0.000000 0.000000 0.000000 0 0.000000 0.000000 0.000000
1789.0 GOOD HOUSEKEEPER 50108.47 678.508182 80.170909 0.145883 .023968 585.301429 64.180476 0.121915 -0.008537 718.685000 92.033125 0.130453 -.004368 720.729333 97.955333 0.134821 .035372 1237.308333 88.210000 0.099450
df.to_dict('r') below:
[{('2016Q4', 'SALES'): 0.0, ('2017Q3', 'PROFIT'): 69.520000000000067, ('2017Q1', 'PROFIT'): 0.0, ('2017Q2', 'SALES'): 20391.666666666668, ('2017Q3', 'MARGIN'): 0.16, ('2016Q4', 'PROFIT'): 0.0, ('2017Q3', 'SALES'): 434.5, ('2017Q1', 'SALES'): 0.0, ('2017Q4', 'SALES'): 0.0, ('2016Q4', 'MARGIN'): 0.0, ('2017Q4', 'PROFIT'): 0.0, ('2017Q1', 'MARGIN'): 0.0, ('2017Q4', 'MARGIN'): 0.0, ('2017Q2', 'MARGIN'): 0.16, ('2017Q2', 'PROFIT'): 3262.6666666666665}, {('2016Q4', 'SALES'): 1237.3083333333332, ('2017Q3', 'PROFIT'): 64.180476190476185, ('2017Q1', 'PROFIT'): 97.9553333333333, ('2017Q2', 'SALES'): 718.68500000000006, ('2017Q3', 'MARGIN'): 0.1219152103415191, ('2016Q4', 'PROFIT'): 88.209999999999994}]
I couldn't get the above solution to work.
So I did this:
df['MARGIN'] = (df['PROFIT'].astype(float) / df['SALES'].astype(float))
df['MARGIN'] = df['MARGIN'].astype(float)
df['PREV_MARGIN'] = df['MARGIN'].shift(-1)
df['VARIANCE'] = df['MARGIN'] - df['PREV_MARGIN']
df = df.drop('PREV_MARGIN', axis=1)
This gets me the data I need to complete my work.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments