def __writeCharts(sheet: Worksheet, tests: Iterable[str], order: Iterable[str], charts: Iterable[Tuple[str, str]], data: Dict[str, dict]) -> None: row = 1 bound_scale = 10 def updateBounds(bounds, x_data, y_data): from statistics import mean if not bounds: bounds = [0, 0, 0, 0, 0, 0, 0] bounds[0] = min(bounds[0], *x_data) bounds[1] = max(bounds[1], *x_data) bounds[2] = mean(bounds[2], *x_data) bounds[3] = min(bounds[3], *y_data) bounds[4] = max(bounds[4], *y_data) bounds[5] = mean(bounds[5], *y_data) return bounds for seq in order: col = 0 for (typeX, typeY) in charts: chart = ScatterChart(scatterStyle='lineMarker') chart.title = seq chart.x_axis.title = typeX chart.y_axis.title = typeY chart.visible_cells_only = False bounds = None for test in tests: #bounds = updateBounds(bounds, data[seq][test][__DATA][typeX], data[seq][test][__DATA][typeY]) rX = data[seq][test][typeX] rY = data[seq][test][typeY] series = Series(Reference(sheet, min_col=rY.min_col, max_col=rY.max_col, min_row=rY.min_row), Reference(sheet, min_col=rX.min_col + 1, max_col=rX.max_col, min_row=rX.min_row), title_from_data=True) series.marker.symbol = 'auto' chart.series.append(series) if bounds: sheet.x_axis.scaling.min = max( bounds[0] - bounds[2] / bound_scale, 0) sheet.x_axis.scaling.max = bounds[1] + bounds[2] / bound_scale sheet.y_axis.scaling.min = max( bounds[3] - bounds[5] / bound_scale, 0) sheet.y_axis.scaling.max = bounds[4] + bounds[5] / bound_scale sheet.add_chart(chart, get_column_letter(7 + col) + str(row)) col += 9 row += 15
def pie_types( self, ws: Worksheet, types_counters: dict = None, cell_start_table: str = "A1", cell_start_chart: str = "D1", ): """Calculates metadata types repartition and add a Pie chart to the wanted sheet of Workbook. :param Worksheet ws: sheet of a Workbook to write analisis :param dict types_counters: dictionary of types/count. If not specified, the class attribute will be used instaed :param str cell_start_table: cell of the sheet where to start writing table :param str cell_start_chart: cell of the sheet where to start writing the chart """ if types_counters is None: types_counters = self.md_types_repartition # get starting cells min_cell_start_table = ws[cell_start_table] # write headers ws.cell( row=min_cell_start_table.row, column=min_cell_start_table.column, value=self.tr.get("type"), ) ws.cell( row=min_cell_start_table.row, column=min_cell_start_table.column + 1, value=self.tr.get("occurrences"), ) # write data into worksheet row = min_cell_start_table.row for md_type, count in self.md_types_repartition.items(): row += 1 ws.cell(row=row, column=min_cell_start_table.column, value=self.tr.get(md_type)) ws.cell(row=row, column=min_cell_start_table.column + 1, value=count) # Pie chart pie = PieChart() labels = Reference( worksheet=ws, min_col=min_cell_start_table.column, min_row=min_cell_start_table.row + 1, max_row=row, ) data = Reference( worksheet=ws, min_col=min_cell_start_table.column + 1, min_row=min_cell_start_table.row + 1, max_row=row, ) pie.add_data(data) pie.set_categories(labels) pie.title = self.tr.get("type") + "s" # Cut the first slice out of the pie slice = DataPoint(idx=0, explosion=20) pie.series[0].data_points = [slice] ws.add_chart(pie, cell_start_chart)
def pie_formats( self, ws: Worksheet, li_formats: list = None, cell_start_table: str = "A20", cell_start_chart: str = "D20", ): """Calculates metadata formats repartition and add a Pie chart to the wanted sheet of Workbook. :param Worksheet ws: sheet of a Workbook to write analisis :param list li_formats: list of all formats labels. If not specified, the class attribute will be used instaed :param str cell_start_table: cell of the sheet where to start writing table :param str cell_start_chart: cell of the sheet where to start writing the chart """ if li_formats is None: li_formats = self.li_data_formats # build the data for pie chart data = Counter(li_formats) # get starting cells min_cell_start_table = ws[cell_start_table] # write headers ws.cell( row=min_cell_start_table.row, column=min_cell_start_table.column, value=self.tr.get("format"), ) ws.cell( row=min_cell_start_table.row, column=min_cell_start_table.column + 1, value=self.tr.get("occurrences"), ) # write data into worksheet row = min_cell_start_table.row for frmt, count in data.items(): row += 1 ws.cell(row=row, column=min_cell_start_table.column, value=frmt.title()) ws.cell(row=row, column=min_cell_start_table.column + 1, value=count) # Pie chart pie = PieChart() labels = Reference( worksheet=ws, min_col=min_cell_start_table.column, min_row=min_cell_start_table.row + 1, max_row=row, ) data = Reference( worksheet=ws, min_col=min_cell_start_table.column + 1, min_row=min_cell_start_table.row + 1, max_row=row, ) pie.add_data(data) pie.set_categories(labels) pie.title = self.tr.get("format") + "s" # Cut the first slice out of the pie slice = DataPoint(idx=0, explosion=20) pie.series[0].data_points = [slice] ws.add_chart(pie, cell_start_chart)
def line_dates( self, ws: Worksheet, li_dates_md_created: list = None, li_dates_md_modified: list = None, cell_start_table: str = "O1", cell_start_chart: str = "S1", ): """Calculates metadata creation and modification dates repartition and add a \ Line chart to the wanted sheet of Workbook. :param Worksheet ws: sheet of a Workbook to write analisis :param list li_dates_md_created: list of metadatas'creation dates. If not specified, the class attribute will be used instead. :param list li_dates_md_modified: list of metadatas'modification dates. If not specified, the class attribute will be used instead. :param str cell_start_table: cell of the sheet where to start writing table :param str cell_start_chart: cell of the sheet where to start writing the chart """ # use passed lists or class ones if li_dates_md_created is None: li_dates_md_created = self.li_dates_md_created if li_dates_md_modified is None: li_dates_md_modified = self.li_dates_md_modified # compare lists # length if len(li_dates_md_created) != len(li_dates_md_modified): logger.warning( "Dates lists should have the same length. Creation: {} | Modification: {}" .format(len(li_dates_md_created), len(li_dates_md_modified))) total_dates = len(set(li_dates_md_created + li_dates_md_modified)) # common logger.debug( "{}/{} dates with both metadata creation and modification.".format( len( set(li_dates_md_created).intersection( li_dates_md_modified)), total_dates, )) # difference logger.debug("{}/{} dates with only metadata creation.".format( len(set(li_dates_md_created).difference(li_dates_md_modified)), total_dates, )) logger.debug("{}/{} dates with only metadata modification.".format( len(set(li_dates_md_modified).difference(li_dates_md_created)), total_dates, )) # use a named tuple DateFrequency = namedtuple( "DateFrequency", ["date", "count_md_created", "count_md_modified"]) # parse dates count_creation = Counter(li_dates_md_created) count_update = Counter(li_dates_md_modified) itr_dates_frequency = [] for crea, mod in zip_longest(sorted(count_creation), sorted(count_update), fillvalue=0): if crea == mod: # means a day with both metadata creation and modification itr_dates_frequency.append( DateFrequency(crea, count_creation.get(crea), count_update.get(mod))) elif crea == 0: print("creation empty: {}".format(count_creation.get(crea))) itr_dates_frequency.append( DateFrequency(mod, 0, count_update.get(mod))) elif mod == 0: print("modification empty: {}".format(count_update.get(mod))) itr_dates_frequency.append( DateFrequency(crea, count_creation.get(crea), 0)) else: itr_dates_frequency.append( DateFrequency(crea, count_creation.get(crea), 0)) itr_dates_frequency.append( DateFrequency(mod, 0, count_update.get(mod))) # get starting cells min_cell_start_table = ws[cell_start_table] # write headers ws.cell( row=min_cell_start_table.row, column=min_cell_start_table.column, value=self.tr.get("date", "Date"), ) ws.cell( row=min_cell_start_table.row, column=min_cell_start_table.column + 1, value="{} - {}".format(self.tr.get("occurrences"), self.tr.get("_created")), ) ws.cell( row=min_cell_start_table.row, column=min_cell_start_table.column + 2, value="{} - {}".format(self.tr.get("occurrences"), self.tr.get("_modified")), ) # write data into worksheet row = min_cell_start_table.row for date_freq in sorted(itr_dates_frequency): row += 1 ws.cell(row=row, column=min_cell_start_table.column, value=date_freq.date) ws.cell( row=row, column=min_cell_start_table.column + 1, value=date_freq.count_md_created, ) ws.cell( row=row, column=min_cell_start_table.column + 2, value=date_freq.count_md_modified, ) # Chart with date axis dates_chart = LineChart() labels = Reference( worksheet=ws, min_col=min_cell_start_table.column, min_row=min_cell_start_table.row + 1, max_row=row, ) data = Reference( worksheet=ws, min_col=min_cell_start_table.column + 1, max_col=min_cell_start_table.column + 2, min_row=min_cell_start_table.row, max_row=row, ) dates_chart.add_data(data, titles_from_data=1) dates_chart.set_categories(labels) # custom chart dates_chart.title = self.tr.get("date", "Date") # dates_chart.style = 2 # dates_chart.smooth = True dates_chart.height = 10 # default is 7.5 dates_chart.width = 30 # default is 15 dates_chart.y_axis.title = self.tr.get("occurrences") dates_chart.y_axis.crossAx = 500 dates_chart.x_axis = DateAxis(crossAx=100) dates_chart.x_axis.number_format = "mmm-y" dates_chart.x_axis.majorTimeUnit = "days" dates_chart.x_axis.title = "Date" # insert chart into the worksheet at the specified anchor ws.add_chart(dates_chart, cell_start_chart)