# 变量说明
一般使用ds来表达数据集类型的变量, 如ds1,ds2
一般使用df来表达pandas对象, 如df1,df2
# 内置函数说明
ds_sql(conn_name, sql)    #通过数据源连接的名称及SQL语句获取ds数据集
ds_save(config,content,update=0)  #将数据写入数据源,成功返回{'status':200,'msg':'success'}
ds_df(ds)    #将ds数据集转化成pandas的df对象
ds_list(df)  #将pandas的df对象转化成ds数据集
ds_get(id)    #获取目标ds数据集[不常用]

import pandas as pd
# 读取excel文件给df对象
df = pd.read_excel('文件路径', '表名')
# 按照省份列统计数量列的加总
df = df.groupby('省份').agg({'数量':'sum'}).reset_index()
# 将df对象转化为ds输出
ds = ds_list(df)

import pandas as pd
df = pd.read_excel('/Users/../smartdemo.xlsx', 'demo')
# 生成0号df
df0 = df.groupby('c3').agg({'qty':'sum'}).reset_index()
# 生成1号df
df1 = df.groupby(['province','c3']).agg({'qty':'sum'}).reset_index()
# 转化为ds并输出
ds = {'df0': ds_list(df0), 'df1': ds_list(df1)}

import pandas as pd
# 查询条件一
sql_str1 = '''select H1 as heroname, sum(qty) as 出场数 from T
/* where H2 = '$H2' */
group by H1 order by sum(qty) desc'''
# 查询条件二
sql_str2 = 'select heroname, qty as 上月出场数 from xxx'

# 获取第一个df数据
ds1 = ds_sql('连接名称1', sql_str1)
df1 = ds_df(ds1)
# 获取第一个df数据
ds2 = ds_sql('连接名称2', sql_str2)
df2 = ds_df(ds2)
# 关联两个数据源的数据
df = pd.merge(df1,df2,how='left',on=['heroname'])
ds = ds_list(df)

import requests
header = {"Content-Type": "application/json", "x-acs-dingtalk-access-token":'xxx'}
res = requests.post('https://xxx/api/xx', headers=header,data={'type':''}).json()
result = res['data']
config = {'table':'tablename', 'conn':'连接名称'}
ds=ds_save(config, result)

import pandas as pd
# 一次性读取excel的多个表格
dfs = pd.read_excel('/../demo.xlsx', ['sheet1','sheet2'])
# 指定表格名称,获取表格的df
df1 = dfs['sheet1']
# 选取指定列
df = df[['date','time']]
# 选择指定行列
df = df.loc[:10,['A','B','C']]
# 按照条件筛选
df[df.A==10]
df[df.A.isin([10,20,30])] #包含
df[(df.A>10)&(df.B=='a')|(df.C=='c')] # 多条件且或
# 重命名列
df.columns=['A','B','C']
# 列日期处理
df['时间'] = df['时间'].dt.strftime('%Y-%m-%d')
# 自定义列处理
df['B'] = df['B'].apply(lambda x:x*2)
# 指定列排序
df.sort_values('时间')
df.sort_values(['A','B'], ascending=False)
# 合并多个df需要表头一样
df = pd.concat([df1,df2]).reset_index()
# 指定关联字段名,关联两个df(left, inner, right, outer)
df = pd.merge(df1,df2,how='left',on=['key1','key2'])
# 聚合统计
df = df.groupby(['province','c1'])['qty'].sum().reset_index()
df = df.groupby("employees")["score"].agg(["sum","max","min","mean","size"]).reset_index()
df = df.groupby("employees").agg({"salary":"sum", "score":"mean"}).reset_index()
# 透视表
df=pd.pivot_table(df,index=['C1','C2'],values=['D1','D2','D3'],aggfunc=[np.sum,np.mean],fill_value=0)
df=df.pivot(index='C1', columns='C2')['D1']

from smart_chart.common.functions import getVectorResult,saveVectorResult,getGptResult
getVectorResult(question, collection=None, vector=0.01, table='vectors')  #查询向量 {'status': 200, 'promote': '', 'msg':'', 'token': ''}
saveVectorResult(contents)   #保存向量{'status': 200, 'msg': ''}
getGptResult(text)  #获取GPT查询结果{'status': 200, 'msg': result, 'token': tokens}

from smart_chart.common.functions import getVectorResult,saveVectorResult,getGptResult
question = 'Smartchart是谁开发的'
answer = '由JohnYan开发的'
content = [['collection','document','answer'],['智能体A',question,answer]]
ds = saveVectorResult(content)

from smart_chart.common.functions import getVectorResult,saveVectorResult,getGptResult

ds = getVectorResult('$question')
if ds['token'] > 0.01:
    ds = getGptResult(question)