python从excel中读取数据并封装成json

import pandas as pd
from collections import OrderedDict
import json

# 获得持仓数据
def get_repostion_data(start, end):
    reposition = pd.read_excel('../产品数据.xlsx',sheetname='持仓')
    # assets = pd.read_excel('../产品数据.xlsx',sheetname='资产')
    # net_val = pd.read_excel('../产品数据.xlsx',sheetname='净值')
    # print('index',reposition.dtypes,reposition.index)

    reposition = reposition.ix[reposition['日期']>start,:]
    reposition = reposition.ix[reposition['日期']<end,:]

    return reposition


# 获得持仓数据
def get_assets_data(start, end):
    # reposition = pd.read_excel('../产品数据.xlsx',sheetname='持仓')
    assets = pd.read_excel('../产品数据.xlsx',sheetname='资产')
    # net_val = pd.read_excel('../产品数据.xlsx',sheetname='净值')
    # print('index',reposition.dtypes,reposition.index)

    assets = assets.ix[assets['统计日期']>start,:]
    assets = assets.ix[assets['统计日期']<end,:]

    print('reposition',assets)
    return assets
# print('net_val',net_val)
# get_assets_data(start, end)


# 获得持仓数据
def get_net_val_data(start, end):
    # reposition = pd.read_excel('../产品数据.xlsx',sheetname='持仓')
    # assets = pd.read_excel('../产品数据.xlsx',sheetname='资产')
    net_val = pd.read_excel('../产品数据.xlsx',sheetname='净值')
    # print('index',reposition.dtypes,reposition.index)

    net_val = net_val.ix[net_val['日期']>start,:]
    net_val = net_val.ix[net_val['日期']<end,:]

    print('net_val',net_val)
    return net_val
# print('net_val',net_val)
# get_net_val_data('2016-03-01','2016-03-06')

# 读取基金数据文件
def get_fund_data(start, end):
    repostion_data = get_repostion_data(start, end)
    assets_data = get_assets_data(start, end)
    net_values_data = get_net_val_data(start, end)
    print('repostion_data',repostion_data)
    print('assets_data',assets_data)
    print('net_values_data',net_values_data)

    fund_data_date = OrderedDict()
    stock_to_nav_rates = []
    stocks = {}
    net_values = {}
    for index, row in repostion_data.iterrows():
        date = row['日期']
        ticker = row['证券代码']
        if ticker.startswith('00') or ticker.startswith('30') or ticker.startswith('60'):
            pass
        else:
            continue
        fund_code = row['基金名称']


        fundnav = assets_data.ix[assets_data['统计日期'] == date,:]
        fundnav = fundnav.ix[fundnav['基金名称'] == fund_code , '净值']
        fundnav = list(fundnav)[0]
        # print('fundnav',fundnav,type(fundnav))
        stock_to_nav_rate = (float(row['最新价']) * float(row['持仓'])) / fundnav
        stock_to_nav_rates.append(stock_to_nav_rate)

        net_value = net_values_data.ix[net_values_data['日期'] == date, fund_code]
        net_value = list(net_value)[0]

        fund_data_date.setdefault(date, OrderedDict()).setdefault(fund_code, OrderedDict())['net_value'] = net_value
        fund_data_date.setdefault(date, OrderedDict()).setdefault(fund_code, OrderedDict()).setdefault('stocks', OrderedDict())[
            ticker] = stock_to_nav_rate

    # print('checks',checks)
    # df = pd.read_csv(path, dtype='str')
    # df['stock_to_nav_rate'] = stock_to_nav_rates
    # print('stock_to_nav_rates',stock_to_nav_rates)
    # df.to_csv(path, index=False)
    # print('stock_to_nav_rates',df)
    return fund_data_date
print('aaaa',json.dumps(get_fund_data('2016-03-01', '2016-03-05')))