入手pandas分析统计

简单的数据统计和分析

前面我们已经完成了基金数据插入到数据表：
http://blog.youkuaiyun.com/github_26672553/article/details/78646417

简单的数据统计和分析，我们可以用SQL直接搞定了。

1、查询出NAV前2的数据

# coding: utf-8

from sqlalchemy import create_engine,desc
from common.config import dbUrl
from sqlalchemy.orm import  sessionmaker
from mappers.FundMapper import FundMapper #基金数据表ORM类

if __name__ == "__main__" :
    engine = create_engine(dbUrl, echo=True)    # 创建数据库引擎
    session = sessionmaker(engine)()    # 数据库会话对象

    result = session.query(FundMapper).order_by(desc(FundMapper.NAV)).limit(2).all()
    print(result) # [<mappers.FundMapper.FundMapper object at 0x1053e3d30>, <mappers.FundMapper.FundMapper object at 0x1053e3da0>]

2、查询NAV最高的

# coding: utf-8

from sqlalchemy import create_engine,desc,func
from common.config import dbUrl
from sqlalchemy.orm import  sessionmaker
from mappers.FundMapper import FundMapper #基金数据表ORM类

if __name__ == "__main__" :
    engine = create_engine(dbUrl, echo=True)    # 创建数据库引擎
    session = sessionmaker(engine)()    # 数据库会话对象

    result = session.query(func.max(FundMapper.NAV)).scalar()   # 获取具体的值
    print(result)  # 9.9999 

生成csv文件

读取数据表中的数据，最后生成csv文件

# coding: utf-8

from sqlalchemy import create_engine,desc,func
from common.config import dbUrl
from sqlalchemy.orm import  sessionmaker
from mappers.FundMapper import FundMapper #基金数据表ORM类

import csv

if __name__ == "__main__" :
    engine = create_engine(dbUrl, echo=True)    # 创建数据库引擎
    session = sessionmaker(engine)()    # 数据库会话对象

    result = session.query(FundMapper).limit(10).all() # 值取10行记录

    with open("./csv/fund.csv","w",encoding="utf-8",newline="") as f:
        writer = csv.writer(f)
        writer.writerow(["code","name","NAV","ACCNAV","fdate","DGR","DGV"]) # 先写表头

        for r in result:
            writer.writerow([r.code, r.name, r.NAV, r.ACCNAV, r.fdate, r.DGR, r.DGV])

        f.close()

知名分析库：pandas

安装：

#当然还是先要cd到虚拟环境目录
./python3 -m pip install pandas -i http://mirrors.aliyun.com/pypi/simple/ --trusted-host mirrors.aliyun.com

2、简单使用pandas

    pd = pandas.read_csv("./csv/fund.csv", dtype={"code":pandas.np.str_})

    print(pd[0:5]) # 取出第0~5行（不包括第5行）
    print(pd[["code","name"]]) # 取出多列
    print(pd["code"]) # 取一列

    res = pd.sort_values(by="NAV",ascending=False) # 根据NAV值 倒序
    print(res.head(5)) # 取出前5条
    print(res.tail(5)) # 取出后5条

    # 把取出的结果 保存的一个新的csv文件
    res[3:7].to_csv("./csv/res.csv",encoding="utf-8")