Automated suggested summarizations of data

• US 8,793,567 B2
• Filed: 11/16/2011
• Issued: 07/29/2014
• Est. Priority Date: 11/16/2011
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Velingkar, Amit, Leong, Weng Keong Peter Anthony, Folting, Allan, Sutter, Carl Brenn IV, Oppenheimer, Diego M., Handy, Stephen Van de Walker
• Primary Examiner(s): Mills, Frank D
• Application Number:
  US13/298,285
• Publication Number:
  US 20130124960A1
• Time in Patent Office: 986 Days
• Field of Search: 715/220
• US Class Current: 715/220
• CPC Class Codes: G06F 16/20 of structured data, e.g. relational data
• CPC Class Codes: G06F 40/18 of spreadsheets form-filling G06F40/174

Abstract

Technologies are described herein for generating field sets of a data summary table. Multiple fields in a spreadsheet document are identified. Multiple field scores for each field of the plurality of fields are determined. A field set incorporating two or more of the plurality of fields is generated based on the plurality of field scores. The field set is inserted into the data summary table, thereby transforming the data summary table without the field set to the data summary table with the field set.

Claims

1. A computer-implemented method for generating field sets of a data summary table, the method comprising computer-implemented operations for:

identifying a plurality of fields in a spreadsheet document;

determining a plurality of field scores for each field of the plurality of fields;

generating a field set incorporating two or more of the plurality of fields based on the plurality of field scores by generating a simple field set and generating a normal field set; and

inserting the field set into the data summary table, thereby transforming the data summary table without the field set to the data summary table with the field set.

2. The computer-implemented method of claim 1, wherein determining the plurality of field scores for each of the plurality of fields comprises:

determining a row score, a column score, and a value score for each of the plurality of fields.

3. The computer-implemented method of claim 2, wherein determining the row score, the column, score and the value score for each of the plurality of fields comprises:

for each field of the plurality of fields,

determining a number of empty cells under the field,

adjusting the row score for the field by a first amount based on the number of empty cells under the field,

adjusting the column score for the field by a second amount based on the number of empty cells under the field, and

adjusting the value score for the field by a third amount based on the number of empty cells under the field.

4. The computer-implemented method of claim 2, wherein determining the row score, the column score and the value score for each of the plurality of fields comprises:

for each field of the plurality of fields,

determining a data type associated each value under the field,

adjusting the row score for the field by a first amount based on the data type of each value under the field,

adjusting the column score for the field by a second amount based on the data type of each value under the field, and

adjusting the value score for the field by a third amount based on the data type of each value under the field.

5. The computer-implemented method of claim 2, wherein determining the row score, the column, score and the value score for each of the plurality of fields comprises:

for each field of the plurality of fields,

determining whether a name of the field matches a keyword,

adjusting the row score for the field by a first amount when the name of the field matches the keyword,

adjusting the column score for the field by a second amount when the name of the field matches the keyword, and

adjusting the value score for the field by a third amount when the name of the field matches the keyword.

6. The computer-implemented method of claim 2, wherein generating the simple field set comprises:

selecting a first highest scoring field of the plurality of fields having the highest row score;

selecting a second highest scoring field of the plurality of fields having the highest value score;

generating the simple field set having a plurality of row labels corresponding to the first highest scoring field and a value column corresponding to the second highest scoring field, the value column comprising a plurality of cells, each of the plurality of cells corresponding to one of the plurality of row labels, each of the plurality of cells containing a computation result of a mathematical result corresponding to one of the plurality of row labels;

reducing the first highest scoring field and the second highest scoring field; and

incrementing a first usage count associated with the first highest scoring field and a second usage count associated with the second highest scoring field.

7. The computer-implemented method of claim 2, wherein generating the normal field set comprises:

selecting a first highest scoring field of the plurality of fields having the highest row score;

selecting a second highest scoring field of the plurality of fields having the highest column score;

selecting a third highest scoring field of the plurality of fields having the highest value score;

determining whether the second highest scoring field is greater than the first highest scoring field;

in response to determining that the second highest scoring field is not greater than the first highest scoring field,

generating the normal field set having a plurality of row labels corresponding to the first highest scoring field and a value column corresponding to the third highest scoring field, the value column comprising a first plurality of cells, each of the first plurality of cells corresponding to one of the plurality of row labels, each of the first plurality of cells containing a computation result of a mathematical function and corresponding to one of the plurality of row labels,

reducing the first highest scoring field and the third highest scoring field, and

incrementing a first usage count associated with the first highest scoring field and a third usage count associated with the third highest scoring field; and

in response to determining that the second highest scoring field is greater than the first highest scoring field,

generating the normal field set having the plurality of row labels corresponding to the first highest scoring field, a plurality of column labels corresponding to the second highest scoring field, and the value column corresponding to the third highest scoring field, the plurality of column labels comprising a first column label and a second column label, the first column label associated with a first additional column in the normal field set and the second column label associated with a second additional column in the normal field set, the first additional column comprising a second plurality of cells, the second plurality of cells corresponding to one of the plurality of row labels and the first column label, each of the second plurality of cells containing a computation result of the mathematical function and corresponding to one of the plurality of row labels and the first column label, the second additional column comprising a third plurality of cells, the third plurality of cells corresponding to one of the plurality of row labels and the second column label, each of the third plurality of cells containing a computation result of the mathematical function and corresponding to one of the plurality of row labels and the second column label,

reducing the first highest scoring field, the second highest scoring field, and the third highest scoring field, and

incrementing the first usage count associated with the first highest scoring field, a second usage count associated with the second highest scoring field, and the third usage count associated with the third highest scoring field.

8. The computer-implemented method of claim 7, further comprising computer-implemented operations for:

generating a hierarchical mapping for the plurality of row labels and the plurality of column labels;

arranging an order of the plurality of row labels in the normal field set in accordance with the hierarchical mapping; and

arranging an order of the plurality of column labels in the normal field set in accordance with the hierarchical mapping.

9. The computer-implemented method of claim 1, wherein generating the field set incorporating two or more of the plurality of fields based on the plurality of field scores comprises:

determining whether the field set fails a quality check; and

in response to determining that the field set fails the quality check, discarding the field set.

10. The computer-implemented method of claim 9, wherein determining whether the field set fails the quality check comprises:

determining whether the field set has either a low width-to-height ratio below a first threshold or a high width-to-height ratio above a second threshold; and

in response to determining that the field has either the low width-to-height ratio below the first threshold or the high width-to-height ratio above the second threshold, determining that the field set fails the quality check.

11. The computer-implemented method of claim 9, wherein determining whether the field set fails the quality check comprises:

determining whether the field set has either a low number of rows below a first threshold, a high number of rows above a second threshold, or a high number of columns above a third threshold; and

in response to determining that the field set has either the low number of rows below the first threshold, the high number of rows above the second threshold, or the high number of columns above the third threshold, determining that the field set fails the quality check.

12. The computer-implemented method of claim 9, wherein determining whether the field set fails the quality comprises:

determining whether the field set has a high number of empty cells above a threshold; and

in response to determining that the field set has the high number of empty cells above the threshold, determining that the field set fails the quality check.

13. The computer-implemented method of claim 2, further comprising computer-implemented operations for:

receiving a user selection of a field in the plurality of fields; and

upon receiving the user selection of the field, increasing a field score in the plurality of field scores corresponding to the field.

14. The computer-implemented method of claim 1, further comprising computer-implemented operations for:

determining whether a quantity of rows in the spreadsheet document is greater than a threshold; and

in response to determining that the quantity of rows in the spreadsheet document is greater than the threshold, performing a sampling process adapted to reduce the quantity of rows in the spreadsheet document evaluated when determining the field scores.

15. The computer-implemented method of claim 1, further comprising computer-implemented operations for:

identifying a single field from the plurality of fields; and

generating a frequency table containing a plurality of row labels corresponding to the single field and a single column containing a plurality of cells, each of the plurality of cells corresponding to one of the plurality of row labels and containing a computation result of a count function.

16. A computer-readable storage medium having computer-executable instructions stored thereon which, when executed by a computer, cause the computer to:

identify a plurality of fields in a spreadsheet document;

determine a plurality of field scores for each field of the plurality of fields;

generate a field set incorporating two or more of the plurality of fields based on the plurality of field scores;

determine whether the field set fails a quality check comprising at least a density check;

discard the field set when the field set fails the quality check; and

insert the field set into a data summary table when the field set does not fail the quality check, thereby transforming the data summary table without the field set to the data summary table with the field set.

17. The computer-readable storage medium of claim 16, wherein the field set comprises a plurality of row labels and at least one of a plurality of value columns, the plurality of value columns comprising a sum column, a count column, and an average column, the sum column comprising a first plurality of cells, each of the first plurality of cells containing a computation result of a sum function and corresponding to one of the plurality of row labels, the count column comprising a second plurality of cells, each of the second plurality of cells containing a computation result of a count function and corresponding to one of the plurality of row labels, the average column comprising a third plurality of cells, each of the third plurality of cells containing a computation result of an average function and corresponding to one of the plurality of row labels.

18. The computer-readable storage medium of claim 16, wherein the quality check comprises a shape check, a compactness check, and the density check.

19. A computer system, comprising:

a processor;

a memory communicatively coupled to the processor; and

a program module which executes in the processor from the memory and which, when executed by the processor, causes the computer system to;

identify a plurality of fields in a spreadsheet document;

determine a plurality of field scores for each field of the plurality of fields, the plurality of field scores including a row score, a column score, a sum score, a count score, and an average score;

generate a field set incorporating two or more of the plurality of fields based on the plurality of field scores;

determine whether the field set fails a quality check;

discard the field set when the field set fails the quality check; and

insert the field set into a data summary table when the field set does not fail the quality check, thereby transforming the data summary table without the field set to the data summary table with the field set.

20. The computer system of claim 19, wherein the quality check comprises a shape check, a compactness check, and a density check.