向前、向后填充的一个实战用法

最近有个需求：枚举0-10的编号，有些编号对应利率0.05，有些是0.1，但是当遇到编号9和10的时候，要看它上一个编号（如果还是9或者10那么还要网上找）。对于我这种只有循环思路的人来说，会觉得写起来有些麻烦，但是pandas很好的解决了这个问，fillna ffill瞬间代码量就小了下来。

同时也可以注意字典配合的map用法，简直不要太赞~~~

import numpy as np
import pandas as pd
from pymysql import connect


JY_CONN = None

def get_jy_conn():
    global JY_CONN
    if JY_CONN is None:
        JY_CONN = connect(host='192.168.0.1', user='YB', password='YJB', db='JYDB')
    return JY_CONN


def close_conn():
    global JY_CONN
    if JY_CONN is None:
        return
    JY_CONN.close()
    JY_CONN = None


def get_rate(order_book_id, factor_date):
    code, market = order_book_id[:6], order_book_id[7:]
    market = 90 if market == 'XSHE' else 83

    sql = """
        SELECT
            a.SpecialTradeTime as MDEntryDate,
            a.SpecialTradeType as rate
        FROM LC_SpecialTrade a
        INNER JOIN SecuMain b ON a.InnerCode = b.InnerCode
        WHERE b.SecuCode = '{}' AND b.SecuMarket = {} AND a.SpecialTradeTime <= '{}'
        ORDER BY a.SpecialTradeTime
    """.format(code, market, factor_date)
    df = pd.read_sql(sql, get_jy_conn())

    if df.empty:
        return 0.1
    else:
        type_map = {
            1: 0.05, 2: 0.1, 3: 0.05, 4: 0.1, 5: 0.05, 6: 0.1, 7: 0.05, 8: 0.05, 9: np.nan, 10: np.nan
        }
        df['rate'] = df['rate'].map(type_map)
        df = df.fillna(method='ffill')
        return df.tail(1).iat[0, 1]


if __name__ == '__main__':
    print(get_rate('000003.XSHE', '2002-12-31'))
    close_conn()