2023MCM-C.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2023MCM-C Wordle\n",
    "\n",
    "使用N-gram模型，以及马尔科夫链"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#导入必要的包\n",
    "import os\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt\n",
    "from matplotlib.pyplot import MultipleLocator\n",
    "import plotly.express as px\n",
    "from sklearn import preprocessing"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 导入数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "wordle=pd.read_excel('wordle_data.xlsx')\n",
    "word_freq=pd.read_excel('word_freq.xlsx')\n",
    "time_series=pd.read_excel('arima.xlsx')\n",
    "n_gram=pd.read_excel('n-gram.xlsx',keep_default_na=False)\n",
    "df_w=pd.read_excel('wordle.xlsx')\n",
    "wordle_try=pd.read_excel('wordle_try.xlsx')"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 导出数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "n_gram.to_excel(\"n-gram.xlsx\") #输出Excel文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "wordle.to_excel(\"wordle.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_w.to_excel('wordl.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "wordle_try.to_excel('wordle_try.xlsx')"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 数据处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in range(len(n_gram)):\n",
    "    n_gram['freq'].loc[i]=word_freq['count'].loc[word_freq['word']==n_gram['word'].loc[i]]\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "n_gram['freq']=n_gram['freq']/63288263237\n",
    "n_gram.to_excel(\"n-gram.xlsx\") #输出Excel文件"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# N-gram\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 首先计算n=1，unigram"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 获得每个字母的出现概率"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "letter1=n_gram['letter1'].value_counts(normalize=True)\n",
    "letter2=n_gram['letter2'].value_counts(normalize=True)\n",
    "letter3=n_gram['letter3'].value_counts(normalize=True)\n",
    "letter4=n_gram['letter4'].value_counts(normalize=True)\n",
    "letter5=n_gram['letter5'].value_counts(normalize=True)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 计算每个单词的难度，并输出到表中"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in range(len(n_gram)):\n",
    "    a=n_gram['word'].iloc[i][0]\n",
    "    b=n_gram['word'].iloc[i][1]\n",
    "    c=n_gram['word'].iloc[i][2]\n",
    "    d=n_gram['word'].iloc[i][3]\n",
    "    e=n_gram['word'].iloc[i][4]\n",
    "    \n",
    "    p_a=n_gram['freq'].loc[n_gram['letter1']==a].sum()+n_gram['freq'].loc[n_gram['letter2']==a].sum()+n_gram['freq'].loc[n_gram['letter3']==a].sum()+n_gram['freq'].loc[n_gram['letter4']==a].sum()+n_gram['freq'].loc[n_gram['letter5']==a].sum()\n",
    "    p_b=n_gram['freq'].loc[n_gram['letter1']==b].sum()+n_gram['freq'].loc[n_gram['letter2']==b].sum()+n_gram['freq'].loc[n_gram['letter3']==b].sum()+n_gram['freq'].loc[n_gram['letter4']==b].sum()+n_gram['freq'].loc[n_gram['letter5']==b].sum()\n",
    "    p_c=n_gram['freq'].loc[n_gram['letter1']==c].sum()+n_gram['freq'].loc[n_gram['letter2']==c].sum()+n_gram['freq'].loc[n_gram['letter3']==c].sum()+n_gram['freq'].loc[n_gram['letter4']==c].sum()+n_gram['freq'].loc[n_gram['letter5']==c].sum()\n",
    "    p_d=n_gram['freq'].loc[n_gram['letter1']==d].sum()+n_gram['freq'].loc[n_gram['letter2']==d].sum()+n_gram['freq'].loc[n_gram['letter3']==d].sum()+n_gram['freq'].loc[n_gram['letter4']==d].sum()+n_gram['freq'].loc[n_gram['letter5']==d].sum()\n",
    "    p_e=n_gram['freq'].loc[n_gram['letter1']==e].sum()+n_gram['freq'].loc[n_gram['letter2']==e].sum()+n_gram['freq'].loc[n_gram['letter3']==e].sum()+n_gram['freq'].loc[n_gram['letter4']==e].sum()+n_gram['freq'].loc[n_gram['letter5']==e].sum()\n",
    "    \n",
    "    log_p1=np.log(p_a)+np.log(p_b)+np.log(p_c)+np.log(p_d)+np.log(p_e)\n",
    "    n_gram['n=1'].loc[i]=log_p1\n",
    "\n",
    "    ppl1=(p_a*p_b*p_c*p_d*p_e)**(-0.2)\n",
    "    n_gram['PPL1'].loc[i]=ppl1  \n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 计算n=2,bigram"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 旧做法"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in range(len(n_gram)):\n",
    "    #a1...5是每个字母出现的频数\n",
    "    #p01,p12,p23,p34,p45,p50,是每个马尔科夫链的条件概率\n",
    "    #log_p2为n=2时，p的对数\n",
    "    a1=len(n_gram.loc[n_gram['letter1']==n_gram['word'].loc[i][0]])\n",
    "    a2=len(n_gram.loc[n_gram['letter2']==n_gram['word'].loc[i][1]])\n",
    "    a3=len(n_gram.loc[n_gram['letter3']==n_gram['word'].loc[i][2]])\n",
    "    a4=len(n_gram.loc[n_gram['letter4']==n_gram['word'].loc[i][3]])\n",
    "    a5=len(n_gram.loc[n_gram['letter5']==n_gram['word'].loc[i][4]])\n",
    "\n",
    "    p01=letter1[n_gram['word'].loc[i][0]]\n",
    "    p12=len(n_gram.loc[n_gram['letter1']==n_gram['word'].loc[i][0]].loc[n_gram['letter2']==n_gram['word'].loc[i][1]])/a1\n",
    "    p23=len(n_gram.loc[n_gram['letter2']==n_gram['word'].loc[i][1]].loc[n_gram['letter3']==n_gram['word'].loc[i][2]])/a2\n",
    "    p34=len(n_gram.loc[n_gram['letter3']==n_gram['word'].loc[i][2]].loc[n_gram['letter4']==n_gram['word'].loc[i][3]])/a3\n",
    "    p45=len(n_gram.loc[n_gram['letter4']==n_gram['word'].loc[i][3]].loc[n_gram['letter5']==n_gram['word'].loc[i][4]])/a4\n",
    "\n",
    "    # 计算概率\n",
    "    log_p2=np.log(p01)+np.log(p12)+np.log(p23)+np.log(p34)+np.log(p45)\n",
    "    n_gram['n=2'].loc[i]=log_p2\n",
    "    \n",
    "#n_gram.to_excel(\"n_gram.xlsx\") #输出Excel文件\n",
    "\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 新做法"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in range(len(n_gram)):\n",
    "    #两个字母的库\n",
    "    a=n_gram['word'].iloc[i][0]+n_gram['word'].iloc[i][1]\n",
    "    b=n_gram['word'].iloc[i][1]+n_gram['word'].iloc[i][2]\n",
    "    c=n_gram['word'].iloc[i][2]+n_gram['word'].iloc[i][3]\n",
    "    d=n_gram['word'].iloc[i][3]+n_gram['word'].iloc[i][4]\n",
    "    #一个字母的库\n",
    "    a0=n_gram['word'].iloc[i][0]\n",
    "    b0=n_gram['word'].iloc[i][1]\n",
    "    c0=n_gram['word'].iloc[i][2]\n",
    "    d0=n_gram['word'].iloc[i][3]\n",
    "    e0=n_gram['word'].iloc[i][4]\n",
    "\n",
    "    p_0=n_gram['freq'].loc[n_gram['letter1']==a0].sum()\n",
    "\n",
    "    p_a1=n_gram['freq'].loc[n_gram['letter12']==a].sum()+n_gram['freq'].loc[n_gram['letter23']==a].sum()+n_gram['freq'].loc[n_gram['letter34']==a].sum()+n_gram['freq'].loc[n_gram['letter45']==a].sum()\n",
    "    p_a2=n_gram['freq'].loc[n_gram['letter1']==a0].sum()+n_gram['freq'].loc[n_gram['letter2']==a0].sum()+n_gram['freq'].loc[n_gram['letter3']==a0].sum()+n_gram['freq'].loc[n_gram['letter4']==a0].sum()\n",
    "    p_a=p_a1/p_a2\n",
    "\n",
    "    p_b1=n_gram['freq'].loc[n_gram['letter12']==b].sum()+n_gram['freq'].loc[n_gram['letter23']==b].sum()+n_gram['freq'].loc[n_gram['letter34']==b].sum()+n_gram['freq'].loc[n_gram['letter45']==b].sum()\n",
    "    p_b2=n_gram['freq'].loc[n_gram['letter1']==b0].sum()+n_gram['freq'].loc[n_gram['letter2']==b0].sum()+n_gram['freq'].loc[n_gram['letter3']==b0].sum()+n_gram['freq'].loc[n_gram['letter4']==b0].sum()\n",
    "    p_b=p_b1/p_b2\n",
    "\n",
    "    p_c1=n_gram['freq'].loc[n_gram['letter12']==c].sum()+n_gram['freq'].loc[n_gram['letter23']==c].sum()+n_gram['freq'].loc[n_gram['letter34']==c].sum()+n_gram['freq'].loc[n_gram['letter45']==c].sum()\n",
    "    p_c2=n_gram['freq'].loc[n_gram['letter1']==c0].sum()+n_gram['freq'].loc[n_gram['letter2']==c0].sum()+n_gram['freq'].loc[n_gram['letter3']==c0].sum()+n_gram['freq'].loc[n_gram['letter4']==c0].sum()\n",
    "    p_c=p_c1/p_c2\n",
    "\n",
    "    p_d1=n_gram['freq'].loc[n_gram['letter12']==d].sum()+n_gram['freq'].loc[n_gram['letter23']==d].sum()+n_gram['freq'].loc[n_gram['letter34']==d].sum()+n_gram['freq'].loc[n_gram['letter45']==d].sum()\n",
    "    p_d2=n_gram['freq'].loc[n_gram['letter1']==d0].sum()+n_gram['freq'].loc[n_gram['letter2']==d0].sum()+n_gram['freq'].loc[n_gram['letter3']==d0].sum()+n_gram['freq'].loc[n_gram['letter4']==d0].sum()\n",
    "    p_d=p_d1/p_d2\n",
    "\n",
    "    log_p2=np.log(p_0)+np.log(p_a)+np.log(p_b)+np.log(p_c)+np.log(p_d)\n",
    "    n_gram['n=2'].loc[i]=log_p2\n",
    "\n",
    "    ppl2=(p_a*p_b*p_c*p_d)**(-0.25)\n",
    "    n_gram['PPL2'].loc[i]=ppl2\n",
    "    "
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 计算n=3，trigram"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 旧做法"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in range(len(n_gram)):\n",
    "    #b1...5是每个字母出现的频数\n",
    "    #p012,p123,p234,p345,p450是每个马尔科夫链的条件概率\n",
    "    #log_p3为n=3时，p的对数\n",
    "    b1=len(n_gram.loc[n_gram['letter1']==n_gram['word'].loc[i][0]].loc[n_gram['letter2']==n_gram['word'].loc[i][1]])\n",
    "    b2=len(n_gram.loc[n_gram['letter2']==n_gram['word'].loc[i][1]].loc[n_gram['letter3']==n_gram['word'].loc[i][2]])\n",
    "    b3=len(n_gram.loc[n_gram['letter3']==n_gram['word'].loc[i][2]].loc[n_gram['letter4']==n_gram['word'].loc[i][3]])\n",
    "    b4=len(n_gram.loc[n_gram['letter4']==n_gram['word'].loc[i][3]].loc[n_gram['letter5']==n_gram['word'].loc[i][4]])\n",
    "\n",
    "    if b1*b2*b3*b4==0:\n",
    "        log_p3=-20\n",
    "    else:\n",
    "        p012=len(n_gram.loc[n_gram['letter1']==n_gram['word'].loc[i][0]].loc[n_gram['letter2']==n_gram['word'].loc[i][1]])/len(word_freq)\n",
    "        p123=len(n_gram.loc[n_gram['letter1']==n_gram['word'].loc[i][0]].loc[n_gram['letter2']==n_gram['word'].loc[i][1]].loc[n_gram['letter3']==n_gram['word'].loc[i][2]])/b1\n",
    "        p234=len(n_gram.loc[n_gram['letter2']==n_gram['word'].loc[i][1]].loc[n_gram['letter3']==n_gram['word'].loc[i][2]].loc[n_gram['letter4']==n_gram['word'].loc[i][3]])/b2\n",
    "        p345=len(n_gram.loc[n_gram['letter3']==n_gram['word'].loc[i][2]].loc[n_gram['letter4']==n_gram['word'].loc[i][3]].loc[n_gram['letter5']==n_gram['word'].loc[i][4]])/b3\n",
    "\n",
    "        # 计算概率\n",
    "        if p012*p123*p234*p345==0:\n",
    "            log_p3=-20\n",
    "        else:\n",
    "            log_p3=np.log(p012)+np.log(p123)+np.log(p234)+np.log(p345)\n",
    "            \n",
    "    n_gram['n=3'].loc[i]=log_p3\n",
    "    \n",
    "#n_gram.to_excel(\"n_gram.xlsx\") #输出Excel文件\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 新做法"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in range(len(n_gram)):\n",
    "    #三个字母的库\n",
    "    a=n_gram['word'].iloc[i][0]+n_gram['word'].iloc[i][1]+n_gram['word'].iloc[i][2]\n",
    "    b=n_gram['word'].iloc[i][1]+n_gram['word'].iloc[i][2]+n_gram['word'].iloc[i][3]\n",
    "    c=n_gram['word'].iloc[i][2]+n_gram['word'].iloc[i][3]+n_gram['word'].iloc[i][4]\n",
    "   \n",
    "    #两个字母的库\n",
    "    a0=n_gram['word'].iloc[i][0]+n_gram['word'].iloc[i][1]\n",
    "    b0=n_gram['word'].iloc[i][1]+n_gram['word'].iloc[i][2]\n",
    "    c0=n_gram['word'].iloc[i][2]+n_gram['word'].iloc[i][3]\n",
    "    d0=n_gram['word'].iloc[i][3]+n_gram['word'].iloc[i][4]\n",
    "\n",
    "\n",
    "    p_0=n_gram['freq'].loc[n_gram['letter12']==a0].sum()\n",
    "\n",
    "    p_a1=n_gram['freq'].loc[n_gram['letter123']==a].sum()+n_gram['freq'].loc[n_gram['letter234']==a].sum()+n_gram['freq'].loc[n_gram['letter345']==a].sum()\n",
    "    p_a2=n_gram['freq'].loc[n_gram['letter12']==a0].sum()+n_gram['freq'].loc[n_gram['letter23']==a0].sum()+n_gram['freq'].loc[n_gram['letter34']==a0].sum()\n",
    "    p_a=p_a1/p_a2\n",
    "\n",
    "    p_b1=n_gram['freq'].loc[n_gram['letter123']==b].sum()+n_gram['freq'].loc[n_gram['letter234']==b].sum()+n_gram['freq'].loc[n_gram['letter345']==b].sum()\n",
    "    p_b2=n_gram['freq'].loc[n_gram['letter12']==b0].sum()+n_gram['freq'].loc[n_gram['letter23']==b0].sum()+n_gram['freq'].loc[n_gram['letter34']==b0].sum()\n",
    "    p_b=p_b1/p_b2\n",
    "\n",
    "    p_c1=n_gram['freq'].loc[n_gram['letter123']==c].sum()+n_gram['freq'].loc[n_gram['letter234']==c].sum()+n_gram['freq'].loc[n_gram['letter345']==c].sum()\n",
    "    p_c2=n_gram['freq'].loc[n_gram['letter12']==c0].sum()+n_gram['freq'].loc[n_gram['letter23']==c0].sum()+n_gram['freq'].loc[n_gram['letter34']==c0].sum()\n",
    "    p_c=p_c1/p_c2\n",
    "\n",
    "\n",
    "    log_p3=np.log(p_0)+np.log(p_a)+np.log(p_b)+np.log(p_c)\n",
    "    n_gram['n=3'].loc[i]=log_p3\n",
    "\n",
    "    ppl3=(p_a*p_b*p_c)**(-1/3)\n",
    "    n_gram['PPL3'].loc[i]=ppl3\n",
    "    \n",
    " "
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 计算n=4，4-gram"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 旧做法"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in range(len(n_gram)):\n",
    "    #b1...5是每个字母出现的频数\n",
    "    #p012,p123,p234,p345,p450是每个马尔科夫链的条件概率\n",
    "    #log_p3为n=4时，p的对数\n",
    "    c1=len(n_gram.loc[n_gram['letter1']==n_gram['word'].loc[i][0]].loc[n_gram['letter2']==n_gram['word'].loc[i][1]].loc[n_gram['letter3']==n_gram['word'].loc[i][2]])\n",
    "    c2=len(n_gram.loc[n_gram['letter2']==n_gram['word'].loc[i][1]].loc[n_gram['letter3']==n_gram['word'].loc[i][2]].loc[n_gram['letter4']==n_gram['word'].loc[i][3]])\n",
    "    #c3=...不用求\n",
    "\n",
    "    if c1*c2==0:\n",
    "        log_p4=-20\n",
    "    else:\n",
    "        p0123=len(n_gram.loc[n_gram['letter1']==n_gram['word'].loc[i][0]].loc[n_gram['letter2']==n_gram['word'].loc[i][1]].loc[n_gram['letter3']==n_gram['word'].loc[i][2]])/len(word_freq)\n",
    "        p1234=len(n_gram.loc[n_gram['letter1']==n_gram['word'].loc[i][0]].loc[n_gram['letter2']==n_gram['word'].loc[i][1]].loc[n_gram['letter3']==n_gram['word'].loc[i][2]].loc[n_gram['letter4']==n_gram['word'].loc[i][3]])/c1\n",
    "        p2345=len(n_gram.loc[n_gram['letter2']==n_gram['word'].loc[i][1]].loc[n_gram['letter3']==n_gram['word'].loc[i][2]].loc[n_gram['letter4']==n_gram['word'].loc[i][3]].loc[n_gram['letter5']==n_gram['word'].loc[i][4]])/c2\n",
    "\n",
    "        # 计算概率\n",
    "        if p0123*p1234*p2345==0:\n",
    "            log_p4=-20\n",
    "        else:\n",
    "            log_p4=np.log(p0123)+np.log(p1234)+np.log(p2345)\n",
    "            \n",
    "    n_gram['n=4'].loc[i]=log_p4\n",
    "    \n",
    "n_gram.to_excel(\"n_gram.xlsx\") #输出Excel文件"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 新做法"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ppl4=0\n",
    "\n",
    "for i in range(len(n_gram)):\n",
    "    #四个字母的库\n",
    "    a=n_gram['word'].iloc[i][0]+n_gram['word'].iloc[i][1]+n_gram['word'].iloc[i][2]+n_gram['word'].iloc[i][3]\n",
    "    b=n_gram['word'].iloc[i][1]+n_gram['word'].iloc[i][2]+n_gram['word'].iloc[i][3]+n_gram['word'].iloc[i][4]\n",
    "\n",
    "   \n",
    "    #三个字母的库\n",
    "    a0=n_gram['word'].iloc[i][0]+n_gram['word'].iloc[i][1]+n_gram['word'].iloc[i][2]\n",
    "    b0=n_gram['word'].iloc[i][1]+n_gram['word'].iloc[i][2]+n_gram['word'].iloc[i][3]\n",
    "    c0=n_gram['word'].iloc[i][2]+n_gram['word'].iloc[i][3]+n_gram['word'].iloc[i][4]\n",
    "\n",
    "\n",
    "    p_0=n_gram['freq'].loc[n_gram['letter123']==a0].sum()\n",
    "\n",
    "    p_a1=n_gram['freq'].loc[n_gram['letter1234']==a].sum()+n_gram['freq'].loc[n_gram['letter2345']==a].sum()\n",
    "    p_a2=n_gram['freq'].loc[n_gram['letter123']==a0].sum()+n_gram['freq'].loc[n_gram['letter234']==a0].sum()\n",
    "    p_a=p_a1/p_a2\n",
    "\n",
    "    p_b1=n_gram['freq'].loc[n_gram['letter1234']==b].sum()+n_gram['freq'].loc[n_gram['letter2345']==b].sum()\n",
    "    p_b2=n_gram['freq'].loc[n_gram['letter123']==b0].sum()+n_gram['freq'].loc[n_gram['letter234']==b0].sum()\n",
    "    p_b=p_b1/p_b2\n",
    "\n",
    "    log_p4=np.log(p_0)+np.log(p_a)+np.log(p_b)\n",
    "    n_gram['n=4'].loc[i]=log_p4\n",
    "\n",
    "    ppl4=(p_a*p_b)**(-0.5)\n",
    "    n_gram['PPL4'].loc[i]=ppl4"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 困惑度评级"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "n1=0\n",
    "n2=0\n",
    "n3=0\n",
    "n4=0\n",
    "for i in range(len(n_gram)):\n",
    "    n1=n1+np.log(n_gram['PPL1'].iloc[i])\n",
    "    n2=n2+np.log(n_gram['PPL2'].iloc[i])\n",
    "    n3=n3+np.log(n_gram['PPL3'].iloc[i])\n",
    "    n4=n4+np.log(n_gram['PPL4'].iloc[i])\n",
    "\n",
    "n1=np.exp(n1/len(n_gram))\n",
    "n2=np.exp(n2/len(n_gram))\n",
    "n3=np.exp(n3/len(n_gram))\n",
    "n4=np.exp(n4/len(n_gram))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#作图\n",
    "plt.plot(n_gram['word'],n_gram['PPL1'])"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 绘制n=3的图像"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import random\n",
    "#设置图片大小\n",
    "plt.figure(figsize=(24,8)) \n",
    "#绘制散点\n",
    "wordn1=[]\n",
    "pn1=[]\n",
    "\n",
    "wordn2=[]\n",
    "pn2=[]\n",
    "\n",
    "wordn3=[]\n",
    "pn3=[]\n",
    "\n",
    "wordn4=[]\n",
    "pn4=[]\n",
    "for i in range(100):\n",
    "    r=random.randint(1,len(n_gram))\n",
    "    wordn1.append(n_gram['word'].loc[r])\n",
    "    pn1.append(n_gram['n=1'].loc[r])\n",
    "\n",
    "    wordn2.append(n_gram['word'].loc[r])\n",
    "    pn2.append(n_gram['n=2'].loc[r])\n",
    "\n",
    "    wordn3.append(n_gram['word'].loc[r])\n",
    "    pn3.append(n_gram['n=3'].loc[r])\n",
    "\n",
    "    wordn4.append(n_gram['word'].loc[r])\n",
    "    pn4.append(n_gram['n=4'].loc[r])\n",
    "#连线\n",
    "plt.scatter(wordn2,pn2)\n",
    "plt.plot(wordn2,pn2,label='n=2')\n",
    "\n",
    "plt.scatter(wordn3,pn3)\n",
    "plt.plot(wordn3,pn3,label='n=3')\n",
    "\n",
    "plt.scatter(wordn4,pn4)\n",
    "plt.plot(wordn4,pn4,label='n=4')\n",
    "\n",
    "\n",
    "#设置标题，坐标轴\n",
    "plt.title(\"N-gram\")  \n",
    "plt.xlabel(\"word\")\n",
    "plt.xticks(rotation=-270)\n",
    "plt.ylabel(\"log_p\")\n",
    "plt.legend()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import random\n",
    "random.randint(1,10)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 进行聚类分析"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#导入包\n",
    "import time\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "from sklearn.cluster import KMeans\n",
    "from sklearn.metrics.pairwise import pairwise_distances_argmin\n",
    "from sklearn.datasets._samples_generator import make_blobs\n",
    "\n",
    "#导入数据\n",
    "batch_size = 45\n",
    "\n",
    "df_stand=n_gram[['n=3','freq']].to_numpy()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 进行聚类"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#进行kmeans聚类，分为5类\n",
    "\n",
    "#k_means = KMeans(n_clusters=5).fit(df_stand)  #简易用法\n",
    "n_clusters=10\n",
    "k_means = KMeans(init='k-means++', n_clusters=n_clusters, n_init=10).fit(df_stand) \n",
    "\n",
    "t0 = time.time()\n",
    "t_batch = time.time() - t0\n",
    "\n",
    "k_means_cluster_centers = np.sort(k_means.cluster_centers_, axis=0)\n",
    "k_means_labels = pairwise_distances_argmin(df_stand, k_means_cluster_centers)\n",
    "\n",
    "#将分类加载到表中\n",
    "n_gram['cluster']=k_means_labels"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 聚类可视化"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 原始数据图"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# plot result\n",
    "fig = plt.figure(dpi=300,figsize=(20,4))\n",
    "\n",
    "# original data\n",
    "ax = fig.add_subplot(1,2,1)\n",
    "row, _ = np.shape(df_stand)\n",
    "for i in range(row):\n",
    "    ax.plot(df_stand[i, 0], df_stand[i, 1] ,'#4EACC5', marker='.')\n",
    "\n",
    "ax.set_title('Original Data Distribution')\n",
    "\n",
    "plt.xlabel('Difficulty Factor')\n",
    "plt.ylabel('Word Frequency')"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### kmeans聚类图"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# plot result\n",
    "fig = plt.figure(dpi=300,figsize=(20,4))\n",
    "\n",
    "colors = ['#9400D3','#FF00FF','#FFC0CB','#0000CD','#FF1493','#7B68EE','#FFD700','#FFA500','#40E0D0','#4EACC5','#7FFF00']\n",
    "\n",
    "\n",
    "# K-means\n",
    "ax = fig.add_subplot(1, 2,1)\n",
    "for k, col in zip(range(n_clusters), colors):\n",
    "    my_members = k_means_labels == k\t\t# my_members是布尔型的数组（用于筛选同类的点，用不同颜色表示）\n",
    "    cluster_center = k_means_cluster_centers[k]\n",
    "    ax.plot(df_stand[my_members, 0], df_stand[my_members, 1], 'w',\n",
    "            markerfacecolor=col, marker='.')\t# 将同一类的点表示出来\n",
    "    ax.plot(cluster_center[0], cluster_center[1], 'o', markerfacecolor=col,\n",
    "            markeredgecolor='k', marker='o',label=' ')\t# 将聚类中心单独表示出来\n",
    "ax.set_title('Divided into ten difficulty levels by using KMeans')\n",
    "ax.legend()\n",
    "\n",
    "plt.xlabel('Difficulty Levels')\n",
    "plt.ylabel('Word Frequency')"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## CH系数评估"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#calinski_harabaz_score需要更新包，使用calinski_harabasz_score则不会报错\n",
    "from sklearn.metrics import calinski_harabasz_score\n",
    "\n",
    "S=[]#存放分数的数组，用于画图\n",
    "#定义CH检验函数\n",
    "def CH_test(df):\n",
    "    for i in range(2,50):\n",
    "        #构建并训练模型\n",
    "        kmeans = KMeans(init='k-means++', n_clusters=i, n_init=10,random_state=123).fit(df)\n",
    "        score = calinski_harabasz_score(df,kmeans.labels_)\n",
    "        S.append(score)\n",
    "        #print('数据聚%d类calinski_harabaz指数为:%f'%(i,score))\n",
    "CH_test(df_stand)\n",
    "\n",
    "plt.plot(range(2,50),S)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 计算某个单词的难度"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#输入单词\n",
    "word='eerie'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### n=2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "e1=len(n_gram.loc[n_gram['letter1']==word[0]])\n",
    "e2=len(n_gram.loc[n_gram['letter2']==word[1]])\n",
    "e3=len(n_gram.loc[n_gram['letter3']==word[2]])\n",
    "e4=len(n_gram.loc[n_gram['letter4']==word[3]])\n",
    "e5=len(n_gram.loc[n_gram['letter5']==word[4]])\n",
    "\n",
    "p_0e=letter1['e']\n",
    "p_ee=len(n_gram.loc[n_gram['letter1']==word[0]].loc[n_gram['letter2']==word[1]])/e1\n",
    "p_er=len(n_gram.loc[n_gram['letter2']==word[1]].loc[n_gram['letter3']==word[2]])/e2\n",
    "p_ri=len(n_gram.loc[n_gram['letter3']==word[2]].loc[n_gram['letter4']==word[3]])/e3\n",
    "p_ie=len(n_gram.loc[n_gram['letter4']==word[3]].loc[n_gram['letter5']==word[4]])/e4\n",
    "\n",
    "# 计算概率\n",
    "np.log(p_0e)+np.log(p_ee)+np.log(p_er)+np.log(p_ri)+np.log(p_ie)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### n=3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "f1=len(n_gram.loc[n_gram['letter1']==word[0]].loc[n_gram['letter2']==word[1]])\n",
    "f2=len(n_gram.loc[n_gram['letter2']==word[1]].loc[n_gram['letter3']==word[2]])\n",
    "f3=len(n_gram.loc[n_gram['letter3']==word[2]].loc[n_gram['letter4']==word[3]])\n",
    "f4=len(n_gram.loc[n_gram['letter4']==word[3]].loc[n_gram['letter5']==word[4]])\n",
    "\n",
    "\n",
    "p_e012=len(n_gram.loc[n_gram['letter1']==word[0]].loc[n_gram['letter2']==word[1]])/len(word_freq)\n",
    "p_e123=len(n_gram.loc[n_gram['letter1']==word[0]].loc[n_gram['letter2']==word[1]].loc[n_gram['letter3']==word[2]])/f1\n",
    "p_e234=len(n_gram.loc[n_gram['letter2']==word[1]].loc[n_gram['letter3']==word[2]].loc[n_gram['letter4']==word[3]])/f2\n",
    "p_e345=len(n_gram.loc[n_gram['letter3']==word[2]].loc[n_gram['letter4']==word[3]].loc[n_gram['letter5']==word[4]])/f3\n",
    "\n",
    "# 计算概率\n",
    "\n",
    "np.log(p_e012)+np.log(p_e123)+np.log(p_e234)+np.log(p_e345)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### n=4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "g1=len(n_gram.loc[n_gram['letter1']==word[0]].loc[n_gram['letter2']==word[1]].loc[n_gram['letter3']==word[2]])\n",
    "g2=len(n_gram.loc[n_gram['letter2']==word[1]].loc[n_gram['letter3']==word[2]].loc[n_gram['letter4']==word[3]])\n",
    "\n",
    "p_e0123=len(n_gram.loc[n_gram['letter1']==word[0]].loc[n_gram['letter2']==word[1]].loc[n_gram['letter3']==word[2]])/len(word_freq)\n",
    "p_e1234=len(n_gram.loc[n_gram['letter1']==word[0]].loc[n_gram['letter2']==word[1]].loc[n_gram['letter3']==word[2]].loc[n_gram['letter4']==word[3]])/g1\n",
    "p_e2345=len(n_gram.loc[n_gram['letter2']==word[1]].loc[n_gram['letter3']==word[2]].loc[n_gram['letter4']==word[3]].loc[n_gram['letter5']==word[4]])/g2\n",
    "\n",
    "np.log(p_e0123)+np.log(p_e1234)+np.log(p_e2345)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "n_gram.to_excel(\"n_gram.xlsx\") #输出Excel文件"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 预测报告数量"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 困难模式和单词难度的关系"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in range(len(wordle)):\n",
    "    wordle['freq'].loc[i]=float(n_gram['freq'].loc[n_gram['word']==wordle['word'].loc[i]])"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 对每周数据做归一化"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in range(50):\n",
    "    week=wordle['Number of  reported results'].loc[6+7*i]+wordle['Number of  reported results'].loc[7+7*i]+wordle['Number of  reported results'].loc[8+7*i]+wordle['Number of  reported results'].loc[9+7*i]+wordle['Number of  reported results'].loc[10+7*i]+wordle['Number of  reported results'].loc[11+7*i]+wordle['Number of  reported results'].loc[12+7*i]\n",
    "\n",
    "    wordle['week avg'].loc[i]=week/7\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "wordle['reports'].loc[6+7*i]=wordle['Number of  reported results'].loc[6+7*i]/week\n",
    "wordle['reports'].loc[7+7*i]=wordle['Number of  reported results'].loc[7+7*i]/week\n",
    "wordle['reports'].loc[8+7*i]=wordle['Number of  reported results'].loc[8+7*i]/week\n",
    "wordle['reports'].loc[9+7*i]=wordle['Number of  reported results'].loc[9+7*i]/week\n",
    "wordle['reports'].loc[10+7*i]=wordle['Number of  reported results'].loc[10+7*i]/week\n",
    "wordle['reports'].loc[11+7*i]=wordle['Number of  reported results'].loc[11+7*i]/week\n",
    "wordle['reports'].loc[12+7*i]=wordle['Number of  reported results'].loc[12+7*i]/week\n",
    "\n",
    "0.145125 0.146178 0.144752 0.143599 0.145098 0.138321 0.137607"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 时间序列预测"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 报告随时间的趋势"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.figure(dpi=300)\n",
    "plt.plot(wordle['Date'],wordle['Number of  reported results'],label='Number of reported results',color='#1E90FF')\n",
    "plt.title('Reports trends over time')\n",
    "plt.ylabel('Reports')\n",
    "plt.xlabel('Data')\n",
    "plt.legend()\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 求导"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 构造示例时间序列数据\n",
    "arima = pd.read_excel('arima.xlsx')\n",
    "x = arima['Week']\n",
    "y = arima['Number of result']\n",
    "\n",
    "# 计算一阶和二阶导数\n",
    "dy = np.diff(y)\n",
    "ddy = np.diff(dy)\n",
    "\n",
    "# 绘制一阶和二阶导数的变化曲线\n",
    "plt.figure(dpi=300)\n",
    "plt.plot(dy, color='#4169E1', label='First derivative')\n",
    "plt.plot(ddy, color='#FF1493', label='Second derivative')\n",
    "plt.plot(ddy>0,label='value=0')\n",
    "plt.legend(loc='best')\n",
    "plt.xlabel('Weeks')\n",
    "plt.ylabel('Reports')\n",
    "plt.show()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 绘制置信区间95%"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "# 定义函数\n",
    "def f(x, a, b):\n",
    "    return a * x ** b\n",
    "\n",
    "# 设置参数范围和步长\n",
    "a_range = np.linspace(1438000, 2196000, 100)\n",
    "b_range = np.linspace(-1.191, -1.067, 100)\n",
    "x_range = np.linspace(20, 59, 100)\n",
    "\n",
    "# 计算函数值\n",
    "y_range = np.zeros((len(a_range), len(b_range), len(x_range)))\n",
    "for i, a in enumerate(a_range):\n",
    "    for j, b in enumerate(b_range):\n",
    "        y_range[i, j, :] = f(x_range, a, b)\n",
    "\n",
    "# 计算均值和标准差\n",
    "y_mean = np.mean(y_range, axis=(0, 1))\n",
    "y_std = np.std(y_range, axis=(0, 1))\n",
    "\n",
    "# 计算置信区间\n",
    "z = 1.96  # 95% 置信区间对应的 z 值\n",
    "y_upper = y_mean + z * y_std\n",
    "y_lower = y_mean - z * y_std\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# ARIMA(0,2,0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "from statsmodels.tsa.arima.model import ARIMA\n",
    "\n",
    "# 读取时间序列数据\n",
    "data = pd.read_excel('arima.xlsx', index_col=0, parse_dates=True)\n",
    "\n",
    "# 构建ARIMA(0,2,0)模型\n",
    "model = ARIMA(data, order=(0, 2, 0))\n",
    "\n",
    "# 拟合模型并进行预测\n",
    "results = model.fit()\n",
    "pred = results.predict(start=20, end=len(data)+9)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 灰色预测"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def predict(x0):\n",
    "    n = len(x0)\n",
    "    x1 = np.cumsum(x0)\n",
    "    z = np.zeros(n-1)\n",
    "    for i in range(n-1):\n",
    "        z[i] = 0.5*(x1[i]+x1[i+1])\n",
    "    B = [-z, [1]*(n-1)]\n",
    "    Y = x0[1:]\n",
    "    u = np.dot(np.linalg.inv(np.dot(B, np.transpose(B))),np.dot(B, Y))\n",
    "    x1_solve = np.zeros(n)\n",
    "    x0_solve = np.zeros(n)\n",
    "    x1_solve[0] = x0_solve[0] = x0[0]\n",
    "    for i in range(1, n):\n",
    "        x1_solve[i] = (x0[0]-u[1]/u[0])*np.exp(-u[0]*i)+u[1]/u[0]\n",
    "    for i in range(1, n):\n",
    "        x0_solve[i] = x1_solve[i] - x1_solve[i-1]\n",
    "    return x0_solve, x1_solve, u\n",
    "\n",
    "data_gm=pd.read_excel('gm.xlsx')\n",
    "x0 = np.array(data_gm.iloc[:,1])\n",
    "x0_solve, x1_solve, u = predict(x0)\n",
    "#plt.plot(x0_solve)\n",
    " \n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 绘制预测图像"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 绘制函数图像和置信区间\n",
    "\n",
    "plt.figure(dpi=300)\n",
    "plt.scatter(time_series['Week'],time_series['Number of result'],label='True result',s=8)\n",
    "plt.plot(x_range, y_mean, 'r-', label='Mean of Exponential fitting',linewidth =1.0)\n",
    "plt.fill_between(x_range, y_upper, y_lower, color='gray', alpha=0.5, label='95% Confidence Interval')\n",
    "\n",
    "plt.plot(pred, label='Pred-ARIMA',color='#F4A460',linewidth =1.0)\n",
    "\n",
    "plt.plot(range(20,51),x0_solve,label='pred-GM(1,1)',color='#8B0000',linewidth =1.0)\n",
    "\n",
    "\n",
    "plt.legend(loc='best')\n",
    "plt.xlabel('Week')\n",
    "plt.ylabel('Reports Number')\n",
    "plt.title('Predictions of the number of reports by three models')\n",
    "\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 单词属性"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#元音字母\n",
    "for i in range(len(df_w)):\n",
    "    a=0\n",
    "    if df_w['l1'].loc[i] in ['a','e','u','i','o']:\n",
    "        a=a+1\n",
    "    if df_w['l2'].loc[i] in ['a','e','u','i','o']:\n",
    "        a=a+1\n",
    "    if df_w['l3'].loc[i] in ['a','e','u','i','o']:\n",
    "        a=a+1\n",
    "    if df_w['l4'].loc[i] in ['a','e','u','i','o']:\n",
    "        a=a+1\n",
    "    if df_w['l5'].loc[i] in ['a','e','u','i','o']:\n",
    "        a=a+1\n",
    "    df_w['yuan'].loc[i]=a"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#重复单词\n",
    "for i in range(len(df_w)):\n",
    "    a=0\n",
    "    repeat=[df_w['l1'].loc[i],df_w['l2'].loc[i],df_w['l3'].loc[i],df_w['l4'].loc[i],df_w['l5'].loc[i]]\n",
    "    for j in range(5):\n",
    "        if repeat[j]!=repeat[j-1]:\n",
    "            a=a+df_w['word'].loc[i].count(repeat[j])-1\n",
    "        elif repeat[j]==repeat[j-1]:\n",
    "            a=a+1\n",
    "    df_w['repeat'].loc[i]=a\n",
    "    "
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 绘图"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_w[['freq','cluster','yuan','rata']]= (df_w[['freq','cluster','yuan','rata']]-df_w[['freq','cluster','yuan','rata']].min())/(df_w[['freq','cluster','yuan','rata']].max()-df_w[['freq','cluster','yuan','rata']].min())\n",
    "plt.figure(dpi=300,figsize=(20,4))\n",
    "plt.plot(df_w['rata'],label='ratio')\n",
    "plt.plot(df_w['cluster'],label='difficulty')\n",
    "plt.plot(df_w['freq'],label='frequency')\n",
    "plt.plot(df_w['yuan'],label='Number of vowels')\n",
    "plt.title('The relationship between word attributes and the proportion of Hardmode committed')\n",
    "plt.xlabel('Date')\n",
    "plt.legend()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 预测通关比例"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def pred_pro(difficulty,try_number):\n",
    "    str_try=str(try_number)+' try'\n",
    "    trys=wordle[str_try].loc[wordle['cluster']==difficulty].to_numpy()\n",
    "    mini=[]\n",
    "    flag=0\n",
    "    for i in range(500):\n",
    "        temp=0\n",
    "        for j in range(len(trys)):\n",
    "            temp=temp+(i*0.1-trys[j])**2\n",
    "        mini.append(temp)\n",
    "    return round(mini.index(min(mini))*0.1,1)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "for i in range(1,11):\n",
    "    a=[]\n",
    "    for j in range(1,8):\n",
    "        a.append(pred_pro(i,j))\n",
    "    wordle_try['t'+str(i)]=np.array(a)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def pred_pro(difficulty,try_number):\n",
    "    str_try=str(try_number)+' try'\n",
    "    trys=wordle[str_try].loc[wordle['cluster']==difficulty].to_numpy()\n",
    "    mini=[]\n",
    "    flag=0\n",
    "    for i in range(500):\n",
    "        temp=0\n",
    "        for j in range(len(trys)):\n",
    "            temp=temp+(i*0.1-trys[j])**2\n",
    "        mini.append(temp)\n",
    "    return min(mini)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pred_pro(2,4)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 绘图"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from scipy.interpolate import make_interp_spline\n",
    "\n",
    "plt.figure(dpi=300,figsize=(24,14))\n",
    "for i in range(1,10):\n",
    "    plt.subplot(3,3,i)\n",
    "    data = wordle_try['d'+str(i)]\n",
    "    plt.bar(['1','2','3','4','5','6','7'],data)\n",
    "\n",
    "    # 计算并绘制趋势曲线\n",
    "    x = np.array(data.index)\n",
    "    y = np.array(data)\n",
    "    spl = make_interp_spline(x, y)\n",
    "    x_new = np.linspace(x.min(), x.max(), 300)\n",
    "    y_new = spl(x_new)\n",
    "    plt.plot(x_new, y_new, color='#FF8C00')\n",
    "    plt.xlabel('Tries')\n",
    "    plt.ylabel('Distribution')\n",
    "    plt.title('WDI='+str(i),x=0.1,y=0.8)\n",
    "\n",
    "# 显示图像\n",
    "plt.show()\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 其他绘图"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 偏态分布绘图"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "df = pd.read_excel('峰度(2).xlsx')\n",
    "\n",
    "plt.figure(dpi=300,figsize = (12,7))\n",
    "#自定义x轴\n",
    "x_ticks=df.columns\n",
    "#自定义y轴 \n",
    "\n",
    "y_ticks = df.columns\n",
    "ax = sns.heatmap(df, annot=True, cmap=\"BuPu\",xticklabels=x_ticks, yticklabels=y_ticks)\n",
    "# 设置y轴字体大小\n",
    "ax.yaxis.set_tick_params(labelsize=10)\n",
    "plt.title(\"\")\n",
    "\n",
    "# 显示图片\n",
    "plt.show()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 困难模式百分比和时间折线图"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df=pd.read_excel('折线图.xlsx')\n",
    "plt.figure(dpi=300,figsize=(8,3))\n",
    "plt.scatter(df['Contest number'],df['hard mode'],s=3,color='#20B2AA')\n",
    "plt.plot(df['Contest number'],df['hard mode'],color='#20B2AA',label='Hardmode percentage')\n",
    "\n",
    "plt.title('The Hardmode percentage changes with past questions')\n",
    "plt.legend()\n",
    "\n",
    "plt.show()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 发音和困难指数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "x=range(1,11)\n",
    "y=[0.0740559,\n",
    "0.07568426,\n",
    "0.07534914,\n",
    "0.07125784,\n",
    "0.07193215,\n",
    "0.07998976,\n",
    "0.0868022,\n",
    "0.0894891,\n",
    "0.08687639]\n",
    "plt.figure(dpi=300,figsize=(8,3))\n",
    "plt.plot(y,label='Hard Mode Proportion',color='#7B68EE')\n",
    "plt.ylim(0.02,0.14)\n",
    "plt.legend()\n",
    "\n",
    "plt.title('Hard Mode Proportion under Different Word difficulty')\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.8"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "0a772f36d6f012e6a612662d86668ecc0866106f9098f9c3a4257ac075db434c"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}