.ipynb_checkpoints/tableQA_single_table-checkpoint.py

#!/usr/bin/env python
# coding: utf-8
#### env base_cp

#main_path = "/Users/svjack/temp/gradio_prj/tableQA-Chinese-main"
#main_path = "/User/tableQA-Chinese-main"
#main_path = "/temp/tableQA-Chinese-main"
main_path = "."

import pandas as pd
import numpy as np
import os
import ast
import re
import json
from icecream import ic
from copy import deepcopy
from itertools import product, combinations


import pandas as pd
import os
import sys
from pyarrow.filesystem import LocalFileSystem
from functools import reduce
import nltk
from nltk import pos_tag, word_tokenize
from collections import namedtuple
from ast import literal_eval

from torch.nn import functional
import numpy as np
import torch
from torch import nn
from torch.nn import init
from torch.nn.utils import rnn as rnn_utils
import math

from icecream import ic
import seaborn as sns

import matplotlib.pyplot as plt

import shutil

#from keybert import KeyBERT
#from bertopic import BERTopic


import sqlite3
import sqlite_utils
from icecream import ic
import jieba
import pandas as pd
import urllib.request
from urllib.parse import quote
from time import sleep
import json
import os
from collections import defaultdict
import re
from functools import reduce, partial

#### used in this condition extract in training.
op_sql_dict = {0:">", 1:"<", 2:"==", 3:"!="}
#### used by clf for intension inference
agg_sql_dict = {0:"", 1:"AVG", 2:"MAX", 3:"MIN", 4:"COUNT", 5:"SUM"}
#### final to combine them (one for 0, and multi for 1 2)
conn_sql_dict = {0:"", 1:"and", 2:"or"}

#### kws and time pattern defination
and_kws = ("且", "而且", "并且", "和", "当中", "同时")
or_kws = ("或", "或者",)
conn_kws = and_kws + or_kws

pattern_list = [u"[年月\.\-\d]+", u"[年月\d]+", u"[年个月\d]+", u"[年月日\d]+"]

time_kws = ("什么时候", "时间", "时候")

sum_count_high_kws = ('多少个', '有几个', '总共') + ('总和','一共',) + ("总数",)
mean_kws = ('平均数', '均值', '平均值', '平均')
max_kws = ('最大', '最多', '最大值', '最高')
min_kws = ('最少', '最小值', '最小', '最低')
sum_count_low_kws = ('个', '总共') + ('总和','加','总','一共','和',) + ("哪些", "查", "数量", "数") + ("几",) + ('多少', "多大")  + ("总数",)
max_special_kws = ("以上", "大于")
min_special_kws = ("以下", "小于")

qst_kws = ("多少", "什么", "多大", "哪些", "怎么", "情况", "那些", "哪个")

only_kws_columns = {"城市": "=="}

##### jointbert predict model init start
#jointbert_path = "../../featurize/JointBERT"
#jointbert_path = "/Users/svjack/temp/gradio_prj/tableQA-Chinese-main/JointBERT-master"
jointbert_path = os.path.join(main_path, "JointBERT-master")
sys.path.append(jointbert_path)


from model.modeling_jointbert import JointBERT
from model.modeling_jointbert import *
from trainer import *
from main import *
from data_loader import *


pred_parser = argparse.ArgumentParser()

pred_parser.add_argument("--input_file", default="conds_pred/seq.in", type=str, help="Input file for prediction")
pred_parser.add_argument("--output_file", default="conds_pred/sample_pred_out.txt", type=str, help="Output file for prediction")
#pred_parser.add_argument("--model_dir", default="bert", type=str, help="Path to save, load model")
pred_parser.add_argument("--model_dir", default= os.path.join(main_path ,"data/bert"), type=str, help="Path to save, load model")
#pred_parser.add_argument("--model_dir", default= os.path.join(main_path ,"JBert_Zh_Condition_Extractor"), type=str, help="Path to save, load model")


pred_parser.add_argument("--batch_size", default=32, type=int, help="Batch size for prediction")
pred_parser.add_argument("--no_cuda", action="store_true", help="Avoid using CUDA when available")


pred_parser_config_dict = dict(map(lambda item:(item.option_strings[0].replace("--", ""), item.default) ,pred_parser.__dict__["_actions"]))
pred_parser_config_dict = dict(filter(lambda t2: t2[0] != "-h", pred_parser_config_dict.items()))

pred_parser_namedtuple = namedtuple("pred_parser_config", pred_parser_config_dict.keys())
for k, v in pred_parser_config_dict.items():
    if type(v) == type(""):
        exec("pred_parser_namedtuple.{}='{}'".format(k, v))
    else:
        exec("pred_parser_namedtuple.{}={}".format(k, v))


from predict import *


pred_config = pred_parser_namedtuple
args = get_args(pred_config)
device = get_device(pred_config)

args_parser_namedtuple = namedtuple("args_config", args.keys())
for k, v in args.items():
    if type(v) == type(""):
        exec("args_parser_namedtuple.{}='{}'".format(k, v))
    else:
        exec("args_parser_namedtuple.{}={}".format(k, v))


args = args_parser_namedtuple

#args.data_dir = "/Users/svjack/temp/gradio_prj/tableQA-Chinese-main/data"
args.data_dir = os.path.join(main_path, "data")

'''
pred_model = MODEL_CLASSES["bert"][1].from_pretrained(args.model_dir,
                                                                  args=args,
                                                            intent_label_lst=get_intent_labels(args),
                                                                  slot_label_lst=get_slot_labels(args))
'''
pred_model = MODEL_CLASSES["bert"][1].from_pretrained(
os.path.join(main_path, "data/bert")
,
                                                                  args=args,
                                                            intent_label_lst=get_intent_labels(args),
                                                                  slot_label_lst=get_slot_labels(args))

pred_model.to(device)
pred_model.eval()

intent_label_lst = get_intent_labels(args)
slot_label_lst = get_slot_labels(args)
pad_token_label_id = args.ignore_index
tokenizer = load_tokenizer(args)
## jointbert predict model init end


###### one sent conds decomp start
def predict_single_sent(question):
    text = " ".join(list(question))
    batch = convert_input_file_to_tensor_dataset([text.split(" ")], pred_config, args, tokenizer, pad_token_label_id).tensors
    batch = tuple(t.to(device) for t in batch)
    inputs = {"input_ids": batch[0],
                      "attention_mask": batch[1],
                      "intent_label_ids": None,
                      "slot_labels_ids": None}
    inputs["token_type_ids"] = batch[2]
    outputs = pred_model(**inputs)
    _, (intent_logits, slot_logits) = outputs[:2]
    intent_preds = intent_logits.detach().cpu().numpy()
    slot_preds = slot_logits.detach().cpu().numpy()
    intent_preds = np.argmax(intent_preds, axis=1)
    slot_preds = np.argmax(slot_preds, axis=2)
    all_slot_label_mask = batch[3].detach().cpu().numpy()
    slot_label_map = {i: label for i, label in enumerate(slot_label_lst)}
    slot_preds_list = [[] for _ in range(slot_preds.shape[0])]
    for i in range(slot_preds.shape[0]):
        for j in range(slot_preds.shape[1]):
            if all_slot_label_mask[i, j] != pad_token_label_id:
                slot_preds_list[i].append(slot_label_map[slot_preds[i][j]])
    pred_l = []
    for words, slot_preds, intent_pred in zip([text.split(" ")], slot_preds_list, intent_preds):
        line = ""
        for word, pred in zip(words, slot_preds):
            if pred == 'O':
                line = line + word + " "
            else:
                line = line + "[{}:{}] ".format(word, pred)
        pred_l.append((line, intent_label_lst[intent_pred]))
    return pred_l[0]


###@@ conn_kws = ["且", "或", "或者", "和"]
'''
and_kws = ("且", "而且", "并且", "和", "当中", "同时")
or_kws = ("或", "或者",)
conn_kws = and_kws + or_kws
'''
#conn_kws = ("且", "或", "或者", "和") + ("而且", "并且", "当中")
#### some algorithm use in it.
def recurrent_extract(question):
    def filter_relation(text):
        #kws = ["且", "或", "或者", "和"]
        kws = conn_kws
        req = text
        for kw in sorted(kws, key= lambda x: len(x))[::-1]:
            req = req.replace(kw, "")
        return req
    def produce_plain_text(text):
        ##### replace tag string from text
        kws = ["[", "]", " ", ":B-HEADER", ":I-HEADER", ":B-VALUE", ":I-VALUE"]
        plain_text = text
        for kw in kws:
            plain_text = plain_text.replace(kw, "")
        return plain_text
    def find_min_commmon_strings(c):
        ##### {"jack", "ja", "ss", "sss", "ps", ""} -> {"ja", "ss", "ps"}
        common_strings = list(filter(lambda x: type(x) == type("") ,
                                     map(lambda t2: t2[0]
                                         if t2[0] in t2[1]
                                             else (t2[1]
                                                if t2[1] in t2[0]
                                                   else (t2[0], t2[1])),combinations(c, 2))))
        req = set([])
        while c:
            ele = c.pop()
            if all(map(lambda cc: cc not in ele, common_strings)):
                req.add(ele)
        req = req.union(set(common_strings))
        return set(filter(lambda x: x, req))
    def extract_scope(scope_text):
        def find_max_in(plain_text ,b_chars, i_chars):
            chars = "".join(b_chars + i_chars)
            while chars and chars not in plain_text:
                chars = chars[:-1]
            return chars
        b_header_chars = re.findall(r"([\w\W]):B-HEADER", scope_text)
        i_header_chars = re.findall(r"([\w\W]):I-HEADER", scope_text)
        b_value_chars = re.findall(r"([\w\W]):B-VALUE", scope_text)
        i_value_chars = re.findall(r"([\w\W]):I-VALUE", scope_text)
        if len(b_header_chars) != 1 or len(b_value_chars) != 1:
            return None
        plain_text = produce_plain_text(scope_text)
        header = find_max_in(plain_text, b_header_chars, i_header_chars)
        value = find_max_in(plain_text, b_value_chars, i_value_chars)
        if (not header) or (not value):
            return None
        return (header, value)
    def find_scope(text):
        start_index = text.find("[")
        end_index = text.rfind("]")
        if start_index == -1 or end_index == -1:
            return text
        scope_text = text[start_index: end_index + 1]
        res_text = filter_relation(text.replace(scope_text, "")).replace(" ", "").strip()
        return (scope_text, res_text)
    def produce_all_attribute_remove(req):
        if not req:
            return None
        string_or_t2 = find_scope(req[-1][0])
        assert type(string_or_t2) in [type(""), type((1,))]
        if type(string_or_t2) == type(""):
            return string_or_t2
        else:
            return string_or_t2[-1]
    def extract_all_attribute(req):
        extract_list = list(map(lambda t2: (t2[0][0], t2[1], t2[0][1]) ,
                                filter(lambda x: x[0] ,
                                   map(lambda tt2_t2: (extract_scope(tt2_t2[0][0]), tt2_t2[1]) ,
                                       filter(lambda t2_t2: "HEADER" in t2_t2[0][0] and "VALUE" in t2_t2[0][0] ,
                                           filter(lambda string_or_t2_t2: type(string_or_t2_t2[0]) == type((1,)),
                                                map(lambda tttt2: (find_scope(tttt2[0]), tttt2[1]),
                                                    req)))))))
        return extract_list
    def extract_attributes_relation_string(plain_text, all_attributes, res):
        if not all_attributes:
            return plain_text.replace(res if res else "", "")
        def replace_by_one_l_r(text ,t3):
            l, _, r = t3
            ##### produce multi l, r to satisfy string contrain problem
            l0, l1 = l, l
            r0, r1 = r, r
            while l0 and l0 not in text:
                l0 = l0[:-1]
            while l1 and l1 not in text:
                l1 = l1[1:]
            while r0 and r0 not in text:
                r0 = r0[:-1]
            while r1 and r1 not in text:
                r1 = r1[1:]
            if not l or not r:
                return text

            conclusion = set([])
            for l_, r_ in product([l0, l1], [r0, r1]):
                l_r_conclusion = re.findall("({}.*?{})".format(l_, r_), text)
                r_l_conclusion = re.findall("({}.*?{})".format(r_, l_), text)
                conclusion = conclusion.union(set(l_r_conclusion + r_l_conclusion))

            ##### because use produce multi must choose the shortest elements from them
            ## to prevent "relation word" also be replaced.
            conclusion_filtered = find_min_commmon_strings(conclusion)

            conclusion = conclusion_filtered
            req_text = text
            for c in conclusion:
                req_text = req_text.replace(c, "")
            return req_text
        req_text_ = plain_text
        for t3 in all_attributes:
            req_text_ = replace_by_one_l_r(req_text_, t3)
        return req_text_.replace(res, "")
    req = []
    t2 = predict_single_sent(question)
    req.append(t2)
    while "[" in t2[0]:
        scope = find_scope(t2[0])
        if type(scope) == type(""):
            break
        else:
            assert type(scope) == type((1,))
            scope_text, res_text = scope
            #ic(req)
            t2 = predict_single_sent(res_text)
            req.append(t2)
    req = list(filter(lambda tt2: "HEADER" in tt2[0] and "VALUE" in tt2[0] , req))
    res = produce_all_attribute_remove(req)
    #ic(req)
    all_attributes = extract_all_attribute(req)
    # plain_text = produce_plain_text(scope_text)

    return all_attributes, res, extract_attributes_relation_string(produce_plain_text(req[0][0] if req else ""), all_attributes, res)


def rec_more_time(decomp):
    assert type(decomp) == type((1,)) and len(decomp) == 3
    assert not decomp[0]
    res, relation_string = decomp[1:]
    new_decomp = recurrent_extract(relation_string)
    #### stop if rec not help by new_decomp[1] != decomp[1]
    if not new_decomp[0] and new_decomp[1] != decomp[1]:
        return rec_more_time(new_decomp)
    return (new_decomp[0], res, new_decomp[1])
### one sent conds decomp end


##### data source start
#train_path = "../TableQA/TableQA/train"
#train_path = "/Users/svjack/temp/gradio_prj/tableQA-Chinese-main/data/TableQA-master/train"
train_path = os.path.join(main_path, "data/TableQA-master/train")
def data_loader(table_json_path = os.path.join(train_path ,"train.tables.json"),
                json_path = os.path.join(train_path ,"train.json"),
                req_table_num = 1):
    assert os.path.exists(table_json_path)
    assert os.path.exists(json_path)
    json_df = pd.read_json(json_path, lines = True)
    all_tables = pd.read_json(table_json_path, lines = True)
    if req_table_num is not None:
        assert type(req_table_num) == type(0) and req_table_num > 0 and req_table_num <= all_tables.shape[0]
    else:
        req_table_num = all_tables.shape[0]
    for i in range(req_table_num):
        #one_table = all_tables.iloc[i]["table"]
        #one_table_df = pd.read_sql("select * from `{}`".format(one_table), train_tables_dump_engine)
        one_table_s = all_tables.iloc[i]
        one_table_df = pd.DataFrame(one_table_s["rows"], columns = one_table_s["header"])
        yield one_table_df, json_df[json_df["table_id"] == one_table_s["id"]]
## data source end


###### string toolkit start
def findMaxSubString(str1, str2):
    """
    """
    maxSub = 0
    maxSubString = ""

    str1_len = len(str1)
    str2_len = len(str2)

    for i in range(str1_len):
        str1_pos = i
        for j in range(str2_len):
            str2_pos = j
            str1_pos = i
            if str1[str1_pos] != str2[str2_pos]:
                continue
            else:
                while (str1_pos < str1_len) and (str2_pos < str2_len):
                    if str1[str1_pos] == str2[str2_pos]:
                        str1_pos = str1_pos + 1
                        str2_pos = str2_pos + 1
                    else:
                        break

                sub_len = str2_pos - j
                if maxSub < sub_len:
                    maxSub = sub_len
                    maxSubString = str2[j:str2_pos]
    return maxSubString


def find_min_commmon_strings(c):
    ##### {"jack", "ja", "ss", "sss", "ps", ""} -> {"ja", "ss", "ps"}
    common_strings = list(filter(lambda x: type(x) == type("") ,
                                map(lambda t2: t2[0]
                                    if t2[0] in t2[1]
                                        else (t2[1]
                                            if t2[1] in t2[0]
                                                else (t2[0], t2[1])),combinations(c, 2))))
    req = set([])
    while c:
        ele = c.pop()
        if all(map(lambda cc: cc not in ele, common_strings)):
            req.add(ele)
    req = req.union(set(common_strings))
    return set(filter(lambda x: x, req))
## string toolkit end



###### datetime column match start
#### only use object dtype to extract
def time_template_extractor(rows_filtered, pattern = u"[年月\.\-\d]+"):
    #re_words = re.compile(u"[年月\.\-\d]+")
    re_words = re.compile(pattern)
    nest_collection = pd.DataFrame(rows_filtered).applymap(lambda x: tuple(sorted(list(re.findall(re_words, x))))).values.tolist()
    def flatten_collection(c):
        if not c:
            return c
        if type(c[0]) == type(""):
            return c
        else:
            c = list(c)
        return flatten_collection(reduce(lambda a, b: a + b, map(list ,c)))
    return flatten_collection(nest_collection)

###@@ pattern_list
#pattern_list = [u"[年月\.\-\d]+", u"[年月\d]+", u"[年个月\d]+", u"[年月日\d]+"]

def justify_column_as_datetime(df, threshold = 0.8, time_template_extractor = lambda x: x):
    object_columns = list(map(lambda tt2: tt2[0] ,filter(lambda t2: t2[1].name == "object" ,dict(df.dtypes).items())))
    time_columns = []
    for col in object_columns:
        input_ = df[[col]].applymap(lambda x: "~" if type(x) != type("") else x)
        output_ = time_template_extractor(input_.values.tolist())
        input_ = input_.iloc[:, 0].values.tolist()
        time_evidence_cnt = sum(map(lambda t2: t2[0].strip() == t2[1].strip() and t2[0] and t2[1] and t2[0] != "~" and t2[1] != "~",zip(input_, output_)))
        if time_evidence_cnt > 0 and time_evidence_cnt / df.shape[0] >= threshold:
            #### use evidence ratio because may have some noise in data
            time_columns.append(col)
    return time_columns

def justify_column_as_datetime_reduce(df, threshold = 0.8, time_template_extractor_list = list(map(lambda p: partial(time_template_extractor, pattern = p), pattern_list))):
    return sorted(reduce(lambda a, b: a.union(b) ,map(lambda func: set(justify_column_as_datetime(df, threshold, func)), time_template_extractor_list)))
## datetime column match end

##### choose question column have a reduce function call below (choose_res_by_kws)
##### this is a tiny first version
###@@ time_kws = ("什么时候", "时间", "时候")
#time_kws = ("什么时候", "时间", "时候")
#####
def choose_question_column(decomp, header, df):
    assert type(decomp) == type((1,)) and type(header) == type([])

    time_columns = justify_column_as_datetime_reduce(df)
    _, res, _ = decomp

    if type(res) != type(""):
        return None

    #ic(res)
    ##### should add time kws to it.
    #time_kws = ("什么时候", "时间", "时候")
    if any(map(lambda t_kw: t_kw in res, time_kws)):
        if len(time_columns) == 1:
            return time_columns[0]
        else:
            '''
            return sorted(map(lambda t_col: (t_col ,len(findMaxSubString(t_col, res)) / len(t_col)), time_columns),
                  key = lambda t2: t2[1])[::-1][0][0]
            '''
            sort_list = sorted(map(lambda t_col: (t_col ,len(findMaxSubString(t_col, res)) / len(t_col)), time_columns),
                  key = lambda t2: t2[1])[::-1]
            if sort_list:
                if sort_list[0]:
                    return sort_list[0][0]
            return None

    c_res_common_dict = dict(filter(lambda t2: t2[1] ,map(lambda c: (c ,findMaxSubString(c, res)), header)))
    common_ratio_c_dict = dict(map(lambda t2: (t2[0], len(t2[1]) / len(t2[0])), c_res_common_dict.items()))
    common_ratio_res_dict = dict(map(lambda t2: (t2[0], len(t2[1]) / len(res)), c_res_common_dict.items()))
    #ic(decomp)
    #ic(common_ratio_c_dict)
    #ic(common_ratio_res_dict)

    if not common_ratio_c_dict or not common_ratio_res_dict:
        return None

    dict_0_max_key = sorted(common_ratio_c_dict.items(), key = lambda t2: t2[1])[::-1][0][0]
    dict_1_max_key = sorted(common_ratio_res_dict.items(), key = lambda t2: t2[1])[::-1][0][0]
    return dict_0_max_key if dict_0_max_key == dict_1_max_key else None


##### agg-classifier start
'''
sum_count_high_kws = ('多少个', '有几个', '总共') + ('总和','一共',) + ("总数",)
mean_kws = ('平均数', '均值', '平均值', '平均')
max_kws = ('最大', '最多', '最大值', '最高')
min_kws = ('最少', '最小值', '最小', '最低')
sum_count_low_kws = ('个', '总共') + ('总和','加','总','一共','和',) + ("哪些", "查", "数量", "数") + ("几",) + ('多少', "多大")  + ("总数",)
max_special_kws = ("以上", "大于")
min_special_kws = ("以下", "小于")
'''

###@@ sum_count_high_kws = ('多少个', '有几个', '总共') + ('总和','一共',) + ("总数",)
###@@ mean_kws = ('平均数', '均值', '平均值', '平均')
###@@ max_kws = ('最大', '最多', '最大值', '最高')
###@@ min_kws = ('最少', '最小值', '最小', '最低')
###@@ sum_count_low_kws = ('个', '总共') + ('总和','加','总','一共','和',) + ("哪些", "查", "数量", "数") + ("几",) + ('多少', "多大")  + ("总数",)
###@@ max_special_kws = ("以上", "大于")
###@@ min_special_kws = ("以下", "小于")

def simple_label_func(s, drop_header = True):
    text_tokens =s.question_cut
    header = list(map(lambda x: x[:x.find("(")] if (not x.startswith("(") and x.endswith(")")) else x ,s.header.split(",")))

    #### not contain samples may not match in fuzzy-match, special column mapping in finance,
    ### or "3" to "三"
    '''
    fit_collection = ('多少个', '有几个', '总共') + ('总和','一共',) + ('平均数', '均值', '平均值', '平均') +     ('最大', '最多', '最大值', '最高') + ('最少', '最小值', '最小', '最低')

    '''
    fit_collection = sum_count_high_kws + mean_kws + max_kws + min_kws
    fit_header = []
    for c in header:
        for kw in fit_collection:
            if kw in c:
                start_idx = c.find(kw)
                end_idx = start_idx + len(kw)
                fit_header.append(c[start_idx: end_idx])

    if not drop_header:
        header = []
        fit_header = []

    input_ = "".join(text_tokens)
    for c in header + fit_header:
        if c in fit_collection:
            continue
        input_ = input_.replace(c, "")
        c0, c1 = c, c
        while c0 and c0 not in fit_collection and len(c0) >= 4:
            c0 = c0[1:]
            if c0 in fit_collection:
                break
            input_ = input_.replace(c0, "")
        while c1 and c1 not in fit_collection and len(c1) >= 4:
            c1 = c1[:-1]
            if c1 in fit_collection:
                break
            input_ = input_.replace(c1, "")

    #ic(input_)
    text_tokens = list(jieba.cut(input_))

    #cat_6_collection_high_level = ('多少个', '有几个', '总共') + ('总和','一共',) + ("哪些", "查", "数量")
    #cat_6_collection_high_level = ('多少个', '有几个', '总共') + ('总和','一共',)
    ##### 高置信度部分 （作为是否构成使用特殊规则的判断标准）
    #### case 2 部分 （高置信度有效匹配）
    #cat_6_collection_high_level =  ('多少个', '有几个', '总共') + ('总和','一共',)
    #cat_6_collection_high_level =  ('多少个', '有几个', '总共') + ('总和','一共',) + ("总数",)
    cat_6_collection_high_level = sum_count_high_kws
    if any(map(lambda high_level_token: high_level_token in "".join(text_tokens), cat_6_collection_high_level)):
        return 6

    #### 够深 够宽 规则部分, change order by header, if header have kws in , lower order
    if any(map(lambda kw: kw in text_tokens, mean_kws)):
        return 1
    if any(map(lambda kw: kw in text_tokens, max_kws)):
        return 2
    if any(map(lambda kw: kw in text_tokens, min_kws)):
        return 3

    ##### 低置信度部分
    #### case 2 部分  (低置信度尾部匹配)
    cat_6_collection = sum_count_low_kws
    if any(map(lambda kw: kw in text_tokens, cat_6_collection)):
        return 6
    if any(map(lambda token: "几" in token, text_tokens)):
        return 6

    #### special case 部分
    if any(map(lambda kw: kw in text_tokens, max_special_kws)):
        return 2
    if any(map(lambda kw: kw in text_tokens, min_special_kws)):
        return 3

    #### 无效匹配
    return 0


def simple_special_func(s, drop_header = True):
    text_tokens =s.question_cut
    header = list(map(lambda x: x[:x.find("(")] if (not x.startswith("(") and x.endswith(")")) else x ,s.header.split(",")))

    #### not contain samples may not match in fuzzy-match, special column mapping in finance,
    ### or "3" to "三"
    fit_collection = sum_count_high_kws + mean_kws + max_kws + min_kws
    fit_header = []
    for c in header:
        for kw in fit_collection:
            if kw in c:
                start_idx = c.find(kw)
                end_idx = start_idx + len(kw)
                fit_header.append(c[start_idx: end_idx])

    input_ = "".join(text_tokens)
    if not drop_header:
        header = []
        fit_header = []

    for c in header + fit_header:
        if c in fit_collection:
            continue
        input_ = input_.replace(c, "")
        c0, c1 = c, c
        while c0 and c0 not in fit_collection and len(c0) >= 4:
            c0 = c0[1:]
            if c0 in fit_collection:
                break
            input_ = input_.replace(c0, "")
        while c1 and c1 not in fit_collection and len(c1) >= 4:
            c1 = c1[:-1]
            if c1 in fit_collection:
                break
            input_ = input_.replace(c1, "")

    #ic(input_)
    text_tokens = list(jieba.cut(input_))
    #ic(text_tokens)

    #cat_6_collection_high_level = ('多少个', '有几个', '总共') + ('总和','一共',) + ("哪些", "查", "数量")
    #cat_6_collection_high_level = ('多少个', '有几个', '总共') + ('总和','一共',)
    #### case 2 部分 （高置信度有效匹配）
    cat_6_collection_high_level =  sum_count_high_kws
    if any(map(lambda high_level_token: high_level_token in "".join(text_tokens), cat_6_collection_high_level)):
        return 6

    #### 够深 够宽 规则部分, change order by header, if header have kws in , lower order
    if any(map(lambda kw: kw in text_tokens, mean_kws)):
        return 1
    if any(map(lambda kw: kw in text_tokens, max_kws)):
        return 2
    if any(map(lambda kw: kw in text_tokens, min_kws)):
        return 3

    return 0


def simple_total_label_func(s):
    is_special = False if simple_special_func(s) == 0 else True
    if not is_special:
        return 0
    return simple_label_func(s)
## agg-classifier end


##### main block of process start
def split_by_cond(s, extract_return = True):
    def recurrent_extract_cond(text):
        #return np.asarray(recurrent_extract(text)[0])
        #return recurrent_extract(text)[0]
        return np.asarray(list(recurrent_extract(text)[0]))

    question = s["question"]
    res =  s["rec_decomp"][1]
    if question is None:
        question = ""
    if res is None:
        res = ""

    common_res = findMaxSubString(question, res)
    #cond_kws = ("或", "而且", "并且", "当中")
    #cond_kws = ("或", "而且" "并且" "当中")
    cond_kws = conn_kws
    condition_part = question.replace(common_res, "")
    fit_kws = set([])
    for kw in cond_kws:
        if kw in condition_part and not condition_part.startswith(kw) and not condition_part.endswith(kw):
            fit_kws.add(kw)
    if not fit_kws:
        will_return = ([condition_part.replace(" ", "") + " " + common_res], "")
        if extract_return:
            #return (list(map(recurrent_extract_cond, will_return[0])), will_return[1])
            will_return = (np.asarray(list(map(recurrent_extract_cond, will_return[0]))) , will_return[1])
            #will_return = (np.concatenate(list(filter(lambda x: x.size ,map(np.asarray ,will_return[0].tolist()))), axis = 0), will_return[1])
            #will_return = (np.concatenate(list(map(np.asarray ,will_return[0].tolist())), axis = 0), will_return[1])
            input_ = list(filter(lambda x: x.size ,map(np.asarray ,will_return[0].tolist())))
            if input_:
                will_return = (np.concatenate(input_, axis = 0), will_return[1])
            else:
                will_return = (np.empty((0, 3)), will_return[1])

            will_return = will_return[0].reshape((-1, 3)) if will_return[0].size else np.empty((0, 3))
        return will_return

    fit_kw = sorted(fit_kws, key = len)[::-1][0]
    condition_part_splits = condition_part.split(fit_kw)
    #if fit_kw in ("或",):
    if fit_kw in or_kws:
        fit_kw = "or"
    #elif fit_kw in ("而且", "并且", "当中",):
    elif fit_kw in and_kws:
        fit_kw = "and"
    else:
        fit_kw = ""

    will_return = (list(map(lambda cond_: cond_.replace(" ", "") + " " + common_res, condition_part_splits)), fit_kw)
    if extract_return:
        #return (list(map(recurrent_extract_cond, will_return[0])), will_return[1])
        will_return = (np.asarray(list(map(recurrent_extract_cond, will_return[0]))), will_return[1])
        #ic(will_return[0])
        #will_return = (np.concatenate(list(map(np.asarray ,will_return[0].tolist())), axis = 0), will_return[1])
        input_ = list(filter(lambda x: x.size ,map(np.asarray ,will_return[0].tolist())))
        if input_:
            will_return = (np.concatenate(input_, axis = 0), will_return[1])
        else:
            will_return = (np.empty((0, 3)), will_return[1])
        #ic(will_return[0])
        will_return = will_return[0].reshape((-1, 3)) if will_return[0].size else np.empty((0, 3))

    return will_return



def filter_total_conds(s, df, condition_fit_num = 0):
    assert condition_fit_num >= 0 and type(condition_fit_num) == type(0)
    df = df.copy()

    #### some col not as float with only "None" as cell, also transform them into float
    df = df.applymap(lambda x: np.nan if x in ["None", None, "/"] else x)
    def justify_column_as_float(s):
        if "float" in str(s.dtype):
            return True
        if all(s.map(type).map(lambda tx: "float" in str(tx))):
            return True
        return False

    float_cols = list(map(lambda tt2: tt2[0],filter(lambda t2: t2[1] ,df.apply(justify_column_as_float, axis = 0).to_dict().items())))
    for f_col in float_cols:
        df[f_col] = df[f_col].astype(np.float64)
    ###

    header = df.columns.tolist()
    units_cols = filter(lambda c: "(" in c and c.endswith(")"), df.columns.tolist())
    if not float_cols:
        float_discribe_df = pd.DataFrame()
    else:
        float_discribe_df = df[float_cols].describe()

    def call_eval(val):
        try:
            return literal_eval(val)
        except:
            return val

    #### find condition column same as question_column
    def find_cond_col(res, header):
        #ic(res, header)
        c_res_common_dict = dict(filter(lambda t2: t2[1] ,map(lambda c: (c ,findMaxSubString(c, res)), header)))
        #ic(c_res_common_dict)
        common_ratio_c_dict = dict(map(lambda t2: (t2[0], len(t2[1]) / len(t2[0])), c_res_common_dict.items()))
        common_ratio_res_dict = dict(map(lambda t2: (t2[0], len(t2[1]) / len(res)), c_res_common_dict.items()))

        if not common_ratio_c_dict or not common_ratio_res_dict:
            return None

        dict_0_max_key = sorted(common_ratio_c_dict.items(), key = lambda t2: t2[1])[::-1][0][0]
        dict_1_max_key = sorted(common_ratio_res_dict.items(), key = lambda t2: t2[1])[::-1][0][0]
        return dict_0_max_key if dict_0_max_key == dict_1_max_key else None
    ###

    #### type comptatible in column type and value type, and fit_num match
    def filter_cond_col(cond_t3):
        assert type(cond_t3) == type((1,)) and len(cond_t3) == 3
        col, _, value = cond_t3

        if type(value) == type(""):
            value = call_eval(value)

        if col not in df.columns.tolist():
            return False

        #### type key value comp
        if col in float_cols and type(value) not in (type(0), type(0.0)):
            return False

        if col not in float_cols and type(value) in (type(0), type(0.0)):
            return False

        #### string value not in corr column
        if col not in float_cols and type(value) not in (type(0), type(0.0)):
            if type(value) == type(""):
                if value not in df[col].tolist():
                    return False

        if type(value) in (type(0), type(0.0)):
            if col in float_discribe_df.columns.tolist():
                if condition_fit_num > 0:
                    if value >= float_discribe_df[col].loc["min"] and value <= float_discribe_df[col].loc["max"]:
                        return True
                    else:
                        return False
                else:
                    assert condition_fit_num == 0
                    return True

        if condition_fit_num > 0:
            if value in df[col].tolist():
                return True
            else:
                return False
        else:
            assert condition_fit_num == 0
            return True

        return True
    ###

    #### condtions with same column may have conflict, choose the nearest one by stats in float or
    ### common_string as find_cond_col do.
    def same_column_cond_filter(cond_list, sort_stats = "mean"):
        #ic(cond_list)
        if len(cond_list) == len(set(map(lambda t3: t3[0] ,cond_list))):
            return cond_list

        req = defaultdict(list)
        for t3 in cond_list:
            req[t3[0]].append(t3[1:])

        def t2_list_sort(col_name ,t2_list):
            if not t2_list:
                return None
            t2 = None
            if col_name in float_cols:
                t2 = sorted(t2_list, key = lambda t2: np.abs(t2[1] - float_discribe_df[col_name].loc[sort_stats]))[0]
            else:
                if all(map(lambda t2: type(t2[1]) == type("") ,t2_list)):
                    col_val_cnt_df = df[col_name].value_counts().reset_index()
                    col_val_cnt_df.columns = ["val", "cnt"]
                    #col_val_cnt_df["val"].map(lambda x: sorted(filter(lambda tt2: tt2[-1] ,map(lambda t2: (t2 ,len(findMaxSubString(x, t2[1]))), t2_list)),
                    #                                          key = lambda ttt2: -1 * ttt2[-1])[0])

                    t2_list_map_to_column_val = list(filter(lambda x: x[1] ,map(lambda t2: (t2[0] ,find_cond_col(t2[1], list(set(col_val_cnt_df["val"].values.tolist())))), t2_list)))
                    if t2_list_map_to_column_val:
                        #### return max length fit val in column
                        t2 = sorted(t2_list_map_to_column_val, key = lambda t2: -1 * len(t2[1]))[0]
            if t2 is None and t2_list:
                t2 = t2_list[0]
            return t2

        cond_list_filtered = list(map(lambda ttt2: (ttt2[0], ttt2[1][0], ttt2[1][1]) ,
            filter(lambda tt2: tt2[1] ,map(lambda t2: (t2[0] ,t2_list_sort(t2[0], t2[1])), req.items()))))

        return cond_list_filtered
    ###

    total_conds_map_to_column = list(map(lambda t3: (find_cond_col(t3[0], header), t3[1], t3[2]), s["total_conds"]))
    total_conds_map_to_column_filtered = list(filter(filter_cond_col, total_conds_map_to_column))
    total_conds_map_to_column_filtered = sorted(set(map(lambda t3: (t3[0], t3[1], call_eval(t3[2]) if type(t3[2]) == type("") else t3[2]), total_conds_map_to_column_filtered)))
    #ic(total_conds_map_to_column_filtered)

    cp_cond_list = list(filter(lambda t3: t3[1] in (">", "<"), total_conds_map_to_column_filtered))
    eq_cond_list = list(filter(lambda t3: t3[1] in ("==", "!="), total_conds_map_to_column_filtered))

    cp_cond_list_filtered = same_column_cond_filter(cp_cond_list)

    #total_conds_map_to_column_filtered = same_column_cond_filter(total_conds_map_to_column_filtered)
    return cp_cond_list_filtered + eq_cond_list
    #return total_conds_map_to_column_filtered

###@@ only_kws_columns = {"城市": "=="}

#### this function only use to cond can not extract by JointBert,
### may because not contain column string in question such as "城市" or difficult to extract kw
### define kw column as all cells in series are string type.
### this function support config relation to column and if future
### want to auto extract relation, this may can be done by head string or tail string by edit pattern "\w?{}\w?"
### "是" or "不是" can be extract in this manner.
def augment_kw_in_question(question_df, df, only_kws_in_string = []):
    #### keep only_kws_in_string empty to maintain all condition
    question_df = question_df.copy()
    #df = df.copy()

    def call_eval(val):
        try:
            return literal_eval(val)
        except:
            return val

    df = df.copy()

    df = df.applymap(call_eval)

    #### some col not as float with only "None" as cell, also transform them into float
    df = df.applymap(lambda x: np.nan if x in ["None", None, "/"] else x)
    def justify_column_as_float(s):
        if "float" in str(s.dtype):
            return True
        if all(s.map(type).map(lambda tx: "float" in str(tx))):
            return True
        return False

    float_cols = list(map(lambda tt2: tt2[0],filter(lambda t2: t2[1] ,df.apply(justify_column_as_float, axis = 0).to_dict().items())))
    #obj_cols = set(df.columns.tolist()).difference(set(float_cols))

    def justify_column_as_kw(s):
        if all(s.map(type).map(lambda tx: "str" in str(tx))):
            return True
        return False

    obj_cols = list(map(lambda tt2: tt2[0],filter(lambda t2: t2[1] ,df.apply(justify_column_as_kw, axis = 0).to_dict().items())))
    obj_cols = list(set(obj_cols).difference(set(float_cols)))
    if only_kws_columns:
        obj_cols = list(set(obj_cols).intersection(set(only_kws_columns.keys())))

    #replace_format = "{}是{}"
    #kw_augmented_df = pd.DataFrame(df[obj_cols].apply(lambda s: list(map(lambda val :(val,replace_format.format(s.name, val)), s.tolist())), axis = 0).values.tolist())
    #kw_augmented_df.columns = obj_cols
    kw_augmented_df = df[obj_cols].copy()
    #ic(kw_augmented_df)

    def extract_question_kws(question):
        if not kw_augmented_df.size:
            return []
        req = defaultdict(set)
        for ridx, r in kw_augmented_df.iterrows():
            for k, v in dict(r).items():
                if v in question:
                    pattern = "\w?{}\w?".format(v)
                    all_match = re.findall(pattern, question)
                    #req = req.union(set(all_match))
                    #req[v] = req[v].union(set(all_match))
                    key = "{}~{}".format(k, v)
                    req[key] = req[key].union(set(all_match))
                #ic(k, v)
                #question = question.replace(v[0], v[1])
        #return question.replace(replace_format.format("","") * 2, replace_format.format("",""))
        #req = list(req)
        if only_kws_in_string:
            req = list(map(lambda tt2: tt2[0] ,filter(lambda t2: sum(map(lambda kw: sum(map(lambda t: kw in t ,t2[1])), only_kws_in_string)), req.items())))
        else:
            req = list(set(req.keys()))

        def req_to_t3(req_string, relation = "=="):
            assert "~" in req_string
            left, right = req_string.split("~")
            if left in only_kws_columns:
                relation = only_kws_columns[left]
            return (left, relation, right)

        if not req:
            return []

        return list(map(req_to_t3, req))

        #return req

    question_df["question_kw_conds"] = question_df["question"].map(extract_question_kws)
    return question_df


def choose_question_column_by_rm_conds(s, df):
    question = s.question
    total_conds_filtered = s.total_conds_filtered
    #cond_kws = ("或", "而且", "并且", "当中")
    cond_kws = conn_kws
    stopwords = ("是", )
    #ic(total_conds_filtered)
    def construct_res(question):
        for k, _, v in total_conds_filtered:
            if "(" in k:
                k = k[:k.find("(")]
                #ic(k)
            question = question.replace(str(k), "")
            question = question.replace(str(v), "")
        for w in cond_kws + stopwords:
            question = question.replace(w, "")
        return question

    res = construct_res(question)
    decomp = (None, res, None)
    return choose_question_column(decomp, df.columns.tolist(), df)


def split_qst_by_kw(question, kw = "的"):
    return question.split(kw)

#qst_kws = ("多少", "什么", "多大", "哪些", "怎么", "情况", "那些", "哪个")
###@@ qst_kws = ("多少", "什么", "多大", "哪些", "怎么", "情况", "那些", "哪个")
def choose_res_by_kws(question):
    #kws = ["的","多少", '是']
    question = question.replace(" ", "")
    #kws = ["的","或者","或", "且","并且","同时"]
    kws = ("的",) + conn_kws
    kws = list(kws)
    def qst_kw_filter(text):
        #qst_kws = ("多少", "什么", "多大", "哪些", "怎么", "情况", "那些", "哪个")
        if any(map(lambda kw: kw in text, qst_kws)):
            return True
        return False

    kws_cp = deepcopy(kws)
    qst_c = set(question.split("，"))
    while kws:
        kw = kws.pop()
        qst_c = qst_c.union(set(filter(qst_kw_filter ,reduce(lambda a, b: a + b,map(lambda q: split_qst_by_kw(q, kw), qst_c))))
                           )
    #print("-" * 10)
    #print(sorted(filter(lambda x: x and (x not in kws_cp) ,qst_c), key = len))
    #print(sorted(filter(lambda x: x and (x not in kws_cp) and qst_kw_filter(x) ,qst_c), key = len))
    #### final choose if or not
    return sorted(filter(lambda x: x and (x not in kws_cp) and qst_kw_filter(x) ,qst_c), key = len)
    #return sorted(filter(lambda x: x and (x not in kws_cp) and True ,qst_c), key = len)


def cat6_to_45_by_column_type(s, df):
    agg_pred = s.agg_pred
    if agg_pred != 6:
        return agg_pred
    question_column = s.question_column

    def call_eval(val):
        try:
            return literal_eval(val)
        except:
            return val

    df = df.copy()

    df = df.applymap(call_eval)

    #### some col not as float with only "None" as cell, also transform them into float
    df = df.applymap(lambda x: np.nan if x in ["None", None, "/"] else x)
    def justify_column_as_float(s):
        if "float" in str(s.dtype):
            return True
        if all(s.map(type).map(lambda tx: "float" in str(tx))):
            return True
        return False

    float_cols = list(map(lambda tt2: tt2[0],filter(lambda t2: t2[1] ,df.apply(justify_column_as_float, axis = 0).to_dict().items())))
    #obj_cols = set(df.columns.tolist()).difference(set(float_cols))

    def justify_column_as_kw(s):
        if all(s.map(type).map(lambda tx: "str" in str(tx))):
            return True
        return False

    #obj_cols = list(map(lambda tt2: tt2[0],filter(lambda t2: t2[1] ,df.apply(justify_column_as_kw, axis = 0).to_dict().items())))
    obj_cols = df.columns.tolist()
    obj_cols = list(set(obj_cols).difference(set(float_cols)))

    #ic(obj_cols, float_cols, df.columns.tolist())
    assert len(obj_cols) + len(float_cols) == df.shape[1]

    if question_column in obj_cols:
        return 4
    elif question_column in float_cols:
        return 5
    else:
        return 0


def full_before_cat_decomp(df, question_df, only_req_columns = False):
    df, question_df = df.copy(), question_df.copy()
    first_train_question_extract_df = pd.DataFrame(question_df["question"].map(lambda question: (question, recurrent_extract(question))).tolist())
    first_train_question_extract_df.columns = ["question", "decomp"]

    first_train_question_extract_df = augment_kw_in_question(first_train_question_extract_df, df)

    first_train_question_extract_df["rec_decomp"] = first_train_question_extract_df["decomp"].map(lambda decomp: decomp if decomp[0] else rec_more_time(decomp))
    #return first_train_question_extract_df.copy()
    first_train_question_extract_df["question_cut"] = first_train_question_extract_df["rec_decomp"].map(lambda t3: jieba.cut(t3[1]) if t3[1] is not None else []).map(list)
    first_train_question_extract_df["header"] = ",".join(df.columns.tolist())
    first_train_question_extract_df["question_column_res"] = first_train_question_extract_df["rec_decomp"].map(lambda decomp: choose_question_column(decomp, df.columns.tolist(), df))

    #### agg
    first_train_question_extract_df["agg_res_pred"] = first_train_question_extract_df.apply(simple_total_label_func, axis = 1)
    first_train_question_extract_df["question_cut"] = first_train_question_extract_df["question"].map(jieba.cut).map(list)
    first_train_question_extract_df["agg_qst_pred"] = first_train_question_extract_df.apply(simple_total_label_func, axis = 1)
    ### if agg_res_pred and agg_qst_pred have conflict use max, to prevent from empty agg with errorous question column
    ### but this "max" can also be replaced by measure the performance of decomp part, and choose the best one
    ### or we can use agg_qst_pred with high balanced_score as 0.86+ in imbalanced dataset.
    ### which operation to use need some discussion.
    ### (balanced_accuracy_score(lookup_df["sql"], lookup_df["agg_pred"]),
    ### balanced_accuracy_score(lookup_df["sql"], lookup_df["agg_res_pred"]),
    ### balanced_accuracy_score(lookup_df["sql"], lookup_df["agg_qst_pred"]))
    ### (0.9444444444444445, 0.861111111111111, 0.9444444444444445) first_train_df conclucion
    ### (1.0, 0.8333333333333333, 1.0) cat6_conclucion
    ### this show that res worse in cat6 situation, but the agg-classifier construct is sufficent to have a
    ### good conclusion in full-question. (because cat6 is the most accurate part in Tupledire tree sense.)
    ### so use max as the best one
    first_train_question_extract_df["agg_pred"] =  first_train_question_extract_df.apply(lambda s: max(s.agg_res_pred, s.agg_qst_pred), axis = 1)

    #### conn and conds
    first_train_question_extract_df["conds"] = first_train_question_extract_df["rec_decomp"].map(lambda x: x[0])
    first_train_question_extract_df["split_conds"] = first_train_question_extract_df.apply(split_by_cond, axis = 1).values.tolist()
    first_train_question_extract_df["conn_pred"] = first_train_question_extract_df.apply(lambda s: split_by_cond(s, extract_return=False), axis = 1).map(lambda x: x[-1]).values.tolist()
    #first_train_question_extract_df["total_conds"] = first_train_question_extract_df.apply(lambda s: list(set(map(tuple,s["conds"] + s["split_conds"].tolist()))), axis = 1).values.tolist()
    first_train_question_extract_df["total_conds"] = first_train_question_extract_df.apply(lambda s: list(set(map(tuple,s["question_kw_conds"] + s["conds"] + s["split_conds"].tolist()))), axis = 1).values.tolist()
    first_train_question_extract_df["total_conds_filtered"] = first_train_question_extract_df.apply(lambda s: filter_total_conds(s, df), axis = 1).values.tolist()

    #### question_column_res more accurate, if not fit then use full-question question_column_qst to extract
    ### can not fit multi question or fuzzy describe, or question need kw replacement.

    #first_train_question_extract_df["question_column_qst"] = first_train_question_extract_df.apply(lambda s: choose_question_column_by_rm_conds(s, df), axis = 1)
    first_train_question_extract_df["question_column_qst"] = first_train_question_extract_df["question"].map(choose_res_by_kws).map(lambda res_list: list(filter(lambda x: x ,map(lambda res: choose_question_column((None, res, None), df.columns.tolist(), df), res_list)))).map(lambda x: x[0] if x else None)
    first_train_question_extract_df["question_column"] = first_train_question_extract_df.apply(lambda s: s.question_column_res if s.question_column_res is not None else s.question_column_qst, axis = 1)

    #### predict cat6 to 4 5 based on question_column and column dtype,
    #### this may performance bad if question_column has error,
    #### and almost 100% accurate if question_column truly provide and user is not an idoit (speak ....)
    agg_sql_dict = {0:"", 1:"AVG", 2:"MAX", 3:"MIN", 4:"COUNT", 5:"SUM"}
    first_train_question_extract_df["agg_pred"] =  first_train_question_extract_df.apply(lambda s: cat6_to_45_by_column_type(s, df), axis = 1).map(lambda x: agg_sql_dict[x])
    if only_req_columns:
        return first_train_question_extract_df[["question",
                                                "total_conds_filtered",
                                                "conn_pred",
                                                "question_column",
                                                "agg_pred"
                                               ]].copy()

    return first_train_question_extract_df.copy()


if __name__ == "__main__":
    ###### valid block
    req = list(data_loader(req_table_num=None))


    train_df, _ = req[2]
    train_df
    question = "哪些股票的收盘价大于20？"
    qs_df = pd.DataFrame([[question]], columns = ["question"])
    ic(question)
    ic(full_before_cat_decomp(train_df, qs_df, only_req_columns=True))


    #### not support select 股票 from table where 市值 = （select max(市值) from table）
    #### this is a nest sql.
    question = "哪个股票代码市值最高？"
    qs_df = pd.DataFrame([[question]], columns = ["question"])
    ic(question)
    ic(full_before_cat_decomp(train_df, qs_df, only_req_columns=True))

    question = "市值的最大值是多少？"
    qs_df = pd.DataFrame([[question]], columns = ["question"])
    ic(question)
    ic(full_before_cat_decomp(train_df, qs_df, only_req_columns=True))


    question = "EPS大于0的股票有哪些？"
    qs_df = pd.DataFrame([[question]], columns = ["question"])
    ic(question)
    ic(full_before_cat_decomp(train_df, qs_df, only_req_columns=True))


    question = "EPS大于0且周涨跌大于5的平均市值是多少？"
    qs_df = pd.DataFrame([[question]], columns = ["question"])
    ic(question)
    ic(full_before_cat_decomp(train_df, qs_df, only_req_columns=True))


    train_df, _ = req[5]
    train_df
    question = "产能小于20、销量大于40而且市场占有率超过1的公司有哪些？"
    qs_df = pd.DataFrame([[question]], columns = ["question"])
    ic(question)
    ic(full_before_cat_decomp(train_df, qs_df, only_req_columns=True))


    #### 特殊符号 "、"
    question = "产能小于20而且销量大于40而且市场占有率超过1的公司有哪些？"
    qs_df = pd.DataFrame([[question]], columns = ["question"])
    ic(question)
    ic(full_before_cat_decomp(train_df, qs_df, only_req_columns=True))


    train_df, _ = req[6]
    train_df
    #### 加入列别名 只需要 复刻列即可
    question = "投资评级为维持的名称有哪些？"
    qs_df = pd.DataFrame([[question]], columns = ["question"])
    ic(question)
    ic(full_before_cat_decomp(train_df, qs_df, only_req_columns=True))


    train_df["公司"] = train_df["名称"]
    question = "投资评级为维持的公司有哪些？"
    qs_df = pd.DataFrame([[question]], columns = ["question"])
    ic(question)
    ic(full_before_cat_decomp(train_df, qs_df, only_req_columns=True))


    question = "投资评级为维持而且变动为增持的公司有哪些？"
    qs_df = pd.DataFrame([[question]], columns = ["question"])
    ic(question)
    ic(full_before_cat_decomp(train_df, qs_df, only_req_columns=True))


    question = "投资评级为维持或者变动为增持的公司有哪些？"
    qs_df = pd.DataFrame([[question]], columns = ["question"])
    ic(question)
    ic(full_before_cat_decomp(train_df, qs_df, only_req_columns=True))


    question = "投资评级为维持或者变动为增持的平均收盘价是多少？"
    qs_df = pd.DataFrame([[question]], columns = ["question"])
    ic(question)
    ic(full_before_cat_decomp(train_df, qs_df, only_req_columns=True))


    train_df, _ = req[7]
    train_df
    question = "宁波的一手房每周交易数据上周成交量是多少？"
    qs_df = pd.DataFrame([[question]], columns = ["question"])
    ic(question)
    ic(full_before_cat_decomp(train_df, qs_df, only_req_columns=True))


    question = "一手房每周交易数据为宁波上周成交量是多少？"
    qs_df = pd.DataFrame([[question]], columns = ["question"])
    ic(question)
    ic(full_before_cat_decomp(train_df, qs_df, only_req_columns=True))

    #### this also can deal with set column as use kw to extract
    ### see function augment_kw_in_question
    train_df["城市"] = train_df["一手房每周交易数据"]
    question = "一手房每周交易数据为宁波上周成交量是多少？"
    qs_df = pd.DataFrame([[question]], columns = ["question"])
    ic(question)
    ic(full_before_cat_decomp(train_df, qs_df, only_req_columns=True))

    question = "王翔知道宁波一手房的当月累计成交量是多少吗？"
    qs_df = pd.DataFrame([[question]], columns = ["question"])
    ic(question)
    ic(full_before_cat_decomp(train_df, qs_df, only_req_columns=True))


    question = "王翔知道上周成交量大于50的最大同比当月是多少吗？"
    qs_df = pd.DataFrame([[question]], columns = ["question"])
    ic(question)
    ic(full_before_cat_decomp(train_df, qs_df, only_req_columns=True))


    train_df, _ = req[9]
    #### the last column should be "周跌幅", can't tackle duplicates columns
    train_df
    cols = train_df.columns.tolist()
    cols[-1] = "周跌幅（%）"
    train_df.columns = cols
    question = "周涨幅大于7的涨股有哪些？"
    qs_df = pd.DataFrame([[question]], columns = ["question"])
    ic(question)
    ic(full_before_cat_decomp(train_df, qs_df, only_req_columns=True))


    #### not recognize as 6 agg-classifier
    question = "周涨幅大于7的涨股总数是多少？"
    qs_df = pd.DataFrame([[question]], columns = ["question"])
    ic(question)
    ic(full_before_cat_decomp(train_df, qs_df, only_req_columns=True))


    question = "周涨幅大于7的涨股总共有多少个？"
    qs_df = pd.DataFrame([[question]], columns = ["question"])
    ic(question)
    ic(full_before_cat_decomp(train_df, qs_df, only_req_columns=True))