如何在熊猫中执行复杂的连接，从统计模型输出中使用交互项

Question

这是这个问题的扩展： 联接复杂的熊猫表

我在statsmodelsGLM 中有三种不同的交互。 我需要一个将系数与其他单变量分析结果配对的最终表。

下面是模型中婚姻状况和年龄交互的表外观示例。final_table是包含单变量结果的表。我想将模型结果中的系数值(以及其他统计数据、p_values、standard_error等)连接到最终表(这在下面的代码中model_results)。

df = {'variable': ['CLded_model','CLded_model','CLded_model','CLded_model','CLded_model','CLded_model','CLded_model'
,'married_age','married_age','married_age', 'class_cc', 'class_cc', 'class_cc', 'class_cc', 'class_v_age'
,'class_v_age','class_v_age', 'class_v_age'],
'level': [0,100,200,250,500,750,1000, 'M_60', 'M_61', 'S_62', 'Harley_100', 'Harley_1200', 'Sport_1500', 'other_100'
,'Street_10', 'other_20', 'Harley_15', 'Sport_10'],
'value': [460955.7793,955735.0532,586308.4028,12216916.67,48401773.87,1477842.472,14587994.92,10493740.36,36388470.44
,31805316.37, 123.4, 4546.50, 439854.23, 2134.4, 2304.5, 2032.30, 159.80, 22]}

final_table1 = pd.DataFrame(df)
final_table1

将上述内容与统计模型将结果传达给以下不同的方式结合起来：

df2 = {'variable': ['intercept','driver_age_model:C(marital_status_model)[M]', 'driver_age_model:C(marital_status_model)[S]'
, 'CLded_model','C(class_model)[Harley]:v_age_model', 'C(class_model)[Sport]:v_age_model'
,'C(class_model)[Street]:v_age_model', 'C(class_model)[other]:v_age_model'
, 'C(class_model)[Harley]:cc_model', 'C(class_model)[Sport]:cc_model' , 'C(class_model)[Street]:cc_model'
, 'C(class_model)[other]:cc_model']
,'coefficient': [-2.36E-14,-1.004648e-02,-1.071730e-02, 0.00174356,-0.07222433,-0.146594998,-0.168168491,-0.084420399
,-0.000181233,0.000872798,0.001229771,0.001402564]}
model_results = pd.DataFrame(df2)
model_results

具有所需的最终结果：

df3 = {'variable': ['intercept', 'CLded_model','CLded_model','CLded_model','CLded_model','CLded_model','CLded_model','CLded_model'
,'married_age','married_age','married_age', 'class_cc', 'class_cc', 'class_cc', 'class_cc', 'class_v_age'
,'class_v_age','class_v_age', 'class_v_age'],
'level': [None,0,100,200,250,500,750,1000, 'M_60', 'M_61', 'S_62', 'Harley_100', 'Harley_1200', 'Sport_1500', 'other_100'
,'Street_10', 'other_20', 'Harley_15', 'Sport_10'],
'value': [None, 460955.7793,955735.0532,586308.4028,12216916.67,48401773.87,1477842.472,14587994.92,10493740.36,36388470.44
,31805316.37, 123.4, 4546.50, 439854.23, 2134.4, 2304.5, 2032.30, 159.80, 22],
'coefficient': [-2.36E-14, 0.00174356,  0.00174356,  0.00174356,  0.00174356,  0.00174356 ,0.00174356 , 0.00174356
,-1.004648e-02, -1.004648e-02,-1.071730e-02,-1.812330e-04,-1.812330e-04,8.727980e-04,1.402564e-03
,-1.681685e-01, -8.442040e-02, -1.812330e-04, -1.465950e-01]}
results = pd.DataFrame(df3)
results

当我实现第一个答案时，它影响了这个答案。

Answer 1

df = {'variable': ['CLded_model','CLded_model','CLded_model','CLded_model','CLded_model','CLded_model','CLded_model','married_age','married_age','married_age'],
'level': [0,100,200,250,500,750,1000, 'M_60', 'M_61', 'S_62'],
'value': [460955.7793,955735.0532,586308.4028,12216916.67,48401773.87,1477842.472,14587994.92,10493740.36,36388470.44,31805316.37]}

df2 = {'variable': ['intercept','driver_age_model:C(marital_status_model)[M]', 'driver_age_model:C(marital_status_model)[S]', 'CLded_model'],
'coefficient': [-2.36E-14,-1.004648e-02,-1.071730e-02, 0.00174356]}
df3 = {'variable': ['intercept', 'CLded_model','CLded_model','CLded_model','CLded_model','CLded_model','CLded_model','CLded_model','married_age','married_age','married_age'],
'level': [None, 0,100,200,250,500,750,1000, 'M_60', 'M_61', 'S_62'],
'value': [None, 60955.7793,955735.0532,586308.4028,12216916.67,48401773.87,1477842.472,14587994.92,10493740.36, 36388470.44,31805316.37],
'coefficient': [-2.36E-14, 0.00174356,  0.00174356,  0.00174356,  0.00174356,  0.00174356 ,0.00174356 , 0.00174356,-1.004648e-02, -1.004648e-02,-1.071730e-02]}
final_table = pd.DataFrame(df)
model_results = pd.DataFrame(df2)
results = pd.DataFrame(df3)
# Change slightly df to match what we're going to merge
final_table.loc[final_table['variable'] == 'married_age', 'variable'] = 'married_age-'+final_table.loc[final_table['variable'] == 'married_age', 'level'].str[0]
# Clean df2 and get it ready for merge
model_results['variable'] = model_results['variable'].str.replace('driver_age_model:C(marital_status_model)[', 'married_age-')
.str.strip(']')
# Merge
df4 = final_table.merge(model_results, how = 'outer', left_on = 'variable', right_on = 'variable')
#Clean
df4['variable'] = df4['variable'].str.replace('-.*', '', regex = True)

与上次几乎相同，唯一的区别是清洁 df2 的方式。