Batch chart automatic generation method and system based on data fusion and cleaning, electronic equipment and computer readable storage medium

Abstract

The invention relates to a batch chart automatic generation method and system based on data fusion and cleaning, electronic equipment and a computer readable storage medium, and the method comprises the following steps: S1, obtaining a measurement data set of a product from at least one data source based on an access parameter preset in a spreadsheet application program; s2, in response to a first interaction operation of a user in the spreadsheet application program, selecting one of a plurality of predefined data retention strategies, and running a first script program to generate a regular data set; the data retention strategy is based on a multiple measurement sequence of the same product and is configured to retain a data subset conforming to a specific quality state and a sequence position; s3, in response to a second interaction operation of the user in the statistical analysis software, selecting one of a plurality of predefined chart generation modes, and generating statistical charts with unified naming specifications in batches; and S4, in response to a third interaction operation of the user in the spreadsheet application program, selecting a report output format.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of computer vision and automatic detection, and particularly relates to a batch chart automatic generation method and system based on data fusion and cleaning, an electronic device and a computer readable storage medium. BACKGROUND

[0002] In modern industrial production and quality management, analysis and visualization of product measurement data is a crucial task. Enterprises often have a large amount of production data, detection data, etc., which may be scattered in different systems (such as factory query systems, customer databases) or stored in various formats (such as local data files). The traditional approach is that engineers or data analysts need to manually obtain data from different data sources, perform complex data cleaning and sorting, and then generate various statistical charts one by one using statistical analysis software, and finally manually insert the charts into the report.

[0003] In the prior art, in order to monitor the size change trend of products in real time and improve the yield, a JMP tool is generally used to generate a box plot to view the product data trend, and corresponding adjustments are made to the machine according to the product size data trend. However, due to the large number of products and the large number of product measurement points (there may be 160-240 points according to different product models), it takes a long time to manually obtain data and generate a box plot, and since each point may not be the same, there may be errors and omissions when manually generating a chart.

[0004] In Chinese Patent CN114546464B, a visual data cleaning script cleaning process analysis system is disclosed, which includes a program executor for detecting and saving intermediate table information and column state information in each intermediate table; a code parser for extracting input table information, output table information and functions of table information data for data conversion operation on the intermediate table; a data conversion inferrer for determining the data conversion operation type and parameters of the table information data, and finally generating data conversion operation description information; a cleaning layout constructor for matching intermediate table information to each node in the directed acyclic graph framework to obtain an intermediate table directed acyclic graph; and a graph symbol plotter for assigning data conversion operation graphs to the edges of the directed acyclic graph to obtain a data cleaning process visualization graph.

[0005] Although the above scheme improves the transparency and controllability of data processing through visualizing the data cleaning process, it does not involve how to automatically and efficiently extract data from scattered and different structures of multiple data sources (such as factory query system, customer database, local file), and the system does not pre-set intelligent cleaning strategies for product measurement data characteristics (such as multiple measurement sequences, invalid jump points, and specific quality state data screening). Users need to write or understand complex cleaning scripts, which is difficult for non-professional users and cannot achieve "one-click" intelligent cleaning and reconstruction.

[0006] Therefore, we propose a batch chart automatic generation method, system, electronic device and computer readable storage medium based on data fusion and cleaning. SUMMARY

[0007] The main purpose of the present application is to provide a batch chart automatic generation method, system, electronic device and computer readable storage medium based on data fusion and cleaning, aiming to solve the problems of scattered data acquisition, complex data cleaning, low efficiency of chart generation and report making in the prior art.

[0008] To achieve the above purpose, the present application provides a batch chart automatic generation method based on data fusion and cleaning, comprising the following steps: S1, based on the access parameters preset in the electronic spreadsheet application program, automatically acquiring the measurement data set of the product from at least one data source, the data source including factory query system, customer database or local data file; S2, in response to the first interactive operation of the user in the electronic spreadsheet application program, selecting one from a plurality of predefined data retention strategies, and running a first script program to perform one-key cleaning and reconstruction on the measurement data set to generate a regular data set; the data retention strategy is based on multiple measurement sequences of the same product, and is configured to retain a data subset that meets a specific quality state and sequence position; S3, in response to the second interactive operation of the user in the statistical analysis software, selecting one from a plurality of predefined chart generation modes, and running a second script program to batch generate statistical charts with uniform naming specifications based on the regular data set; S4, in response to the third interactive operation of the user in the electronic spreadsheet application program, selecting a report output format, and running a third script program to automatically insert the statistical charts into a specified electronic spreadsheet report or a newly created presentation report.

[0009] Preferably, in step S2, the plurality of predefined data retention strategies include: All measurement data is retained for analysis of overall data distribution before and after maintenance; Retain first measurement data for analyzing overall data distribution in initial production state; Retain last measurement data for analyzing overall data distribution in final state; Retain first OK data for analyzing data distribution of initial production qualified products to evaluate initial state of machine; Retain last OK data for analyzing data distribution of final qualified products to verify optimization effect of machine; Retain first NG data for analyzing specific distribution of initial failure to locate problem nature; Retain last NG data for verifying whether there are still residual defects after optimization of machine.

[0010] Preferably, in step S2, based on the selected data retention strategy, the multiple measurement records in the measurement data set are screened, invalid jump points in the measurement records are automatically identified and removed, a configurable point position screening list is generated, and data is extracted and standardized in format; Wherein, the invalid jump points are set as pre-defined specific abnormal values.

[0011] Preferably, in step S2, the processing logic of the configurable point position list is specifically: When there is no user-configured point position screening list, a full-size or full-point position list is generated for data processing according to the selected chart generation mode; When there is a user-configured point position screening list, data screening and extraction are performed with reference to the point position screening list.

[0012] Preferably, the pre-defined multiple chart generation modes include: generation by size and with classification, generation by size and without classification, generation by point position and with classification, and generation by point position and without classification. When the chart type with “classification” is selected, the method allows the user to customize a classification field in the normalized data set, and the value of the classification field is generated based on at least one of the rules of date, risk level or production batch, so that the generated composite statistical chart can display data distribution comparison according to user-defined dimensions.

[0013] Preferably, the statistical chart is a box plot, and the box plot is a composite statistical chart integrating the features of a conventional box plot, a heat map and an average value moving line.

[0014] To achieve the above-mentioned purposes, the present application provides a batch chart automatic generation system for implementing any one of the above-mentioned methods, comprising: A data interface module configured to be communicatively connected with at least one data source to obtain a measurement data set of a product; An information maintenance module integrated in the spreadsheet application and configured to provide a graphical user interface for a user to configure access parameters and data cleaning rules of the data source; A data governance module integrated in the spreadsheet application and configured to run a first script program to perform data cleaning and reconstruction tasks; A chart rendering module integrated in the statistical analysis software and configured to run a second script program to perform batch generation of statistical charts; A report synthesis module integrated in the spreadsheet application and configured to run a third script program to perform automatic assembly and output of reports.

[0015] To achieve the above object, the present application provides an electronic device, comprising: one or more processors; a memory for storing one or more programs; When the one or more programs are executed by the one or more processors, the one or more processors implement the method as described above.

[0016] To achieve the above object, the present application provides a computer readable storage medium having a computer program stored thereon, wherein the program is executed by a processor to implement the method as described above.

[0017] The technical scheme of the present application has the following advantages: By presetting access parameters in the spreadsheet application, the system can automatically obtain measurement data sets from scattered heterogeneous data sources, i.e., factory query systems, customer databases, and local data files, realize data fusion, greatly reduce the time and workload of manually obtaining and integrating data, and avoid human errors.

[0018] By introducing predefined data retention strategies and a first script program, the user can perform one-key cleaning and reconstruction of measurement data according to the selected strategy, automatically identify and process redundant and non-standard data, eliminate invalid jump points, and generate regular data sets; this process is based on multiple measurement sequences of the same product and can configure data subsets that retain specific quality states and sequence positions, improving the intelligence and accuracy of data processing and reducing the difficulty of data processing.

[0019] By predefining multiple chart generation modes and a second script program in the statistical analysis software, the system can batch generate statistical charts with uniform naming conventions. The user only needs to perform simple interactive operations to quickly generate a large number of required charts based on the regular data sets, improving the efficiency and consistency of chart production.

[0020] Using a third-party scripting program, the system can automatically insert batch-generated statistical charts into specified spreadsheet reports or newly created presentation reports according to the report output format selected by the user. This simplifies the report production process, reduces manual typesetting and copy-pasting work, and improves the speed and professionalism of report generation.

[0021] Predefined data retention strategies and chart generation modes enable the system to flexibly respond to different analysis needs. Users can quickly switch strategies or modes to efficiently analyze data from different dimensions, thereby improving the depth and breadth of data analysis. Attached Figure Description

[0022] Figure 1 This is a schematic diagram of the program logic of a batch chart automatic generation method based on data fusion and cleaning in one embodiment of this application; Figure 2 This is a schematic diagram of the module structure of a batch chart automatic system in one embodiment of this application; Figure 3 This is a schematic diagram of the module structure of an electronic device in one embodiment of this application. Detailed Implementation

[0023] The embodiments of this application are described in detail below. Examples of the embodiments are shown in the accompanying drawings, wherein the same or similar reference numerals denote the same or similar elements or elements having the same or similar functions throughout. The embodiments described below with reference to the accompanying drawings are exemplary and intended to explain this application, and should not be construed as limiting this application. All other embodiments obtained by those skilled in the art based on the embodiments of this application without inventive effort are within the scope of protection of this application.

[0024] Furthermore, descriptions using terms such as "first" and "second" in this application are for descriptive purposes only (e.g., to distinguish identical or similar elements) and should not be construed as indicating or implying their relative importance or implicitly specifying the number of technical features indicated. Therefore, features defined with "first" or "second" may explicitly or implicitly include at least one of those features. Additionally, technical solutions from different embodiments can be combined with each other, but this must be based on the ability of those skilled in the art to implement them. If a combination of technical solutions is contradictory or impossible to implement, such a combination should be considered nonexistent and not within the scope of protection claimed in this application.

[0025] like Figure 1 As shown, this invention proposes a method for automatically generating batch charts based on data fusion and cleaning, including the following steps: S1. Automatically obtain the product measurement dataset from at least one data source based on access parameters preset in the spreadsheet application.

[0026] Specifically, in step S1, the user first pre-configures data access parameters in an electronic spreadsheet application (e.g. Microsoft Excel, WPS Table, etc.).

[0027] The access parameters include the type of data source, such as database connection string, file path, API interface information, etc.; authentication credentials, such as username, password, API key, etc.; and the data range, table name or query statement to be queried, etc.

[0028] The data source can be an in-house query system, such as an MES system, a LIMS system; a customer database or a local data file, such as a CSV file, an Excel file, an XML file, etc.

[0029] In summary, the system uses plug-ins or macros built-in or extended in the electronic spreadsheet application to establish a connection with different data sources using these pre-set access parameters, and automatically performs data query and extraction operations. The measurement data sets from one or more data sources, such as product size measurement data, sensor data, test results, etc., are unified and aggregated into the electronic spreadsheet application, realizing data fusion. Thus, the tedious process of manually searching, exporting and merging data is avoided, and the efficiency and accuracy of data acquisition are improved.

[0030] S2, in response to the user's first interactive operation in the electronic spreadsheet application, one of the pre-defined data retention strategies is selected, and a first script program is run to perform one-key cleaning and reconstruction on the measurement data set to generate a regular data set.

[0031] Specifically, after successfully acquiring the measurement data set, the user performs a first interactive operation in the electronic spreadsheet application, such as clicking a button, selecting a drop-down menu item or entering a command, to trigger the data cleaning and reconstruction process. The system will display a pre-defined plurality of data retention strategies 220 for the user to choose from. The data retention strategies are all set based on multiple measurement sequences of the same product, aiming to retain data subsets that meet specific quality states (such as OK, NG) and sequence positions (such as first, last) according to the user's analysis needs.

[0032] Through the above cleaning and reconstruction process, the original, possibly chaotic measurement data set is converted into a regular data set with unified structure, standardized format, invalid data removed and in accordance with specific retention strategies.

[0033] S3. In response to a second user interaction in the statistical analysis software, one of the pre-defined multiple chart generation modes is selected and a second script is run to batch generate statistical charts with uniform naming convention based on the pre-processed data set. After the pre-processed data set is generated, the user selects one of the pre-defined multiple chart generation modes by performing a second interaction in the statistical analysis software (e.g. JMP, Minitab, RStudio, Python Jupyter Notebook, etc.) (e.g. clicking on a custom menu item, running a pre-defined macro or script).

[0034] S4. In response to a third user interaction in the spreadsheet application, a report output format is selected and a third script is run to automatically insert the statistical charts into a designated spreadsheet report or a newly created presentation report. After the batch chart generation is completed, the user performs a third interaction in the spreadsheet application (e.g. clicking on a “Generate Report” button) to select a report output format. The report output format can include “Embed into existing Excel report”, “New Excel report” or “New PowerPoint presentation”, etc. Finally, after the user selects the output format, the system runs the third script.

[0035] In summary, the third script intelligently and automatically inserts the statistical charts batch generated in step S3 into the designated positions of the user-specified spreadsheet report or the corresponding slides of the newly created presentation report in an orderly manner. For example, the script can automatically assign the insertion positions based on the chart names or types, and perform basic layout and formatting adjustments. This greatly reduces the user’s workload of manually copying, pasting and formatting the charts, and ensures the efficiency and consistency of the report.

[0036] In one embodiment, the pre-defined multiple data retention policies include, but are not limited to: Retain all measurement data: No filtering is performed and all original measurement records are retained.

[0037] Retain first measurement data: For multiple measurements of the same product or batch, only the first measurement data is retained.

[0038] Retain last measurement data: For multiple measurements of the same product or batch, only the last measurement data is retained.

[0039] Retain first OK data: For multiple measurements of the same product or batch, only the first measurement data with a “OK” status is retained.

[0040] Retain last OK data: For multiple measurements of the same product or batch, only the last measurement data with a “OK” status is retained.

[0041] Keep first NG data: For multiple measurements of the same product or batch, keep the measurement data of the first occurrence of the unqualified (NG) state.

[0042] Keep last NG data: For multiple measurements of the same product or batch, keep the measurement data of the last occurrence of the unqualified (NG) state.

[0043] Further, after the user selects one of the strategies, the system will run the first script program to perform one-key cleaning and reconstruction on the original measurement data set. This cleaning process also includes intelligent filtering of multiple measurement records in the measurement data set based on the selected data retention strategy. For example, invalid jump points (i.e. abnormal values or measurement error values that deviate significantly from the normal range) in the measurement records are automatically identified and removed, which can be achieved by statistical methods (such as the three-sigma method, boxplot outlier detection) or preset thresholds. Subsequently, the system generates a configurable point selection list for data extraction and format standardization processing.

[0044] On the other hand, when there is no user-configured point selection list, the system will automatically generate a full-size or full-point list according to the selected chart generation mode for data processing, ensuring that all relevant data is included in subsequent analysis; when there is a user-configured point selection list, the data is filtered and extracted according to the point selection list, so that the user can pre-set a list of sizes or points of interest, and the system will only process these specified data.

[0045] In one embodiment, the pre-defined multiple chart generation modes can include: Generate by size dimension with classification: Generate grouped charts according to different sizes of the product (such as length, width, height or specific tolerance size) and according to user-defined classification fields (such as date, risk level, production batch, etc.).

[0046] Generate by size dimension without classification: Generate charts only according to different sizes of the product without additional classification.

[0047] Generate by point dimension with classification: Generate grouped charts according to different measurement points of the product (such as roughness at a certain point on the surface, thickness at a specific location, etc.) and according to user-defined classification fields.

[0048] Generate by point dimension without classification: Generate charts only according to different measurement points of the product without additional classification.

[0049] When the generation mode with classification is selected, the method further comprises: defining a custom classification field in the normalized dataset. The classification field can be generated based on at least one of the following rules: date (e.g. grouped by month, quarter), risk level (e.g. high-risk products, low-risk products are grouped), or production batch (e.g. batch grouping of different production lines). These classification fields will serve as dimensions for chart generation, making the analysis results more insightful.

[0050] After the user selects the chart generation mode, the system will run a second script program to automatically generate a large number of statistical charts based on the normalized dataset generated in step S2. In order to maintain the professionalism and consistency of the report, these charts will all adopt a unified naming specification (for example, the chart name can contain product ID, size / point name, data retention policy name and generation date, etc.).

[0051] In a preferred embodiment, the statistical chart can be a box plot.

[0052] In a more preferred embodiment, the box plot can be a composite statistical chart that combines the features of a conventional box plot, a heat map, and an average value trend line.

[0053] The composite box plot not only shows the median, quartiles, outliers, and other conventional statistical information of the data, but also visually displays the data density or trend through color coding of the heat map, and shows the historical trend of the data over time or other dimensions in combination with the average value trend line, thereby providing more rich and intuitive data insights.

[0054] As shown in Figure 2 The present application also proposes a batch chart automatic generation system, which comprises: a data interface module configured to be communicatively connected with at least one data source to obtain a measurement dataset of a product; an information maintenance module integrated in a spreadsheet application and configured to provide a graphical user interface for a user to configure access parameters and data cleaning rules of the data source; a data governance module integrated in the spreadsheet application and configured to run a first script program to perform data cleaning and reconstruction tasks; a chart rendering module integrated in a statistical analysis software and configured to run a second script program to perform batch generation tasks of statistical charts; a report synthesis module integrated in the spreadsheet application and configured to run a third script program to perform automatic assembly and output tasks of reports.

[0055] Further provided is an electronic device, such as Figure 3As shown, the electronic device can implement the batch chart automatic generation method based on data fusion and cleaning as described above. The electronic device generally includes: One or more processors: The processor can be a central processing unit (CPU), a graphics processing unit (GPU), an application-specific integrated circuit (ASIC), or a programmable logic device (FPGA), etc., for executing computer instructions and processing data.

[0056] Memory: The memory can be a volatile memory (such as RAM) or a non-volatile memory (such as ROM, hard disk, solid state disk, USB flash memory, etc.), for storing one or more programs.

[0057] The present application also provides a computer readable storage medium storing a computer program, which, when executed by a processor, implements the steps of the batch chart automatic generation method based on data fusion and cleaning as described above. The computer program, when executed, can include the processes of the embodiments of the batch chart automatic generation method based on data fusion and cleaning as described above. Any reference to memory, storage, databases, or other media herein includes non-volatile and / or volatile memory. Non-volatile memory can include read-only memory (ROM), programmable ROM (PROM), electrically programmable ROM (EPROM), electrically erasable programmable ROM (EEPROM), or flash memory. Volatile memory can include random access memory (RAM), or external cache memory. As an illustration and not a limitation, RAM is available in many forms such as static RAM (SRAM), dynamic RAM (DRAM), synchronous DRAM (SDRAM), double data rate SDRAM (SSRSDRAM), enhanced SDRAM (ESDRAM), Synchlink DRAM (SLDRAM), Rambus DRAM (RDRAM), direct Rambus dynamic RAM (DRDRAM), and Rambus dynamic RAM (RDRAM), etc.

[0058] It should be noted that in this document, the terms "comprise", "contain" or any other variant thereof are intended to cover non-exclusive inclusion, so that a process, device, article or batch chart automatic generation method based on data fusion and cleaning including a series of elements not only includes those elements, but also includes other elements not explicitly listed or inherent to such a process, device, article or batch chart automatic generation method based on data fusion and cleaning. Without more limitations, the element defined by the statement "comprises a" does not exclude the presence of another identical element in the process, device, article or batch chart automatic generation method based on data fusion and cleaning that includes the element.

[0059] The above description is only a preferred embodiment of this application and does not limit the patent scope of this application. Any equivalent structural or procedural changes made based on the content of this application's specification and drawings, or direct or indirect applications in other related technical fields, are similarly included within the patent protection scope of this application.

Claims

1. A method for automatically generating batch charts based on data fusion and cleaning, characterized in that, Includes the following steps: S1. Based on access parameters preset in a spreadsheet application, automatically obtain the product measurement dataset from at least one data source, including an in-plant query system, a customer database, or a local data file. S2. In response to the user's first interactive operation in the spreadsheet application, select one of a variety of predefined data retention strategies and run a first script program to perform one-click cleaning and reconstruction of the measurement dataset to generate a regularized dataset; the data retention strategy is based on multiple measurement sequences of the same product and is configured to retain a subset of data that conforms to a specific quality state and sequence position. S3. In response to the user's second interactive operation in the statistical analysis software, select one of the predefined multiple chart generation modes and run the second script program to generate statistical charts with unified naming conventions in batches based on the regularized dataset. S4. In response to a third interactive operation by the user in the spreadsheet application, select a report output format and run a third script program to automatically insert the statistical charts into the specified spreadsheet report or a newly created presentation report.

2. The method for automatically generating batch charts based on data fusion and cleaning according to claim 1, characterized in that, In step S2, the predefined multiple data retention strategies include: All measurement data are retained for analysis of the overall data distribution before and after maintenance; The initial measurement data is retained for analyzing the overall data distribution under initial production conditions; The last measurement data is retained for analysis of the overall data distribution in the final state; The initial OK data is retained for analysis of the data distribution of initial production qualified products to assess the initial state of the machine; The last OK data is retained for analysis of the data distribution of the final qualified products to verify the machine optimization effect; The initial NG data is retained for analysis of the specific distribution of initial defects to pinpoint the nature of the problem; The last NG data is retained to verify whether residual defects still exist after machine optimization.

3. The method for automatically generating batch charts based on data fusion and cleaning according to claim 2, characterized in that, In step S2, based on the selected data retention strategy, multiple measurement records in the measurement dataset are filtered, invalid jump points in the measurement records are automatically identified and removed, a configurable point filtering list is generated, and the data is extracted and format standardized. The invalid jump point is set to a predefined specific abnormal value.

4. The method for automatically generating batch charts based on data fusion and cleaning according to claim 3, characterized in that, In step S2, the specific processing logic for the configurable point list is as follows: When the user-configured list of points for filtering does not exist, a full-size or full-point list will be automatically generated for data processing based on the selected chart generation mode. When the user-configured list of locations for filtering exists, data filtering and extraction are performed by referring to the list of locations for filtering.

5. The method for automatically generating batch charts based on data fusion and cleaning according to claim 1, characterized in that, The predefined chart generation modes include: generation by size and with categories, generation by size and without categories, generation by location and with categories, and generation by location and without categories. When the "categorized" chart type is selected, the method allows users to customize a categorization field in the regularized dataset. The value of the categorization field is generated based on at least one rule among date, risk level, or production batch, so that the generated composite statistical chart can display the data distribution comparison according to the user-defined dimensions.

6. The method for automatically generating batch charts based on data fusion and cleaning according to claim 5, characterized in that, The statistical chart is a box plot, and the box plot is a composite statistical chart that combines the features of a conventional box plot, a heat map, and a mean shift line.

7. A batch chart automatic generation system for implementing the method of any one of claims 1 to 6, characterized in that, include: The data interface module is configured to communicate with at least one data source to obtain the product's measurement dataset. The information maintenance module, integrated into the spreadsheet application, is configured to provide a graphical user interface for users to configure access parameters and data cleaning rules for the data source. The data governance module is integrated into the spreadsheet application and configured to run a first script program to perform data cleaning and reconstruction tasks; The chart rendering module is integrated into the statistical analysis software and is configured to run a second script program to perform batch generation tasks of statistical charts; The report synthesis module, integrated into the spreadsheet application, is configured to run a third script to perform the automatic assembly and output of reports.

8. An electronic device, comprising: One or more processors; Memory, used to store one or more programs; When the one or more programs are executed by the one or more processors, the one or more processors implement the method as described in any one of claims 1 to 6.

9. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the program is executed by the processor, it implements the method as described in any one of claims 1 to 6.

Images