def get_fund_detail_position(fund_symbol, rpt_date): """ 从数据库中获取给定基金的持仓 Parameter --------- fund_symbol: string 基金的代码,可以通过各大基金网站查询 rpt_date: datetime like 基金报告期 Return ------ position: pandas.Series """ sql = ''' SELECT M2.SecuCode, S.RatioInNV FROM SecuMain M, MF_StockPortfolioDetail S, SecuMain M2 WHERE S.InnerCode = M.InnerCode AND M.SecuCode = \'{fund_symbol}\' AND S.StockInnerCode = M2.InnerCode AND M.SecuCategory = 8 AND M2.SecuCategory = 1 AND M2.SecuMarket in (83, 90) AND S.ReportDate = \'{rpt_date}\' '''.format(fund_symbol=fund_symbol, rpt_date=rpt_date) fund_data = fetch_db_data(jydb, sql, ['symbol', 'ratio'], dtypes={'ratio': 'float64'}) fund_data.symbol = fund_data.symbol.apply(add_suffix) fund_data = fund_data.set_index('symbol')['ratio'] return fund_data
def get_liststatus(start_time, end_time): ''' 获取上市公司的上市状态,1表示正常上市,2表示暂停上市,3表示退市整理,4表示终止上市,NA表示非 正常上市状态 ''' sql = ''' SELECT M.Secucode, S.ChangeType, S.changeDate FROM SECUMAIN M, LC_ListStatus S WHERE M.INNERCODE = S.INNERCODE AND M.SecuCategory = 1 AND S.SecuMarket in (90, 83) AND S.ChangeType != 9 ORDER BY M.SECUCODE ASC, S.changeDate ASC ''' ls_data = fetch_db_data(jydb, sql, ['symbol', 'data', 'time'], dtypes={'data': 'int'}) # 原数据库中1表示上市,2表示暂停上市,3表示恢复上市,4表示退市,6表示退市整理 ls_map = {1: 1, 2: 2, 3: 1, 4: 4, 6: 3} ls_data['data'] = ls_data.data.map(ls_map) ls_data['symbol'] = ls_data.symbol.apply(add_stock_suffix) by_symbol = ls_data.groupby('symbol') tds = get_calendar('stock.sse').get_tradingdays(start_time, end_time) ls_data = by_symbol.apply(map2td, days=tds, timecol='time', fillna={'symbol': lambda x: x.symbol.iloc[0]}) ls_data = ls_data.pivot_table('data', index='time', columns='symbol') last_td = get_calendar('stock.sse').latest_tradingday(end_time, 'PAST') universe = sorted( pitcache_getter('UNIVERSE', 10).get_csdata(last_td).index) ls_data = ls_data.reindex(columns=universe).sort_index(ascending=True) if not check_completeness(ls_data.index, start_time, end_time): raise ValueError('Error, data missed!') return ls_data
def get_padjfactor(start_time, end_time): ''' 从数据库中获取复权因子 ''' sql = ''' SELECT A.ExDiviDate, A.RatioAdjustingFactor, M.SecuCode FROM QT_AdjustingFactor A, SecuMain M WHERE A.InnerCode = M.InnerCode AND M.secuMarket in (83, 90) AND M.SECUCATEGORY = 1 ORDER BY M.SecuCode ASC, A.ExDiviDate ASC ''' data = fetch_db_data(jydb, sql, ['exdivdate', 'data', 'symbol'], dtypes={'data': 'float64'}) data.symbol = data.symbol.apply(add_stock_suffix) by_symbol = data.groupby('symbol') tds = get_calendar('stock.sse').get_tradingdays(start_time, end_time) data = by_symbol.apply(map2td, days=tds, timecol='exdivdate', fillna={ 'data': lambda x: 1, 'symbol': lambda x: x.symbol.iloc[0] }) data = data.pivot_table('data', index='exdivdate', columns='symbol') last_td = get_calendar('stock.sse').latest_tradingday(end_time, 'PAST') universe = sorted( pitcache_getter('UNIVERSE', 10).get_csdata(last_td).index) data = data.reindex(columns=universe).sort_index(ascending=True).fillna(1) if not check_completeness(data.index, start_time, end_time): raise ValueError('Data missed!') return data
def inner(start_time, end_time): sql = ''' SELECT S.{share_type}, S.EndDate, M.SecuCode FROM SecuMain M, LC_ShareStru S WHERE M.CompanyCode = S.CompanyCode AND M.SecuMarket in (83, 90) AND M.SecuCategory = 1 AND M.ListedState != 9 ORDER BY M.SecuCode ASC, S.EndDate ASC '''.format(share_type=share_type) data = fetch_db_data(jydb, sql, ['data', 'time', 'symbol'], dtypes={'data': 'float64'}) data.symbol = data.symbol.apply(add_stock_suffix) data = data.drop_duplicates(['symbol', 'time' ]) # 若证券代码和时间相同则认为是相同数据,一般不会出现股本数据更正 by_symbol = data.groupby('symbol') tds = get_calendar('stock.sse').get_tradingdays(start_time, end_time) data = by_symbol.apply(map2td, days=tds, timecol='time', fillna={'symbol': lambda x: x.symbol.iloc[0]}) data = data.pivot_table('data', index='time', columns='symbol') last_td = get_calendar('stock.sse').latest_tradingday(end_time, 'PAST') universe = sorted( pitcache_getter('UNIVERSE', 10).get_csdata(last_td).index) data = data.reindex(columns=universe) if not check_completeness(data.index, start_time, end_time): raise ValueError('Error, data missed!') return data
def inner(start_time, end_time): sql = ''' SELECT S.{level}, S.InfoPublDate, M.SecuCode FROM LC_exgIndustry S, SecuMain M WHERE S.CompanyCOde = M.CompanyCode AND S.Standard = {standard} AND M.SecuCategory = 1 AND M.SecuMarket in (90, 83) ORDER BY M.Secucode, S.InfoPublDate ASC '''.format(level=INDUSTRY_LEVEL_MAP[class_level], standard=class_standard) data = fetch_db_data(jydb, sql, ['data', 'time', 'symbol']) data['data'] = data.data.map(translate_table) data['symbol'] = data.symbol.apply(add_stock_suffix) by_symbol = data.groupby('symbol') tds = get_calendar('stock.sse').get_tradingdays(start_time, end_time) data = by_symbol.apply(map2td, days=tds, timecol='time', fillna={'data': lambda x: NaS, 'symbol': lambda x: x.symbol.iloc[0]}) data = data.pivot_table('data', index='time', columns='symbol', aggfunc=lambda x: ';'.join(x)) check_agg_error(data) last_td = get_calendar('stock.sse').latest_tradingday(end_time, 'PAST') universe = sorted(pitcache_getter('UNIVERSE', 10).get_csdata(last_td).index) data = data.reindex(columns=universe).fillna(NaS).astype(np_unicode) if not check_completeness(data.index, start_time, end_time): raise ValueError('Error, data missed!') return data
def inner(start_time, end_time): nonlocal sql_kwargs if sql_kwargs is None: sql_kwargs = {} sql = sql_template.format(start_time=start_time, end_time=end_time, **sql_kwargs) data = fetch_db_data(db, sql, cols, dtypes) return data
def get_trade_status(start_time, end_time): ''' 获取股票的交易状态 Notes ----- 将成交量为0或者最高价等于最低价视为不能交易,返回值为1表示正常交易,0表示不能交易,NA表示未上市而不能交易 ''' sql = ''' SELECT S.TradingDay, S.TurnoverVolume, S.HighPrice, S.LowPrice, M.Secucode FROM QT_DailyQuote S, SecuMain M WHERE S.InnerCode = M.InnerCode AND M.SecuMarket in (83, 90) AND S.TradingDay <= \'{end_time:%Y-%m-%d}\' AND S.TradingDay >= \'{start_time:%Y-%m-%d}\' AND M.SecuCategory = 1 ORDER BY M.Secucode ASC, S.TradingDay ASC ''' start_time, end_time = trans_date(start_time, end_time) offset = 30 start_time_shifted = get_calendar('stock.sse').shift_tradingdays( start_time, -offset - 10) sql = sql.format(start_time=start_time_shifted, end_time=end_time) data = fetch_db_data(jydb, sql, ['time', 'vol', 'high', 'low', 'symbol'], dtypes={ 'vol': 'float64', 'high': 'float64', 'low': 'float64' }) data.symbol = data.symbol.apply(add_stock_suffix) ma_vol = data.groupby('symbol', as_index=False).vol.rolling( offset, min_periods=offset).mean() ma_vol = ma_vol.reset_index(level=0, drop=True) data = data.assign(ma_vol=ma_vol) data = data.assign(flag=1) # pdb.set_trace() data.loc[np.isclose(data.ma_vol, 0, 0.1), 'ma_vol'] = np.nan # (上个交易日)移动平均成交量过低 data.ma_vol = data.vol / data.ma_vol data.loc[np.isclose(data.vol, 0, 0.1), 'flag'] = 0 # 成家量过低,不可交易 data.loc[data.high == data.low, 'flag'] = 0 # 最高价等于最低价,不可交易 # 移动平均成交量过低,不可交易 data.loc[np.isclose(data.ma_vol, 0, 0.05) | (pd.isnull(data.ma_vol)), 'flag'] = 0 data = data.pivot_table('flag', index='time', columns='symbol') last_td = get_calendar('stock.sse').latest_tradingday(end_time, 'PAST') universe = sorted( pitcache_getter('UNIVERSE', 10).get_csdata(last_td).index) data = data.loc[(data.index >= start_time) & (data.index <= end_time)].reindex(columns=universe) if not check_completeness(data.index, start_time, end_time): raise ValueError('Error, data missed!') return data
def xps_shift_year(symbol, date, data_name, tab_name, offset): # 计算年度偏移后的XPS指标 symbol = drop_suffix(symbol) date = trans_date(date) if tab_name == 'LC_BalanceSheetAll': if_adjusted = '' else: if_adjusted = 'IfAdjusted NOT IN (4, 5) AND' sql = ''' SELECT m.SecuCode, s.TotalShares, s.{data}, s.enddate, s.infopubldate FROM (select s1.EndDate, s1.InfoPublDate, s1.{data}, s1.CompanyCode, s1.rnum, s2.TotalShares from (SELECT EndDate, InfoPublDate, {data}, CompanyCode, BulletinType, IfAdjusted, IfMerged, ROW_NUMBER() OVER(PARTITION BY COMPANYCODE, ENDDATE ORDER BY INFOPUBLDATE DESC) as rnum FROM {tab_name} WHERE BulletinType != 10 AND InfoPublDate < \'{date:%Y-%m-%d}\' AND {if_adjusted} IfMerged = 1) s1 left outer join LC_ShareStru s2 on (s1.CompanyCode = s2.CompanyCode AND s1.EndDate = s2.EndDate)) s, SecuMain M where s.rnum = 1 AND S.CompanyCode = M.CompanyCode AND M.SecuCode = \'{symbol}\' AND M.SecuCategory = 1 AND M.SecuMarket IN (83, 90) AND S.EndDate >= (SELECT TOP(1) S3.CHANGEDATE FROM LC_ListStatus S3 WHERE S3.INNERCODE = M.INNERCODE AND S3.ChangeType = 1) ORDER BY S.EndDate ASC '''.format(data=data_name, tab_name=tab_name, date=date, symbol=symbol, if_adjusted=if_adjusted) data = fetch_db_data( jydb, sql, ['symbol', 'total_share', 'data', 'rpt_date', 'update_time'], { 'data': 'float64', 'total_share': 'float64' }) data['total_share'] = data.total_share.fillna(method='ffill') if len(data) < 1: return np.nan data = data.loc[data.rpt_date.dt.month == 12] if len(data) < offset or get_calendar('stock.sse').count( data.iloc[-1, -1], date) > 120: return np.nan return data.iloc[-offset, 2] / data.iloc[-offset, 1]
def get_suspend_state(start_time, end_time): """ 获取股票停牌状态 Notes ----- 其中,0表示未停牌,1表示整日停牌,2表示日内部分交易时间停牌 """ start_time, end_time = trans_date(start_time, end_time) sql = ''' SELECT M.SecuCode, S.SuspendDate, S.ResumptionDate FROM LC_SuspendResumption S, SecuMain M WHERE S.InnerCode = M.InnerCode AND M.SecuMarket in (83, 90) AND (S.ResumptionDate >= \'{start_time: %Y-%m-%d}\' OR s.ResumptionDate = '1900-01-01') AND S.suspenddate <= \'{end_time: %Y-%m-%d}\' AND M.SecuCategory = 1 ORDER BY M.Secucode ASC, S.SuspendDate ASC '''.format(start_time=start_time, end_time=end_time) data = fetch_db_data(jydb, sql, ['symbol', 'suspend_date', 'resumption_date']) data.symbol = data.symbol.apply(add_stock_suffix) tds = get_calendar('stock.sse').get_tradingdays(start_time, end_time) def process_per_symbol(df): state = {} for _, line in df.iterrows(): _, start, end = line if start < end: # 正常超过一个交易日的停牌 state[start] = 1 state[end] = 0 elif end == pd.to_datetime('1900-01-01'): # 停牌至计算日还未复牌 state[start] = 1 else: # 日内停牌 state[start] = 2 state = pd.Series(state).sort_index() return state data = data.groupby('symbol').apply(process_per_symbol).unstack().T data = data.fillna(method='ffill') data = map2td(data, tds) latest_td = get_calendar('stock.sse').latest_tradingday(end_time, 'PAST') universe = sorted( pitcache_getter('UNIVERSE', 10).get_csdata(latest_td).index) data = data.reindex(columns=universe).fillna(0) if not check_completeness(data.index, start_time, end_time): raise ValueError('Data missed!') return data
def ttm_db(symbol, date, data_name, tab_name): # 从SQL数据库中取出ttm数据 # symbol: 测试股票代码,带后缀 # date: 测试数据的时间 # data_name: 数据名称(通过聚源查询) # tab_name: 表名称,仅支持[LC_IncomeStatementAll, LC_CashFlowStatementAll] symbol = drop_suffix(symbol) date = trans_date(date) sql = ''' SELECT m.SecuCode, s.{data}, s.enddate, s.infopubldate FROM (SELECT EndDate, InfoPublDate, {data}, CompanyCode, BulletinType, IfMerged, ROW_NUMBER() OVER(PARTITION BY COMPANYCODE, ENDDATE ORDER BY INFOPUBLDATE DESC) as rnum FROM {tab_name} WHERE BulletinType != 10 AND InfoPublDate < \'{date:%Y-%m-%d}\' AND IfMerged = 1 ) s, SecuMain M where s.rnum = 1 AND S.CompanyCode = M.CompanyCode AND M.SecuCode = \'{symbol}\' AND M.SecuCategory = 1 AND M.SecuMarket IN (83, 90) AND S.EndDate >= (SELECT TOP(1) S2.CHANGEDATE FROM LC_ListStatus S2 WHERE S2.INNERCODE = M.INNERCODE AND S2.ChangeType = 1) ORDER BY S.EndDate ASC'''.format(data=data_name, symbol=symbol, date=date, tab_name=tab_name) data = fetch_db_data(jydb, sql, ['symbol', 'data', 'rpt_date', 'update_time'], {'data': 'float64'}) # pdb.set_trace() if len(data) <= 4 or get_calendar('stock.sse').count( data.iloc[-1, 3], date) > 120: if len(data) == 4: if data['rpt_date'].iloc[0].month != 3: return np.nan else: return np.nan raw_data = data.set_index('rpt_date').data seasonly_data = raw_data - raw_data.shift(1) seasonly_data.loc[seasonly_data.index.month == 3] = np.nan seasonly_data = seasonly_data.fillna(raw_data) return seasonly_data.iloc[-4:].sum()
def bs_shift_db_year(symbol, date, offset, data_name, tab_name='LC_BalanceSheetAll'): # 按年度进行偏移的资产负债表数据 # 计算资产负债表偏移后的数据 # symbol: 股票代码 # date: 测试数据时间 # data_name: 数据名称(查询聚源) # offset: 数据时间偏移量,以季度为单位 # tab_name: 报表名称,默认为资产负债表 symbol = drop_suffix(symbol) date = trans_date(date) sql = ''' SELECT m.SecuCode, s.{data}, s.enddate, s.infopubldate FROM (SELECT EndDate, InfoPublDate, {data}, CompanyCode, BulletinType, IfMerged, ROW_NUMBER() OVER(PARTITION BY COMPANYCODE, ENDDATE ORDER BY INFOPUBLDATE DESC) as rnum FROM {tab_name} WHERE BulletinType != 10 AND InfoPublDate < \'{date:%Y-%m-%d}\' AND IfMerged = 1 ) s, SecuMain M where s.rnum = 1 AND S.CompanyCode = M.CompanyCode AND M.SecuCode = \'{symbol}\' AND M.SecuCategory = 1 AND M.SecuMarket IN (83, 90) AND S.EndDate >= (SELECT TOP(1) S2.CHANGEDATE FROM LC_ListStatus S2 WHERE S2.INNERCODE = M.INNERCODE AND S2.ChangeType = 1) ORDER BY S.EndDate ASC '''.format(symbol=symbol, data=data_name, date=date, tab_name=tab_name) data = fetch_db_data(jydb, sql, ['symbol', 'data', 'rpt_date', 'update_time'], {'data': 'float64'}) if len(data) < 1: # 没有发不过年报 return np.nan data = data.loc[data.rpt_date.dt.month == 12] if len(data) < offset or get_calendar('stock.sse').count( data.iloc[-1, 3], date) > 120: return np.nan # pdb.set_trace() return data.iloc[-offset, 1]
def get_universe(start_time, end_time): sql = ''' SELECT SecuCode FROM SecuMain WHERE SecuCategory = 1 AND SecuMarket in (83, 90) AND ListedState != 9 ''' if not check_jydb_update_state(end_time): raise ValueError('JYDB has not been updated!') tds = get_calendar('stock.sse').get_tradingdays(start_time, end_time) data = fetch_db_data(jydb, sql, ['symbol']) symbols = sorted(add_stock_suffix(s) for s in data.symbol) out = DataFrame(1, index=tds, columns=symbols) return out
def inner(start_time, end_time): start_time, end_time = trans_date(start_time, end_time) start_time_shift = get_calendar('stock.sse').shift_tradingdays(start_time, -180) nonlocal sql sql = sql.format(code=index_symbol, start_time=start_time_shift, end_time=end_time) data = fetch_db_data(jydb, sql, ['symbol', 'weight', 'time'], dtypes={'weight': 'float64'}) data.symbol = data.symbol.apply(add_stock_suffix) tds = get_calendar('stock.sse').get_tradingdays(start_time, end_time) data = data.pivot_table('weight', index='time', columns='symbol') data = map2td(data, tds) last_td = get_calendar('stock.sse').latest_tradingday(end_time, 'PAST') universe = sorted(pitcache_getter('UNIVERSE', 10).get_csdata(last_td).index) data = data.reindex(columns=universe) if not check_completeness(data.index, start_time, end_time): raise ValueError('Error, data missed!') return data
def cshift_db(symbol, date, data_name, tab_name, offset): # 计算流量类报表偏移后的数据 # symbol: 股票代码 # date: 测试数据时间 # data_name: 数据名称(查询聚源) # tab_name: 报表名称(查询聚源) # offset: 数据时间偏移量,以季为单位 symbol = drop_suffix(symbol) date = trans_date(date) sql = ''' SELECT m.SecuCode, s.{data}, s.enddate, s.infopubldate FROM (SELECT EndDate, InfoPublDate, {data}, CompanyCode, BulletinType, IfAdjusted, IfMerged, ROW_NUMBER() OVER(PARTITION BY COMPANYCODE, ENDDATE ORDER BY INFOPUBLDATE DESC) as rnum FROM {tab_name} WHERE BulletinType != 10 AND InfoPublDate < \'{date:%Y-%m-%d}\' AND IfAdjusted NOT IN (4, 5) AND IfMerged = 1) s, SecuMain M where s.rnum = 1 AND S.CompanyCode = M.CompanyCode AND M.SecuCode = \'{symbol}\' AND M.SecuCategory = 1 AND M.SecuMarket IN (83, 90) AND S.EndDate >= (SELECT TOP(1) S2.CHANGEDATE FROM LC_ListStatus S2 WHERE S2.INNERCODE = M.INNERCODE AND S2.ChangeType = 1) ORDER BY S.EndDate ASC'''.format(data=data_name, symbol=symbol, date=date, tab_name=tab_name) data = fetch_db_data(jydb, sql, ['symbol', 'data', 'rpt_date', 'update_time'], {'data': 'float64'}) # pdb.set_trace() if len(data) < offset or get_calendar('stock.sse').count( data.iloc[-1, 3], date) > 120: return np.nan raw_data = data.set_index('rpt_date').data seasonly_data = raw_data - raw_data.shift(1) seasonly_data.loc[seasonly_data.index.month == 3] = np.nan seasonly_data = seasonly_data.fillna(raw_data) return seasonly_data.iloc[-offset]
def inner(start_time, end_time): start_time, end_time = trans_date(start_time, end_time) nonlocal sql sql = sql.format(start_time=start_time, end_time=end_time) data = fetch_db_data(jydb, sql, cols, dtypes=dtypes) data.symbol = data.symbol.apply(add_stock_suffix) if len(cols) == 4: # 当前数据为需要使用前收盘填充的数据 data.loc[data.data == 0, 'data'] = data['prevclose'] data = data.drop('prevclose', axis=1) data = data.pivot_table('data', index='time', columns='symbol') latest_td = get_calendar('stock.sse').latest_tradingday( end_time, 'PAST') universe = sorted( pitcache_getter('UNIVERSE', 10).get_csdata(latest_td).index) data = data.reindex(columns=universe).sort_index(ascending=True) if not check_completeness(data.index, start_time, end_time): raise ValueError('Data missed!') return data
def inner(start_time, end_time): start_time, end_time = trans_date(start_time, end_time) if not check_jydb_update_state(end_time): raise ValueError('JYDB has not been updated!') sql = ''' SELECT S.{field}, S.TradingDay FROM QT_IndexQuote S, SecuMain M WHERE S.InnerCode = M.InnerCode AND M.SecuCode = \'{symbol}\' AND M.SecuCategory = 4 AND S.TradingDay >= \'{start_time:%Y-%m-%d}\' AND S.TradingDay <= \'{end_time:%Y-%m-%d}\' ORDER BY S.TradingDay ASC '''.format(symbol=index_symbol, start_time=start_time, end_time=end_time, field=field) data = fetch_db_data(jydb, sql, ['data', 'time'], dtypes={'data': 'float64'}) data = data.set_index('time').data if (start_time != trans_date(DATA_START_DATE) and not check_completeness(data.index, start_time, end_time)): raise ValueError('Data Missed!') return data
def get_db_update_time(): ''' 以指数行情作为标志获取聚源数据库中最新数据的日期 Return ------ date: datetime like 更新时间,如果数据库没有数据,返回时间为1900-01-01 ''' sql = ''' SELECT S.XGRQ FROM QT_IndexQuote S, SecuMain M WHERE S.InnerCode = M.InnerCode AND M.SecuCode = '000001' AND M.SecuCategory = 4 AND S.TradingDay >= '2007-01-01' ORDER BY S.XGRQ ASC ''' data = fetch_db_data(jydb, sql, ['date']) if len(data) <= 0: return trans_date('1900-01-01') else: return data.date.iloc[-1]
def get_st_status(start_time, end_time): ''' 获取股票特殊处理的情况 ''' sql = ''' SELECT S.SpecialTradeTime, S.SecurityAbbr, C.MS, M.SecuCode FROM LC_SpecialTrade S, SecuMain M, CT_SystemConst C WHERE S.InnerCode = M.InnerCode AND M.SecuMarket in (83, 90) AND S.SpecialTradeType = C.DM AND C.LB = 1185 AND M.SecuCategory = 1 ''' data = fetch_db_data(jydb, sql, ['time', 'abbr', 'ms', 'symbol']) def _assign_st(row): map_dict = { 'ST': 1., 'PT': 5., '撤销ST': 0., '*ST': 2., '撤消*ST并实行ST': 1., '从ST变为*ST': 2., '撤销*ST': 0., '退市整理期': 3., '高风险警示': 4. } if row.ms in map_dict: return map_dict[row.ms] else: assert row.ms == '撤销PT', "Error, cannot handle tag '{tag}'".format( tag=row.ms) if 'ST' in row.abbr: return 1 elif '*ST' in row.abbr: return 2 else: return 0 data = data.assign(tag=lambda x: x.apply(_assign_st, axis=1)) data['symbol'] = data.symbol.apply(add_stock_suffix) # 剔除日期重复项,因为数字越大表示越风险越高,因而只保留数字大的 data = data.sort_values(['symbol', 'time', 'tag']) by_snt = data.groupby(['symbol', 'time']) data = by_snt.tail(1) data = data.reset_index(drop=True) tds = get_calendar('stock.sse').get_tradingdays(start_time, end_time) by_symbol = data.groupby('symbol') data = by_symbol.apply(map2td, days=tds, timecol='time', fillna={'symbol': lambda x: x.symbol.iloc[0]}) data = data.pivot_table('tag', index='time', columns='symbol').dropna(axis=0, how='all') last_td = get_calendar('stock.sse').latest_tradingday(end_time, 'PAST') universe = sorted( pitcache_getter('UNIVERSE', 10).get_csdata(last_td).index) data = data.reindex(columns=universe).fillna(0) if not check_completeness(data.index, start_time, end_time): raise ValueError('Error, data missed!') return data