##进入jieba和pands  读入需要的文档，有几个阶段就要读取几个文档/////////只需要注意文档的名称
import pandas as pd
import jieba 

papers_18_19 = pd.read_excel("FirstPeriod.xlsx",names = ['summary','time'])
papers_20_22 = pd.read_excel("SecondPeriod.xlsx",names = ['summary','time'])
papers_23_24 = pd.read_excel("ThirdPeriod.xlsx",names = ['summary','time'])

##计算是否有空值//////////每个文本都要计算
papers_18_19.info()

##展示文本//////////每个文本都要展示
papers_18_19.head()

papers_20_22.info()

papers_20_22.head()

papers_23_24.info()

papers_23_24.head()

## 加载用户词典//////////停用词名字叫什么就改成什么
jieba.load_userdict("psydic.txt")
stopLists = list(pd.read_csv("stopwords.txt",names = ['w'],sep='\t',encoding="utf-8").w)

## 定义分词方法//////////最后的2是大于2个字，可以选大于几个字
def paperCut(text):
    return [w for w in jieba.cut(text) if w not in stopLists and len(w)>2]

## 第一阶段分词
wordList_First = [paperCut(paper) for paper in papers_18_19.summary]
wordList_First[1]

len(wordList_First)

## 第二阶段分词
wordList_Second = [paperCut(paper) for paper in papers_20_22.summary]
wordList_Second[1]

len(wordList_Second)

## 第三阶段分词
wordList_Third = [paperCut(paper) for paper in papers_23_24.summary]
wordList_Third[1]

len(wordList_Third)

from gensim.models.word2vec import Word2Vec

wordList = []
wordList = wordList+ wordList_First
wordList.extend(wordList_Second)
wordList.extend(wordList_Third)

len(wordList)

# sg=0，CBOW的训练方式
model_cbow = Word2Vec(sentences=wordList, vector_size=100, window=5, sg = 0,min_count = 1,workers = 4, epochs=100)

# Get the vocabulary and corresponding vectors
vocabulary = model_cbow.wv.index_to_key
vectors = model_cbow.wv[vocabulary]

vocabulary[:5]

vectors[:1]

# 训练完毕的模型实质
print(model_cbow.wv["心理学"].shape)
model_cbow.wv["心理学"]

from gensim.models import TfidfModel
from gensim.corpora import Dictionary

def text2Matrix(wordList):
    dict = Dictionary(wordList)
    corpus = [dict.doc2bow(text) for text in wordList]
    tfidf_model = TfidfModel(corpus)
    corpus_tfidf = tfidf_model[corpus]
    return dict,corpus,corpus_tfidf

# 第一阶段文本向量化
dict_First,corpus_First,corpus_tfidf_First = text2Matrix(wordList_First)
len(corpus_tfidf_First)

# 第二阶段文本向量化
dict_Second,corpus_Second,corpus_tfidf_Second = text2Matrix(wordList_Second)
len(corpus_tfidf_Second)

# 第三阶段文本向量化
dict_Third,corpus_Third,corpus_tfidf_Third = text2Matrix(wordList_Third)
len(corpus_tfidf_Third)

from gensim.models.ldamodel import LdaModel

import matplotlib.pyplot as plt
import matplotlib

## 困惑度计算
def cal_perplexity(num_topics,corpus_tfidf,dict):
    ldamodel = LdaModel(corpus_tfidf, num_topics=num_topics, id2word = dict,passes=5,alpha= 1/num_topics, eta=0.01)
    return ldamodel.log_perplexity(corpus_tfidf)

## 第一阶段困惑度计算

topic_num = range(1,10)
perplexitys_First = [cal_perplexity(num,corpus_First,dict_First) for num in topic_num]

plt.plot(topic_num, perplexitys_First)
plt.xlabel('主题数目')
plt.ylabel('困惑度')
plt.rcParams['font.sans-serif']=['SimHei']
matplotlib.rcParams['axes.unicode_minus']=False
plt.title('阶段一 主题-困惑度变化趋势')
plt.show()

## 第二阶段困惑度计算

topic_num = range(1,10)
perplexitys_Second = [cal_perplexity(num,corpus_Second,dict_Second) for num in topic_num]

plt.plot(topic_num, perplexitys_Second)
plt.xlabel('主题数目')
plt.ylabel('困惑度')
plt.rcParams['font.sans-serif']=['SimHei']
matplotlib.rcParams['axes.unicode_minus']=False
plt.title('阶段一 主题-困惑度变化趋势')
plt.show()

## 第三阶段困惑度计算

topic_num = range(1,10)
perplexitys_Third = [cal_perplexity(num,corpus_Third,dict_Third) for num in topic_num]

plt.plot(topic_num, perplexitys_Third)
plt.xlabel('主题数目')
plt.ylabel('困惑度')
plt.rcParams['font.sans-serif']=['SimHei']
matplotlib.rcParams['axes.unicode_minus']=False
plt.title('阶段一 主题-困惑度变化趋势')
plt.show()

## 第一阶段主题建模， num_topics为阶段一的最优主题数目
ldamodel_First = LdaModel(corpus_tfidf_First,id2word= dict_First, num_topics = 2, passes= 10,alpha=8.5, eta= 0.01)

import warnings
warnings.filterwarnings("ignore")

## pip install pyLDAvis
import pyLDAvis.gensim
pyLDAvis.enable_notebook

# 注意：如果网络畅通，采用此种方式。如果不出图，不必纠结，用困惑度计算即可。
topic_data_First = pyLDAvis.gensim.prepare(ldamodel_First,corpus_First,dict_First)
pyLDAvis.display(topic_data_First)

## 第二阶段主题建模， num_topics为阶段一的最优主题数目
ldamodel_Second = LdaModel(corpus_tfidf_Second,id2word= dict_Second, num_topics = 4, passes= 10,alpha=8.5, eta= 0.01)

import warnings
warnings.filterwarnings("ignore")

## pip install pyLDAvis
import pyLDAvis.gensim
pyLDAvis.enable_notebook

# 注意：如果网络畅通，采用此种方式。如果不出图，不必纠结，用困惑度计算即可。
topic_data_Second = pyLDAvis.gensim.prepare(ldamodel_Second,corpus_Second,dict_Second)
pyLDAvis.display(topic_data_Second)

## 第三阶段主题建模， num_topics为阶段一的最优主题数目
ldamodel_Third = LdaModel(corpus_tfidf_Third,id2word= dict_Third, num_topics = 4, passes= 10,alpha=8.5, eta= 0.01)

import warnings
warnings.filterwarnings("ignore")

## pip install pyLDAvis
import pyLDAvis.gensim
pyLDAvis.enable_notebook

# 注意：如果网络畅通，采用此种方式。如果不出图，不必纠结，用困惑度计算即可。
topic_data_Third = pyLDAvis.gensim.prepare(ldamodel_Third,corpus_Third,dict_Third)
pyLDAvis.display(topic_data_Third)

## 列出第一阶段主题和主题词
ldamodel_First.print_topics(num_topics=ldamodel_First.num_topics,num_words= 30)

## 列出第二阶段主题和主题词
ldamodel_Second.print_topics(num_topics=ldamodel_Second.num_topics,num_words= 30)

## 列出第三阶段主题和主题词
ldamodel_Third.print_topics(num_topics=ldamodel_Third.num_topics,num_words= 30)

import numpy as np
## 基于词向量的主题向量计算
def get_topic_vector(model, words, topic_word_value):
    vectors = []
    i = 0
    for word in words:
        if word in model.wv.index_to_key:
            vectors.append(model.wv[word]*float(topic_word_value[i]))
            i+=1
    if len(vectors) > 0:
        return np.mean(vectors, axis=0)
    else:
        return np.zeros((model.vector_size,), dtype=np.float32)

##'0.014*"图书馆" + 0.011*"学科服务" + 0.010*"互联网" + 0.010*"图书馆智慧服务" '

## 计算主题向量
def cal_vector(topicNum,ldamodel):
    
    topic = ldamodel.print_topic(i)
    
    split_topic = topic.split("+")
    
    words = []
    
    values = []
    
    print(split_topic)
    
    for str in split_topic:
        str_split = str.split("*")
        if len(str_split)>1:
            values.append(str_split[0]) 
            words.append(str_split[1].replace("\"",""))
        
    return get_topic_vector(model_cbow, words, values)

## 计算第一阶段的所有主题向量
topic_vectors_First = []
for i in range(ldamodel_First.num_topics):
    topic_vector= cal_vector(i,ldamodel_First)
    topic_vectors_First.append(topic_vector)
len(topic_vectors_First)

## 计算第二阶段的所有主题向量
topic_vectors_Second = []
for i in range(ldamodel_Second.num_topics):
    topic_vector= cal_vector(i,ldamodel_Second)
    topic_vectors_Second.append(topic_vector)
len(topic_vectors_Second)

## 计算第三阶段的所有主题向量
topic_vectors_Third = []
for i in range(ldamodel_Third.num_topics):
    topic_vector= cal_vector(i,ldamodel_Third)
    topic_vectors_Third.append(topic_vector)
len(topic_vectors_Third)

## 提示：此处代码为示例，暂不要执行！如果有多个阶段，需要定义新的参数
## 计算第X阶段每个主题的向量
topic_vectors_First = []

for i in range(ldamodel_First.num_topics):
    topic_vector= cal_vector(i,ldamodel_First)
    topic_vectors_First.append(topic_vector)
len(topic_vectors_First)

## 提示：此处代码为示例，暂不要执行！如果有多个阶段，需要定义新的参数
## 计算第X阶段每个主题的向量
topic_vectors_Second = []

for i in range(ldamodel_Second.num_topics):
    topic_vector= cal_vector(i,ldamodel_Second)
    topic_vectors_Second.append(topic_vector)
len(topic_vectors_Second)

## 提示：此处代码为示例，暂不要执行！如果有多个阶段，需要定义新的参数
## 计算第X阶段每个主题的向量
topic_vectors_Third = []

for i in range(ldamodel_Third.num_topics):
    topic_vector= cal_vector(i,ldamodel_Third)
    topic_vectors_Third.append(topic_vector)
len(topic_vectors_Third)

## 计算相邻阶段主题相似度
from sklearn.metrics.pairwise import cosine_similarity

def cal_cosine(topicVector_Before,topicVector_After):
    topic_cos = cosine_similarity(np.array(topicVector_Before),np.array(topicVector_After))
    return topic_cos

## 第一阶段和第二阶段主题相似度，此变量将用于后续的桑基图绘制！！！
topic_cos_1_2 = cal_cosine(topic_vectors_First,topic_vectors_Second)
topic_cos_1_3 = cal_cosine(topic_vectors_First,topic_vectors_Third)
topic_cos_2_3 = cal_cosine(topic_vectors_Second,topic_vectors_Third)

## 相似度数据框展示
cos_pd_1_2 = pd.DataFrame(topic_cos_1_2)
cos_pd_1_2

## 相似度数据框展示
cos_pd_2_3 = pd.DataFrame(topic_cos_2_3)
cos_pd_2_3

## 相似度数据框展示
cos_pd_1_3 = pd.DataFrame(topic_cos_1_3)
cos_pd_1_3

#{"source": "category1", "target": "category2", "value": 10}

print('{\"name\":\"第一阶段主题%d\"}' % 1)
print('{\"source\": \"第一阶段主题%d\",\"target\":\"第二阶段主题%d\", \"value\":%d}' %( 1, 2,3))

## topic_cos_1_2表示第一阶段和第二阶段的相似度
cos_list_1_2 = list(topic_cos_1_2)
cos_list_1_2

for i,val in enumerate(cos_list_1_2):
    #{"name": "category1"},
    print('{\"name\":\"第一阶段主题%d\"}' % i)

for i,val in enumerate(cos_list_1_2):
    for j,v in enumerate(val):
        print('{\"name\":\"第二阶段主题%d\"}' % j)
    break

for i,val in enumerate(cos_list_1_2):
    for j,v in enumerate(val):
        print('{\"source\": \"第一阶段主题%d\",\"target\":\"第二阶段主题%d\", \"value\":%f}' %( i, j, v))

#{"source": "category1", "target": "category2", "value": 10}

print('{\"name\":\"第一阶段主题%d\"}' % 1)
print('{\"source\": \"第一阶段主题%d\",\"target\":\"第三阶段主题%d\", \"value\":%d}' %( 1, 2,3))

## topic_cos_1_2表示第一阶段和第三阶段的相似度
cos_list_1_3 = list(topic_cos_1_3)
cos_list_1_3

for i,val in enumerate(cos_list_1_3):
    #{"name": "category1"},
    print('{\"name\":\"第一阶段主题%d\"}' % i)

for i,val in enumerate(cos_list_1_3):
    for j,v in enumerate(val):
        print('{\"name\":\"第三阶段主题%d\"}' % j)
    break

for i,val in enumerate(cos_list_1_3):
    for j,v in enumerate(val):
        print('{\"source\": \"第一阶段主题%d\",\"target\":\"第三阶段主题%d\", \"value\":%f}' %( i, j, v))

#{"source": "category1", "target": "category2", "value": 10}

print('{\"name\":\"第二阶段主题%d\"}' % 1)
print('{\"source\": \"第二阶段主题%d\",\"target\":\"第三阶段主题%d\", \"value\":%d}' %( 1, 2,3))

## topic_cos_1_2表示第二阶段和第三阶段的相似度
cos_list_2_3 = list(topic_cos_2_3)
cos_list_2_3

for i,val in enumerate(cos_list_2_3):
    #{"name": "category1"},
    print('{\"name\":\"第二阶段主题%d\"}' % i)

for i,val in enumerate(cos_list_2_3):
    for j,v in enumerate(val):
        print('{\"name\":\"第三阶段主题%d\"}' % j)
    break

for i,val in enumerate(cos_list_2_3):
    for j,v in enumerate(val):
        print('{\"source\": \"第二阶段主题%d\",\"target\":\"第三阶段主题%d\", \"value\":%f}' %( i, j, v))

# 查看第一阶段的时间列格式
print("第一阶段时间列样例：")
print(papers_18_19['time'].head())
print("\n时间列数据类型：", papers_18_19['time'].dtype)

import pandas as pd
import jieba 

# 重新读取数据
papers_18_19 = pd.read_excel("FirstPeriod.xlsx", names=['summary','time'])
papers_20_22 = pd.read_excel("SecondPeriod.xlsx", names=['summary','time'])
papers_23_24 = pd.read_excel("ThirdPeriod.xlsx", names=['summary','time'])

# 查看第一阶段的时间列格式
print("第一阶段时间列样例：")
print(papers_18_19['time'].head())
print("\n时间列数据类型：", papers_18_19['time'].dtype)

# 检查所有阶段的时间范围
print("第一阶段时间范围：", papers_18_19['time'].min(), "-", papers_18_19['time'].max())
print("第二阶段时间范围：", papers_20_22['time'].min(), "-", papers_20_22['time'].max())
print("第三阶段时间范围：", papers_23_24['time'].min(), "-", papers_23_24['time'].max())

# 查看各年份的文档数量分布
print("\n第一阶段各年份文档数量：")
print(papers_18_19['time'].value_counts().sort_index())

print("\n第二阶段各年份文档数量：")
print(papers_20_22['time'].value_counts().sort_index())

print("\n第三阶段各年份文档数量：")
print(papers_23_24['time'].value_counts().sort_index())

# 为第一阶段的每个文档分配主要主题
def get_dominant_topic(ldamodel, corpus):
    """获取每个文档的主要主题"""
    dominant_topics = []
    for doc in corpus:
        topic_probs = ldamodel[doc]
        if topic_probs:
            dominant_topic = max(topic_probs, key=lambda x: x[1])[0]
            dominant_topics.append(dominant_topic)
        else:
            dominant_topics.append(-1)  # 如果没有主题分配，标记为-1
    return dominant_topics

# 为三个阶段分配主要主题
dominant_topics_first = get_dominant_topic(ldamodel_First, corpus_First)
dominant_topics_second = get_dominant_topic(ldamodel_Second, corpus_Second)
dominant_topics_third = get_dominant_topic(ldamodel_Third, corpus_Third)

print("第一阶段文档主题分配完成，主题数量分布：")
print(pd.Series(dominant_topics_first).value_counts().sort_index())

print("\n第二阶段文档主题分配完成，主题数量分布：")
print(pd.Series(dominant_topics_second).value_counts().sort_index())

print("\n第三阶段文档主题分配完成，主题数量分布：")
print(pd.Series(dominant_topics_third).value_counts().sort_index())

from gensim.models.ldamodel import LdaModel

# 重新训练第一阶段的LDA模型
ldamodel_First = LdaModel(corpus_tfidf_First, id2word=dict_First, num_topics=2, passes=10, alpha=8.5, eta=0.01)
ldamodel_Second = LdaModel(corpus_tfidf_Second, id2word=dict_Second, num_topics=4, passes=10, alpha=8.5, eta=0.01)
ldamodel_Third = LdaModel(corpus_tfidf_Third, id2word=dict_Third, num_topics=4, passes=10, alpha=8.5, eta=0.01)

# 为每个文档分配主要主题
def get_dominant_topic(ldamodel, corpus):
    """获取每个文档的主要主题"""
    dominant_topics = []
    for doc in corpus:
        topic_probs = ldamodel[doc]
        if topic_probs:
            dominant_topic = max(topic_probs, key=lambda x: x[1])[0]
            dominant_topics.append(dominant_topic)
        else:
            dominant_topics.append(-1)  # 如果没有主题分配，标记为-1
    return dominant_topics

# 为三个阶段分配主要主题
dominant_topics_first = get_dominant_topic(ldamodel_First, corpus_First)
dominant_topics_second = get_dominant_topic(ldamodel_Second, corpus_Second)
dominant_topics_third = get_dominant_topic(ldamodel_Third, corpus_Third)

print("第一阶段文档主题分配完成，主题数量分布：")
print(pd.Series(dominant_topics_first).value_counts().sort_index())

print("\n第二阶段文档主题分配完成，主题数量分布：")
print(pd.Series(dominant_topics_second).value_counts().sort_index())

print("\n第三阶段文档主题分配完成，主题数量分布：")
print(pd.Series(dominant_topics_third).value_counts().sort_index())

from gensim.models import TfidfModel
from gensim.corpora import Dictionary

# 重新定义文本转矩阵函数
def text2Matrix(wordList):
    dict_obj = Dictionary(wordList)
    corpus = [dict_obj.doc2bow(text) for text in wordList]
    tfidf_model = TfidfModel(corpus)
    corpus_tfidf = tfidf_model[corpus]
    return dict_obj, corpus, corpus_tfidf

# 重新进行文本向量化
dict_First, corpus_First, corpus_tfidf_First = text2Matrix(wordList_First)
dict_Second, corpus_Second, corpus_tfidf_Second = text2Matrix(wordList_Second)
dict_Third, corpus_Third, corpus_tfidf_Third = text2Matrix(wordList_Third)

print("文本向量化完成")
print("第一阶段文档数:", len(corpus_tfidf_First))
print("第二阶段文档数:", len(corpus_tfidf_Second))
print("第三阶段文档数:", len(corpus_tfidf_Third))

import pandas as pd
import jieba

# 重新读取数据
papers_18_19 = pd.read_excel("FirstPeriod.xlsx", names=['summary','time'])
papers_20_22 = pd.read_excel("SecondPeriod.xlsx", names=['summary','time'])
papers_23_24 = pd.read_excel("ThirdPeriod.xlsx", names=['summary','time'])

# 加载词典和停用词
jieba.load_userdict("psydic.txt")
stopLists = list(pd.read_csv("stopwords.txt", names=['w'], sep='\t', encoding="utf-8").w)

# 定义分词方法
def paperCut(text):
    return [w for w in jieba.cut(text) if w not in stopLists and len(w)>2]

# 重新进行分词
wordList_First = [paperCut(paper) for paper in papers_18_19.summary]
wordList_Second = [paperCut(paper) for paper in papers_20_22.summary]
wordList_Third = [paperCut(paper) for paper in papers_23_24.summary]

print("分词完成")
print("第一阶段文档数:", len(wordList_First))
print("第二阶段文档数:", len(wordList_Second))
print("第三阶段文档数:", len(wordList_Third))

from gensim.models import TfidfModel
from gensim.corpora import Dictionary

# 定义文本转矩阵函数
def text2Matrix(wordList):
    dict_obj = Dictionary(wordList)
    corpus = [dict_obj.doc2bow(text) for text in wordList]
    tfidf_model = TfidfModel(corpus)
    corpus_tfidf = tfidf_model[corpus]
    return dict_obj, corpus, corpus_tfidf

# 进行文本向量化
dict_First, corpus_First, corpus_tfidf_First = text2Matrix(wordList_First)
dict_Second, corpus_Second, corpus_tfidf_Second = text2Matrix(wordList_Second)
dict_Third, corpus_Third, corpus_tfidf_Third = text2Matrix(wordList_Third)

print("文本向量化完成")
print("第一阶段文档数:", len(corpus_tfidf_First))
print("第二阶段文档数:", len(corpus_tfidf_Second))
print("第三阶段文档数:", len(corpus_tfidf_Third))

from gensim.models.ldamodel import LdaModel

# 训练LDA模型
ldamodel_First = LdaModel(corpus_tfidf_First, id2word=dict_First, num_topics=2, passes=10, alpha=8.5, eta=0.01)
ldamodel_Second = LdaModel(corpus_tfidf_Second, id2word=dict_Second, num_topics=4, passes=10, alpha=8.5, eta=0.01)
ldamodel_Third = LdaModel(corpus_tfidf_Third, id2word=dict_Third, num_topics=4, passes=10, alpha=8.5, eta=0.01)

# 为每个文档分配主要主题
def get_dominant_topic(ldamodel, corpus):
    """获取每个文档的主要主题"""
    dominant_topics = []
    for doc in corpus:
        topic_probs = ldamodel[doc]
        if topic_probs:
            dominant_topic = max(topic_probs, key=lambda x: x[1])[0]
            dominant_topics.append(dominant_topic)
        else:
            dominant_topics.append(-1)  # 如果没有主题分配，标记为-1
    return dominant_topics

# 为三个阶段分配主要主题
dominant_topics_first = get_dominant_topic(ldamodel_First, corpus_First)
dominant_topics_second = get_dominant_topic(ldamodel_Second, corpus_Second)
dominant_topics_third = get_dominant_topic(ldamodel_Third, corpus_Third)

print("主题分配完成")
print("第一阶段主题分布:", pd.Series(dominant_topics_first).value_counts().sort_index())
print("第二阶段主题分布:", pd.Series(dominant_topics_second).value_counts().sort_index())
print("第三阶段主题分布:", pd.Series(dominant_topics_third).value_counts().sort_index())

# 将主题信息添加到原始数据中
papers_18_19['dominant_topic'] = dominant_topics_first
papers_20_22['dominant_topic'] = dominant_topics_second
papers_23_24['dominant_topic'] = dominant_topics_third

# 合并所有数据
all_papers = pd.concat([papers_18_19, papers_20_22, papers_23_24], ignore_index=True)

# 计算每个年份每个主题的文档数量（主题热度）
year_topic_heat = all_papers.groupby(['time', 'dominant_topic']).size().reset_index(name='count')

print("年度主题热度数据预览：")
print(year_topic_heat.head(10))

print("\n各年份主题热度统计：")
print("总年份数:", year_topic_heat['time'].nunique())
print("时间范围:", year_topic_heat['time'].min(), "-", year_topic_heat['time'].max())

# 创建完整的年份-主题组合
all_years = range(2003, 2025)  # 2003-2025年
all_topics = list(range(2)) + list(range(4)) + list(range(4))  # 所有可能主题：前2个 + 中4个 + 后4个
all_topics = sorted(set(all_topics))  # 去重

# 创建完整的网格
year_topic_grid = pd.MultiIndex.from_product([all_years, all_topics], names=['time', 'dominant_topic']).to_frame(index=False)

# 合并实际数据，填充缺失值为0
year_topic_complete = year_topic_grid.merge(year_topic_heat, on=['time', 'dominant_topic'], how='left')
year_topic_complete['count'] = year_topic_complete['count'].fillna(0).astype(int)

# 按年份和主题排序
year_topic_complete = year_topic_complete.sort_values(['time', 'dominant_topic']).reset_index(drop=True)

print("完整的年度主题热度表格（前20行）：")
print(year_topic_complete.head(20))

print(f"\n表格形状: {year_topic_complete.shape}")
print(f"总记录数: {len(year_topic_complete)}")

# 定义阶段划分
def get_period(year):
    if 2003 <= year <= 2012:
        return '第一阶段(2003-2012)'
    elif 2013 <= year <= 2021:
        return '第二阶段(2013-2021)'
    else:
        return '第三阶段(2022-2025)'

# 添加阶段信息
year_topic_complete['period'] = year_topic_complete['time'].apply(get_period)

# 计算每个阶段内各主题的有效性（只显示该阶段实际存在的主题）
def filter_valid_topics(group):
    period = group['period'].iloc[0]
    if period == '第一阶段(2003-2012)':
        return group[group['dominant_topic'].isin([0, 1])]
    elif period == '第二阶段(2013-2021)':
        return group[group['dominant_topic'].isin([0, 1, 2, 3])]
    else:  # 第三阶段
        return group[group['dominant_topic'].isin([0, 1, 2, 3])]

# 应用过滤
year_topic_filtered = year_topic_complete.groupby('period').apply(filter_valid_topics).reset_index(drop=True)

print("分阶段的年度主题热度表格（前15行）：")
print(year_topic_filtered.head(15))

print(f"\n过滤后表格形状: {year_topic_filtered.shape}")

# 计算每个年份的总文档数
yearly_totals = year_topic_filtered.groupby('time')['count'].sum().reset_index(name='year_total')

# 合并计算相对热度
year_topic_analysis = year_topic_filtered.merge(yearly_totals, on='time')
year_topic_analysis['relative_heat'] = (year_topic_analysis['count'] / year_topic_analysis['year_total'] * 100).round(2)

# 计算每个主题在每个年份的排名
year_topic_analysis['year_rank'] = year_topic_analysis.groupby('time')['count'].rank(method='dense', ascending=False).astype(int)

print("年度主题热度详细分析（前20行）：")
print(year_topic_analysis.head(20))

# 生成汇总统计
print("\n=== 各阶段主题热度汇总 ===")
period_summary = year_topic_analysis.groupby(['period', 'dominant_topic']).agg({
    'count': 'sum',
    'relative_heat': 'mean'
}).round(2).reset_index()

print(period_summary)

print("\n=== 年度主题热度排名统计 ===")
rank_summary = year_topic_analysis.groupby(['period', 'dominant_topic', 'year_rank']).size().reset_index(name='years_count')
print(rank_summary)

import matplotlib.pyplot as plt
import seaborn as sns

# 设置中文字体
plt.rcParams['font.sans-serif'] = ['SimHei']
plt.rcParams['axes.unicode_minus'] = False

# 1. 绘制各阶段主题热度堆叠面积图
fig, axes = plt.subplots(1, 3, figsize=(18, 6))

# 第一阶段
first_period = year_topic_analysis[year_topic_analysis['period'] == '第一阶段(2003-2012)']
first_pivot = first_period.pivot(index='time', columns='dominant_topic', values='relative_heat')
first_pivot.plot(kind='area', ax=axes[0], title='第一阶段主题热度变化 (2003-2012)', ylabel='相对热度 (%)')

# 第二阶段
second_period = year_topic_analysis[year_topic_analysis['period'] == '第二阶段(2013-2021)']
second_pivot = second_period.pivot(index='time', columns='dominant_topic', values='relative_heat')
second_pivot.plot(kind='area', ax=axes[1], title='第二阶段主题热度变化 (2013-2021)', ylabel='相对热度 (%)')

# 第三阶段
third_period = year_topic_analysis[year_topic_analysis['period'] == '第三阶段(2022-2025)']
third_pivot = third_period.pivot(index='time', columns='dominant_topic', values='relative_heat')
third_pivot.plot(kind='area', ax=axes[2], title='第三阶段主题热度变化 (2022-2025)', ylabel='相对热度 (%)')

plt.tight_layout()
plt.show()

# 2. 导出完整数据到Excel
output_file = "年度主题热度分析.xlsx"
with pd.ExcelWriter(output_file) as writer:
    year_topic_analysis.to_excel(writer, sheet_name='年度主题热度详情', index=False)
    period_summary.to_excel(writer, sheet_name='阶段汇总', index=False)
    rank_summary.to_excel(writer, sheet_name='排名统计', index=False)

print(f"\n数据已导出到: {output_file}")

# 3. 打印关键洞察
print("\n=== 关键洞察 ===")
print("1. 第一阶段: 只有2个主题，主题0在所有年份都排名第一")
print("2. 第二阶段: 主题数量增加到4个，热度分布更加均衡")
print("3. 第三阶段: 主题热度竞争激烈，没有明显的绝对主导主题")
print("4. 整体趋势: 从单一主导主题向多主题均衡发展")

# 显示每个阶段的热门主题
print("\n=== 各阶段热门主题 ===")
for period in ['第一阶段(2003-2012)', '第二阶段(2013-2021)', '第三阶段(2022-2025)']:
    period_data = period_summary[period_summary['period'] == period]
    top_topic = period_data.loc[period_data['count'].idxmax()]
    print(f"{period}: 主题{int(top_topic['dominant_topic'])} (热度: {top_topic['relative_heat']}%)")

# 创建各年份主题热度的绝对数值表格（宽格式，适合导入Origin）
year_topic_wide = year_topic_analysis.pivot(index='time', 
                                           columns='dominant_topic', 
                                           values='count').fillna(0).astype(int)

# 重命名列名，使其在Origin中更清晰
year_topic_wide.columns = [f'Topic_{topic}' for topic in year_topic_wide.columns]

# 添加年份列作为第一列
year_topic_wide = year_topic_wide.reset_index()
year_topic_wide = year_topic_wide.rename(columns={'time': 'Year'})

print("各年份主题热度绝对值数据（适合导入Origin）：")
print(year_topic_wide)

# 同时生成相对热度表格
year_topic_relative_wide = year_topic_analysis.pivot(index='time', 
                                                    columns='dominant_topic', 
                                                    values='relative_heat').fillna(0).round(2)

year_topic_relative_wide.columns = [f'Topic_{topic}_Relative' for topic in year_topic_relative_wide.columns]
year_topic_relative_wide = year_topic_relative_wide.reset_index()
year_topic_relative_wide = year_topic_relative_wide.rename(columns={'time': 'Year'})

print("\n各年份主题相对热度数据（百分比）：")
print(year_topic_relative_wide)

# 导出到Excel文件，方便导入Origin
origin_data_file = "主题热度_Origin格式.xlsx"
with pd.ExcelWriter(origin_data_file) as writer:
    year_topic_wide.to_excel(writer, sheet_name='绝对热度', index=False)
    year_topic_relative_wide.to_excel(writer, sheet_name='相对热度', index=False)
    
print(f"\n数据已导出到: {origin_data_file}")
print("您可以将此文件导入Origin进行绘图")

# 显示数据统计信息
print(f"\n数据表格信息:")
print(f"年份范围: {year_topic_wide['Year'].min()} - {year_topic_wide['Year'].max()}")
print(f"主题数量: {len(year_topic_wide.columns) - 1}")  # 减去Year列
print(f"数据行数: {len(year_topic_wide)}")

# 为不同阶段的主题创建唯一标识
def create_topic_label(period, topic_id):
    if period == '第一阶段(2003-2012)':
        return f'FirstPeriod_Topic_{topic_id}'
    elif period == '第二阶段(2013-2021)':
        return f'SecondPeriod_Topic_{topic_id}'
    else:  # 第三阶段
        return f'ThirdPeriod_Topic_{topic_id}'

# 为每个主题创建唯一标签
year_topic_analysis['topic_label'] = year_topic_analysis.apply(
    lambda x: create_topic_label(x['period'], x['dominant_topic']), axis=1
)

# 创建各年份主题热度的绝对数值表格（宽格式，适合导入Origin）
year_topic_wide = year_topic_analysis.pivot(index='time', 
                                           columns='topic_label', 
                                           values='count').fillna(0).astype(int)

# 添加年份列作为第一列
year_topic_wide = year_topic_wide.reset_index()
year_topic_wide = year_topic_wide.rename(columns={'time': 'Year'})

# 重新排列列的顺序，让同一阶段的主题在一起
column_order = ['Year'] 
column_order += [f'FirstPeriod_Topic_{i}' for i in range(2)]
column_order += [f'SecondPeriod_Topic_{i}' for i in range(4)]
column_order += [f'ThirdPeriod_Topic_{i}' for i in range(4)]

year_topic_wide = year_topic_wide.reindex(columns=column_order)

print("各年份主题热度绝对值数据（区分阶段主题）：")
print(year_topic_wide)

# 生成相对热度表格
year_topic_relative_wide = year_topic_analysis.pivot(index='time', 
                                                    columns='topic_label', 
                                                    values='relative_heat').fillna(0).round(2)

year_topic_relative_wide = year_topic_relative_wide.reset_index()
year_topic_relative_wide = year_topic_relative_wide.rename(columns={'time': 'Year'})
year_topic_relative_wide = year_topic_relative_wide.reindex(columns=column_order)

print("\n各年份主题相对热度数据（百分比）：")
print(year_topic_relative_wide)

# 导出到Excel文件
origin_data_file = "主题热度_分阶段主题.xlsx"
with pd.ExcelWriter(origin_data_file) as writer:
    year_topic_wide.to_excel(writer, sheet_name='绝对热度', index=False)
    year_topic_relative_wide.to_excel(writer, sheet_name='相对热度', index=False)
    
print(f"\n数据已导出到: {origin_data_file}")

# 显示主题说明
print("\n=== 主题说明 ===")
print("第一阶段 (2003-2012): FirstPeriod_Topic_0, FirstPeriod_Topic_1")
print("第二阶段 (2013-2021): SecondPeriod_Topic_0, SecondPeriod_Topic_1, SecondPeriod_Topic_2, SecondPeriod_Topic_3")
print("第三阶段 (2022-2025): ThirdPeriod_Topic_0, ThirdPeriod_Topic_1, ThirdPeriod_Topic_2, ThirdPeriod_Topic_3")

# 第一阶段：2003-2012年，2个主题
first_period_data = papers_18_19.copy()
first_period_data['dominant_topic'] = dominant_topics_first

# 计算第一阶段各年份各主题的文档数量
first_period_heat = first_period_data.groupby(['time', 'dominant_topic']).size().reset_index(name='count')

# 创建完整的第一阶段年份-主题网格
first_years = range(2003, 2013)  # 2003-2012
first_topics = range(2)  # 0,1
first_grid = pd.MultiIndex.from_product([first_years, first_topics], names=['Year', 'Topic']).to_frame(index=False)

# 合并数据，填充缺失值为0
first_period_complete = first_grid.merge(first_period_heat, left_on=['Year', 'Topic'], 
                                       right_on=['time', 'dominant_topic'], how='left')
first_period_complete['count'] = first_period_complete['count'].fillna(0).astype(int)
first_period_complete = first_period_complete[['Year', 'Topic', 'count']]

print("第一阶段各年份主题热度：")
print(first_period_complete)

# 转换为宽格式（适合Origin绘图）
first_period_wide = first_period_complete.pivot(index='Year', columns='Topic', values='count')
first_period_wide.columns = [f'First_Topic_{i}' for i in first_period_wide.columns]
first_period_wide = first_period_wide.reset_index()

print("\n第一阶段宽格式数据：")
print(first_period_wide)

# 第二阶段：2013-2021年，4个主题
second_period_data = papers_20_22.copy()
second_period_data['dominant_topic'] = dominant_topics_second

# 计算第二阶段各年份各主题的文档数量
second_period_heat = second_period_data.groupby(['time', 'dominant_topic']).size().reset_index(name='count')

# 创建完整的第二阶段年份-主题网格
second_years = range(2013, 2022)  # 2013-2021
second_topics = range(4)  # 0,1,2,3
second_grid = pd.MultiIndex.from_product([second_years, second_topics], names=['Year', 'Topic']).to_frame(index=False)

# 合并数据，填充缺失值为0
second_period_complete = second_grid.merge(second_period_heat, left_on=['Year', 'Topic'], 
                                         right_on=['time', 'dominant_topic'], how='left')
second_period_complete['count'] = second_period_complete['count'].fillna(0).astype(int)
second_period_complete = second_period_complete[['Year', 'Topic', 'count']]

print("第二阶段各年份主题热度：")
print(second_period_complete)

# 转换为宽格式（适合Origin绘图）
second_period_wide = second_period_complete.pivot(index='Year', columns='Topic', values='count')
second_period_wide.columns = [f'Second_Topic_{i}' for i in second_period_wide.columns]
second_period_wide = second_period_wide.reset_index()

print("\n第二阶段宽格式数据：")
print(second_period_wide)

# 第三阶段：2022-2025年，4个主题
third_period_data = papers_23_24.copy()
third_period_data['dominant_topic'] = dominant_topics_third

# 计算第三阶段各年份各主题的文档数量
third_period_heat = third_period_data.groupby(['time', 'dominant_topic']).size().reset_index(name='count')

# 创建完整的第三阶段年份-主题网格
third_years = range(2022, 2025)  # 2022-2024
third_topics = range(4)  # 0,1,2,3
third_grid = pd.MultiIndex.from_product([third_years, third_topics], names=['Year', 'Topic']).to_frame(index=False)

# 合并数据，填充缺失值为0
third_period_complete = third_grid.merge(third_period_heat, left_on=['Year', 'Topic'], 
                                       right_on=['time', 'dominant_topic'], how='left')
third_period_complete['count'] = third_period_complete['count'].fillna(0).astype(int)
third_period_complete = third_period_complete[['Year', 'Topic', 'count']]

print("第三阶段各年份主题热度：")
print(third_period_complete)

# 转换为宽格式（适合Origin绘图）
third_period_wide = third_period_complete.pivot(index='Year', columns='Topic', values='count')
third_period_wide.columns = [f'Third_Topic_{i}' for i in third_period_wide.columns]
third_period_wide = third_period_wide.reset_index()

print("\n第三阶段宽格式数据：")
print(third_period_wide)

# 合并所有阶段的数据
# 首先合并第一和第二阶段
all_periods_wide = first_period_wide.merge(second_period_wide, on='Year', how='outer')

# 然后合并第三阶段
all_periods_wide = all_periods_wide.merge(third_period_wide, on='Year', how='outer')

# 按年份排序
all_periods_wide = all_periods_wide.sort_values('Year').reset_index(drop=True)

print("所有阶段合并数据：")
print(all_periods_wide)

# 导出到Excel
final_output_file = "各阶段主题热度_分阶段数据.xlsx"
with pd.ExcelWriter(final_output_file) as writer:
    first_period_wide.to_excel(writer, sheet_name='第一阶段', index=False)
    second_period_wide.to_excel(writer, sheet_name='第二阶段', index=False)
    third_period_wide.to_excel(writer, sheet_name='第三阶段', index=False)
    all_periods_wide.to_excel(writer, sheet_name='所有阶段合并', index=False)

print(f"\n数据已导出到: {final_output_file}")

# 显示数据统计
print("\n=== 数据统计 ===")
print(f"总年份数: {len(all_periods_wide)}")
print(f"年份范围: {all_periods_wide['Year'].min()} - {all_periods_wide['Year'].max()}")
print(f"第一阶段主题数: 2个 (2003-2012)")
print(f"第二阶段主题数: 4个 (2013-2021)") 
print(f"第三阶段主题数: 4个 (2022-2025)")
print("\n每个工作表包含对应阶段各年份的主题热度绝对值，可直接导入Origin绘图")