<jupyter_start><jupyter_text>Primary Stats Rates.R<jupyter_code>td_str = '20170808'
td = datetime.datetime.strptime(td_str, '%Y%m%d')
print td
sql_query = "".join(["SELECT   c.*, d.S_INFO_WINDCODE, d.TRADE_DT, d.B_ANAL_YIELD_CNBD FROM( ",
               "SELECT ifnull(a.S_INFO_FORMERWINDCODE, a.S_INFO_WINDCODE) AS ticker, a.S_INFO_WINDCODE, a.S_INFO_NAME, ",
               "a.B_INFO_TERM_YEAR_, a.B_INFO_ISSUERCODE, a.B_INFO_CARRYDATE, a.B_ISSUE_FIRSTISSUE, ",
               "a.B_ISSUE_AMOUNTPLAN, a.IS_FAILURE, a.B_INFO_LISTDATE, ",
               "a.B_INFO_MATURITYDATE, a.B_INFO_COUPONRATE, b.S_IPO_OVRSUBRATIO, ",
               "b.B_TENDER_OBJECT, b.B_TENDRST_HIGHTEST, b.B_TENDRST_LOWEST, ",
               "b.B_TENDRST_FINALPRICE, ifnull(b.B_TENDRST_REFERYIELD, b.B_TENDRST_BIDRATE) AS yield, ",
               "b.B_TENDRST_OUGHTTENDER, b.B_TENDRST_INVESTORTENDERED, b.B_TENDRST_WINNERBIDDER, ",
               "b.B_TENDRST_EFFECTTENDER, b.B_TENDRST_WINNINGBIDDER ",
               "FROM CBONDDESCRIPTION a LEFT JOIN CBONDTENDERRESULT b ON a.S_INFO_WINDCODE = b.S_INFO_WINDCODE ",
               "WHERE a.B_INFO_ISSUERTYPE IN ('财政部', '政策性银行') ",
               "AND a.B_INFO_COUPON <> '505003000' ",
               "AND a.B_INFO_INTERESTTYPE = '501002000'",
               "AND a.B_INFO_SPECIALBONDTYPE IS NULL ",
               "AND a.B_INFO_LISTDATE IS NOT NULL ",
               "AND a.B_ISSUE_FIRSTISSUE > '20130101' ",
               "AND a.S_INFO_EXCHMARKET = 'NIB' ",
               # "AND a.S_INFO_WINDCODE LIKE '140016%' ",
               ") c LEFT JOIN CBONDANALYSISCNBD d ",
               "ON c.ticker = d.S_INFO_WINDCODE ",
               "AND date_add(str_to_date(c.B_ISSUE_FIRSTISSUE, '%Y%m%d'), INTERVAL 3 MONTH) > str_to_date(d.TRADE_DT, '%Y%m%d') ",
               "AND date_sub(str_to_date(c.B_ISSUE_FIRSTISSUE, '%Y%m%d'), INTERVAL 1 MONTH) <= str_to_date(d.TRADE_DT, '%Y%m%d') ",
               "AND d.B_ANAL_CREDIBILITY = '推荐' "])

data = pd.read_sql(sql_query, connection)
my_columns = ["px.ticker","id.ticker","name","term","issuer","dt.carry", "dt.issue","amt.issue","failure","dt.list","dt.mature",
                   "cpn","over.sub.ratio","td.type","td.high","td.low","td.px","td.yld","bidder.max","bidder.in","bidder.won",
                   "bids.effect","bids.win","ref.ticker","ref.dt","ref.yld"]
data.columns = my_columns
print data.shape
print data.dtypes
data.head()
def cleanRatesData(df):
    df.loc[:, 'reopen'] = (df.loc[:, 'px.ticker'] == df.loc[:, 'id.ticker']).astype(int)
    for col in ['dt.issue', 'dt.list', 'dt.mature', 'ref.dt']:
        df.loc[:, col] = pd.to_datetime(df.loc[:, col], format="%Y%m%d")
    
    # seperate bond info and bond price  
    bond_info_columns = [col for col in list(df.columns) if col not in ['ref.ticker', 'ref.dt', 'ref.yld'] ]
    bond_info = df.loc[:, bond_info_columns].copy()
    bond_info = bond_info.drop_duplicates()
    
    # regroup bond price by term and issuer, rather than by ticker
    bond_px_columns = ['issuer', 'term', 'px.ticker', 'ref.dt', 'ref.yld']
    bond_px = df.loc[:, bond_px_columns].copy()
    bond_px.columns = ['issuer', 'term', 'ref.ticker', 'ref.dt', 'ref.yld']
    
    mask_null = np.logical_or(df.loc[:, 'ref.dt'].isnull(), df.loc[:, 'ref.yld'].isnull())
    mask_null = np.logical_not(mask_null)
    bond_px = bond_px.loc[mask_null, :] # get the price history
    bond_px = bond_px.drop_duplicates() 
    #bond_px = bond_px.sort_index(axis=0, by='ref.ticker')
    grouped = bond_px.groupby(by=['issuer', 'term', 'ref.dt'], as_index=False) # select only the most recent issue for each issuer / term / date
    bond_px = grouped.head(n=1) #TODO
    print "Sort top n by ref.ticker???"
    return df, bond_info, bond_px

data_x, bond_info, bond_px = cleanRatesData(data)
# rejoin bond info with bond price, by term and issuer
data_join = pd.merge(bond_info, bond_px, how='outer', left_on=['issuer', 'term'], right_on=['issuer', 'term'])# join yld history by issuer and term
data_join = data_join.loc[data_join.loc[:, 'dt.issue'] > data_join.loc[:, 'ref.dt'], :] # limit range to 30 days before issue
grouped = data_join.groupby(by='id.ticker', as_index=False)
data_join = grouped.head(1)
data_join.loc[:, 'dt.diff'] = data_join.loc[:, 'dt.issue'] - data_join.loc[:, 'ref.dt']
data_join = data_join.sort_index(axis=0, by='id.ticker')
print data_join.shape
data_join.head()
def get_bond_upcoming(larger, smaller):
    s1 = set(larger.loc[:, 'id.ticker'].drop_duplicates())
    s2 = set(smaller.loc[:, 'id.ticker'].drop_duplicates())
    return list(s1.difference(s2))
bond_upcoming = get_bond_upcoming(data_x, data_join)
mask = bond_info.loc[:, 'id.ticker'].apply(lambda x: x in bond_upcoming)
data_x = pd.concat([bond_info.loc[mask, :], data_join], axis=0)
print data_x.shape
def firstDayOfWeek(dt):
    t = dt - datetime.timedelta(dt.weekday())
    return datetime.date(t.year, t.month, t.day)
    
def firstDayOfMonth(dt):
    t = dt.replace(day=1)
    return datetime.date(t.year, t.month, t.day)
def map_term_to_sector(t):
    if t <= 3:
        res = "Short"
    else:
        if t <= 7:
            res = "Mid"
        elif t == 10:
            res = "Ten"
        else:
            res = "Long"
    return res

RATES_ISSUER_DICT = {"2000850": u"国债", "2002700": u"国开", "0MT64BBFB6": u"口行", "04M5F620A3": u"农发"}

def process_data_x(df):
    df = df.copy()
    df.loc[:, 'dur.impact'] = df.loc[:, 'amt.issue'] * df.loc[:, 'term'] / 10.
    df.loc[:, 'term.remain'] = (df.loc[:, 'dt.mature'] - df.loc[:, 'dt.list']) / np.timedelta64(1,'D') /365.25 # remaining term in years
    df.loc[:, 'through'] = (df.loc[:, 'ref.yld'] - df.loc[:, 'td.yld']) * 100.
    df.loc[:, 'dt.month'] = df.loc[:, 'dt.issue'].apply(firstDayOfMonth)
    df.loc[:, 'dt.week'] = df.loc[:, 'dt.issue'].apply(firstDayOfWeek)
    df.loc[:, 'sector'] = df.loc[:, 'term'].apply(map_term_to_sector)
    df.loc[:, 'issuer.name'] = df.loc[:, 'issuer'].apply(lambda key: RATES_ISSUER_DICT[key])
    
    return df

data_x = process_data_x(data_x)
print data_x.shape
data_x.head()
data_plot_columns = ["dt.issue", "dt.week", "dt.month", "term", "sector", "issuer.name", "amt.issue", "dur.impact"]
data_plot = data_x.loc[:, data_plot_columns].copy()
print data_plot.shape
data_plot.head()
data_plot_melt = pd.melt(data_plot, id_vars=["dt.issue", "dt.week", "dt.month", "term", "sector", "issuer.name"],)
print data_plot_melt.shape
data_plot_melt.head()
#ISSUER_RANK = [u"国债", u"国开", u"口行", u"农发"]
ISSUER_RANK = collections.OrderedDict([(u"国债", 1), (u"国开", 2), (u"口行", 3), (u"农发", 4)])

def get_data_new(df):
    df = df.copy()
    
    mask = df.loc[:, 'dt.issue'] >= td
    cols = ["dt.issue", "issuer.name", "term", "amt.issue", "id.ticker"]
    df = df.loc[mask, cols]
    
    df.loc[:, 'weekday'] = df.loc[:, 'dt.issue'].apply(lambda dt: dt.weekday() + 1)
    
    #df.loc[:, 'issuer.name'] = pd.Categorical(df.loc[:, 'issuer.name'], ISSUER_RANK)
    df.loc[:, 'rank'] = df.loc[:, 'issuer.name'].apply(lambda key: ISSUER_RANK.get(key))
    df = df.sort(columns=['dt.issue', 'rank', 'term'])
    df = df.drop('rank', axis=1)
    return df

data_new = get_data_new(data_x)
print data_new.shape
data_new.head()
RATES_RECENT_DAYS_RANGE = 7
def get_data_recent(df):
    df = df.copy()
    
    mask = np.logical_and(df.loc[:, 'dt.issue'] < td, 
                          df.loc[:, 'dt.issue'] >= td-datetime.timedelta(RATES_RECENT_DAYS_RANGE))
    cols = ["dt.issue", "issuer.name", "term", "amt.issue", "id.ticker"]
    df = df.loc[mask, cols]
    
    df.loc[:, 'weekday'] = df.loc[:, 'dt.issue'].apply(lambda dt: dt.weekday() + 1)
    
    #df.loc[:, 'issuer.name'] = pd.Categorical(df.loc[:, 'issuer.name'], ISSUER_RANK)
    df.loc[:, 'rank'] = df.loc[:, 'issuer.name'].apply(lambda key: ISSUER_RANK.get(key))
    df = df.sort(columns=['dt.issue', 'rank', 'term'])
    df = df.drop('rank', axis=1)
    return df

data_recent = get_data_recent(data_x)
print data_recent.shape
data_recent.head()
TERM_RANK = ["Short", "Mid", "Ten", "Long"]<jupyter_output><empty_output><jupyter_text>Total Issuance-  monthly<jupyter_code>grouped = data_plot.groupby(by=['dt.month', 'sector'], as_index=False)
grouped.agg({'amt.issue': np.sum, 'dur.impact': np.sum})
SECTOR_COLOR_MAP.keys()
SECTOR_COLOR_MAP = collections.OrderedDict([('Long', 'violet'), ('Ten', 'c'),
                                            ('Mid', 'mediumseagreen'), ('Short', 'salmon')])

def get_factors(df, yname, idxname, factorname):
    idx = np.sort(df.loc[:, idxname].drop_duplicates())
    df.index = pd.DatetimeIndex(df.loc[:, idxname])
    res_df = pd.DataFrame(index=idx, columns=SECTOR_COLOR_MAP.keys())
    
    for factor in SECTOR_COLOR_MAP.keys():
        mask = df.loc[:, factorname] == factor
        source = df.loc[mask, :]
        res_df.loc[source.index, factor] = source.loc[:, yname].values
    res_df = res_df.fillna(0)
    return res_df

def plot_total_issuance(df):
    df = df.copy()
    df.index = df.loc[:, 'dt.month']
    
    grouped = df.groupby(by=['dt.month', 'sector'], as_index=False)
    monthly_sum = grouped.agg({'amt.issue': np.sum, 'dur.impact': np.sum})
    
    mask = np.logical_and(monthly_sum.loc[:, 'dt.month'] >= datetime.date(2013, 1, 1), 
                          monthly_sum.loc[:, 'dt.month'] < datetime.date(td.year, td.month, td.day) + datetime.timedelta(14))
    monthly_sum = monthly_sum.loc[mask, :]
    
    df_issue_plot = get_factors(monthly_sum, 'amt.issue', 'dt.month', 'sector')
    df_impact_plot = get_factors(monthly_sum, 'dur.impact', 'dt.month', 'sector')
    
    fig, (ax1, ax2) = plt.subplots(2, 1, sharex=True, figsize=(30, 22), dpi=300)
    common_styles = {'edgecolor': '', 'width': 24.0}
    
    last_values = np.zeros(df_issue_plot.shape[0], dtype=float)
    for sector, color in SECTOR_COLOR_MAP.iteritems():
        ax1.bar(df_issue_plot.index, df_issue_plot.loc[:, sector], bottom=last_values,
                color=color, label=sector, **common_styles)
        last_values += df_issue_plot.loc[:, sector].values
    ax1.legend(loc='upper right')
    
    last_values = np.zeros(df_impact_plot.shape[0], dtype=float)
    for sector, color in SECTOR_COLOR_MAP.iteritems():
        ax2.bar(df_impact_plot.index, df_impact_plot.loc[:, sector], bottom=last_values,
                color=color, label=sector, **common_styles)
        last_values += df_impact_plot.loc[:, sector].values
    
    ax2.xaxis.set_ticks(pd.date_range(start=df_impact_plot.index[0], end=df_impact_plot.index[-1], freq='3M'))
    ax2.xaxis.set_major_formatter(mdates.DateFormatter('%Y%m'))
    
    
    ax2.set_xlabel(u"月份", fontproperties=zhfont)
    ax1.set_ylabel(u"值", fontproperties=zhfont)
    plt.suptitle(u"总发行 - 每月", fontproperties=zhfont)
    ax1.set_title(u"发行量", fontproperties=zhfont)
    ax2.set_title(u"久期影响", fontproperties=zhfont)
#     ax2.set_xlabel("dt.month")
#     ax1.set_ylabel("value")
#     plt.suptitle("Total Issuance - monthly", fontproperties=zhfont)
#     ax1.set_title("amt.issue")
#     ax2.set_title("dur.impact")
    return fig

fig = plot_total_issuance(data_plot)
fig.savefig('RATES_1.png', bbox_inches='tight')<jupyter_output><empty_output><jupyter_text>Total Issuance - weekly<jupyter_code>def plot_total_issuance_week(df):
    df = df.copy()
    df.index = df.loc[:, 'dt.week']
    
    grouped = df.groupby(by=['dt.week', 'sector'], as_index=False)
    weekly_sum = grouped.agg({'amt.issue': np.sum, 'dur.impact': np.sum})
    
    mask = np.logical_and(weekly_sum.loc[:, 'dt.week'] >= datetime.date(td.year, td.month, td.day) - datetime.timedelta(420), 
                          weekly_sum.loc[:, 'dt.week'] < datetime.date(td.year, td.month, td.day) + datetime.timedelta(14))
    weekly_sum = weekly_sum.loc[mask, :]
    
    df_issue_plot = get_factors(weekly_sum, 'amt.issue', 'dt.week', 'sector')
    df_impact_plot = get_factors(weekly_sum, 'dur.impact', 'dt.week', 'sector')
    
    fig, (ax1, ax2) = plt.subplots(2, 1, sharex=True, figsize=(30, 22), dpi=300)
    common_styles = {'edgecolor': '', 'width': 6.0}
    
    last_values = np.zeros(df_issue_plot.shape[0], dtype=float)
    for sector, color in SECTOR_COLOR_MAP.iteritems():
        ax1.bar(df_issue_plot.index, df_issue_plot.loc[:, sector], bottom=last_values,
                color=color, label=sector, **common_styles)
        last_values += df_issue_plot.loc[:, sector].values
    ax1.set_ylabel(u"值", fontproperties=zhfont)
    
    last_values = np.zeros(df_impact_plot.shape[0], dtype=float)
    for sector, color in SECTOR_COLOR_MAP.iteritems():
        ax2.bar(df_impact_plot.index, df_impact_plot.loc[:, sector], bottom=last_values,
                color=color, label=sector, **common_styles)
        last_values += df_impact_plot.loc[:, sector].values
    ax2.set_ylabel(u"值", fontproperties=zhfont)
    
    
    ax2.xaxis.set_ticks(pd.date_range(start=df_impact_plot.index[0], end=df_impact_plot.index[-1], freq='3W'))
    ax2.xaxis.set_major_formatter(mdates.DateFormatter('%Y%m'))
    
    ax1.legend(loc='upper right')
    ax2.set_xlabel(u"星期", fontproperties=zhfont)
    
    plt.suptitle(u"总发行 - 每周", fontproperties=zhfont)
    ax1.set_title(u"发行量", fontproperties=zhfont)
    ax2.set_title(u"久期影响", fontproperties=zhfont)
#     ax2.set_xlabel("dt.week")
#     ax1.set_ylabel("value")
#     plt.suptitle("Total Issuance - weekly", fontproperties=zhfont)
#     ax1.set_title("amt.issue")
#     ax2.set_title("dur.impact")
    return fig

fig = plot_total_issuance_week(data_plot)
fig.savefig('RATES_2.png', bbox_inches='tight')<jupyter_output><empty_output><jupyter_text>Total Issuance Amt - weekly by Issuer<jupyter_code>data_plot.head()
def plot_total_issue_amt_week_issuer(df):
    df = df.copy()
    df.index = df.loc[:, 'dt.week']
    
    grouped = df.groupby(by=['dt.week', 'sector', 'issuer.name'], as_index=False)
    weekly_sum = grouped.agg({'amt.issue': np.sum, 'dur.impact': np.sum})
    
    mask = np.logical_and(weekly_sum.loc[:, 'dt.week'] >= datetime.date(td.year, td.month, td.day) - datetime.timedelta(420), 
                          weekly_sum.loc[:, 'dt.week'] < datetime.date(td.year, td.month, td.day) + datetime.timedelta(14))
    weekly_sum = weekly_sum.loc[mask, :]
    
    df_issuers = {}
    for issuer in ISSUER_RANK.keys():
        df_issuer = weekly_sum.loc[weekly_sum.loc[:, 'issuer.name'] == issuer, :]
        df_issuers[issuer] = get_factors(df_issuer, 'amt.issue', 'dt.week', 'sector')
    
    fig, axes = plt.subplots(len(ISSUER_RANK.keys()), 1, sharex=True, figsize=(30, 22), dpi=300)
    common_styles = {'edgecolor': '', 'width': 6.0}
    
    i = 3
    for issuer, df_plot in df_issuers.iteritems():
        last_values = np.zeros(df_plot.shape[0], dtype=float)
        for sector, color in SECTOR_COLOR_MAP.iteritems():
            axes[i].bar(df_plot.index, df_plot.loc[:, sector], bottom=last_values,
                        color=color, label=sector, **common_styles)
            last_values += df_plot.loc[:, sector].values
        axes[i].set_ylabel(u"值", fontproperties=zhfont)
        axes[i].set_title(issuer, fontproperties=zhfont)
        i -= 1
    
    axes[-1].xaxis.set_ticks(pd.date_range(start=df_plot.index[0], end=df_plot.index[-1], freq='3W'))
    axes[-1].xaxis.set_major_formatter(mdates.DateFormatter('%m%d'))
    axes[0].legend(loc='upper right')
    #axes[-1].set_xlabel("dt.week")
    axes[-1].set_xlabel(u"星期", fontproperties=zhfont)
    plt.suptitle(u"总发行 - 每周", fontproperties=zhfont)
    #plt.suptitle(u"Total Issuance - weekly", fontproperties=zhfont)
    return fig

fig = plot_total_issue_amt_week_issuer(data_plot)
fig.savefig('RATES_3.png', bbox_inches='tight')<jupyter_output><empty_output><jupyter_text>Total Issuance Duration - weekly by Issuer<jupyter_code>def plot_total_dur_impact_week_issuer(df):
    df = df.copy()
    df.index = df.loc[:, 'dt.week']
    
    grouped = df.groupby(by=['dt.week', 'sector', 'issuer.name'], as_index=False)
    weekly_sum = grouped.agg({'amt.issue': np.sum, 'dur.impact': np.sum})
    
    mask = np.logical_and(weekly_sum.loc[:, 'dt.week'] >= datetime.date(td.year, td.month, td.day) - datetime.timedelta(420), 
                          weekly_sum.loc[:, 'dt.week'] < datetime.date(td.year, td.month, td.day) + datetime.timedelta(14))
    weekly_sum = weekly_sum.loc[mask, :]
    
    df_issuers = {}
    for issuer in ISSUER_RANK.keys():
        df_issuer = weekly_sum.loc[weekly_sum.loc[:, 'issuer.name'] == issuer, :]
        df_issuers[issuer] = get_factors(df_issuer, 'dur.impact', 'dt.week', 'sector')
    
    fig, axes = plt.subplots(len(ISSUER_RANK.keys()), 1, sharex=True, figsize=(30, 22), dpi=300)
    common_styles = {'edgecolor': '', 'width': 6.0}
    
    i = 3
    for issuer, df_plot in df_issuers.iteritems():
        last_values = np.zeros(df_plot.shape[0], dtype=float)
        for sector, color in SECTOR_COLOR_MAP.iteritems():
            axes[i].bar(df_plot.index, df_plot.loc[:, sector], bottom=last_values,
                        color=color, label=sector, **common_styles)
            last_values += df_plot.loc[:, sector].values
        axes[i].set_ylabel(u"值", fontproperties=zhfont)
        axes[i].set_title(issuer, fontproperties=zhfont)
        i -= 1
    
    axes[-1].xaxis.set_ticks(pd.date_range(start=df_plot.index[0], end=df_plot.index[-1], freq='3W'))
    axes[-1].xaxis.set_major_formatter(mdates.DateFormatter('%m%d'))
    axes[0].legend(loc='upper right')
    #axes[-1].set_xlabel("dt.week")
    axes[-1].set_xlabel(u"星期", fontproperties=zhfont)
    #plt.suptitle("Total Issuance - weekly", fontproperties=zhfont)
    plt.suptitle(u"总发行 - 每周", fontproperties=zhfont)
    return fig

fig = plot_total_dur_impact_week_issuer(data_plot)
fig.savefig('RATES_4.png', bbox_inches='tight')<jupyter_output><empty_output><jupyter_text>Bid-Cover ratio by Sector<jupyter_code>grouped = data_x.groupby(by='sector')
grouped.get_group('Long')
def bp_set_box(elem, box_color='red'):
    elem.set_color('white')
    elem.set_edgecolor(box_color)
    elem.set_linewidth(1)

def bp_set_median(elem, box_color='red'):
    elem.set_color(box_color)
    elem.set_linewidth(4)

def bp_set_whisker(elem, box_color='red'):
    elem.set_linestyle('-')
    elem.set_color(box_color)
    elem.set_linewidth(1)

def bp_set_cap(elem):
    global box_color
    elem.set_alpha(0.0)

def plot_bc_ratio_sector(df, yname, title="", ynamecn=u""):
    df = df.copy()
    df.index = df.loc[:, 'dt.week']
    
    mask = df.loc[:, 'dt.week'] >= (datetime.date(td.year, td.month, td.day) - datetime.timedelta(240))
    df = df.loc[mask, :]
    
    ylim = df.loc[:, yname].min() * 0.98, df.loc[:, yname].max() * 1.02
    
    grouped = df.groupby(by='sector', as_index=False)
    
    sector_colors = ['salmon', 'mediumseagreen', 'dodgerblue']
    sector_selected = ['Short', 'Mid', 'Ten']
    fig, axes = plt.subplots(len(sector_selected), 1, sharex=True, figsize=(30, 22), dpi=300)
    common_styles = {'sym': '+', 'vert': True, 'patch_artist': True}
    
    for i, sector in enumerate(sector_selected):
        df_plot = grouped.get_group(sector)
        df_plot = df_plot.loc[:, ['dt.week', yname]]
        grouped2 = df_plot.groupby(by='dt.week', as_index=False)
        
        box_data = []
        for week, idx in grouped2.groups.iteritems():
            box_data.append(df_plot.loc[idx, yname].values)
        
        bp_res = axes[i].boxplot(box_data, **common_styles)
        
        box_color = sector_colors[i]
        map(lambda x: bp_set_box(x, box_color), bp_res['boxes'])
        map(lambda x: bp_set_median(x, box_color), bp_res['medians'])
        map(lambda x: bp_set_whisker(x, box_color), bp_res['whiskers'])
        map(bp_set_cap, bp_res['caps'])
        plt.setp(bp_res['fliers'], color='red', marker='+')
        
        axes[i].set_ylim(ylim)
        axes[i].set_title(u"期限: " + sector, fontproperties=zhfont)#TODOzhch期限
        axes[i].set_ylabel(ynamecn, fontproperties=zhfont)#TODOzhcn投标倍数
    
    weeks = map(lambda dt: dt.strftime("%Y%m%d"), grouped2.groups.keys())
    axes[-1].set_xticks(range(1, 34), ['lll']*33)
    axes[-1].set_xlabel(u"星期", fontproperties=zhfont)
    plt.suptitle(title, fontproperties=zhfont)#TODOzhcn
    return fig

fig = plot_bc_ratio_sector(data_x, 'over.sub.ratio', u"投标倍数 - 按期限分类", u"投标倍数")#"Bid Cover Ratio by Sector")
fig.savefig('RATES_5.png', bbox_inches='tight')<jupyter_output><empty_output><jupyter_text>Bid-Cover ratio by Issuer<jupyter_code>def plot_bc_ratio_issuer(df, yname, title="", ynamecn=u""):
    df = df.copy()
    df.index = df.loc[:, 'dt.week']
    
    mask = df.loc[:, 'dt.week'] >= (datetime.date(td.year, td.month, td.day) - datetime.timedelta(240))
    df = df.loc[mask, :]
    
    ylim = df.loc[:, yname].min() * 0.98, df.loc[:, yname].max() * 1.02
    
    grouped = df.groupby(by='issuer.name', as_index=False)
    
    sector_colors = ['salmon', 'mediumseagreen', 'dodgerblue', 'violet']
    sector_selected = ISSUER_RANK.keys()
    fig, axes = plt.subplots(len(sector_selected), 1, sharex=True, figsize=(30, 22), dpi=300)
    common_styles = {'sym': '+', 'vert': True, 'patch_artist': True}
    
    for i, sector in enumerate(ISSUER_RANK.keys()):
        #print sector
        df_plot = grouped.get_group(sector)
        df_plot = df_plot.loc[:, ['dt.week', yname]]
        grouped2 = df_plot.groupby(by='dt.week', as_index=False)
        
        box_data = []
        for week, idx in grouped2.groups.iteritems():
            box_data.append(df_plot.loc[idx, yname].values)
        
        bp_res = axes[i].boxplot(box_data, **common_styles)
        
        box_color = sector_colors[i]
        map(lambda x: bp_set_box(x, box_color), bp_res['boxes'])
        map(lambda x: bp_set_median(x, box_color), bp_res['medians'])
        map(lambda x: bp_set_whisker(x, box_color), bp_res['whiskers'])
        map(bp_set_cap, bp_res['caps'])
        plt.setp(bp_res['fliers'], color='red', marker='+')
        
        axes[i].set_ylim(ylim )
        axes[i].set_title(u"发行主体: " + sector, fontproperties=zhfont)
        axes[i].set_ylabel(ynamecn, fontproperties=zhfont)#TODOzhcn
    
    weeks = map(lambda dt: dt.strftime("%Y%m%d"), grouped2.groups.keys())
    axes[-1].set_xticks(range(1, 34), ['lll']*33)
    axes[-1].set_xlabel(u"星期", fontproperties=zhfont)
    plt.suptitle(title, fontproperties=zhfont)#TODOzhcn
    return fig

fig = plot_bc_ratio_issuer(data_x, 'over.sub.ratio', u"投标倍数 - 按发行主体分类", u"投标倍数")#"Bid Cover Ratio by Issuer")
fig.savefig('RATES_6.png', bbox_inches='tight')<jupyter_output><empty_output><jupyter_text>Through-tail by Sector<jupyter_code>fig = plot_bc_ratio_sector(data_x, 'through', u"招标利率差值 - 按期限分类", u"差值")#TODOzhcn招标利率差值（实际- 市场）
fig.savefig('RATES_7.png', bbox_inches='tight')<jupyter_output><empty_output><jupyter_text>Through-tail by Issuer<jupyter_code>fig = plot_bc_ratio_issuer(data_x, 'through', u"招标利率差值 - 按发行主体分类", u"差值")#"Through/Tail by Issuer")#TODOzhcn
fig.savefig('RATES_8.png', bbox_inches='tight')
from reportlab.pdfgen import canvas
import reportlab.lib.pagesizes as rlps

mypagesize = rlps.A4[1], rlps.A4[0]

def calc_center(pagesize, fig):
    width, height = pagesize
    fw, fh = fig.get_size_inches() * 72.
    
    scale_ratio = width / fw * 0.95
    paint_w, paint_h = fw * scale_ratio, fh * scale_ratio
    x = (width - paint_w) / 2.
    return x, paint_w, paint_h

def draw_one_img_one_page(canvas, img_name, pagesize):
    fig_x, fig_width, fig_height = calc_center(pagesize, fig)
    canvas.drawImage(img_name, fig_x, 20, width=fig_width, height=fig_height)
    canvas.showPage()
    
def generate_PDF(fname='ex'):
    c = canvas.Canvas(fname+'.pdf', pagesize=mypagesize)
    
    from reportlab.pdfbase import pdfmetrics
    from reportlab.pdfbase.ttfonts import TTFont
    pdfmetrics.registerFont(TTFont('wqy', '/home/********/wqy-microhei.ttc'))

    c.setFont("wqy", 50)
    c.drawCentredString(mypagesize[0] / 2, mypagesize[1] * 4. / 5., u"利率债一级市场-大数据汇总")
    fig_width, fig_height = 756./3, 672./3
    c.drawImage("logo.png", (mypagesize[0] - fig_width) / 2., mypagesize[1] / 5, width=fig_width, height=fig_height)
    c.showPage()
    
    for i in range(1, 9):
        draw_one_img_one_page(c, "RATES_{}.png".format(i), mypagesize)
    
    c.save()
    return

generate_PDF("RATES_Stats_"+td_str)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.17162922501950797, 'fraction_numerical': 0.013758265226498008, 'mean_word_length': 3.664750957854406, 'pattern_counts': {'":': 4, '<': 46, '<?xml version=': 0, '>': 49, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '37570', 'license': 'CC0-1.0', 'n_tokens_mistral': 9917, 'n_tokens_neox': 9652, 'n_words': 1520, 'path': 'IPythonNotebooks_for_Research/complex_pandas_operations_and_visualizations.ipynb', 'repo_name': 'brillliantz/Quantitative_Finance', 'size': '24349'}