# 레시피 데이터베이스 불러오기
from muyong.nlp import mdx_to_df
df    = mdx_to_df('backup/recipe.db')
menus = df.entry.values.tolist()
len(menus), " ".join(menus)[:200]

food_recipes = df.paraphrase.values.tolist()
result = []
import re
for _ in food_recipes:
    result += re.findall("[가-힣]+", _)
len(result), len(list(set(result)))

from konlpy.tag import Hannanum, Okt, Mecab, Komoran
food_text = " ".join(list(set(result)))
food_tokens = Mecab().nouns(food_text)
len(food_tokens)

from nltk import Text
food_tokens_text = Text(food_tokens)
food_nouns = [_[0]  for _ in food_tokens_text.vocab().most_common()]
len(food_nouns)

query     = "한천"  # 원문 내용의 확인
q_data    = nerDict[nerDict.Text == query].Data.values.tolist()
q_data

# 명사 추출사전 데이터 호출
with open("data/nouns_tokens.txt", "r") as f:
    valid_token = f.read()
valid_token = valid_token.split(",")
len(valid_token)

# 분석할 메뉴데이터 호출
import pandas as pd  # 분석을 의뢰할 메뉴 데이터 [list]
menus_muyong  = pd.read_csv('data/menu_muyong.csv', encoding='ms949')
menus_muyong  = menus_muyong.dropna(subset=['메인1', '메인2'])
menus_muyong  = menus_muyong.fillna('')  # NaN 값을 지운다
tokens = []                # DataFrame 에서 Token List 추출
for _ in menus_muyong.columns[4:]:
    tokens += menus_muyong[_].values.tolist()

import re
from nltk import Text
menus_muyong = re.findall(r"[가-힣]+", " ".join(tokens))
menus_muyong = list(set(menus_muyong))
len(menus_muyong), " ".join(menus_muyong[:5])

# 메뉴 명사추출기
menu_token  = menus_muyong[1828]
print("원본내용:", menu_token)
from muyong.nlp import food_nouns
food_nouns(menu_token, valid_token)

import pandas as pd
menus_man = pd.read_csv("data/menu_1000recipe.csv", sep="|")
menus_man = menus_man.fillna('')
menus_man = menus_man.Menu.values.tolist()
menus_man[:3]

# 메뉴 명사추출기
menu_token  = menus_man[4528]
print("원본내용:", menu_token)
from muyong.nlp import food_nouns
food_nouns(menu_token, valid_token)

%%time
# # 10,000 개 레시피 분석
# result = ["_".join(sorted(food_nouns(menu, valid_token)))  
#           for menu in menus_man  
#           if "_".join(sorted(food_nouns(menu, valid_token)))]

# result = list(sorted(set(result)))
# print(len(menus_man), len(result))
# with open("backup/menus_unique_tokens_10000.txt", 'w') as f:
#     f.write(",".join(result))
# print("file saved")

# Muyong 레시피 분석
result = ["_".join(sorted(food_nouns(menu, valid_token)))  
          for menu in menus_muyong  
          if "_".join(sorted(food_nouns(menu, valid_token)))]

result = list(sorted(set(result)))
print(len(menus_muyong), len(result))
with open("backup/menus_unique_tokens_muyong.txt", 'w') as f:
    f.write(",".join(result))
print("file saved")

%%time
import os, sqlite3, json, time
con = sqlite3.connect("backup/food_api_muyong.db")

import pandas as pd
df         = pd.read_sql("select * from 'foods'", con, index_col=None).drop('index', axis=1)
con.close()
food_links = df.data.values.tolist()
df.data    = [json.loads(_) for _ in food_links] # json 디코딩
index_err  = [no  for no,_ in enumerate(df.data.values.tolist())  if len(_) == 0]
print(df.iloc[index_err, :])

%%time
import os, sqlite3, json, time
con    = sqlite3.connect("backup/food_api_10000_01.db")
tables = [_[0] for _ in con.execute("SELECT name FROM sqlite_master WHERE type='table';")]
print(tables)  # 테이블 이름의 확인

import pandas as pd
df  = pd.read_sql("SELECT * FROM '{}'".format(tables[0]), con, index_col=None).drop('index', axis=1)
con.close()

# 내부 Json String 을 Decoding
food_links = df.data.values.tolist()
df.data    = [json.loads(_) for _ in food_links] # json 디코딩
index_err  = [no  for no,_ in enumerate(df.data.values.tolist())  if len(_) == 0]
print(df.iloc[index_err, :]) # 자료수집시 오류가 난 Key Word 선별

%%time
from muyong.naver import get_blog_list
from tqdm import tqdm
result = {}
for _ in tqdm(token_temp_list):
    temp = []
    query_token  = _+"_만들기"
    for i in range(1, 500, 100):
        temp += get_blog_list(query_token, i)['items']
    time.sleep(.3)
    result[_] = temp

for no, i in enumerate(index_err):
    df.iloc[i, 1] = result[token_temp_list[no]]
    
import pickle
with open('data/naver_key_list.pk', 'wb') as handle:
    pickle.dump(df, handle, protocol=pickle.HIGHEST_PROTOCOL)
print("save is done")

# with open("menus_unique_token.txt", "r") as f:
with open("data/nouns_tokens.txt", "r") as f:
    word_tokens = f.read().split(",")

# get_blog_list("가슴살_닭_매콤_볶음_우동"+"_만들기", 101)["items"][:2]
# len(word_tokens), word_tokens[1000]
list(result.keys())

import pandas as pd
len(result[list(result.keys())[0]])

%%time
import pandas as pd
from tqdm import tqdm
from muyong.naver import get_blog_list
import time, json, os, sqlite3
file_db = "foods.db"

token_temp_list = ["가쓰오_어묵_유부","가쓰오_유부","가쓰오_유부_팽이","감자_미트볼_알감자_조림",\
              "돈육_불고기_간장","떡_부추_해물","무침_부추_콩나물","부추_전_해물","오징어_탕수"]

for _ in tqdm(token_temp_list):
    # web link 목록 수집하기
    result, temp = [], []
    query_token  = _+"_만들기"
    for i in range(1, 500, 100):
        temp += get_blog_list(query_token, i)['items']
    time.sleep(.3)
    result.append([_, temp])
    
    # sqlite3 저장하기 (단어별 결과를 Json으로 저장 (list는 저장시 오류))
    df = pd.DataFrame(result)
    df.columns = ['title', 'data']
    df.data    = [ json.dumps(_)  for _ in df.data ]
    con = sqlite3.connect(file_db)
    df.to_sql('foods', con, if_exists='append')
    con.commit()
    time.sleep(.1)
    
df

import pickle
with open('backup/temp/naver_api_org.pk', 'rb') as handle:
    df = pickle.load(handle)

# data 에서 naver 링크만 찾아서 정리하기
df.head(3)

df.head(20)

err_index = [no for no, _ in enumerate(df.data)   if len(_.split('|')) < 10]
err_index

df_sum = df.drop(err_index, axis=0).reset_index(drop=True)
df_sum.to_csv("data/naver_links_muyong.csv", index=None)
df.shape, df_sum.shape

len(df.data[0].split('|'))

import pandas as pd
# df = pd.read_csv("data/naverPost_muyong_fixed.csv")
# df = df.iloc[:,1:]
df = pd.read_csv("data/naverPost_muyong.csv")
df.shape, len(df.Posts[1].split("|")), df.head(3)

Mecab().pos(text)

Okt().pos(text)