Untitled

[6/6 4:01 PM] Ganesh, Karthik (HM Health Solutions Inc)
q1 = pd.read_excel(r'O MM V_Q1 Extract.xlsx', sheet_name='Q1')

q2 = pd.read_excel(r'O MM V_Q2 Extract.xlsx', )

q3 = pd.read_excel(r'O MM V_Q3 Extract.xlsx', )
 
q1['key'] = q1['CPC']+q1['CPBR_STATEMENT']

q2['key'] = q2['CPC']+q2['CPBR_STATEMENT']

q2        = q2[['key', 'PRICING_DEF']]
 
q1_q2     = pd.merge(left=q1, right=q2, on='key',how='left')

q3_part1  = q3[q3['CPBR_STMT_TYPE']=='FIXED PARAMETER'].reset_index(drop=True)

q3_part2  = q3[q3['CPBR_STMT_TYPE']=='VARIABLE PARAMETER'].reset_index(drop=True)
 
final = pd.merge(left=q1_q2, right=q3_part1, on='PRICING_DEF', how='left')

final = pd.merge(left=final, right=q3_part2, on='PRICING_DEF', how='left')


extract1 file cols - CPC	ELEMENT_NAME	ELEMENT_NAME_SHORT	VALUE_NAME	ORDER_N	CPBR_STATEMENT	CPBRE_SHORT_STMT	CPBR_STMT_TYPE	ELEMENT_ORDER	DATA_ELEMENT	OPERATOR_TYP	ARGUMENT	TAB_TYPE	RESULT_ACTION	RESULT_OBJECT

extract2 file cols - CPC	ELEMENT_NAME	ELEMENT_NAME_SHORT	VALUE_NAME	ORDER_N	CPBR_STATEMENT	CPBRE_SHORT_STMT	PRICING_DEF	CPBR_STMT_TYPE	ELEMENT_ORDER	DATA_ELEMENT	OPERATOR_TYP	ARGUMENT	TAB_TYPE	RESULT_ACTION	RESULT_OBJECT

extract3 file cols - PRICING_DEF	PRICING_DEF_DESC	CPBR_STMT_TYPE	ELEMENT_ORDER	DATA_ELEMENT	OPERATOR_TYP	ARGUMENT	TAB_TYPE	RESULT_ACTION	RESULT_OBJECT

 Concatenate column ORDER and CPBR_Statemtnt as primary key
Split Extract 1 on based CPBRE_STATEMENT_TYPE into 3 new extract
Condition
FIXED PAram
Variable Param
Merge extract based on primary key
Repeat Step 2-3 for extract 3
Merge extract 2 and 3 based 'PRICING_DEF'
 Repeat step one one Extract 2
Merge extract 1 and 2 based on primary key