英语四级单词频率统计及翻译

英语四级单词文本处理

• 单词频率统计，并排序
• excel、数据库操作

import re
import xlwt
import sqlite3


def get_file():
    """
    将txt中的文本进行处理，删除其他字符和中文，返回文本内容
    """
    f1 = open(r"C:\Users\Administrator\Desktop\英语四级.txt", "r",encoding="utf-8" )
    content = f1.read().lower() # 将所有单词小写

    f1.close()
    for sub in '!@#$%^&*()（）: ;：；_+-{}[]|\<>?/.,`~1234567890"\'￡“”':  # 将其他字符去除
        content = content.replace(sub," ")

        pattern = re.compile(r'[\u4e00-\u9fa5]')
        content = re.sub(pattern, "", content)  # 排除汉字
        content = re.sub('\s', ' ', content)    # 删除空白符
        
    # print(content)

    # f2 = open(r".\english4.txt","w",encoding="utf-8")  # 将筛选完的内容存到本目录下
    # f2.write(content)
    # f2.close()
    # print(content)
    return content


def count_words(content):
    """
    将文本内容中的单词进行统计数量并排序
    :param content: 文本
    :return:排序好的单词列表（字典嵌套在列表中）
    """
    ls = content.split(" ")
    counts = {}
    for word in ls:
        counts[word] = counts.get(word, 0) + 1   # get得到字典中的值，初始值为0
    items = list(counts.items())  # 字典转换为列表
    items.sort(key=lambda x:x[1],reverse=True)                  # 把列表按照键值对的第二个元素，由大到小排序

    # print(items)
    return items


def proceedings_ls(words_list):
    """
    再一次处理排好序的列表，将不必要的单词删除
    :return: 处理好的列表
    """
    words=[]
    # print(words_list[i][0]+" "+str(len(words_list[i][0])))
    # 删除所有不必要的英文单词
    for word in words_list:
        if len(word[0]) > 4 and word[1]>=4:
            words.append(list(word))

    return words


def save_excel(data_list):
    """将数据存入excel中"""
    workbook = xlwt.Workbook(encoding="utf-8")  # 创建workbook对象
    worksheet = workbook.add_sheet("sheet1")  # 创建工作表
    worksheet.write(0, 0, "单词")  # 写入数据，第一个参数是行，第二个参数是列，第三个是保存的内容
    worksheet.write(0,1,"频率")
    for i in range(len(data_list)):
        word =data_list[i][0]
        rate = data_list[i][1]
        worksheet.write(i+1,0,word)
        worksheet.write(i+1,1,rate)

    workbook.save("english4.xlsx")  # 保存数据


def save_db(datalist,dbpath):
    """
    将数据保存到数据库
    """
    init_db(dbpath)
    conn = sqlite3.connect(dbpath)
    cursor = conn.cursor()

    for data in datalist:
        for index in range(len(data)):
            data[index] = '"'+str(data[index])+'"'
        sql ='''
            insert into word4(
            Word,Rate)
            values(%s)
        '''%",".join(data)
        # print(sql)
        cursor.execute(sql)
        conn.commit()
    cursor.close()
    conn.close()


# 初始创建一个数据库和一个数据表
def init_db(dbpath):
    sql = '''
        create table word4(
        id integer primary  key autoincrement,
        Word text,
        Rate text
        )
    ''' # 创建数据表

    conn = sqlite3.connect(dbpath)
    cursor = conn.cursor()
    cursor.execute(sql)
    conn.commit()
    conn.close()

def main():
    # 获取处理好的文本 以空格为界限创建一个单词列表
    text = get_file()          # 文本处理
    words_ls=count_words(text)  # 文本内容处理 返回列表
    data_list = proceedings_ls(words_ls)    # 文本内容再次处理
    print(data_list)
    save_excel(data_list)       # excel操作
    save_db(data_list,"English.db")



if __name__ == '__main__':
    main()

英语单词翻译

import pandas as pd

# 注意：stardict.csv的地址需要替换成你自己的文件地址
df_dict = pd.read_csv(r"C:\Users\Administrator\Desktop\stardict.csv")
df_dict.shape
df_dict.sample(10).head()

# 把word、translation之外的列扔掉
df_dict = df_dict[["word", "translation"]]
df_dict.head()


# # Pandas官方文档中的一个URL
# url = "https://pandas.pydata.org/docs/user_guide/indexing.html"
# html_cont = requests.get(url).text
# html_cont[:100]

# soup = BeautifulSoup(html_cont)
# html_text = soup.get_text()
# html_text[:500]

# # 分词
# word_list = re.split("""[ ,.\(\)/\n|\-:=\$\["']""",html_text)
# word_list[:10]
#
# 读取停用词表，从网上复制的，位于当前目录下
# with open(r".\translate.txt",encoding="utf-8") as fin:
#     stop_words=set(fin.read().split("\n"))
# list(stop_words)[:10]
#
# # 数据清洗
# word_list_clean = []
# for word in word_list:
#     word = str(word).lower().strip()
#     # 过滤掉空词、数字、单个字符的词、停用词
#     if not word or word.isnumeric() or len(word)<=1 or word in stop_words:
#         continue
#     word_list_clean.append(word)
# word_list_clean[:20]

f = open("translate.txt","r",encoding="utf-8")
content = f.read()
wordlist = content.split(" ")
# print(wordlist)
df_words = pd.DataFrame({
    "word": wordlist
})
# df_words.head()
# df_merge=df_words.head()
# print(df_merge)

# 统计词频
# df_words = (
#     df_words
#     .groupby("word")["word"]
#     .agg(count="size")
#     .reset_index()
#     .sort_values(by="count", ascending=False)
# )
# df_words.head(10)


df_merge = pd.merge(
    left = df_dict,
    right = df_words,
    left_on = "word",
    right_on = "word"
)

df_merge.to_excel("translate.xlsx", index=False)