import pandas as pd

df = pd.read_csv('乌蒙大草原旅游问卷调查_final.csv')

print(df.shape)
df.head()

# 先把答题时间转换为时间格式
def convert_to_seconds(time_str):
    if '分' in time_str:
        minutes, seconds = time_str[:-1].split('分')
        return int(minutes) * 60 + int(seconds)
    else:
        return int(time_str[:-1])

df['答题时长'] = df['答题时长'].astype(str)
df['答题时长'] = pd.to_timedelta(df['答题时长'].apply(convert_to_seconds), unit='s')

# 删除答题时间小于1分钟的数据
df = df[df['答题时长'] > '00:01:00']

print(df.shape)
df.head()

import seaborn as sns
import matplotlib.pyplot as plt

# 查看Q2有没有非数字的数据
df['Q2'].unique()

# 修改数据
df['Q2'] = df['Q2'].replace('1995', 29)
df['Q2'] = df['Q2'].replace('50\n\n50', '50')

df['Q2'].unique()

df['Q2'] = pd.to_numeric(df['Q2'], errors='coerce')
print(df['Q2'].dtypes)

# 检查df中是否有重复的行
df_duplicates = df.duplicated().sum()
print(f"df中有{df_duplicates}行重复的数据。")

# 列名列表
columns = ['Q1', 'Q2', 'Q3', 'Q3|open', 'Q4', 'Q5', 'Q6|1|open', 'Q6|2|open', 'Q6|3|open', 'Q7|1', 'Q7|4', 'Q7|2', 'Q7|3', 'Q7|5', 'Q7|6', 'Q8', 'Q9', 'Q10', 'Q11', 'Q12', 'Q13', 'Q14', 'Q14|open', 'Q15', 'Q16|6', 'Q16|1', 'Q16|2', 'Q16|3', 'Q16|7', 'Q16|4', 'Q16|10', 'Q16|11', 'Q16|9', 'Q16|9|open', 'Q17', 'Q18', 'Q19', 'Q20', 'Q21|R1', 'Q21|R2', 'Q21|R3', 'Q21|R4', 'Q21|R5', 'Q22|1', 'Q23', 'Q24|R1', 'Q24|R2', 'Q24|R3', 'Q24|R4', 'Q24|R5', 'Q24|R6', 'Q24|R7', 'Q24|R8', 'Q24|R9', 'Q24|R10', 'Q24|R11', 'Q24|R12', 'Q24|R13', 'Q24|R14', 'Q24|R18', 'Q24|R15', 'Q24|R16', 'Q24|R17', 'Q25|R5', 'Q25|R8', 'Q25|R10', 'Q25|R13', 'Q25|R14', 'Q25|R15', 'Q25|R16', 'Q25|R17', 'Q25|R18', 'Q26|1', 'Q27|1', 'Q28|1', 'Q28|23', 'Q28|21', 'Q28|22', 'Q28|24', 'Q28|25', 'Q28|4', 'Q28|2', 'Q28|26', 'Q28|27', 'Q28|28', 'Q28|29', 'Q28|35', 'Q28|34', 'Q28|34|open', 'Q29|R1', 'Q29|R2', 'Q29|R3', 'Q29|R4', 'Q30|R1', 'Q30|R2', 'Q30|R3', 'Q30|R4', 'Q30|R5', 'Q30|R6', 'Q31|R1', 'Q31|R2', 'Q31|R8', 'Q31|R4', 'Q31|R7', 'Q31|R9', 'Q31|R6', 'Q31|R5', 'Q32|R3', 'Q32|R7', 'Q32|R4', 'Q32|R5', 'Q32|R1', 'Q32|R2', 'Q32|R6', 'Q33|R10', 'Q33|R12', 'Q33|R11', 'Q33|R4', 'Q33|R5', 'Q33|R1', 'Q33|R8', 'Q33|R7', 'Q34', 'IP地址']

# 找出重复的行
duplicates = df.duplicated(subset=columns)

# 只保留没有重复的行
df = df[~duplicates]

# 打印数据的形状
print(df.shape)

columns_to_drop = ['答题序号', '来源', '开始时间', '提交时间', '答题时长', 'IP省份', 'IP城市', 'IP地址', '浏览器', '操作系统']
df = df.drop(columns_to_drop, axis=1)
print(df.shape)
df.head()

common_columns = ['Q1', 'Q2', 'Q3', 'Q3|open', 'Q4', 'Q5', 'Q6|1|open', 'Q6|2|open', 'Q6|3|open', 'Q7|1', 'Q7|4', 'Q7|2', 'Q7|3', 'Q7|5', 'Q7|6', 'Q8', 'Q9', 'Q10', 'Q11', 'Q12', 'Q13', 'Q14', 'Q14|open', 'Q15', 'Q16|6', 'Q16|1', 'Q16|2', 'Q16|3', 'Q16|7', 'Q16|4', 'Q16|10', 'Q16|11', 'Q16|9', 'Q16|9|open', 'Q17', 'Q30|R1', 'Q30|R2', 'Q30|R3', 'Q30|R4', 'Q30|R5', 'Q30|R6', 'Q33|R10', 'Q33|R12', 'Q33|R11', 'Q33|R4', 'Q33|R5', 'Q33|R1', 'Q33|R8', 'Q33|R7']
missing_values = df[common_columns].isnull().any()
print(missing_values)

missing_rows = df[df['Q2'].isnull()]
print(missing_rows)

# 依照Q17的选项1（去过），2（没去过）把数据分为去过的和没有去过的，同时保留原始数据

print(df['Q17'].value_counts())

# 去过的数据
df_gone = df[df['Q17'] == 1 ]

# 没去过的数据
df_not_gone = df[df['Q17'] == 2]

print("-"*100)
print("df_gone shape"+str(df_gone.shape))
print("df_not_gone shape"+str(df_not_gone.shape))

# 使用孤立森林算法检测异常值
from sklearn.ensemble import IsolationForest

# 对于去过的
# 屏蔽开放性问题
df_gone_IsolationForest = df_gone.drop(['Q3|open', 'Q6|1|open', 'Q6|2|open', 'Q6|3|open', 'Q14|open', 'Q16|9|open', 'Q28|34|open','Q34'], axis=1)
print("df_gone_IsolationForest shape"+str(df_gone_IsolationForest.shape))
# 屏蔽没有去过的问题，会出现nan
df_gone_IsolationForest = df_gone_IsolationForest.drop(['Q29|R1','Q29|R2','Q29|R3','Q29|R4'], axis=1)
print("df_gone_IsolationForest shape"+str(df_gone_IsolationForest.shape))

# 使用中位数填充数值列的空值
for col in df_gone_IsolationForest.columns:
    if df_gone_IsolationForest[col].dtype == 'int64' or df_gone_IsolationForest[col].dtype == 'float64':
         df_gone_IsolationForest[col] = df_gone_IsolationForest[col].fillna(df[col].median())

# 使用最常见的值填充类别列的空值
for col in df_gone_IsolationForest.columns:
    if df_gone_IsolationForest[col].dtype == 'object':
        df_gone_IsolationForest[col] = df_gone_IsolationForest[col].fillna(df_gone_IsolationForest[col].mode()[0])
        
# 对于没有去过的
# 屏蔽开放性问题
df_not_gone_IsolationForest = df_not_gone.drop(['Q3|open', 'Q6|1|open', 'Q6|2|open', 'Q6|3|open', 'Q14|open', 'Q16|9|open', 'Q28|34|open','Q34',], axis=1)
print("df_not_gone_IsolationForest shape"+str(df_not_gone_IsolationForest.shape))
# 屏蔽去过的问题，会出现nan
columns_to_drop_1 = ['Q19','Q20','Q21|R1','Q21|R2','Q21|R3','Q21|R4','Q21|R5']
columns_to_drop_2 = ['Q24|R1', 'Q24|R2', 'Q24|R3', 'Q24|R4', 'Q24|R5', 'Q24|R6', 'Q24|R7', 'Q24|R8', 'Q24|R9', 'Q24|R10', 'Q24|R11', 'Q24|R12', 'Q24|R13', 'Q24|R14', 'Q24|R18', 'Q24|R15', 'Q24|R16', 'Q24|R17']
columns_to_drop_3 = ['Q25|R5', 'Q25|R8', 'Q25|R10', 'Q25|R13', 'Q25|R14', 'Q25|R15', 'Q25|R16', 'Q25|R17', 'Q25|R18', 'Q26|1', 'Q27|1']
columns_to_drop_4 = ['Q28|1', 'Q28|23', 'Q28|21', 'Q28|22', 'Q28|24', 'Q28|25', 'Q28|4', 'Q28|2', 'Q28|26', 'Q28|27', 'Q28|28', 'Q28|29', 'Q28|35', 'Q28|34']
columns_to_drop_5 = ['Q31|R1', 'Q31|R2', 'Q31|R8', 'Q31|R4', 'Q31|R7', 'Q31|R9', 'Q31|R6', 'Q31|R5', 'Q32|R3', 'Q32|R7', 'Q32|R4', 'Q32|R5', 'Q32|R1', 'Q32|R2', 'Q32|R6']
columns_to_drop = columns_to_drop_1 + columns_to_drop_2 + columns_to_drop_3 + columns_to_drop_4 + columns_to_drop_5


df_not_gone_IsolationForest = df_not_gone_IsolationForest.drop(columns_to_drop, axis=1)
print("df_not_gone_IsolationForest shape"+str(df_not_gone_IsolationForest.shape))

# 使用中位数填充数值列的空值
for col in df_not_gone_IsolationForest.columns:
    if df_not_gone_IsolationForest[col].dtype == 'int64' or df_not_gone_IsolationForest[col].dtype == 'float64':
         df_not_gone_IsolationForest[col] = df_not_gone_IsolationForest[col].fillna(df[col].median())

# 使用最常见的值填充类别列的空值
for col in df_not_gone_IsolationForest.columns:
    if df_not_gone_IsolationForest[col].dtype == 'object':
        df_not_gone_IsolationForest[col] = df_not_gone_IsolationForest[col].fillna(df_not_gone_IsolationForest[col].mode()[0])

print(df_gone_IsolationForest.head(100))
print(df_not_gone_IsolationForest.head(100))

X = df_gone_IsolationForest.values
Y = df_not_gone_IsolationForest.values

# 创建孤立森林模型，contamination参数表示你预计的异常点的比例
clf_X = IsolationForest(contamination=0.01)
clf_Y = IsolationForest(contamination=0.01)

# 训练模型
clf_X.fit(X)
clf_Y.fit(Y)

# 预测异常点，返回1表示正常点，-1表示异常点
pred_X = clf_X.predict(X)
pred_Y = clf_Y.predict(Y)

# 将预测结果添加到数据框中
df_gone_IsolationForest['anomaly'] = pred_X
df_not_gone_IsolationForest['anomaly'] = pred_Y

# 打印出预测结果
print("-"*100+"\n去过的数据异常值预测结果：\n")
print(df_gone_IsolationForest['anomaly'].value_counts())
print("-"*100+"\n异常数据：\n")
print(df_gone_IsolationForest[df_gone_IsolationForest['anomaly'] == -1])
print("-"*100+"\n没有去过的数据异常值预测结果：\n")
print(df_not_gone_IsolationForest['anomaly'].value_counts())
print("-"*100+"\n异常数据：\n")
print(df_not_gone_IsolationForest[df_not_gone_IsolationForest['anomaly'] == -1])


# 在原来的df上删除异常值，先找到异常值的序号
print("删除异常值前df的形状"+str(df.shape))
df = df.drop(df_gone_IsolationForest[df_gone_IsolationForest['anomaly'] == -1].index)
df = df.drop(df_not_gone_IsolationForest[df_not_gone_IsolationForest['anomaly'] == -1].index)
print("删除异常值后df的形状"+str(df.shape))
# 在df_gone和df_not_gone上删除异常值
print("删除异常值前df_gone的形状"+str(df_gone.shape))
df_gone = df_gone.drop(df_gone_IsolationForest[df_gone_IsolationForest['anomaly'] == -1].index)
print("删除异常值后df_gone的形状"+str(df_gone.shape))
print("删除异常值前df_not_gone的形状"+str(df_not_gone.shape))
df_not_gone = df_not_gone.drop(df_not_gone_IsolationForest[df_not_gone_IsolationForest['anomaly'] == -1].index)
print("删除异常值后df_not_gone的形状"+str(df_not_gone.shape))


# 删除异常值
df_gone_IsolationForest = df_gone_IsolationForest[df_gone_IsolationForest['anomaly'] == 1]
df_not_gone_IsolationForest = df_not_gone_IsolationForest[df_not_gone_IsolationForest['anomaly'] == 1]

# 检查df中是否有重复的行
df_duplicates = df.duplicated().sum()
print(f"df中有{df_duplicates}行重复的数据。")

# 检查df_gone中是否有重复的行
df_gone_duplicates = df_gone.duplicated().sum()
print(f"df_gone中有{df_gone_duplicates}行重复的数据。")

# 检查df_not_gone中是否有重复的行
df_not_gone_duplicates = df_not_gone.duplicated().sum()
print(f"df_not_gone中有{df_not_gone_duplicates}行重复的数据。")

print(df.shape)
df.head()

print(df_gone.shape)
df_gone.head()

print(df_not_gone.shape)
df_not_gone.head()

# 去过的游客 df_gone

from kmodes.kprototypes import KPrototypes
import matplotlib.pyplot as plt

columns = ['Q1', 'Q2', 'Q3', 'Q4', 'Q5',  'Q7|1', 'Q7|4', 'Q7|2', 'Q7|3', 'Q7|5', 'Q7|6', 'Q8', 'Q9', 'Q10', 'Q11', 'Q12', 'Q13', 'Q14', 'Q15', 'Q19', 'Q20','Q21|R1','Q21|R2','Q21|R3','Q21|R4','Q21|R5']
numerical_columns = ['Q2', 'Q4', 'Q5','Q7|1', 'Q7|4', 'Q7|2', 'Q7|3', 'Q7|5', 'Q7|6']

# 类别型列
categorical_columns = [col for col in columns if col not in numerical_columns]

# 将类别型列的索引转换为数字
categorical_columns_indices = [columns.index(col) for col in categorical_columns]

costs = []
for n_clusters in range(1, 10):
    kproto = KPrototypes(n_clusters=n_clusters, init='Cao', verbose=2)
    kproto.fit_predict(df_gone[columns].values, categorical=categorical_columns_indices)
    costs.append(kproto.cost_)

# 绘制成本随簇数变化的图
plt.plot(range(1, 10), costs, 'o-')
plt.title('Elbow Method (visited tourists)')
plt.xlabel('Number of clusters')
plt.ylabel('Cost')
plt.show()

# 轮廓图，去过的游客

from kmodes.kprototypes import KPrototypes
from sklearn.metrics import silhouette_score
import matplotlib.pyplot as plt

columns = ['Q1', 'Q2', 'Q3', 'Q4', 'Q5',  'Q7|1', 'Q7|4', 'Q7|2', 'Q7|3', 'Q7|5', 'Q7|6', 'Q8', 'Q9', 'Q10', 'Q11', 'Q12', 'Q13', 'Q14', 'Q15', 'Q19', 'Q20','Q21|R1','Q21|R2','Q21|R3','Q21|R4','Q21|R5']
numerical_columns = ['Q2', 'Q4', 'Q5','Q7|1', 'Q7|4', 'Q7|2', 'Q7|3', 'Q7|5', 'Q7|6']

# 类别型列
categorical_columns = [col for col in columns if col not in numerical_columns]

# 将类别型列的索引转换为数字
categorical_columns_indices = [columns.index(col) for col in categorical_columns]

silhouette_scores = []
for n_clusters in range(2, 10):  # 轮廓系数至少需要2个簇
    kproto = KPrototypes(n_clusters=n_clusters, init='Cao', verbose=2)
    clusters = kproto.fit_predict(df_gone[columns].values, categorical=categorical_columns_indices)
    score = silhouette_score(df_gone[columns], clusters)
    silhouette_scores.append(score)

# 绘制轮廓系数随簇数变化的图
plt.plot(range(2, 10), silhouette_scores, 'o-')
plt.title('Silhouette Method (visited tourists)')
plt.xlabel('Number of clusters')
plt.ylabel('Silhouette Score')
plt.show()

from kmodes.kprototypes import KPrototypes

# 加入乌蒙大草原游客的基本信息，只能对已经来过的游客进行聚类
columns = ['Q1', 'Q2', 'Q3', 'Q4', 'Q5',  'Q7|1', 'Q7|4', 'Q7|2', 'Q7|3', 'Q7|5', 'Q7|6', 'Q8', 'Q9', 'Q10', 'Q11', 'Q12', 'Q13', 'Q14', 'Q15', 'Q19', 'Q20','Q21|R1','Q21|R2','Q21|R3','Q21|R4','Q21|R5']

# 数值型列
numerical_columns = ['Q2', 'Q4', 'Q5','Q7|1', 'Q7|4', 'Q7|2', 'Q7|3', 'Q7|5', 'Q7|6']

# 类别型列
categorical_columns = [col for col in columns if col not in numerical_columns]

# 将类别型列的索引转换为数字
categorical_columns_indices = [columns.index(col) for col in categorical_columns]

# 初始化 K-原型聚类器
kproto = KPrototypes(n_clusters=3, init='Cao', verbose=2)

clusters = kproto.fit_predict(df_gone[columns].values, categorical=categorical_columns_indices)

# 将聚类结果添加到数据集作为新的特征
df_gone['k-prototypes cluster'] = clusters

# 想查看每个簇的中心点
print(kproto.cluster_centroids_)

# 查看每个簇的数量
print(df_gone['k-prototypes cluster'].value_counts())

df_gone.head()

# 把'k-prototypes cluster'为0的数据保存到csv文件
df_gone[df_gone['k-prototypes cluster'] == 0].to_csv('cluster_0.csv', index=False)

from scipy.stats import f_oneway, chi2_contingency

# 对 numerical_columns 进行方差分析
for column in numerical_columns:
    groups = df_gone.groupby('k-prototypes cluster')[column].apply(list)
    f_val, p_val = f_oneway(*groups)
    if p_val < 0.05:
        print(f'Column: {column}, F-value: {f_val}, p-value: {p_val}. significant.')
    else:
        print(f'Column: {column}, F-value: {f_val}, p-value: {p_val}. NO.')

# 对 categorical_columns 进行卡方检验
for column in categorical_columns:
    contingency_table = pd.crosstab(df_gone[column], df_gone['k-prototypes cluster'])
    chi2, p_val, dof, expected = chi2_contingency(contingency_table)
    if p_val < 0.05:
        print(f'Column: {column}, Chi2: {chi2}, p-value: {p_val}. significant.')
    else:
        print(f'Column: {column}, Chi2: {chi2}, p-value: {p_val}. NO.')

from kmodes.kprototypes import KPrototypes

# # 列表中的列名
# columns = ['Q1', 'Q2', 'Q3', 'Q4', 'Q5', 'Q7|1', 'Q7|4', 'Q7|2', 'Q7|3', 'Q7|5', 'Q7|6', 'Q8', 'Q9', 'Q10', 'Q11', 'Q12', 'Q13', 'Q14', 'Q15']
# columns = ['Q2', 'Q4', 'Q5', 'Q8', 'Q10', 'Q11', 'Q12', 'Q13', 'Q14']
# 加入乌蒙大草原游客的基本信息，只能对已经来过的游客进行聚类
columns_again = ['Q4', 'Q5', 'Q7|5', 'Q10', 'Q19']

# # 数值型列
numerical_columns = ['Q4', 'Q5','Q7|5']
# numerical_columns = ['Q2', 'Q4', 'Q5']

# 类别型列
categorical_columns = [col for col in columns_again if col not in numerical_columns]

# 将类别型列的索引转换为数字
categorical_columns_indices = [columns_again.index(col) for col in categorical_columns]

# 初始化 K-原型聚类器
kproto = KPrototypes(n_clusters=2, init='Cao', verbose=2)

# 对所有游客进行聚类
# clusters = kproto.fit_predict(df[columns].values, categorical=categorical_columns_indices)
clusters = kproto.fit_predict(df_gone[columns_again].values, categorical=categorical_columns_indices)


# 将聚类结果添加到数据集作为新的特征
# df['k-prototypes cluster'] = clusters
df_gone['k-prototypes cluster'] = clusters

# 想查看每个簇的中心点
print(kproto.cluster_centroids_)

# 查看每个簇的数量
# print(df['k-prototypes cluster'].value_counts())
print(df_gone['k-prototypes cluster'].value_counts())


# df.head()
df_gone.head()

import matplotlib.pyplot as plt
import seaborn as sns
import pandas as pd

df_selected = df_gone

df_cluster_1 = df_selected[df_selected['k-prototypes cluster'] == 0]
df_cluster_2 = df_selected[df_selected['k-prototypes cluster'] == 1]
df_cluster_3 = df_selected[df_selected['k-prototypes cluster'] == 2]

clusters_list = [df_cluster_1, df_cluster_2, df_cluster_3]
cluster_names = ['人群 1', '人群 2','人群 3']

# 数值数据的描述性统计
desc_stats = []
for i, df_cluster in enumerate(clusters_list):
    desc_stats.append(df_cluster[numerical_columns].describe())

desc_stats_df = pd.concat(desc_stats, axis=1, keys=cluster_names)
print(desc_stats_df)

# 分类数据的各个类别的计数和比例
cat_counts = []
for i, df_cluster in enumerate(clusters_list):
    cat_counts.append(df_cluster[categorical_columns].apply(lambda x: x.value_counts()).T.stack() / df_cluster.shape[0])

cat_counts_df = pd.concat(cat_counts, axis=1, keys=cluster_names)
print(cat_counts_df)

####################
import matplotlib.pyplot as plt
import seaborn as sns
import pandas as pd
import numpy as np

# 设置全局字体
plt.rcParams['font.sans-serif'] = ['SimHei']  # 用来正常显示中文标签
plt.rcParams['axes.unicode_minus'] = False  # 用来正常显示负号

# 计算子图的行数和列数
n = len(numerical_columns)
ncols = 3
nrows = n // ncols if n % ncols == 0 else n // ncols + 1

# 创建一个大图和子图的数组，每个子图都有自己独立的y轴刻度
fig, axs = plt.subplots(nrows, ncols, figsize=(15, 5*nrows), sharey=False)

# 创建一个ExcelWriter对象
with pd.ExcelWriter('output.xlsx') as writer:
    # 描述性统计的可视化
    for idx, question in enumerate(numerical_columns):
        # 计算子图的位置
        row = idx // ncols
        col = idx % ncols

        # 创建一个空的数据框来存储所有的簇的数据
        all_clusters = pd.DataFrame()
        for i, df_cluster in enumerate(clusters_list):
            # 添加一个新的列来标识簇
            df_cluster = df_cluster.copy()
            df_cluster['Cluster'] = f'Cluster {i+1}'
            all_clusters = pd.concat([all_clusters, df_cluster])

        # 在子图上绘制小提琴图，这里将使用各自的数据范围设置y轴刻度
        sns.violinplot(x='Cluster', y=question, data=all_clusters, ax=axs[row, col])
        axs[row, col].set_title(f'各簇{question}的小提琴图')
        axs[row, col].set_ylabel('')

        # 将数据写入Excel文件的一个新工作表
        all_clusters.to_excel(writer, sheet_name=question)

# 删除多余的子图
if n % ncols != 0:
    for col in range(n % ncols, ncols):
        fig.delaxes(axs[nrows-1, col])

plt.tight_layout()
plt.show()

import matplotlib.pyplot as plt
import seaborn as sns
import pandas as pd
import numpy as np

# 设置全局字体
plt.rcParams['font.sans-serif'] = ['SimHei']  # 用来正常显示中文标签
plt.rcParams['axes.unicode_minus'] = False  # 用来正常显示负号

# 计算子图的行数和列数
n = len(categorical_columns)
ncols = 5
nrows = n // ncols if n % ncols == 0 else n // ncols + 1

# 创建一个大图和子图的数组
fig, axs = plt.subplots(nrows, ncols, figsize=(15, 5*nrows))

# 创建一个ExcelWriter对象
with pd.ExcelWriter('output.xlsx') as writer:
    # 描述性统计的可视化
    for idx, question in enumerate(categorical_columns):
        # 计算子图的位置
        row = idx // ncols
        col = idx % ncols

        # 创建一个空的数据框来存储所有的簇的数据
        all_clusters = pd.DataFrame()
        for i, df_cluster in enumerate(clusters_list):
            # 添加一个新的列来标识簇
            df_cluster = df_cluster.copy()
            df_cluster['Cluster'] = f'Cluster {i+1}'
            all_clusters = pd.concat([all_clusters, df_cluster], ignore_index=True)

        # 数据透视以便于绘图，计算每个簇每个分类的数量
        pivot_df = all_clusters.pivot_table(index='Cluster', columns=question, aggfunc='size').fillna(0)

        # 将数量转换为占比
        pivot_df_percent = pivot_df.div(pivot_df.sum(axis=1), axis=0) * 100  # 计算占总数的百分比

        # 绘制堆叠条形图，展示百分比
        pivot_df_percent.plot(kind='bar', stacked=True, ax=axs[row, col], legend=True)

        axs[row, col].set_title(f'各簇{question}的堆叠条形图')
        axs[row, col].set_ylabel('百分比 (%)')

        # 将数据写入Excel文件的一个新工作表
        pivot_df_percent.to_excel(writer, sheet_name=question)

# 删除多余的子图
if n % ncols != 0:
    for col in range(n % ncols, ncols):
        fig.delaxes(axs[nrows-1, col])

plt.tight_layout()
plt.show()

from sklearn.decomposition import PCA
from sklearn.manifold import TSNE
from umap import UMAP
import matplotlib.pyplot as plt
from mpl_toolkits.mplot3d import Axes3D

# 数据
# X = df[columns].values
X = df_gone[columns].values

# 创建PCA，t-SNE，UMAP对象
pca = PCA(n_components=2)
tsne = TSNE(n_components=2)
umap = UMAP(n_components=2)

# 降维
X_pca = pca.fit_transform(X)
X_tsne = tsne.fit_transform(X)
X_umap = umap.fit_transform(X)

# 创建图形和子图
fig, axs = plt.subplots(3, 2, figsize=(15, 20))

# 绘制二维版本
sc = axs[0, 0].scatter(X_pca[:, 0], X_pca[:, 1], c=clusters, cmap='viridis', alpha=0.5)
axs[0, 0].set_title('PCA 2D')
fig.colorbar(sc, ax=axs[0, 0], shrink=0.6)

sc = axs[1, 0].scatter(X_tsne[:, 0], X_tsne[:, 1], c=clusters, cmap='viridis', alpha=0.5)
axs[1, 0].set_title('t-SNE 2D')
fig.colorbar(sc, ax=axs[1, 0], shrink=0.6)

sc = axs[2, 0].scatter(X_umap[:, 0], X_umap[:, 1], c=clusters, cmap='viridis', alpha=0.5)
axs[2, 0].set_title('UMAP 2D')
fig.colorbar(sc, ax=axs[2, 0], shrink=0.6)

# 创建PCA，t-SNE，UMAP对象
pca = PCA(n_components=3)
tsne = TSNE(n_components=3)
umap = UMAP(n_components=3)

# 降维
X_pca = pca.fit_transform(X)
X_tsne = tsne.fit_transform(X)
X_umap = umap.fit_transform(X)

# 绘制三维版本
axs[0, 1] = fig.add_subplot(3, 2, 2, projection='3d')
sc = axs[0, 1].scatter(X_pca[:, 0], X_pca[:, 1], X_pca[:, 2], c=clusters, cmap='viridis', alpha=0.5)
axs[0, 1].set_title('PCA 3D')
fig.colorbar(sc, ax=axs[0, 1], shrink=0.6, pad=0.2)  # 修改的代码

axs[1, 1] = fig.add_subplot(3, 2, 4, projection='3d')
sc = axs[1, 1].scatter(X_tsne[:, 0], X_tsne[:, 1], X_tsne[:, 2], c=clusters, cmap='viridis', alpha=0.5)
axs[1, 1].set_title('t-SNE 3D')
fig.colorbar(sc, ax=axs[1, 1], shrink=0.6, pad=0.2)  # 修改的代码

axs[2, 1] = fig.add_subplot(3, 2, 6, projection='3d')
sc = axs[2, 1].scatter(X_umap[:, 0], X_umap[:, 1], X_umap[:, 2], c=clusters, cmap='viridis', alpha=0.5)
axs[2, 1].set_title('UMAP 3D')
fig.colorbar(sc, ax=axs[2, 1], shrink=0.6, pad=0.2)  # 修改的代码
# 调整子图之间的间隙
plt.subplots_adjust(wspace=0.5, hspace=0.5)

plt.tight_layout()
plt.show()

from sklearn.decomposition import PCA
import matplotlib.pyplot as plt

df_selected = df_gone

# 使用PCA进行降维
pca = PCA(n_components=2)
X_pca = pca.fit_transform(df_selected[columns].values)

# 绘制散点图
plt.figure(figsize=(8, 6))
plt.scatter(X_pca[:, 0], X_pca[:, 1], c=clusters, cmap='viridis', alpha=0.5)
plt.title('K-Prototypes Clustering (PCA-reduced data)')
plt.xlabel('Principal Component 1')
plt.ylabel('Principal Component 2')
plt.colorbar(label='Cluster')
plt.show()

from sklearn.decomposition import PCA
import matplotlib.pyplot as plt
from mpl_toolkits.mplot3d import Axes3D

df_selected = df_gone

# 使用PCA进行降维
pca = PCA(n_components=3)
X_pca = pca.fit_transform(df_selected[columns].values)

# 创建一个3D图形
fig = plt.figure(figsize=(8, 6))
ax = fig.add_subplot(111, projection='3d')

# 绘制散点图
sc = ax.scatter(X_pca[:, 0], X_pca[:, 1], X_pca[:, 2], c=clusters, cmap='viridis', alpha=0.5)

plt.title('K-Prototypes Clustering (PCA-reduced data)')
ax.set_xlabel('Principal Component 1')
ax.set_ylabel('Principal Component 2')
ax.set_zlabel('Principal Component 3')
fig.colorbar(sc, label='Cluster', pad=0.1)

plt.show()
df.head()

from sklearn.decomposition import PCA
import matplotlib.pyplot as plt
from mpl_toolkits.mplot3d import Axes3D
import imageio
import os

df_selected = df_gone

# 使用PCA进行降维
pca = PCA(n_components=3)
X_pca = pca.fit_transform(df_selected[columns].values)

# 创建GIF的帧
filenames = []

for azim in range(-90, 90, 2):  # 从0度旋转到360度，每次增加10度
    # 创建一个3D图形
    fig = plt.figure(figsize=(8, 6))
    ax = fig.add_subplot(111, projection='3d')

    # 绘制散点图
    sc = ax.scatter(X_pca[:, 0], X_pca[:, 1], X_pca[:, 2], c=clusters, cmap='viridis', alpha=0.5)

    ax.set_title('K-Prototypes Clustering (PCA-reduced data)')
    ax.set_xlabel('Principal Component 1')
    ax.set_ylabel('Principal Component 2')
    ax.set_zlabel('Principal Component 3')
    fig.colorbar(sc, label='Cluster', pad=0.1)

    # 设置视角
    ax.view_init(elev=20., azim=azim)

    # 保存帧
    filename = f'frame_{azim}.png'
    plt.savefig(filename)
    filenames.append(filename)

    plt.close(fig)  # 关闭图形，防止在notebook中显示

# 使用所有的帧创建一个GIF
with imageio.get_writer('mygif.gif', mode='I') as writer:
    for filename in filenames:
        image = imageio.imread(filename)
        writer.append_data(image)

# 删除所有的帧
for filename in filenames:
    os.remove(filename)

from sklearn.manifold import TSNE
import matplotlib.pyplot as plt
from mpl_toolkits.mplot3d import Axes3D

# 使用t-SNE进行降维
tsne = TSNE(n_components=3)
X_tsne = tsne.fit_transform(df[columns].values)

# 创建一个3D图形
fig = plt.figure(figsize=(8, 6))
ax = fig.add_subplot(111, projection='3d')

# 绘制散点图
sc = ax.scatter(X_tsne[:, 0], X_tsne[:, 1], X_tsne[:, 2], c=clusters, cmap='viridis', alpha=0.5)

plt.title('K-Prototypes Clustering')
ax.set_xlabel('t-SNE Dimension 1')
ax.set_ylabel('t-SNE Dimension 2')
ax.set_zlabel('t-SNE Dimension 3')
fig.colorbar(sc, label='Cluster')
plt.show()
df.head()

import numpy as np
import matplotlib.pyplot as plt
from mpl_toolkits.mplot3d import Axes3D
import imageio
from sklearn.manifold import TSNE

# 初始化t-SNE
tsne = TSNE(n_components=3)
#, init='random', random_state=0, n_iter=1000, method='exact'

# 保存每次迭代的图像
frames = []
for i in range(250, 1000, 50):
    tsne.n_iter = i
    X_tsne = tsne.fit_transform(df[columns].values)
    
    fig = plt.figure(figsize=(8, 6))
    ax = fig.add_subplot(111, projection='3d')
    scatter = ax.scatter(X_tsne[:, 0], X_tsne[:, 1], X_tsne[:, 2], c=clusters, cmap='viridis', alpha=0.5)
    ax.set_title(f't-SNE with {i} iterations')
    ax.set_xlabel('t-SNE Dimension 1')
    ax.set_ylabel('t-SNE Dimension 2')
    ax.set_zlabel('t-SNE Dimension 3')
    fig.colorbar(scatter, label='Cluster')
    plt.savefig(f'tsne_{i}.png')
    plt.close(fig)
    
    frames.append(imageio.imread(f'tsne_{i}.png'))

# 保存为GIF
imageio.mimsave('tsne.gif', frames, 'GIF', duration=1)
df.head()

import umap
import matplotlib.pyplot as plt
from mpl_toolkits.mplot3d import Axes3D

# 假设df是你的数据框，columns是你想要降维的列
reducer = umap.UMAP(n_components=3, random_state=8)
embedding = reducer.fit_transform(df[columns].values)

# 创建一个3D图形
fig = plt.figure(figsize=(8, 6))
ax = fig.add_subplot(111, projection='3d')

# 绘制散点图
sc = ax.scatter(embedding[:, 0], embedding[:, 1], embedding[:, 2], c=clusters, cmap='viridis', alpha=0.5)

plt.title('UMAP projection')
ax.set_xlabel('Dimension 1')
ax.set_ylabel('Dimension 2')
ax.set_zlabel('Dimension 3')
fig.colorbar(sc, label='Cluster')

# 调整布局
plt.tight_layout()
plt.show()
df.head()

import umap
import matplotlib.pyplot as plt
from mpl_toolkits.mplot3d import Axes3D
import imageio

# 假设df是你的数据框，columns是你想要降维的列
frames = []
for n in range(10, 51, 5):
    reducer = umap.UMAP(n_components=3, random_state=8, n_neighbors=n)
    embedding = reducer.fit_transform(df[columns].values)

    # 创建一个3D图形
    fig = plt.figure(figsize=(8, 6))
    ax = fig.add_subplot(111, projection='3d')

    # 绘制散点图
    sc = ax.scatter(embedding[:, 0], embedding[:, 1], embedding[:, 2], c=clusters, cmap='viridis', alpha=0.5)

    plt.title(f'UMAP projection with {n} neighbors')
    ax.set_xlabel('Dimension 1')
    ax.set_ylabel('Dimension 2')
    ax.set_zlabel('Dimension 3')
    fig.colorbar(sc, label='Cluster')

    # 调整布局
    plt.tight_layout()

    # 保存图像
    plt.savefig(f'umap_{n}.png')
    plt.close(fig)
    
    frames.append(imageio.imread(f'umap_{n}.png'))

# 保存为GIF
imageio.mimsave('umap.gif', frames, 'GIF', duration=1)
df.head()

import csv
# 聚类中心
centroids = kproto.cluster_centroids_

# show_columns = ['Q2', 'Q4', 'Q5','Q7|1', 'Q7|4', 'Q7|2', 'Q7|3', 'Q7|5', 'Q7|6','Q1', 'Q3', 'Q8', 'Q9', 'Q10', 'Q11', 'Q12', 'Q13', 'Q14', 'Q15', 'Q16|6', 'Q16|1', 'Q16|2', 'Q16|3', 'Q16|7', 'Q16|4', 'Q16|10', 'Q16|11', 'Q16|9']
show_columns = columns

# 打开你想要写入的文件
with open('centroids.csv', 'w', newline='') as file:
    writer = csv.writer(file)
    # 写入题号作为第一行
    writer.writerow(show_columns)
    # 写入数据
    writer.writerows(centroids)

# 打印题号和聚类中心
print("Question IDs:")
print(show_columns)
print("Cluster centroids:")
print(centroids)


# 聚类标签
labels = kproto.labels_
print("Labels of first 10 data points:")
print(labels[:10])
df.head()

import csv
import json
import re

# 读取 structured_questions.json 文件
with open('structured_questions.json', 'r') as json_file:
    structured_questions = json.load(json_file)

# 读取 centroids.csv 文件
with open('centroids.csv', 'r') as csv_file:
    csv_reader = csv.reader(csv_file)
    data = list(csv_reader)

# 先替换选项
for i, row in enumerate(data):
    if i == 0:  
        continue
    for j, value in enumerate(row):  # 遍历每一列
        question_id = data[0][j]  # 获取题号，只有单选和填空的格式是Q1这种，可以匹配上
        if question_id in structured_questions:  # 如果题号在 structured_questions 中
            # 替换选项
            data[i][j] = structured_questions[question_id]['options'].get(value, value)
            
# 再替换题目
for i, row in enumerate(data):
    if i == 0:  # 指针对第一行替换题目
        for j, value in enumerate(row):
            if value in structured_questions:
                data[i][j] = value +" "+structured_questions[value]['text']
            
            # 对于题目为Q7 | 1这种格式，把Q7替换为text的内容，1替换为options中的内容
            # 用正则表达式匹配
            if re.match(r"^Q\d+\|\d+$", value):
                question_id, option_id = value.split('|', 1)
                if question_id in structured_questions:
                    data[i][j] = question_id +" "+ structured_questions[question_id]['text'] + ": " + structured_questions[question_id]['options'][option_id]            
    if i!=0:
        break

# 将更新后的数据写回 centroids.csv 文件
with open('centroids.csv', 'w', newline='') as csv_file:
    csv_writer = csv.writer(csv_file)
    csv_writer.writerows(data)
    
df.head()

from sklearn.cluster import DBSCAN
from sklearn.preprocessing import StandardScaler
from sklearn.manifold import TSNE
import matplotlib.pyplot as plt
import pandas as pd
print(df)

# 列表中的列名
columns = ['Q1', 'Q2', 'Q3', 'Q4', 'Q5', 'Q7|1', 'Q7|4', 'Q7|2', 'Q7|3', 'Q7|5', 'Q7|6', 'Q8', 'Q9', 'Q10', 'Q11', 'Q12', 'Q13', 'Q14', 'Q15', 'Q16|6', 'Q16|1', 'Q16|2', 'Q16|3', 'Q16|7', 'Q16|4', 'Q16|10', 'Q16|11', 'Q16|9']

# 数值型列
numerical_columns = ['Q2', 'Q4', 'Q5','Q7|1', 'Q7|4', 'Q7|2', 'Q7|3', 'Q7|5', 'Q7|6']

# 对数值型列进行标准化
scaler = StandardScaler()
df[numerical_columns] = scaler.fit_transform(df[numerical_columns])

X = df[columns].values

# 初始化 DBSCAN 聚类器
dbscan = DBSCAN(eps=4.7, min_samples=7)

# 进行聚类
clusters = dbscan.fit_predict(X)

# 添加数据标签
df['dbscan cluster'] = clusters


# 使用t-SNE进行降维
tsne = TSNE(n_components=2, random_state=42)
X_tsne = tsne.fit_transform(X)

# 绘制散点图
plt.figure(figsize=(8, 6))
plt.scatter(X_tsne[:, 0], X_tsne[:, 1], c=clusters, cmap='viridis', alpha=0.5)

plt.title('t-SNE projection')
plt.xlabel('Dimension 1')
plt.ylabel('Dimension 2')
plt.colorbar(label='Cluster')

plt.show()
df.head()

from sklearn.cluster import DBSCAN
from sklearn.preprocessing import StandardScaler
from sklearn.manifold import TSNE
import matplotlib.pyplot as plt
from mpl_toolkits.mplot3d import Axes3D
import pandas as pd

print(df.columns)
# 列表中的列名
columns = ['Q1', 'Q2', 'Q3', 'Q4', 'Q5', 'Q7|1', 'Q7|4', 'Q7|2', 'Q7|3', 'Q7|5', 'Q7|6', 'Q8', 'Q9', 'Q10', 'Q11', 'Q12', 'Q13', 'Q14', 'Q15', 'Q16|6', 'Q16|1', 'Q16|2', 'Q16|3', 'Q16|7', 'Q16|4', 'Q16|10', 'Q16|11', 'Q16|9']

# 数值型列
numerical_columns = ['Q2', 'Q4', 'Q5','Q7|1', 'Q7|4', 'Q7|2', 'Q7|3', 'Q7|5', 'Q7|6']

# 对数值型列进行标准化
scaler = StandardScaler()
df[numerical_columns] = scaler.fit_transform(df[numerical_columns])

X = df[columns].values

# 初始化 DBSCAN 聚类器
dbscan = DBSCAN(eps=4.7, min_samples=7)

# 进行聚类
clusters = dbscan.fit_predict(X)

# 使用t-SNE进行降维
tsne = TSNE(n_components=3, random_state=42)
X_tsne = tsne.fit_transform(X)

# 创建一个3D图形
fig = plt.figure(figsize=(8, 6))
ax = fig.add_subplot(111, projection='3d')

# 绘制散点图
sc = ax.scatter(X_tsne[:, 0], X_tsne[:, 1], X_tsne[:, 2], c=clusters, cmap='viridis', alpha=0.5)

plt.title('t-SNE projection')
ax.set_xlabel('Dimension 1')
ax.set_ylabel('Dimension 2')
ax.set_zlabel('Dimension 3')
fig.colorbar(sc, label='Cluster')

plt.show()
df.head()