Pivot table processing method and apparatus, electronic device, and readable storage medium

Abstract

The application discloses a processing method and device of a pivot table, electronic equipment and a readable storage medium, and the method comprises the following steps: acquiring a pivot table, wherein the pivot table has one numerical field and at least two row and column fields; generating a corresponding data cross table according to the row and column fields; and generating a source data table of a visual analysis result of the pivot table according to the data cross table.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of computer, more particularly, to a processing method and device of pivot table, electronic equipment and readable storage medium. BACKGROUND

[0002] A pivot table is an interactive table that can perform certain calculations, such as sum and count. The calculations performed are related to the arrangement of data in the pivot table. They are called pivot tables because they can be dynamically rearranged to analyze data in different ways, and row numbers, column labels, and page fields can be rearranged. Each time the layout is changed, the pivot table will immediately recalculate the data according to the new layout. In addition, if the original data changes, the pivot table can be updated.

[0003] In the prior art, the visualization processing of the pivot table has a high threshold, and most of the pivot tables cannot directly perform data analysis and visualization display. Users can only analyze and organize new tables according to the table data of the pivot table for visualization display, which requires high self-business ability of the user and increases the labor cost. SUMMARY

[0004] An object of the present application is to provide a new technical solution for automatically processing a pivot table to obtain a source data table for generating visualized data.

[0005] According to a first aspect of the present application, a processing method of a pivot table is provided, comprising:

[0006] obtaining a pivot table, the pivot table having a numerical field and at least two row and column fields;

[0007] generating a corresponding data cross table according to the row and column fields;

[0008] generating a source data table of a visualized analysis result of the pivot table according to the data cross table.

[0009] Optionally, the at least two row and column fields include at least one row field and at least one column field.

[0010] The generating of the corresponding data cross table according to the row and column fields comprises:

[0011] traversing the row fields, combining the traversed row fields, the numerical field and any column field to form at least one first field combination;

[0012] According to the first field combination and the numerical values in the pivot table, a data cross table corresponding to the first field combination is obtained.

[0013] Optionally, the generating the source data table of the visual analysis result of the pivot table according to the data cross table further includes:

[0014] Traversing the data cross table;

[0015] According to a target direction, performing variance analysis on the traversed data cross table to obtain a corresponding F value; wherein the target direction includes a row direction and / or a column direction;

[0016] When the F value is less than a first preset threshold, processing the numerical values in the traversed data cross table according to the target direction to obtain a first data table;

[0017] Taking the first data table as the source data table.

[0018] Optionally, the generating the source data table of the visual analysis result of the pivot table according to the data cross table further includes:

[0019] When the F value is less than a first preset threshold, judging whether a proportion value of blank cells in the traversed data cross table is less than a second preset threshold;

[0020] When the proportion value of blank cells in the traversed data cross table is less than the second preset threshold, calculating a correlation coefficient matrix value of the traversed data cross table in the target direction;

[0021] When any of the correlation coefficient matrix values is greater than a third preset threshold, taking the traversed data cross table as the source data table.

[0022] Optionally, when the proportion value of blank cells in the traversed data cross table is less than the second preset threshold, further including:

[0023] Calculating a Mahalanobis distance value of data of each data cell in the traversed data cross table and the traversed data cross table; wherein in a case where the target direction is a row direction, one data cell represents a row of the traversed data cross table; and in a case where the target direction is a column direction, one data cell represents a column of the traversed data cross table;

[0024] Determining a degree of freedom value of the traversed data cross table in the target direction;

[0025] According to the Mahalanobis distance value and the degree of freedom value, using chi-square test to determine a P value of data of each data cell in the traversed data cross table.

[0026] generate a second data table according to a comparison result of a percentage of data of data units with a P value less than a fourth preset threshold and data of all data units of the data cross table traversed;

[0027] use the second data table as the source data table.

[0028] Optionally, the row-column field only contains a row field and does not contain a column field.

[0029] The generating of the corresponding data cross table according to the row-column field comprises:

[0030] traversing the row field and combining the traversed row field and the numerical value field to form a second field combination;

[0031] obtaining a data cross table corresponding to the second field combination according to the second field combination and numerical values in the pivot table.

[0032] Optionally, the generating of the source data table of the visual analysis result of the pivot table according to the data cross table further comprises:

[0033] traversing the data cross table and judging whether a type of the row field corresponding to the traversed data cross table is a date type;

[0034] when the type of the row field corresponding to the traversed data cross table is the date type, reordering rows in the traversed data cross table according to a time sequence to obtain the source data table;

[0035] when the type of the row field corresponding to the traversed data cross table is not the date type, reordering rows in the traversed data cross table according to numerical values of the numerical value field to obtain the source data table.

[0036] According to a second aspect of the present disclosure, a processing apparatus of a pivot table is provided, comprising:

[0037] a pivot table obtaining module configured to obtain a pivot table, the pivot table having one numerical value field and at least two row-column fields;

[0038] a cross table generating module configured to generate a corresponding data cross table according to the row-column field;

[0039] a source data table generating module configured to generate a source data table of a visual analysis result of the pivot table according to the data cross table.

[0040] According to a third aspect of the present disclosure, an electronic device is provided, comprising:

[0041] The apparatus according to the second aspect of the present disclosure; or

[0042] A processor and a memory, the memory being configured to store instructions for controlling the processor to perform the method according to the first aspect of the present disclosure.

[0043] According to a fourth aspect of the present disclosure, there is provided a readable storage medium having stored thereon a computer program which, when executed by a processor, implements the method according to the first aspect of the present disclosure.

[0044] Through the embodiments of the present disclosure, for the data perspective with one numerical field and at least two row and column fields, the corresponding data cross table is generated according to the row and column fields, and the source data table of the visual analysis result of the data perspective table is generated according to the data cross table, so that the source data table worth analyzing by the user can be accurately obtained, and the interference of excessive data on the user is reduced.

[0045] Other features of the present disclosure and its advantages will become apparent from the following detailed description of exemplary embodiments of the present disclosure with reference to the drawings. BRIEF DESCRIPTION OF DRAWINGS

[0046] The accompanying drawings, which are incorporated in and constitute a part of this specification, illustrate embodiments of the present disclosure and, together with the description, serve to explain the principles of the present disclosure.

[0047] Figure 1 is a block diagram of one example of a hardware configuration of an electronic device that can be used to implement embodiments of the present disclosure.

[0048] Figure 2 A flowchart of a processing method of a data perspective table of one embodiment of the present disclosure is shown.

[0049] Figure 3 A data perspective representation of one embodiment of the present disclosure is shown.

[0050] Figure 4 A flowchart of one example of a processing method of a data perspective table of one embodiment of the present disclosure is shown.

[0051] Figure 5 A block diagram of a processing apparatus of a data perspective table of one embodiment of the present disclosure is shown.

[0052] Figure 6 A block diagram of an electronic device of one embodiment of the present disclosure is shown. DETAILED DESCRIPTION

[0053] Various exemplary embodiments of the present application will now be described in detail with reference to the accompanying drawings. It should be noted that the relative arrangement of the components and steps set forth in the embodiments, numerical expressions, and numerical values, unless specifically stated otherwise, do not limit the scope of the present application.

[0054] The following description of at least one exemplary embodiment is merely exemplary in nature and is in no way intended to limit the scope of the application its application or uses.

[0055] Techniques, methods, and apparatus known to those of ordinary skill in the relevant art can not be discussed in detail herein, but should be considered as part of the specification, where appropriate.

[0056] In all of the examples shown and discussed herein, any specific values should be interpreted as merely exemplary, and not as a limitation. Thus, other examples of exemplary embodiments can have different values.

[0057] It should be noted that like numbers and letters refer to like items throughout the drawings, and once an item is defined in one drawing, it should not require further discussion in subsequent drawings.

[0058] <Hardware Configuration>

[0059] Figure 1 is a structural schematic diagram of an electronic device that can be used to implement embodiments of the present disclosure.

[0060] The electronic device 1000 can be a smartphone, a portable computer, a desktop computer, a tablet computer, a server, etc., and is not limited herein.

[0061] The electronic device 1000 can include, but is not limited to, a processor 1100, a memory 1200, an interface device 1300, a communication device 1400, a display device 1500, an input device 1600, a speaker 1700, a microphone 1800, and the like. The processor 1100 can be a central processing unit (CPU), a graphics processing unit (GPU), a micro controller unit (MCU), and the like, configured to execute a computer program, which can be written in an instruction set of an architecture such as x86, Arm, RISC, MIPS, SSE, and the like. The memory 1200 includes, for example, a ROM (read only memory), a RAM (random access memory), a non-volatile memory such as a hard disk, and the like. The interface device 1300 includes, for example, a USB interface, a serial interface, a parallel interface, and the like. The communication device 1400 can be configured to perform wired communication using an optical fiber or a cable, or wireless communication, and can include, for example, WiFi communication, Bluetooth communication, 2G / 3G / 4G / 5G communication, and the like. The display device 1500 can be, for example, a liquid crystal display, a touch display, and the like. The input device 1600 can include, for example, a touch screen, a keyboard, a body-sensing input, and the like. The speaker 1700 is configured to output an audio signal. The microphone 1800 is configured to acquire an audio signal.

[0062] In the embodiments of the present disclosure, the memory 1200 of the electronic device 1000 is configured to store a computer program configured to control the processor 1100 to perform operations to implement the method according to the embodiments of the present disclosure. The computer program can be designed by a skilled person according to the solutions disclosed in the present disclosure. How the computer program controls the processor to perform operations is known in the art, and thus will not be described in detail herein. The electronic device 1000 can be installed with a smart operating system (such as Windows, Linux, Android, IOS, and the like) and application software.

[0063] Those skilled in the art will understand that, although at least two devices of the electronic device 1000 are shown in Figure 1 the present disclosure, the electronic device 1000 of the embodiments of the present disclosure can only involve part of the devices, for example, only the processor 1100 and the memory 1200, and the like.

[0064] In the embodiments of the present disclosure, a pivot table is an interactive table that can perform certain calculations, such as summation and counting. The calculations performed are related to the arrangement of data in the pivot table. The reason why it is called a pivot table is that they can dynamically change their layout to analyze data in different ways, and can also rearrange row numbers, column labels, and page fields. Each time the layout is changed, the pivot table will immediately recalculate the data according to the new layout. In addition, if the original data changes, the pivot table can be updated.

[0065] And the ordinary data table is a grid virtual table (a table representing data in memory) temporarily storing data.

[0066] Data visualization refers to a process of representing data in large data sets in graphical images and discovering unknown information in the data using data analysis and development tools.

[0067] Due to the more complex data structure of the pivot table than the ordinary data table, the existing visualization tools cannot directly visualize the pivot table, that is, the visualization processing method of the ordinary data table cannot be applied to the pivot table.

[0068] Next, various embodiments and examples according to the present application are described with reference to the accompanying drawings.

[0069] <Method Embodiment>

[0070] In this embodiment, a processing method of a pivot table is provided. The method is implemented by an electronic device. The electronic device can be an electronic product with a processor and a memory. For example, it can be a desktop computer, a notebook computer, a mobile phone, a tablet computer, etc. In one example, the electronic device can be an electronic device 1000 as shown in the figure. Figure 1

[0071] Figure 2 A schematic flowchart of the processing method of the pivot table of one embodiment is shown. As shown in the figure, the processing method of the pivot table of the embodiment includes the following steps S2100-S2300: Figure 2

[0072] Step S2100, obtaining a pivot table, the pivot table having a numerical field and at least two row and column fields.

[0073] In this embodiment, the at least two row and column fields can include at least one row field and at least one column field, or at least two row fields and no column field.

[0074] In the pivot table as shown in the figure, a numerical field, two row fields and a column field can be included, wherein the numerical field can include sales quantity, the row field can include office and store, and the column field can include brand. Figure 3

[0075] Step S2200, generating a corresponding data cross table according to the row and column fields.

[0076] The cross table is a table in matrix format, showing the frequency distribution of variables.

[0077] ​​​In the embodiment in which the at least two row-column fields include at least one row field and at least one column field, generating the corresponding data cross table according to the row-column field can include steps S2211-S2212 as shown below:

[0078] Step S2211: traversing the row field, combining the traversed row field, the value field and any column field to form at least one first field combination.

[0079] In the embodiment, one first field combination can include the value field, one row field and one column field.

[0080] For example, in the pivot table as shown in Table 1 below, two first field combinations can be obtained, the first first field combination can include the sales quantity, the office and the brand, and the second first field combination can include the sales quantity, the store and the brand. Figure 3

[0081] Step S2212: obtaining the data cross table corresponding to the first field combination according to the first field combination and the values in the pivot table.

[0082] In the example as shown in Table 1 below, the data cross table obtained according to the first first field combination and the values in the pivot table can be as shown in Table 1 below, and the data cross table obtained according to the second first field combination and the values in the pivot table can be as shown in Table 2 below. Figure 3 Table 1

[0083]

[0084]

[0085]

[0086] Table 2

[0087]

[0088]

[0089] NaN in the above Table 1 and Table 2 indicates that the sales quantity is empty.

[0090] In the embodiment in which the at least two row-column fields include at least two row fields and do not include column fields, generating the corresponding data cross table according to the row-column field can include steps S2221-S2222 as shown below:

[0091] Step S2221: traversing the row field, combining the traversed row field and the value field to form a second field combination.

[0092] ​​In the embodiment, one second field combination can include a numerical field and one row field.

[0093] In step S2222, a data cross table corresponding to the second field combination is obtained according to the numerical value in the second field combination and the pivot table.

[0094] In step S2300, a source data table of the visual analysis result of the pivot table is generated according to the data cross table.

[0095] The source data table of the visual analysis result of the pivot table is a source data table used for visual analysis of the pivot table.

[0096] In the embodiment in which the at least two row-column fields include at least one row field and at least one column field, generating the source data table of the visual analysis result of the pivot table according to the data cross table can include steps S2311-S2313 as shown below:

[0097] In step S2311, the data cross table is traversed.

[0098] In step S2312, variance analysis is performed on the traversed data cross table according to a target direction to obtain a corresponding F value.

[0099] The target direction includes a row direction and / or a column direction.

[0100] In the embodiment, the row direction can be taken as the target direction, and step S2300 of the embodiment can be executed once; and / or the column direction can be taken as the target direction, and step S2300 of the embodiment can be executed once.

[0101] Analysis of Variance (ANOVA) is also called “variance analysis”, and is used for significant test of difference between two or more sample means.

[0102] The F value obtained by performing variance analysis on the traversed data cross table according to the target direction can reflect the difference between the means of the traversed data cross table in the target direction. For example, when the target direction is the row direction, the F value can reflect the difference between the means of all rows of the traversed data cross table. For another example, when the target direction is the column direction, the F value can reflect the difference between the means of all columns of the traversed data cross table.

[0103] In step S2313, when the F value is less than a first preset threshold, the numerical values in the traversed data cross table are processed according to the target direction to obtain a first data table.

[0104] In the embodiment, the first preset threshold can be set in advance according to application scenarios or specific requirements. For example, the first preset threshold can be 0.5.

[0105] According to the target direction, the values in the data cross table can be processed according to the value field and the row-column field corresponding to the target direction, that is, the first data table can be obtained by processing the values in the traversed data cross table.

[0106] In the case that the traversed cross data table is Table 1 and the target direction is the row direction, and the F value of the cross data table in the row direction is less than the first preset threshold, the value field of the traversed cross data table is the sales quantity, and the row-column field corresponding to the row direction is the office. Therefore, the values corresponding to each office in the sales quantity of the traversed data cross table can be summed up according to the sales quantity and the office, and the first data table obtained can be shown in Table 3 as follows.

[0107] Table 3

[0108] Office Chongqing Office West Chongqing Office Wanxian Office Fuling Office Guang'an Office Sales Quantity 230 194 60 42 3

[0109] In the case that the traversed cross data table is Table 1 and the target direction is the row direction, and the F value of the cross data table in the row direction is less than the first preset threshold, the value field of the traversed cross data table is the sales quantity, and the row-column field corresponding to the row direction is the office. Therefore, the values corresponding to each office in the sales quantity of the traversed data cross table can be summed up according to the sales quantity and the office, and the first data table obtained can be shown in Table 3 as follows.

[0110] In the case that the traversed cross data table is Table 1 and the target direction is the column direction, and the F value of the cross data table in the column direction is less than the first preset threshold, the value field of the traversed cross data table is the sales quantity, and the row-column field corresponding to the column direction is the brand. Therefore, the values corresponding to each brand in the sales quantity of the traversed data cross table can be summed up according to the sales quantity and the brand, and the first data table obtained can be shown in Table 4 as follows.

[0111] Table 4

[0112] Brand Hisense TCL Changhong Skyworth Konka Haier CAN Sales Quantity 122 121 108 98 61 18 1

[0113] In the case that the traversed cross data table is Table 2 and the target direction is the row direction, and the F value of the cross data table in the row direction is less than the first preset threshold, the value field of the traversed cross data table is the sales quantity, and the row-column field corresponding to the row direction is the store. Therefore, the values corresponding to each store in the sales quantity of the traversed data cross table can be summed up according to the sales quantity and the store, and the first data table obtained can be shown in Table 5 as follows.

[0114] Table 5

[0115]

[0116]

[0117] Step S2314, taking the first data table as a source data table.

[0118] Further, according to the data cross table, generating the source data table of the visual analysis result of the pivot table can further include steps S2315-S2317 as follows:

[0119] Step S2315, when the F value is less than the first threshold value, judging whether the proportion value of the blank cells in the traversed data cross table is less than a second preset threshold value.

[0120] The blank cells can be the cells with the value of NaN in the data cross table.

[0121] The proportion value of the blank cells can be a ratio of a first quantity and a second quantity, where the first quantity is the number of the cells with the value of NaN in the traversed data cross table, and the second quantity is the number of the cells with the value in the traversed data cross table.

[0122] The second preset threshold value can be set according to the application scenario or specific requirements in advance. For example, the second preset threshold value can be 50%.

[0123] Step S2316, when the proportion value of the blank cells in the traversed data cross table is less than the second preset threshold value, calculating the correlation coefficient matrix value of the traversed data cross table in the target direction.

[0124] In this embodiment, the value matrix can be constructed according to the values in the traversed data cross table.

[0125] In the case where the target direction is the column direction, the correlation coefficient matrix can be composed of the correlation coefficients between the columns of the value matrix. That is, the element in the i-th row and the j-th column of the correlation coefficient matrix is the correlation coefficient between the i-th column and the j-th column of the value matrix. In the case where the target direction is the row direction, the correlation coefficient matrix can be composed of the correlation coefficients between the columns of the transpose matrix of the value matrix. That is, the element in the i-th row and the j-th column of the correlation coefficient matrix is the correlation coefficient between the i-th column and the j-th column of the transpose matrix of the value matrix.

[0126] The correlation coefficient matrix value can be the values of the elements in the correlation coefficient matrix.

[0127] Step S2317, when any correlation coefficient matrix value is greater than a third preset threshold value, the traversed data cross table is taken as the source data table.

[0128] In the embodiment, in the case that the numerical value of each element in the correlation coefficient matrix is greater than the third threshold value, the traversed data cross table can be taken as the source data table.

[0129] The third preset threshold value can be set in advance according to application scenarios or specific requirements. For example, the third preset threshold value can be 0.75.

[0130] Further, after step S2315 is executed, the following steps S2318-S23112 can be executed when the proportion of blank cells in the traversed data cross table is less than a second preset threshold value. In the embodiment, steps S2316-S2317 and steps S2318-S23112 can be executed simultaneously, or only steps S2316-S2317 or steps S2318-S23112 can be executed.

[0131] Step S2318, the Mahalanobis distance value of each data cell in the traversed data cross table and the traversed data cross table is calculated.

[0132] In the case that the target direction is the row direction, one data cell represents a row of the traversed data cross table; in the case that the target direction is the column direction, one data cell represents a column of the traversed data cross table.

[0133] The Mahalanobis distance can be defined as the difference between two random variables subject to the same distribution and whose covariance matrix is Σ. It represents the distance between a point and a distribution. It is an effective method for calculating the similarity of two unknown sample sets.

[0134] In the case that the target direction is the row direction, the row vector composed of the numerical value of each row in the traversed data cross table can be calculated, and the Mahalanobis distance value of the numerical value matrix composed of the traversed data cross table is calculated. In the case that the target direction is the column direction, the column vector composed of the numerical value of each column in the traversed data cross table can be calculated, and the Mahalanobis distance value of the numerical value matrix composed of the traversed data cross table is calculated.

[0135] Step S2319, the degree of freedom value of the traversed data cross table in the target direction is determined.

[0136] In the embodiment, determining the degree of freedom value of the traversed data cross table in the target direction can include: constructing a numerical value matrix according to the numerical value in the traversed data cross table; and calculating the degree of freedom value of the numerical value matrix in the target direction.

[0137] For the numerical matrix constituted according to the traversed data cross table, the degree of freedom value can reflect the constraint state of each data unit in the numerical matrix.

[0138] In step S23110, according to the Mahalanobis distance value and the degree of freedom value, the P value of the data of each data unit in the traversed data cross table is determined by using the chi-square test.

[0139] The chi-square test is to determine the deviation degree between the actual observation value and the theoretical inference value of the statistical sample. The deviation degree between the actual observation value and the theoretical inference value determines the size of the chi-square value. If the chi-square value is larger, the deviation degree is larger; otherwise, the deviation degree is smaller. If the two values are completely equal, the chi-square value is 0, indicating that the theoretical value is completely consistent.

[0140] In step S23111, according to the percentage comparison result of the data of the data unit with the P value less than the fourth preset threshold value and the data of all data units of the traversed data cross table, a second data table is generated.

[0141] In this embodiment, the fourth preset threshold value can be set in advance according to the application scene or specific requirements. For example, the fourth preset threshold value can be 0.005.

[0142] In the case where the target direction is the row direction, according to the percentage comparison result of the data of the data unit with the P value less than the fourth preset threshold value and the data of all data units of the traversed data cross table, the value of the row corresponding to any column in the second data table can be obtained according to the percentage of the value of the row corresponding to any column with the sum of the values of all rows corresponding to the column.

[0143] In the case where the traversed data cross table is Table 2, the target direction is the row direction, and the P values of the rows corresponding to all stores, Chongqing Zhongbai Commercial Electrical Appliances Co., Ltd. Yangjiaping Store, and Chongqing Zhongbai Commercial Electrical Appliances Co., Ltd. Jiefangbei Store are less than the fourth preset threshold value, the values of the corresponding elements in the second data table can be obtained according to the ratio between the values of the rows corresponding to each brand and the sum of the values corresponding to each brand.

[0144] Table 6

[0145]

[0146] In step S23112, the second data table is taken as the source data table.

[0147] In the embodiment in which the at least two row and column fields include at least two row fields and do not include column fields, according to the data cross table, the source data table of the visual analysis result of the pivot table can include steps S2321-S2323 as follows:

[0148] Step S2321, traversing the data cross table, judging whether the type of the row field corresponding to the traversed data cross table is the date type.

[0149] Step S2322, when the type of the row field corresponding to the traversed data cross table is the date type, reordering the rows in the traversed data cross table according to the time sequence to obtain the source data table.

[0150] In the case that the traversed data cross table is shown in Table 7 below, the source data table obtained by reordering the rows in the traversed data cross table according to the time sequence can be shown in Table 8 below.

[0151] Table 7

[0152] Date 1st 5th 2nd 4th 3rd Sales Quantity 230 194 60 42 3

[0153] Table 8

[0154] Date 1st 2nd 3rd 4th 5th Sales Quantity 230 60 3 42 194

[0155] Step S2323, when the type of the row field corresponding to the traversed data cross table is not the date type, reordering the rows in the traversed data cross table according to the values of the value fields to obtain the source data table.

[0156] In the case that the traversed data cross table is shown in Table 9 below, the source data table obtained by reordering the rows in the traversed data cross table according to the values of the value fields can be shown in Table 10 below.

[0157] Table 9

[0158] Office West Chongqing Office Fuling Office Wanxian Office Guang'an Office Chongqing Office Sales Quantity 194 42 60 3 230

[0159] Table 10

[0160] Office Chongqing Office West Chongqing Office Wanxian Office Fuling Office Guang'an Office Sales Quantity 230 194 60 42 3

[0161] In this embodiment, by reordering the rows in the data cross table, the user can conveniently view the source data table.

[0162] According to the embodiments of the present disclosure, for the data perspective with one value field and at least two row and column fields, the corresponding data cross table is generated according to the row and column fields, and the source data table of the visual analysis result of the data perspective table is generated according to the data cross table, so that the source data table worth analyzing by the user can be accurately obtained, and the interference of excessive data on the user is reduced.

[0163] <Example>

[0164] Figure 4A flow chart of one example of a processing method of a pivot table of one embodiment of the present disclosure is shown.

[0165] According to Figure 4 As shown in the figure, the processing method of the pivot table can include steps S4001-S4021 as shown below:

[0166] Step S4001, a pivot table is acquired, the pivot table having one numerical field and at least two row-column fields.

[0167] Step S4002, it is determined whether the at most two row-column fields include a column field, if yes, step S4003 is executed; if no, step S4017 is executed.

[0168] Step S4003, the row fields are traversed, and the traversed row fields, the numerical field and any column field are combined to form at least one first field combination.

[0169] Step S4004, according to the first field combination and the numerical values in the pivot table, a data cross table corresponding to the first field combination is obtained.

[0170] Step S4005, the data cross table is traversed.

[0171] Step S4006, according to a target direction, a variance analysis is performed on the traversed data cross table to obtain a corresponding F value.

[0172] Step S4007, when the F value is less than a first preset threshold value, according to the target direction, the numerical values in the traversed data cross table are processed to obtain a first data table.

[0173] Step S4008, the first data table is taken as a source data table.

[0174] Step S4009, when the F value is less than the first threshold value, it is judged whether a proportion value of blank cells in the traversed data cross table is less than a second preset threshold value.

[0175] Step S4010, when the proportion value of blank cells in the traversed data cross table is less than the second preset threshold value, a correlation coefficient matrix value of the traversed data cross table in the target direction is calculated.

[0176] Step S4011, when any correlation coefficient matrix value is greater than a third preset threshold value, the traversed data cross table is taken as the source data table.

[0177] Step S4012, a Mahalanobis distance value of each data cell in the traversed data cross table and the traversed data cross table is calculated.

[0178] Step S4013: Determine the degree of freedom of the traversed data cross-tab in the target direction.

[0179] Step S4014: Based on the Mahalanobis distance and degrees of freedom, use the chi-square test to determine the P-value of each data cell in the traversed data cross-tabulation.

[0180] Step S4015: Generate a second data table based on the percentage comparison of data units with P values ​​less than the fourth preset threshold and all data units in the traversed data cross-tabulation.

[0181] Step S4016: Use the second data table as the source data table.

[0182] Step S4017: Traverse the row fields, combine the traversed row fields and numeric fields to form the second field combination.

[0183] Step S4018: Based on the second field combination and the values ​​in the pivot table, obtain the corresponding data cross-tabulation for the second field combination.

[0184] Step S4019: Traverse the data cross-tabulation and determine whether the data type of the row field corresponding to the traversed data cross-tabulation is date type.

[0185] Step S4020: When the row field type of the data cross table being traversed is date type, the rows in the data cross table being traversed are reordered according to the time order to obtain the source data table.

[0186] Step S4021: When the row field type of the data cross table being traversed is not date type, the rows in the data cross table being traversed are reordered according to the value of the numeric field to obtain the source data table.

[0187] <Device Embodiment>

[0188] In this embodiment, a pivot table processing apparatus 5000 is provided, such as... Figure 5 As shown, the system includes a pivot table acquisition module 5100, a crosstab generation module 5200, and a source data table generation module 5300. The pivot table acquisition module 5100 acquires a pivot table, which has one numeric field and at least two row and column fields. The crosstab generation module 5200 generates a corresponding crosstab based on the row and column fields. The source data table generation module 5300 generates a source data table representing the visualization analysis results of the pivot table based on the crosstab.

[0189] In one embodiment of this disclosure, at least two row and column fields include at least one row field and at least one column field;

[0190] The cross table generation module 5200 can include:

[0191] The first combination unit is configured to traverse the row field, and combine the traversed row field, the value field and any column field to form at least one first field combination.

[0192] The first cross table obtaining unit is configured to obtain a data cross table corresponding to the first field combination according to the first field combination and the values in the pivot table.

[0193] In an embodiment of the present disclosure, the source data table generation module 5300 can further include:

[0194] The first traversal unit is configured to traverse the data cross table.

[0195] The F value obtaining unit is configured to perform analysis of variance on the traversed data cross table according to the target direction to obtain a corresponding F value; wherein the target direction includes a row direction and / or a column direction.

[0196] The first processing unit is configured to, when the F value is less than a first preset threshold, process the values in the traversed data cross table according to the target direction to obtain a first data table.

[0197] The first source table obtaining unit is configured to take the first data table as a source data table.

[0198] In an embodiment of the present disclosure, the source data table generation module 5300 can further include:

[0199] The judging unit is configured to, when the F value is less than the first preset threshold, judge whether a proportion value of blank cells in the traversed data cross table is less than a second preset threshold.

[0200] The first calculation unit is configured to, when the proportion value of blank cells in the traversed data cross table is less than the second preset threshold, calculate a correlation coefficient matrix value of the traversed data cross table in the target direction.

[0201] The second source table obtaining unit is configured to, when any correlation coefficient matrix value is greater than a third preset threshold, take the traversed data cross table as a source data table.

[0202] In an embodiment of the present disclosure, the source data table generation module 5300 can further include:

[0203] The second calculation unit is configured to calculate a Mahalanobis distance value of each data unit in the traversed data cross table and the traversed data cross table; wherein, in a case where the target direction is a row direction, one data unit represents a row of the traversed data cross table; and in a case where the target direction is a column direction, one data unit represents a column of the traversed data cross table.

[0204] a third computing unit, configured to determine a degree of freedom value of the traversed data cross table in the target direction;

[0205] a P value determining unit, configured to determine, according to the Mahalanobis distance value and the degree of freedom value, a P value of data in each data cell in the traversed data cross table by using a chi-square test;

[0206] a second processing unit, configured to generate a second data table according to a comparison result of a percentage of data in data cells with P values less than a fourth preset threshold and data in all data cells in the traversed data cross table;

[0207] a third source table obtaining unit, configured to obtain the second data table as a source data table.

[0208] In an embodiment of the present disclosure, the row-column field only contains the row field and does not contain the column field.

[0209] The cross table generating module 5200 can further include:

[0210] a second combining unit, configured to traverse the row field, and combine the traversed row field and the value field to form a second field combination;

[0211] a second cross table obtaining unit, configured to obtain a data cross table corresponding to the second field combination according to the second field combination and the values in the pivot table.

[0212] In an embodiment of the present disclosure, the source data table generating module 5300 can further include:

[0213] a second traversing unit, configured to traverse the data cross table, and determine whether a type of a row field corresponding to the traversed data cross table is a date type;

[0214] a fourth source table obtaining unit, configured to, when the type of the corresponding row field is the date type, reorder rows in the traversed data cross table according to a time sequence to obtain the source data table;

[0215] a fifth source table obtaining unit, configured to, when the type of the corresponding row field is not the date type, reorder the rows in the traversed data cross table according to values of the value field to obtain the source data table.

[0216] Those skilled in the art should understand that the pivot table processing apparatus 5000 can be implemented in various ways. For example, the pivot table processing apparatus 5000 can be implemented by configuring a processor with instructions. For example, the instructions can be stored in a ROM, and when the device is started, the instructions are read from the ROM to a programmable device to implement the pivot table processing apparatus 5000. For example, the pivot table processing apparatus 5000 can be fixed in a special device (such as an ASIC). The pivot table processing apparatus 5000 can be divided into independent units, or they can be combined together to implement. The pivot table processing apparatus 5000 can be implemented by one of the above-mentioned various implementations, or can be implemented by a combination of two or more of the above-mentioned various implementations.

[0217] In this embodiment, the pivot table processing apparatus 5000 can have various forms of implementation. For example, the pivot table processing apparatus 5000 can be a functional module running in any software product or application program that provides pivot table processing services, or a peripheral embedded, plug-in, patch, etc. of these software products or application programs, or the software products or application programs themselves.

[0218] <Embodiment of electronic device>

[0219] The present disclosure also provides an electronic device 6000.

[0220] In one embodiment, the electronic device 6000 can include the aforementioned pivot table processing apparatus 5000.

[0221] In another embodiment, the electronic device 6000 can also include a processor 6100 and a memory 6200 as shown in Figure 6 The memory 6200 is used to store executable instructions; the instructions are used to control the processor 6100 to execute the aforementioned pivot table processing method.

[0222] In this embodiment, the electronic device 6000 can be any electronic product with a processor 6100 and a memory 6200, such as a mobile phone, a tablet computer, a palmtop computer, a desktop computer, a notebook computer, a workstation, a game console, a server, etc.

[0223] <Embodiment of readable storage medium>

[0224] In this embodiment, a readable storage medium is also provided, which stores a computer program, and the computer program is executed by a processor to implement the pivot table processing method of any embodiment of the present disclosure.

[0225] The present application can be a system, a method, and / or a computer program product. The computer program product can include a computer readable storage medium (or media) having computer readable program instructions thereon for causing a processor to carry out aspects of the present application.

[0226] The computer readable storage medium can be a tangible device that can retain and store instructions for use by an instruction execution device. The computer readable storage medium can be, for example, but is not limited to, an electronic storage device, a magnetic storage device, an optical storage device, an electromagnetic storage device, a semiconductor storage device, or any suitable combination of the foregoing. More specific examples (a non-exhaustive list) of the computer readable storage medium include the following: a portable computer diskette, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or Flash memory), a static random access memory (SRAM), a portable compact disc read-only memory (CD-ROM), a digital versatile disk (DVD), a memory stick, a floppy disk, a mechanically encoded device such as punch-cards or punched tape, a

[0227] The computer readable program instructions described herein can be downloaded to respective computing / processing devices from a computer readable storage medium or to an external computer or external storage device via a network, for example, the Internet, a local area network, a wide area network and / or a wireless network. The network can comprise copper transmission cables, optical transmission fibers, wireless transmission, routers, firewalls, switches, gateway computers and / or edge servers. A network adapter card or network interface in each computing / processing device receives computer readable program instructions from the network and forwards the computer readable program instructions for storage in a computer readable storage medium within the respective computing / processing device.

[0228] Computer readable program instructions for carrying out operations of the present application can be assembler instructions, instruction-set-architecture (ISA) instructions, machine instructions, machine dependent instructions, microcode, firmware instructions, state-setting data, or either source code or object code written in any combination of one or more programming languages, including an object oriented programming language such as Smalltalk, C++ or the like and conventional procedural programming languages, such as the "C" programming language or similar programming languages. The computer readable program instructions can execute entirely on the user's computer, partly on the user's computer, as a stand-alone software package, partly on the user's computer and partly on a remote computer or entirely on the remote computer or server. In the latter scenario, the remote computer can be connected to the user's computer through any type of network, including a local area network (LAN) or a wide area network (WAN), or the connection can be made to an external computer (for example, through the Internet using an Internet Service Provider). In some embodiments, electronic circuitry including, for example, programmable logic circuitry, field-programmable gate array (FPGA), or programmable logic array (PLA) can execute the computer readable program instructions by utilizing state information of the computer readable program instructions to personalize the electronic circuitry, in order to perform aspects of the present application.

[0229] The computer readable program instructions can also be loaded onto a computer, other programmable data processing apparatus, or other device to cause a series of operational steps to be performed on the computer, other programmable apparatus or other device to produce a computer implemented process such that the instructions which execute on the computer or other programmable apparatus provide processes for implementing the functions / acts specified in the flowchart and / or block diagram block or blocks.

[0230] These computer readable program instructions can be provided to a processor of a general purpose computer, special purpose computer, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, create means for implementing the functions / acts specified in the flowchart and / or block diagram block or blocks. These computer readable program instructions can also be stored in a computer readable storage medium that can include random access memory (RAM), read only memory (ROM), electrically erasable programmable read only memory (EEPROM), flash memory or other data storage device. When the computer readable program instructions are loaded into the computer and other programmable data processing apparatus, a series of operational steps are implemented that provide processes such that the computer or other programmable apparatus provide processes for implementing the functions / acts specified in the flowchart and / or block diagram block or blocks.

[0231] The computer readable program instructions can also be loaded onto a computer, other programmable data processing apparatus, or other device to cause a series of operational steps to be performed on the computer, other programmable data processing apparatus or other device to produce a computer implemented process such that the instructions which execute on the computer, other programmable data processing apparatus, or other device implement the functions / acts specified in the flowchart and / or block diagram block or blocks.

[0232] The flow diagrams and the block diagrams in the drawings are meant as possible implementations of a system, method, and computer program product according to one or more embodiments of the application. In this regard, each block in the flow diagram or block diagram can represent a module, a segment, or a portion of instructions, which includes one or more executable instructions for implementing the specified logical function(s). In some alternative implementations, the functions noted in the blocks can occur out of the order noted in the figures. For example, two blocks shown in succession may, in fact, be executed substantially concurrently, or the blocks may sometimes be executed in the reverse order, depending upon the functionality involved. It will also be noted that each block of the block diagrams and / or flowchart illustrations, and combinations thereof, can be implemented by special purpose hardware-based systems that perform the specified functions or acts, or combinations of special purpose hardware and computer instructions.

[0233] Embodiments of the application have been described above with the aid of example implementations of components and methods that are exemplary, but not exhaustive, in nature. Many modifications and variations of the described implementations are possible and will be apparent to those of ordinary skill in the art. The specific embodiments described are illustrative embodiments that do not limit the scope of the application. The scope of the present application is defined by the following claims.

Claims

1. A method for processing pivot tables, characterized in that, include: Obtain a pivot table that has one numeric field and at least two row and column fields; Generate a corresponding cross-tab based on the row and column fields; The source data table for generating the visualization analysis results of the pivot table based on the data crosstab; The source data table for generating the visualization analysis results of the pivot table based on the data crosstab includes: Traverse the data crosstab; Based on the target direction, an analysis of variance is performed on the traversed data crosstab to obtain the corresponding F-value; wherein, the target direction includes the row direction and / or the column direction; When the F value is less than the first preset threshold, the values ​​in the traversed data cross table are processed according to the target direction to obtain the first data table. The first data table is used as the source data table.

2. The method according to claim 1, characterized in that, In the case where the at least two row and column fields include at least one row field and at least one column field; The step of generating the corresponding cross-tabulation based on the row and column fields includes: Traverse the row fields, and combine the traversed row fields, the numeric fields, and any of the column fields to form at least one first field combination; Based on the first field combination and the values ​​in the pivot table, a cross-tabulation corresponding to the first field combination is obtained.

3. The method according to claim 1, characterized in that, The source data table for generating the visualization analysis results of the pivot table based on the data crosstab also includes: When the F value is less than the first preset threshold, determine whether the proportion of blank cells in the traversed data cross table is less than the second preset threshold. When the proportion of blank cells in the traversed data cross table is less than the second preset threshold, the correlation coefficient matrix value of the traversed data cross table in the target direction is calculated. When any of the correlation coefficient matrix values ​​is greater than a third preset threshold, the traversed data cross-tabulation is used as the source data table.

4. The method according to claim 3, characterized in that, When the proportion of blank cells in the traversed data cross table is less than the second preset threshold, the method further includes: Calculate the Mahalanobis distance between the data of each data cell in the traversed data crosstab and the traversed data crosstab; wherein, when the target direction is the row direction, one data cell represents a row of the traversed data crosstab; when the target direction is the column direction, one data cell represents a column of the traversed data crosstab. Determine the degree of freedom value of the traversed data crosstab in the target direction; Based on the Mahalanobis distance value and the degree of freedom value, the p-value of the data in the data cell of the traversed data cross-tab is determined using the chi-square test; A second data table is generated based on the percentage comparison between the data of the data unit whose P value is less than the fourth preset threshold and the data of all data units in the traversed data cross table. The second data table is used as the source data table.

5. The method according to claim 1, characterized in that, In the case where the row and column fields only contain row fields and not column fields; The step of generating the corresponding cross-tabulation based on the row and column fields includes: Traverse the row fields and combine the traversed row fields and the numeric fields to form a second field combination; Based on the second field combination and the values ​​in the pivot table, a cross-tabulation corresponding to the second field combination is obtained.

6. The method according to claim 5, characterized in that, The source data table for generating the visualization analysis results of the pivot table based on the data crosstab also includes: Traverse the data cross table and determine whether the row field corresponding to the traversed data cross table is of type date. If the corresponding row field is of type date, the rows in the traversed data cross table are reordered according to time order to obtain the source data table; When the corresponding row field is not of type date, the rows in the traversed data cross table are reordered according to the value of the numeric field to obtain the source data table.

7. A data pivot table processing apparatus, characterized in that, include: A pivot table acquisition module is used to acquire a pivot table, which has a numeric field and at least two row and column fields. The cross-tab generation module is used to generate corresponding data cross-tabs based on the row and column fields. The source data table generation module is used to generate a source data table for the visualization analysis results of the pivot table based on the data crosstab. The source data table generation module also includes: The first traversal unit is used to traverse the data cross table; The F-value acquisition unit is used to perform variance analysis on the traversed data cross-tabulation according to the target direction to obtain the corresponding F-value; wherein, the target direction includes the row direction and / or the column direction; The first processing unit is used to process the values ​​in the traversed data cross table according to the target direction when the F value is less than the first preset threshold, so as to obtain the first data table. The first source table acquisition unit is used to use the first data table as the source data table.

8. An electronic device, characterized in that, include: The apparatus as described in claim 7; or, A processor and a memory, the memory being used to store instructions for controlling the processor to perform the method according to any one of claims 1 to 6.

9. A readable storage medium, characterized in that, It stores a computer program that, when executed by a processor, implements the method as described in any one of claims 1 to 6.

Images