数据可视化7_股票K线图

题目要求

excel里的股票数据搞成K线图

数据源

阿里巴巴2020年股票数据.xlsx

代码

简单版

"""
Time:2021/6/12 14:24
Author:小李今天敲代码了吗
"""
from openpyxl import load_workbook
from pyecharts import options as opts
from pyecharts.charts import Kline
import numpy as np
# 股票K线图
"""
step1: 搞个二维表装数据
step2：把时间和数据分开，用了numpy， 
step3: 函数
用了np.tolist把np转list
"""


def get_stock_all(file_addr):
    # 创建一个工作簿,这里用不到，因为我只想打开一个工作簿
    # wb =Workbook()
    # 打开文件
    wb = load_workbook(file_addr)
    # 激活工作簿
    ws = wb.active
    # 读取数据
    all_list = []
    # print(ws.max_column)
    for r in range(2, ws.max_row + 1):
        row_list = []
        for c in range(1, ws.max_column):
            val = ws.cell(r, c).value
            # print(val)
            row_list.append(val)
        all_list.append(row_list)
    data = all_list
    return data

# 这个是引用的改的
def k_line(data: list):
    # 对二维函数的处理
    data_sha = np.array(data)  # list转np
    data_fin = data_sha[:, 1:6].tolist()  # np转list
    c = (
        Kline()
            .add_xaxis([data[i][0].date() for i in range(len(data))])
            .add_yaxis("kline", data_fin)
            .set_global_opts(
            xaxis_opts=opts.AxisOpts(is_scale=True),
            yaxis_opts=opts.AxisOpts(
                is_scale=True,
                splitarea_opts=opts.SplitAreaOpts(
                    is_show=True, areastyle_opts=opts.AreaStyleOpts(opacity=1)
                ),
            ),
            datazoom_opts=[opts.DataZoomOpts(type_="inside")],
            title_opts=opts.TitleOpts(title="Kline-DataZoom-inside"),
        )
            .render("kline_datazoom_inside.html")
    )


if __name__=='__main__':
    file_addr=r'..\python_with_mysql\数据库相关操作\阿里巴巴2020年股票数据.xlsx'
    k_line(get_stock_all(file_addr))

复杂版

"""
Time:2021/6/12 16:03
Author:小李今天敲代码了吗
"""

from typing import List, Union

from openpyxl import load_workbook
from pyecharts import options as opts
from pyecharts.charts import Kline, Line, Bar, Grid


def get_data(file_addr):
    wb = load_workbook(file_addr)
    # 激活工作簿
    ws = wb.active
    # 读取数据
    all_list = []
    # print(ws.max_column)
    for r in range(2, ws.max_row + 1):
        row_list = []
        val = str(ws.cell(r, 1).value.date())
        # print(val,type(val))
        row_list.append(val)
        for c in range(2, ws.max_column):
            val = ws.cell(r, c).value
            # print(val)
            row_list.append(val)
        all_list.append(row_list)
    json_response = all_list
    # 解析数据
    return split_data(data=json_response)


# def get_data():
#     response = requests.get(
#         url="https://echarts.apache.org/examples/data/asset/data/stock-DJI.json"
#     )
#     json_response = response.json()
#     # 解析数据
#     print(type(json_response),json_response)
#     return split_data(data=json_response)

# 引用的 具体的处理没研究
def split_data(data):
    category_data = []
    values = []
    volumes = []

    for i, tick in enumerate(data):
        # print(tick)
        category_data.append(tick[0])
        values.append(tick)
        # print(type(tick[0]))
        volumes.append([i, tick[4], 1 if tick[1] > tick[2] else -1])
    return {"categoryData": category_data, "values": values, "volumes": volumes}


def calculate_ma(day_count: int, data):
    result: List[Union[float, str]] = []
    for i in range(len(data["values"])):
        if i < day_count:
            result.append("-")
            continue
        sum_total = 0.0
        for j in range(day_count):
            sum_total += float(data["values"][i - j][1])
        result.append(abs(float("%.3f" % (sum_total / day_count))))
    return result


def draw_charts():
    kline_data = [data[1:-1] for data in chart_data["values"]]
    kline = (
        Kline()
            .add_xaxis(xaxis_data=chart_data["categoryData"])
            .add_yaxis(
            series_name="Dow-Jones index",
            y_axis=kline_data,
            itemstyle_opts=opts.ItemStyleOpts(color="#ec0000", color0="#00da3c"),
        )
            .set_global_opts(
            legend_opts=opts.LegendOpts(
                is_show=False, pos_bottom=10, pos_left="center"
            ),
            datazoom_opts=[
                opts.DataZoomOpts(
                    is_show=False,
                    type_="inside",
                    xaxis_index=[0, 1],
                    range_start=98,
                    range_end=100,
                ),
                opts.DataZoomOpts(
                    is_show=True,
                    xaxis_index=[0, 1],
                    type_="slider",
                    pos_top="85%",
                    range_start=98,
                    range_end=100,
                ),
            ],
            yaxis_opts=opts.AxisOpts(
                is_scale=True,
                splitarea_opts=opts.SplitAreaOpts(
                    is_show=True, areastyle_opts=opts.AreaStyleOpts(opacity=1)
                ),
            ),
            tooltip_opts=opts.TooltipOpts(
                trigger="axis",
                axis_pointer_type="cross",
                background_color="rgba(245, 245, 245, 0.8)",
                border_width=1,
                border_color="#ccc",
                textstyle_opts=opts.TextStyleOpts(color="#000"),
            ),
            visualmap_opts=opts.VisualMapOpts(
                is_show=False,
                dimension=2,
                series_index=5,
                is_piecewise=True,
                pieces=[
                    {"value": 1, "color": "#00da3c"},
                    {"value": -1, "color": "#ec0000"},
                ],
            ),
            axispointer_opts=opts.AxisPointerOpts(
                is_show=True,
                link=[{"xAxisIndex": "all"}],
                label=opts.LabelOpts(background_color="#777"),
            ),
            brush_opts=opts.BrushOpts(
                x_axis_index="all",
                brush_link="all",
                out_of_brush={"colorAlpha": 0.1},
                brush_type="lineX",
            ),
        )
    )

    line = (
        Line()
            .add_xaxis(xaxis_data=chart_data["categoryData"])
            .add_yaxis(
            series_name="MA5",
            y_axis=calculate_ma(day_count=5, data=chart_data),
            is_smooth=True,
            is_hover_animation=False,
            linestyle_opts=opts.LineStyleOpts(width=3, opacity=0.5),
            label_opts=opts.LabelOpts(is_show=False),
        )
            .add_yaxis(
            series_name="MA10",
            y_axis=calculate_ma(day_count=10, data=chart_data),
            is_smooth=True,
            is_hover_animation=False,
            linestyle_opts=opts.LineStyleOpts(width=3, opacity=0.5),
            label_opts=opts.LabelOpts(is_show=False),
        )
            .add_yaxis(
            series_name="MA20",
            y_axis=calculate_ma(day_count=20, data=chart_data),
            is_smooth=True,
            is_hover_animation=False,
            linestyle_opts=opts.LineStyleOpts(width=3, opacity=0.5),
            label_opts=opts.LabelOpts(is_show=False),
        )
            .add_yaxis(
            series_name="MA30",
            y_axis=calculate_ma(day_count=30, data=chart_data),
            is_smooth=True,
            is_hover_animation=False,
            linestyle_opts=opts.LineStyleOpts(width=3, opacity=0.5),
            label_opts=opts.LabelOpts(is_show=False),
        )
            .set_global_opts(xaxis_opts=opts.AxisOpts(type_="category"))
    )

    bar = (
        Bar()
            .add_xaxis(xaxis_data=chart_data["categoryData"])
            .add_yaxis(
            series_name="Volume",
            y_axis=chart_data["volumes"],
            xaxis_index=1,
            yaxis_index=1,
            label_opts=opts.LabelOpts(is_show=False),
        )
            .set_global_opts(
            xaxis_opts=opts.AxisOpts(
                type_="category",
                is_scale=True,
                grid_index=1,
                boundary_gap=False,
                axisline_opts=opts.AxisLineOpts(is_on_zero=False),
                axistick_opts=opts.AxisTickOpts(is_show=False),
                splitline_opts=opts.SplitLineOpts(is_show=False),
                axislabel_opts=opts.LabelOpts(is_show=False),
                split_number=20,
                min_="dataMin",
                max_="dataMax",
            ),
            yaxis_opts=opts.AxisOpts(
                grid_index=1,
                is_scale=True,
                split_number=2,
                axislabel_opts=opts.LabelOpts(is_show=False),
                axisline_opts=opts.AxisLineOpts(is_show=False),
                axistick_opts=opts.AxisTickOpts(is_show=False),
                splitline_opts=opts.SplitLineOpts(is_show=False),
            ),
            legend_opts=opts.LegendOpts(is_show=False),
        )
    )

    # Kline And Line
    overlap_kline_line = kline.overlap(line)

    # Grid Overlap + Bar
    grid_chart = Grid(
        init_opts=opts.InitOpts(
            width="1000px",
            height="800px",
            animation_opts=opts.AnimationOpts(animation=False),
        )
    )
    grid_chart.add(
        overlap_kline_line,
        grid_opts=opts.GridOpts(pos_left="10%", pos_right="8%", height="50%"),
    )
    grid_chart.add(
        bar,
        grid_opts=opts.GridOpts(
            pos_left="10%", pos_right="8%", pos_top="63%", height="16%"
        ),
    )

    grid_chart.render("professional_kline_brush.html")


if __name__ == "__main__":
    file_addr = r'..\python_with_mysql\数据库相关操作\阿里巴巴2020年股票数据.xlsx'
    chart_data = get_data(file_addr)
    draw_charts()

遗忘的知识

1. numpy

对二维数组进行切片的时候，可以先把list转成np,处理之后，再用np转list

参考文章Python二维数组与三维数组切片详解

2. json和python数据转换

json 类型转换到 python 的类型对照表：

JSON	Python
object	dict
array	list
string	unicode
number (int)	int, long
number (real)	float
true	True
false	False
null	None

ython数据转换

json 类型转换到 python 的类型对照表：

JSON	Python
object	dict
array	list
string	unicode
number (int)	int, long
number (real)	float
true	True
false	False
null	None