# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

# # 讀取字典

import pandas as pd
import numpy as np
import os

# +
filepath = '/Volumes/backup_128G/z_repository/Yumin_data/玉敏_俄羅斯課本的研究'

chinese8k_dic_file = '華語八千詞(內含注音字型檔)/Chinese_8000W_20190515_v1.xlsx'
hsk_dic_file = 'HSK-2012_5000W_20190517_v1.xlsx'
book_file = '實用漢語教科書2010_生詞表.xlsx'

read_chinese8k_dic = '{0}/{1}'.format(filepath, chinese8k_dic_file)
read_hsk_dic = '{0}/{1}'.format(filepath, hsk_dic_file)
read_book_file = '{0}/{1}'.format(filepath, book_file)
# -

# # Chinese_8000W

# +
dicC8kDf = pd.DataFrame()

with pd.ExcelFile(read_chinese8k_dic) as reader:
    # read sheet by sheet
    for sheet in reader.sheet_names:
#         print(sheet)
        sheetDf = pd.read_excel(reader, sheet, header=None)
        sheetDf = sheetDf.fillna(0)

        dicC8kDf = dicC8kDf.append(sheetDf, ignore_index=True)

# change to lowercase
len(dicC8kDf.index)
# -

dicC8kDf.head()

# # HSK-2012_5000W

# +
with pd.ExcelFile(read_hsk_dic) as reader:
    dicH5kDf = pd.read_excel(reader, 'HSK5000')

len(dicH5kDf.index)
# -

dicH5kDf.head()

# # 讀取待分析檔

bookDf = pd.read_excel(read_book_file)

bookDf.head()

bookWordsDf = bookDf['生詞']

bookWordsDf.head()

print(len(bookWordsDf[bookWordsDf == '色粉']))

# # Start analysize

# Chinese 8k 前三級
lowerC8kDf = dicC8kDf[dicC8kDf[1] <= 3]
print(len(lowerC8kDf), lowerC8kDf.head())

# HSK 4k 前三級
lowerH5kDf = dicH5kDf[dicH5kDf['Level'] <= 3]
print(len(lowerH5kDf), lowerH5kDf.head())

# +
c8kNotIn = []
c8kIn = []
for idx in range(0, len(lowerC8kDf)):
    row = lowerC8kDf.loc[idx]
    
    word = row[0]
    level = row[1]
    
    if len(bookWordsDf[bookWordsDf == word]) == 0:
#         print(word)
        if word not in c8kNotIn:
            c8kNotIn.append([word, level])
#         else:
#             print("DUP!!", word)
    else:
#         print(word)
        if word not in c8kIn:
            c8kIn.append([word, level])

print(len(lowerC8kDf), len(c8kNotIn), len(c8kIn))    

# +
h5kNotIn = []
h5kIn = []
for idx in range(0, len(lowerH5kDf)):
    row = lowerH5kDf.loc[idx]
    
    word = row['WordTw']
    level = row['Level']
    
    if len(bookWordsDf[bookWordsDf == word]) == 0:
#         print(word)
        if word not in h5kNotIn:
            h5kNotIn.append([word, level])
#         else:
#             print("DUP!!", word)
    else:
#         print(word)
        if word not in h5kIn:
            h5kIn.append([word, level])

print(len(lowerH5kDf), len(h5kNotIn), len(h5kIn))   
# -

c8kNotInDf = pd.DataFrame(c8kNotIn)
# c8kNotInDf = c8kNotInDf.sort_values(by=[1])
c8kNotInDf.head()

c8kInDf = pd.DataFrame(c8kIn)
# c8kInDf = c8kInDf.sort_values(by=[1])
c8kInDf.head()

h5kNotInDf = pd.DataFrame(h5kNotIn)
h5kNotInDf.head()

h5kInDf = pd.DataFrame(h5kIn)
h5kInDf.head()

headers = ['word', 'level']
write_to_file = '{0}/{1}'.format(filepath, "processed/C8k_H5K_notIn_20190518_v2.xlsx")
with pd.ExcelWriter(write_to_file) as writer:
    c8kNotInDf.to_excel(writer, 'c8kNotIn', index=False, header=headers)
    c8kInDf.to_excel(writer, 'c8kIn', index=False, header=headers)
    h5kNotInDf.to_excel(writer, 'h5kNotIn', index=False, header=headers)
    h5kInDf.to_excel(writer, 'h5kIn', index=False, header=headers)

    writer.save()



StarcoderdataJupyterScriptsDedupFiltered