<jupyter_start><jupyter_text>变量数据表格命名即为symbol变量名字，其中首行字段分别应为**Date,	Open,	High,	Low,	Close,	Volume,	Market Cap**, 其中Date与Close是必要的，区分大小写<jupyter_code>symbol = '铁矿石'<jupyter_output><empty_output><jupyter_text>读取与输出<jupyter_code>df = pd.read_excel('%s.xlsx' % symbol)
statistic_analysis(df, symbol)<jupyter_output>C:\Users\xueli\Anaconda3\lib\site-packages\xlrd\xlsx.py:266: PendingDeprecationWarning: This method will be removed in future versions.  Use 'tree.iter()' or 'list(tree.iter())' instead.
  for elem in self.tree.iter() if Element_has_iter else self.tree.getiterator():
C:\Users\xueli\Anaconda3\lib\site-packages\xlrd\xlsx.py:312: PendingDeprecationWarning: This method will be removed in future versions.  Use 'tree.iter()' or 'list(tree.iter())' instead.
  for elem in self.tree.iter() if Element_has_iter else self.tree.getiterator():<jupyter_text>批量分析<jupyter_code>symbol_list = ['btc', '铁矿石']
for symbol in symbol_list:
    print('现在处理%s的数据' % symbol)
    df = pd.read_excel('%s.xlsx' % symbol)
    statistic_analysis(df, symbol)<jupyter_output><empty_output><jupyter_text>具体分析代码<jupyter_code>def statistic_analysis(df, symbol):
    
    print('以下是%s的基础性统计分析：' % symbol)
    
    
    # ===清洗数据
    df.sort_values(by=['Date'], inplace=True)
    df.drop_duplicates(subset=['Date'], inplace=True)
    df.reset_index(inplace=True, drop=True)
    df['Date'] = pd.to_datetime(df['Date'])  # 转换时间格式
    df = df.resample(rule='1D', on='Date', label='left', closed='left').agg(
        {'Close': 'last',
         'Open': 'first',
         'High': 'max',
         'Low': 'min',
         'Volume': 'sum',
         'Market Cap': 'last',
         })
    df.reset_index(inplace=True)
    df['day_return'] = df['Close'].pct_change()  # 计算日收益率
    df = df.dropna()
    print('前十行数据：',  '\n', df.head(10))
    
    
    # === 简单统计分析
    print('\n', '------------------------', '\n')
    print('日收益率最大值: ', df['day_return'].max(), '\n', '日收益率最小值: ', df['day_return'].min())
    print('\n', '------------------------', '\n')
    print('日单向波动大于10%的时间与幅度: ', '\n', df.loc[abs(df['day_return']) > 0.1, ['Date', 'day_return']])
    print('\n', '------------------------', '\n')
    print('阳线数量：', len(df[df['day_return']>0]), '阴线数量', len(df[df['day_return']<0]))
    print('\n', '------------------------', '\n')
    
    
    # === 连续盈亏统计
    print('\n', '------------------------', '\n')
    print('最大连续上涨笔数: ', max(
    [len(list(v)) for k, v in itertools.groupby(np.where(df['day_return'] > 0, 1, np.nan))]), '\n')  # 最大连续上涨笔数
    print('最大连续下跌笔数: ', max(
    [len(list(v)) for k, v in itertools.groupby(np.where(df['day_return'] < 0, 1, np.nan))]))  # 最大连续下跌笔数
    print('\n', '------------------------', '\n')
    # 连续盈亏天数画图
    df.loc[df['day_return'] > 0, 'larger_than_0'] = 1
    df.loc[df['day_return'] < 0, 'larger_than_0'] = -1
    df.dropna(subset=['larger_than_0'], inplace=True)
    condition = df['larger_than_0'] != df['larger_than_0'].shift()
    df.loc[condition, 'start_time'] = df['Date']
    df['start_time'].fillna(method='ffill', inplace=True)
    df_list = []
    for k, g in df.groupby('start_time'):
        g = g.copy()
        g.reset_index(drop=True, inplace=True)
        if g.loc[0, 'larger_than_0'] == -1:
            continue
        g['up_days'] = g.index + 1
        g.set_index('Date', inplace=True)
        df_list.append(g)
    df_all = pd.concat(df_list, sort=False)  
    for k, g in df.groupby('start_time'):
        g = g.copy()
        g.reset_index(drop=True, inplace=True)
        if g.loc[0, 'larger_than_0'] == 1:
            continue
        g['down_days'] = g.index + 1
        g.set_index('Date', inplace=True)
        df_list.append(g)
    df_all = pd.concat(df_list, sort=False)
    long_group = pd.DataFrame(df_all['up_days'].groupby(df_all['up_days']).count())
    short_group = pd.DataFrame(df_all['down_days'].groupby(df_all['down_days']).count())
    long_group['up_days'] = (long_group['up_days'] - long_group['up_days'].shift(-1)).fillna(1)
    short_group['down_days'] = (short_group['down_days'] - short_group['down_days'].shift(-1)).fillna(1)
          
    long_and_short = Bar()
    if len(long_group.index) > len(short_group.index):
          indexOfbar = long_group.index
    else:
          indexOfbar = short_group.index
    long_and_short.add_xaxis(indexOfbar.tolist())
    long_and_short.add_yaxis('连续上涨', long_group['up_days'].tolist())
    long_and_short.add_yaxis('连续下跌', short_group['down_days'].tolist())
    long_and_short.set_global_opts(title_opts=opts.TitleOpts(title="连续上涨下跌天数统计"))
    long_and_short.render('%s_连续上涨下跌天数统计.html' % symbol)
    print('连续盈亏天数柱状图已绘制完毕')
    
    
    # === 收益率画图
    return_all = (Line()
             .add_xaxis(df['Date'].tolist())
             .add_yaxis('day_return', df['day_return'].tolist(), label_opts=opts.LabelOpts(is_show=False))
             .set_global_opts(
              datazoom_opts=[opts.DataZoomOpts(type_="inside")],)
             )
    return_all.set_global_opts(title_opts=opts.TitleOpts(title="日收益率波动"))
    return_all.render('%s_日收益率波动图.html' % symbol)
    print('日收益率波动图已绘制完毕')
    
    
    # === 分布画图
    
    # 收益率的分布情况
    bins1 = np.arange(round(df['day_return'].min(), 2), -0.05, 0.02)
    bins2 = np.arange(-0.05, 0.05, 0.01)
    bins3 = np.arange(0.05, round(df['day_return'].max(), 2), 0.02)
    bins = np.append(bins1, bins2)
    bins = np.append(bins, bins3)
    df['day_return_layer'] = pd.cut(df['day_return'], bins)
    aggResult = df.groupby(by=['day_return_layer'])['day_return'].agg({'day_return':np.size})
    pAggResute = pd.DataFrame(round(aggResult/aggResult.sum(), 4, ))
    distribution = Bar(init_opts=opts.InitOpts(width='1200px', height='600px'))
    distribution.add_xaxis(pAggResute.index.astype(str).tolist())
    distribution.add_yaxis('', pAggResute['day_return'].tolist(), category_gap=0, color=Faker.rand_color())
    distribution.set_global_opts(title_opts=opts.TitleOpts(title="日收益率分布"))
    distribution.set_global_opts(yaxis_opts=opts.AxisOpts(axistick_opts=opts.AxisTickOpts(is_show=False)))
    distribution.render('%s_日收益率分布图.html' % symbol)
    print('日收益率分布图已绘制完毕')
    
    # 收益率大于0.05和0.1的时间分布情况
    df.loc[abs(df['day_return']) >= 0.05, 'Year1'] = df['Date'].dt.year
    df_ = df.loc[df['Year1'] != None, ]
    aggResult1 = df_.groupby(by=['Year1']).count()
    df.loc[abs(df['day_return']) >= 0.1, 'Year2'] = df['Date'].dt.year
    df_ = df.loc[df['Year2'] != None, ]
    aggResult2 = df_.groupby(by=['Year2']).count()
    distribution_year = Bar()
    distribution_year.add_xaxis(aggResult1.index.tolist())
    distribution_year.add_yaxis('>0.05', aggResult1['Date'].tolist(), color=Faker.rand_color())
    distribution_year.add_yaxis('>0.1', aggResult2['Date'].tolist(), color=Faker.rand_color())
    distribution_year.set_global_opts(title_opts=opts.TitleOpts(title="大收益率时间分布"))
    distribution_year.render('%s_大收益率时间分布图.html' % symbol)
    print('大收益率时间分布图已绘制完毕')
    
    
    # === 周期性画图
    df['year'] = df['Date'].dt.year
    year_bins_no = list(df['year'].drop_duplicates())
    year_bins = list(df['year'].drop_duplicates())
    year_bins.append(df['year'].max() + 1)
#     year_bins_ = pd.to_datetime(year_bins, format='%Y')
#     df['date_layer'] = pd.cut(df['Date'], year_bins_)
    circle_line = Line()
    circle_line.add_xaxis(list(range(len(df[df['year']==(df['year'].min()+1)]['year']))))
    for _ in year_bins_no:
        circle_line.add_yaxis('%s' % _, df.loc[df['year']==int('%d' % _), 'Close'], label_opts=opts.LabelOpts(is_show=False))
    circle_line.set_global_opts(title_opts=opts.TitleOpts(title="每年情况"))
    circle_line.render('%s_年周期图.html' % symbol)
    print('年周期图已绘制完毕')
    
    
    # ===++++月收益率系列
    print('\n', '------------------------', '\n')
    print('接下来是以月收益率计算得到的结果')
    df_month = df.resample(rule='1M', on='Date', label='left', closed='left').agg(
        {'Close': 'last',
         'Open': 'first',
         'High': 'max',
         'Low': 'min',
         'Volume': 'sum',
         'Market Cap': 'last',
         })
    df_month.reset_index(inplace=True)
    df_month['month_return'] = df_month['Close'].pct_change()  # 计算月收益率
    df_month = df_month.dropna()
    print('前十行数据：',  '\n', df_month.head(10))
    
    month_return = (Line()
               .add_xaxis(df_month['Date'].tolist())
               .add_yaxis('月收益率', df_month['month_return'].tolist(), label_opts=opts.LabelOpts(is_show=False)))
    month_return.set_global_opts(title_opts=opts.TitleOpts(title="月收益率波动"))
    month_return.render('%s_月收益率波动图.html' % symbol)
    print('月收益率波动图已绘制完毕')
    
    # === 月均收益率与胜率
    month_return_groupby_mean = (df_month['month_return'].groupby(df_month['Date'].dt.month)).mean().round(2)
    month_return_groupby_median = (df_month['month_return'].groupby(df_month['Date'].dt.month)).median().round(2)
    month_return_groupby1 = df_month[df_month['month_return']>0].groupby(df_month['Date'].dt.month)[['month_return']].size()
    month_return_groupby2 = df_month.groupby(df_month['Date'].dt.month)['month_return'].size()
    month_return_groupby_win_rate = (month_return_groupby1 / month_return_groupby2).round(2)
    month_return_groupby_plt1 = (Bar()
                               .add_xaxis(month_return_groupby_mean.index.tolist())
                               .add_yaxis('月均收益率', month_return_groupby_mean.tolist(), stack="stack1", yaxis_index=0)
                               .add_yaxis('月收益率中位数', month_return_groupby_median.tolist(), stack="stack1", yaxis_index=0)
                               .extend_axis(yaxis=opts.AxisOpts())
                               .set_global_opts(yaxis_opts=opts.AxisOpts(type_='value'))
                               .set_series_opts(
                                                label_opts=opts.LabelOpts(
                                                position="inside",
                                                ))
                                )
    month_return_groupby_plt2 = (Line()
                                .add_xaxis(['1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12'])
                                .add_yaxis('胜率', month_return_groupby_win_rate.tolist(), yaxis_index=1, linestyle_opts=opts.LineStyleOpts(width=3))
                                .extend_axis(yaxis=opts.AxisOpts())
                                )
    month_return_plt = month_return_groupby_plt2.overlap(month_return_groupby_plt1)
    month_return_plt.render('%s_月均收益率与胜算图.html' % symbol)
    print('月均收益率与胜算图已绘制完毕')
    
    # === 月累计收益率
    df_month['month'] = df_month['Date'].dt.month
    df_month1 = df_month[df_month['month'] == 1]
    df_month2 = df_month[df_month['month'] == 2]
    df_month3 = df_month[df_month['month'] == 3]
    df_month4 = df_month[df_month['month'] == 4]
    df_month5 = df_month[df_month['month'] == 5]
    df_month6 = df_month[df_month['month'] == 6]
    df_month7 = df_month[df_month['month'] == 7]
    df_month8 = df_month[df_month['month'] == 8]
    df_month9 = df_month[df_month['month'] == 9]
    df_month10 = df_month[df_month['month'] == 10]
    df_month11 = df_month[df_month['month'] == 11]
    df_month12 = df_month[df_month['month'] == 12]
    month_cumprod = Line()
    month_cumprod.add_xaxis(list(range(df['year'].max() - df['year'].min() + 1)))
    for i in [df_month1, df_month2, df_month3, df_month4, df_month5, df_month6, df_month7, df_month8, df_month9, df_month10, df_month11, df_month12]:
        i['equity'] = (i['month_return'] + 1).cumprod()
        month_cumprod.add_yaxis(str(i['month'].tolist()[1]), i['equity'].tolist(), label_opts=opts.LabelOpts(is_show=False))
    month_cumprod.set_global_opts(xaxis_opts=opts.AxisOpts(is_show=False))
    month_cumprod.render('%s_月累计收益率图.html' % symbol)
    print('月累计收益率图已绘制完毕')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.19247074783788368, 'fraction_numerical': 0.016279464134305578, 'mean_word_length': 3.264280549530007, 'pattern_counts': {'":': 0, '<': 19, '<?xml version=': 0, '>': 26, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 7, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '487892', 'license': 'MIT', 'n_tokens_mistral': 4732, 'n_tokens_neox': 4828, 'n_words': 541, 'path': 'auto_statistic_analysis.ipynb', 'repo_name': 'xuelixunhua/statistic_analysis_for_price_data', 'size': '11794'}