# %%time
# file = 'csv/Food-data.xlsx'
# import xlrd
# xls        = xlrd.open_workbook(file)
# sheet_list = xls.sheet_names()[4:-8]

# import pandas as pd
# result, no = {}, 0
# for sheet in sheet_list:
#     df = pd.read_excel(file, sheet_name=sheet)
#     df = df.fillna(method='ffill')
#     df = df.loc[:,['메뉴명', '단품명', '1인량']]
#     no += 1
#     if no % 10 == 0: 
#         print('{:4,} th sheet working'.format(no))
#     # 컬럼의 오타를 변경
#     df.메뉴명 = list(map(lambda x : x.replace('\n','') , list(df.메뉴명)))  
#     temps   = {}
#     for menu in list(set(df.메뉴명)):
#         df_temp = df[df.메뉴명 == menu]
#         foods   = {df_temp.iloc[idx,1]: df_temp.iloc[idx,2]  for idx in range(len(df_temp.단품명))}
#         temps[menu] = foods
#     for menu in temps.keys():
#         # 기존에 없던 메뉴가 확인시
#         if menu not in result.keys():
#             result[menu] = temps[menu]
#         # 메뉴가 있으면 레시피만 추가
#         else:
#             # 재료가 없으면 재료이름과 해당 내용을 추라
#             for recipie in temps[menu].keys():
#                 if recipie not in result[menu].keys():
#                     result[menu][recipie] = temps[menu][recipie]
# import json
# data = json.dumps(result, ensure_ascii=False)
# with open('food_recipie.json', 'w', encoding='UTF-8-sig') as file:
#      file.write(data)

file = 'data/2018_muyong_menu.xlsx'

import xlrd
xls        = xlrd.open_workbook(file)
sheet_list = xls.sheet_names()
len(sheet_list), sheet_list[::10]

# from openpyxl import Workbook, load_workbook
# wb        = load_workbook(file_name)
# st_names  = [ws.title  for ws in wb.worksheets]`

import xlrd, re
import pandas as pd

# xls 파일에서 sheet list 추출
def menu_sheet_names(file):
    xls        = xlrd.open_workbook(file)
    return file, xls.sheet_names()

# 날짜 인덱스로 검색여부 확인
def menu_valid_check(file, sheet_list, sheet_num):
    df = pd.read_excel(file, sheet_name=sheet_list[sheet_num],header=None)
    for _ in range(df.shape[0]): 
        tokens = re.findall(r"(월|화|수|목|금)", 
                        " ".join(df.iloc[_,:].dropna().tolist()))
        if len(tokens) >= 4:
            #print("주간 데이터 작업시작")
            return df, _ #break
    print(sheet_num, "작업할 내용이 없습니다")
    return df, None

# 유효값 데이터 추출하기
def menu_valid_data(df, col_start):

    # valid sector 추출함수
    def menu_filter(df, rows, cols, col_name):
        menu = df.iloc[rows[0]:rows[1], cols]
        menu.columns = col_name
        return menu.reset_index(drop=True)

    # col_start : 요일값 인덱스 주소
    # col_valid : 요일값 포함된 컬럼들
    # col_data : 요얄값 컬럼 중 12개 이상 값이 있는 컬럼 만 추출
    col_valid = list(df.iloc[col_start,:].dropna().index)
    col_data = [ _ for _ in col_valid  if sum(df.iloc[:,_].notnull()) > 12] 
    col_name = df.iloc[col_start, col_data].tolist()
    col_name = [_.strip().replace(" ","") for _ in col_name]

    # 요일별 12개 이상 유효값 있는 필드 인덱스 추출
    row_valid = df.iloc[:, col_data[0]].isna()
    row_valid = list(row_valid[row_valid].index)[:3]
    row_valid[0] = col_start
    limit_row = [ [row_valid[0]+1, row_valid[1]], [1+row_valid[1], row_valid[2]-1] ]
    result = []
    for no,_ in enumerate(limit_row):
        midname = {0:"중식", 1:"석식"}
        menu_temp = menu_filter(df, _, col_data, col_name)
        result += [(_,midname[no],";".join(menu_temp[_]))    for _ in list(menu_temp.columns)]
    return result