9.5 某集团为研究商品销售点所在的地理位置、销售点处的广告、销售点的装横这3个因素对商品的影响度，选了3个位置，2种广告形式，2种装横档次在4个城市进行了搭配实验，如表中所示，试在显著水平0.05下

import pandas as pd
from statsmodels.formula.api import ols
from statsmodels.stats.anova import anova_lm
import openpyxl

column_names = ['城市1', '城市2', '城市3', '城市4']
factor_combinations = [
f"位置{i+1}广告形式{j+1}高档" for i in range(3) for j in range(2)
] + [
f"位置{i+1}广告形式{j+1}低档" for i in range(3) for j in range(2)
]

df_raw = pd.read_excel('9.5.xlsx', header=None, names=column_names, index_col=0)
df_raw.index = factor_combinations

location_factor = [int(x.split('位置')[1].split('广告')[0]) for x in df_raw.index]
ad_factor = [int(x.split('广告形式')[1].split('高档')[0]) if '高档' in x else int(x.split('广告形式')[1].split('低档')[0]) for x in df_raw.index]
decoration_factor = ['高档' if '高档' in x else '低档' for x in df_raw.index]

data = {
'销售量': df_raw.values.flatten(),
'位置': location_factor * 4,
'广告形式': ad_factor * 4,
'装横档次': decoration_factor * 4
}
df = pd.DataFrame(data)

city_factor = [f'城市{i+1}' for i in range(4) for _ in range(3 * 2)]
df['城市'] = city_factor

model = ols('销售量 ~ C(位置) + C(广告形式) + C(装横档次) + C(城市)', data=df).fit()
anova_table = anova_lm(model, typ=2)

print(anova_table)

significant_factors = anova_table[anova_table['PR(>F)'] < 0.05]['source']
print("在显著水平0.05下，以下因素对销售量有显著差异：")
print(significant_factors)