__Author__ = "PimeiChen"
__Desc__ = "根据nltk计算词频,并标记包含对应1个单词的评论,最后完成计数"
import re
import nltk
import pandas as pd
from nltk import FreqDist
from nltk.corpus import stopwords
nltk.download('punkt')
nltk.download('stopwords')
def word_frequencies(text, stop_words):
words = re.findall(r'\b\w+\b', text.lower())
filtered_words = [word for word in words if word.isalnum() and word not in stop_words]
return FreqDist(filtered_words)
#文件名,不用改后缀
document_name = 'abc'
data = pd.read_excel(f'{document_name}.xlsx')
all_comments = ' '.join(data['title'].fillna('').astype(str).tolist())
#停用词列表
custom_stop_words = set(stopwords.words('english'))
additional_stop_words = {'custom'}
custom_stop_words.update(additional_stop_words)
word_freqs = word_frequencies(all_comments, custom_stop_words)
#生成多少列
columns = [word[0] for word in word_freqs.most_common(600)]
binary_data = []
for title in data['title'].fillna('').astype(str):
match_dict = {word: 0 for word in columns}
words = re.findall(r'\b\w+\b', title.lower())
for word in words:
if word in match_dict:
match_dict[word] = 1
binary_data.append(match_dict)
binary_df = pd.DataFrame(binary_data)
result_data = pd.concat([data, binary_df], axis=1)
with pd.ExcelWriter(f'{document_name}_1_word.xlsx') as writer:
result_data.to_excel(writer, sheet_name='result_data', index=False)
# 创建一个新的DataFrame用于存储每个单词的评论次数
word_counts = []
for column in columns:
count = binary_df[column].sum()
word_counts.append({'word': column, 'count': count})
comment_count_df = pd.DataFrame(word_counts)
comment_count_df.to_excel(writer, sheet_name='word_counts', index=False)
小工具,直接上代码 nltk默认词频抓取,无视标点符号,可自定义停用词列表。标记出现某词的评论,赋值为1,并在sheet2里列出出现某次的评论数