def draw_pattern_bubblechart(data_work_sheet, data_rows_seq, data_columns_list, chart_position, chart_title):
chart = BubbleChart()
chart.title = str(chart_title)
chart.style = 18
chart.x_axis.title = data_work_sheet.title + '..'
chart.y_axis.title = 'lantency(ms)'
#same sheet same sample width of chart
xvalues = Reference(data_work_sheet, min_col=1, min_row=data_rows_seq[0], max_row=data_rows_seq[-1])
for i in data_columns_list :
BubbleChart_line_title = data_work_sheet.cell(row=1,column=i).value
yvalues = Reference(data_work_sheet, min_col=i, min_row=data_rows_seq[0], max_row=data_rows_seq[-1])
size = Reference(data_work_sheet, min_col=i, min_row=data_rows_seq[0], max_row=data_rows_seq[-1])
series = Series(values=yvalues, xvalues=xvalues, zvalues=size, title=BubbleChart_line_title)
chart.series.append(series)
wb['ScatterChart'].add_chart(chart, chart_position)
def plot_bubble_chart():
wb = xl.load_workbook('C:\\Resources\\Program_Xl_Sheets\\Groceriesptfinal.xlsx') # load xl workbook
sheet = wb['dATA'] # get the worksheet
for i in range(2, 10):
cell = sheet['f'+str(i)]
sheet['a'+str(i)] = cell.value
values = Reference(
sheet,
min_row=2,
max_row=10,
min_col=1,
max_col=1
)
series_object = xl.chart.Series(values, title="Yearly Revenue")
chart = BubbleChart()
chart.append(series_object)
sheet.add_chart(chart, 'j2')
wb.save('C:\\Resources\\Program_Xl_Sheets\\Groceriesptfinal.xlsx')
def _plot_bubble(self, sheet, title, xlabel, ylabel):
chart = BubbleChart()
chart.style = 18
# plot on the sheet
chart.title = title
chart.x_axis.title = xlabel
chart.y_axis.title = ylabel
chart.y_axis.scaling.min = 0
chart.legend.position = 'r' # 凡例は右に設定
for row in range(2, sheet.max_row + 1):
xvalues = Reference(sheet, min_col = 3, min_row = row)
yvalues = Reference(sheet, min_col = 2, min_row = row)
size = Reference(sheet, min_col = 4, min_row = row)
chart.series.append(Series(values = yvalues, xvalues = xvalues, zvalues = size, title = sheet.cell(row, 1).value))
sheet.add_chart(chart, "A1")
import pandas as pd
from openpyxl import Workbook
from openpyxl.chart import Series, Reference, BubbleChart
wb = Workbook()
ws = wb.active
df = pd.read_csv('store_sales_share.csv')
ws.append(df.columns.tolist())
for row in df.values:
ws.append(list(row))
chart = BubbleChart()
chart.style = 18
i = 0
for store in df['店名'].unique():
min_row = i + 2
max_row = i + 4
x = Reference(ws, min_col=3, max_col=3, min_row=min_row, max_row=max_row)
y = Reference(ws, min_col=4, max_col=4, min_row=min_row, max_row=max_row)
z = Reference(ws, min_col=5, max_col=5, min_row=min_row, max_row=max_row)
series = Series(values=y, xvalues=x, zvalues=z, title=store)
chart.series.append(series)
i += 3
ws.add_chart(chart, 'A13')
wb.save('store_sales_share_bubble.xlsx')
("Number of Products", "Sales in USD", "Market share"),
(14, 12200, 15),
(20, 60000, 33),
(18, 24400, 10),
(22, 32000, 42),
(),
(12, 8200, 18),
(15, 50000, 30),
(19, 22400, 25),
(25, 25000, 50)
]
for row in rows:
ws.append(row)
chart2 = BubbleChart()
chart2.style = 18
###add the first series of data
xvalues = Reference(ws, min_col=1, min_row=2, max_row=5)
yvalues = Reference(ws, min_col=2, min_row=2, max_row=5)
size = Reference(ws, min_col=3, min_row=2, max_row=5)
series = Series(values=yvalues, xvalues=xvalues, zvalues=size, title='2013')
chart2.series.append(series)
xvalues = Reference(ws, min_col=1, min_row=7, max_row=10)
yvalues = Reference(ws, min_col=2, min_row=7, max_row=10)
size = Reference(ws, min_col=3, min_row=7, max_row=10)
series = Series(values=yvalues, xvalues=xvalues, zvalues=size, title='2014')
chart2.series.append(series)
("Number of Products", "Sales in USD", "Market share"),
(14, 12200, 15),
(20, 60000, 33),
(18, 24400, 10),
(22, 32000, 42),
(),
(12, 8200, 18),
(15, 50000, 30),
(19, 22400, 15),
(25, 25000, 50),
]
for row in rows:
ws.append(row)
chart = BubbleChart()
chart.style = 18 # use a preset style
# add the first series of data
xvalues = Reference(ws, min_col=1, min_row=2, max_row=5)
yvalues = Reference(ws, min_col=2, min_row=2, max_row=5)
size = Reference(ws, min_col=3, min_row=2, max_row=5)
series = Series(values=yvalues, xvalues=xvalues, zvalues=size, title="2013")
chart.series.append(series)
# add the second
xvalues = Reference(ws, min_col=1, min_row=7, max_row=10)
yvalues = Reference(ws, min_col=2, min_row=7, max_row=10)
size = Reference(ws, min_col=3, min_row=7, max_row=10)
series = Series(values=yvalues, xvalues=xvalues, zvalues=size, title="2014")
chart.series.append(series)
chart1.set_categories(cats)
chart1.shape = 4
ws.add_chart(chart1, "Cell") #Top-left cell where graph starts
#Bubble Chart
from openpyxl.chart import Series, Reference, BubbleChart
rows = [
("Number of Products", "Sales in USD", "Market share"),
(14, 12200, 15),
(20, 60000, 33)]
for row in rows:
ws.append(row)
chart = BubbleChart()
chart.style = 18 # use a preset style
# add the first series of data
xvalues = Reference(ws, min_col=1, min_row=2, max_row=5)
yvalues = Reference(ws, min_col=2, min_row=2, max_row=5)
size = Reference(ws, min_col=3, min_row=2, max_row=5)
series = Series(values=yvalues, xvalues=xvalues, zvalues=size, title="2013")
chart.series.append(series)
# add the second
xvalues = Reference(ws, min_col=1, min_row=7, max_row=10)
yvalues = Reference(ws, min_col=2, min_row=7, max_row=10)
size = Reference(ws, min_col=3, min_row=7, max_row=10)
series = Series(values=yvalues, xvalues=xvalues, zvalues=size, title="2014")
chart.series.append(series)
def bubble_chart(ws, rag_count):
chart = BubbleChart()
chart.style = 18 # use a preset style
# add the first series of data
amber_stop = 2 + rag_count['Amber']
xvalues = Reference(ws, min_col=3, min_row=3, max_row=amber_stop)
yvalues = Reference(ws, min_col=4, min_row=3, max_row=amber_stop)
size = Reference(ws, min_col=5, min_row=3, max_row=amber_stop)
series = Series(values=yvalues,
xvalues=xvalues,
zvalues=size,
title="Amber")
chart.series.append(series)
series.graphicalProperties.solidFill = "fce553"
# add the second
amber_g_stop = amber_stop + rag_count['Amber/Green']
xvalues = Reference(ws,
min_col=3,
min_row=amber_stop + 1,
max_row=amber_g_stop)
yvalues = Reference(ws,
min_col=4,
min_row=amber_stop + 1,
max_row=amber_g_stop)
size = Reference(ws,
min_col=5,
min_row=amber_stop + 1,
max_row=amber_g_stop)
series = Series(values=yvalues,
xvalues=xvalues,
zvalues=size,
title="Amber/Green")
chart.series.append(series)
series.graphicalProperties.solidFill = "a5b700"
amber_r_stop = amber_g_stop + rag_count['Amber/Red']
xvalues = Reference(ws,
min_col=3,
min_row=amber_g_stop + 1,
max_row=amber_r_stop)
yvalues = Reference(ws,
min_col=4,
min_row=amber_g_stop + 1,
max_row=amber_r_stop)
size = Reference(ws,
min_col=5,
min_row=amber_g_stop + 1,
max_row=amber_r_stop)
series = Series(values=yvalues,
xvalues=xvalues,
zvalues=size,
title="Amber/Red")
chart.series.append(series)
series.graphicalProperties.solidFill = "f97b31"
green_stop = amber_r_stop + rag_count['Green']
xvalues = Reference(ws,
min_col=3,
min_row=amber_r_stop + 1,
max_row=green_stop)
yvalues = Reference(ws,
min_col=4,
min_row=amber_r_stop + 1,
max_row=green_stop)
size = Reference(ws,
min_col=5,
min_row=amber_r_stop + 1,
max_row=green_stop)
series = Series(values=yvalues,
xvalues=xvalues,
zvalues=size,
title="Green")
chart.series.append(series)
series.graphicalProperties.solidFill = "17960c"
red_stop = green_stop + rag_count['Red']
xvalues = Reference(ws,
min_col=3,
min_row=green_stop + 1,
max_row=red_stop)
yvalues = Reference(ws,
min_col=4,
min_row=green_stop + 1,
max_row=red_stop)
size = Reference(ws, min_col=5, min_row=green_stop + 1, max_row=red_stop)
series = Series(values=yvalues, xvalues=xvalues, zvalues=size, title="Red")
chart.series.append(series)
series.graphicalProperties.solidFill = "cb1f00"
ws.add_chart(chart, "E1")
return ws
("Number of Products", "Sales in USD", "Market share"),
(14, 12200, 15),
(20, 60000, 33),
(18, 24400, 10),
(22, 32000, 42),
(),
(12, 8200, 18),
(15, 50000, 30),
(19, 22400, 15),
(25, 25000, 50),
]
for row in rows:
ws.append(row)
chart = BubbleChart()
chart.style = 18 # use a preset style
# add the first series of data
xvalues = Reference(ws, min_col=1, min_row=2, max_row=5)
yvalues = Reference(ws, min_col=2, min_row=2, max_row=5)
size = Reference(ws, min_col=3, min_row=2, max_row=5)
series = Series(values=yvalues, xvalues=xvalues, zvalues=size, title="2013")
chart.series.append(series)
# add the second
xvalues = Reference(ws, min_col=1, min_row=7, max_row=10)
yvalues = Reference(ws, min_col=2, min_row=7, max_row=10)
size = Reference(ws, min_col=3, min_row=7, max_row=10)
series = Series(values=yvalues, xvalues=xvalues, zvalues=size, title="2014")
chart.series.append(series)