Practical Solutions for Automated Spreadsheet Table Transformations

Large-scale, repetitive tasks often lack efficient tools for automation. By leveraging common patterns, a language is designed to express diverse transformations efficiently. Ease of use and expressiveness are emphasized for seamless transformation handling. Explore a demo showcasing the power of these tools.

• Automation
• Spreadsheet
• Transformations
• Language Design
• Data Processing

aaliyiah Follow

Uploaded on Sep 20, 2024 | 0 Views

Download Presentation

Please find below an Image/Link to download the presentation.

The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.

Presentation Transcript

1. Spreadsheet Table Transformations from Examples William Harris Sumit Gulwani

2. General Problem End-users have large-scale, repetitive tasks, and don t have the right tools to do them automatically. Transform strings [POPL 11] Transform layout of tables

3. A Running Example: Quals Qual 1 01.02.2003 31.08.2001 Qual 2 27.06.2008 Qual 3 06.04.2007 05.07.2004 09.12.2009 Example input table Andrew Ben Carl 18.04.2003 Qual 1 Qual 2 Qual 3 Qual 1 Qual 3 Qual 2 Qual 3 01.02.2003 27.06.2008 06.04.2007 31.08.2001 05.07.2004 18.04.2003 09.12.2009 Andrew Andrew Andrew Ben Ben Carl Carl Example output table 3

4. Qual 1 Qual 2 Qual 3 Andrew 01.02.2003 27.06.2008 06.04.2007 Ben Carl Dennis Emma Fred Garry Howard Isolde Janice Kathy Larry Mario Neville 31.08.2001 05.07.2004 09.12.2009 10.01.2010 11.02.2010 18.04.2003 28.07.2009 02.03.2004 03.05.2005 29.08.2010 30.08.2010 31.08.2010 04.06.2006 12.03.2010 13.04.2010 14.05.2010 05.07.2007 06.08.2008 07.09.2009 08.10.2010 01.09.2009 02.09.2009 15.06.2010 16.06.2010 03.09.2009 08.11.2010 17.07.2010 Oscar 08.12.2010 04.09.2009 18.08.2010 Andrew 01.02.2003 27.06.2008 06.04.2007 Ben 31.08.2001 05.07.2004 Carl 18.04.2003 09.12.2009 Dennis 02.03.2004 28.07.2009 10.01.2010 Emma 03.05.2005 11.02.2010 Fred 29.08.2010 Garry 04.06.2006 30.08.2010 12.03.2010 Howard 31.08.2010 13.04.2010 Isolde 05.07.2007 14.05.2010 Janice 06.08.2008 01.09.2009 Kathy 07.09.2009 02.09.2009 15.06.2010 Larry 08.10.2010 16.06.2010 Mario 03.09.2009 Neville 08.11.2010 17.07.2010 Oscar 08.12.2010 04.09.2009 18.08.2010 Andrew 01.02.2003 27.06.2008 06.04.2007 Ben 31.08.2001 05.07.2004 Carl 18.04.2003 09.12.2009 Dennis 02.03.2004 28.07.2009 10.01.2010 Emma 03.05.2005 11.02.2010 Fred 29.08.2010 Garry 04.06.2006 30.08.2010 12.03.2010 4

5. Solution Space GUI tools programs from examples Ease of Use traditional programming Expressiveness

6. A Running Example: Quals Qual 1 01.02.2003 31.08.2001 Qual 2 27.06.2008 Qual 3 06.04.2007 05.07.2004 09.12.2009 Andrew Ben Carl Input table 18.04.2003 Qual 1 Qual 2 Qual 3 Qual 1 Qual 3 Qual 2 Qual 3 01.02.2003 27.06.2008 06.04.2007 31.08.2001 05.07.2004 18.04.2003 09.12.2009 Andrew Andrew Andrew Ben Ben Carl Carl Output table 6

7. Demo on Quals 7

8. Methodology 1. Study a large corpus of practical transformations 2. Identify common patterns in transformations 3. Design a language: (a) expressive enough for most transformations (b) amenable to inference

9. Outline 1. Give a language of transformations 2. Give an inference algorithm for the language 3. Demonstrate inference algorithm 4. Usage and Experiments

10. Layout Program = (Filter Programs, Associative Programs) 10

11. Transformation Step 1: Filtering 1. Filter cells from input table to output column

12. Filtering Cells to Quals Column 3 Qual 1 01.02.2003 31.08.2001 Qual 2 27.06.2008 Qual 3 06.04.2007 05.07.2004 09.12.2009 Andrew Ben Carl 18.04.2003 01.02.2003 27.06.2008 Qual 1 Qual 2 Qual 3 Qual 1 Qual 3 Qual 2 Qual 3 01.02.2003 27.06.2008 06.04.2007 31.08.2001 05.07.2004 18.04.2003 09.12.2009 09.12.2009 Andrew Andrew Andrew Ben Ben Carl Carl not in row 1 not in column 1 not empty 06.04.2007 31.08.2001 05.07.2004 18.04.2003 12

13. Filter Program: Quals Column 3 Filter Programs: General Form while (in_cell = get_next_row_major_cell()) { if (row(in_cell) != 1 && col(in_cell) != 1 && text(in_cell) != ) { map_next_row(in_cell, } } if (guard(in_cell)) { OutColumn Col3 ); 13

14. Transformation Step 2: Association 1. Filter cells from input table to output column 2. Map spatially associated output cells from spatially associated input cells

15. Associative Program for Col 1 Qual 1 01.02.2003 31.08.2001 Qual 2 27.06.2008 Col. 1 18.04.2003 Qual 3 06.04.2007 05.07.2004 09.12.2009 Andrew Ben Carl Carl 18.04.2003 Qual 1 Qual 2 Qual 3 Qual 1 Qual 3 Qual 2 Qual 3 01.02.2003 27.06.2008 06.04.2007 31.08.2001 05.07.2004 18.04.2003 09.12.2009 Andrew Andrew Andrew Ben Ben Carl Carl Lookup input Carl 18.04.2003 Col. 3 15

16. Associative Program: Quals Column 1 Associative Programs: General Form outNbor := ( ); pre := preImage( , outNbor); inCell := (pre); map(inCell, X); EqRowCol3 AssocFuncOut X col3Filter filter EqRowCol1 AssocFuncIn

17. Outline 1. A language of transformations 2. An inference algorithm for the language 3. Demonstration of inference algorithm 4. Usage and Experiments

18. Inferring Layout Programs Inferring Filter Programs Inferring Associative Programs

19. Filter Programs: General Form while (in_cell = get_next_row_major_cell()) { if (guard(in_cell)){ map_next_row(in_cell, OutColumn); } } 19

20. Filter Predicates Cell Features row column text Constants 1, , COL_MAX Predicates FEATURE(cell) = CONST FEATURE(cell) != CONST

21. Filtering Cells to Quals Column 3 Qual 1 01.02.2003 31.08.2001 Qual 2 27.06.2008 Qual 3 06.04.2007 05.07.2004 09.12.2009 Andrew Ben Carl 18.04.2003 01.02.2003 27.06.2008 Qual 1 Qual 2 Qual 3 Qual 1 Qual 3 Qual 2 Qual 3 01.02.2003 27.06.2008 06.04.2007 31.08.2001 05.07.2004 18.04.2003 09.12.2009 09.12.2009 Andrew Andrew Andrew Ben Ben Carl Carl cell.row != COL_1 && cell.col != ROW_1 && cell.text != 06.04.2007 31.08.2001 05.07.2004 18.04.2003 21

22. Inference Algorithm Inferring Filter Programs Inferring Associative Programs

23. Associative Programs: General Form outNbor := AssocFuncOut(X); pre := preImage(filter, outNbor); inCell := AssocFuncIn(pre); map(inCell, X);

24. Associative Functions RowEqCol1 RowEqCol2 RowEqColN Row1ColEq

25. Inferring an Associative Program for Col 1 Qual 1 01.02.2003 31.08.2001 Qual 2 27.06.2008 Col. 1 18.04.2003 Qual 3 06.04.2007 05.07.2004 09.12.2009 Andrew Ben Carl Carl Col. 2 18.04.2003 Qual 1 Qual 2 Qual 3 Qual 1 Qual 3 Qual 2 Qual 3 01.02.2003 27.06.2008 06.04.2007 31.08.2001 05.07.2004 18.04.2003 09.12.2009 Andrew Andrew Andrew Ben Ben Carl Carl Lookup input Carl 18.04.2003 Col. 3 25

26. Outline 1. A language of transformations 2. An inference algorithm for the language 3. Demonstration of inference algorithm 4. Usage and Experiments

27. Experiments Examples from 50+ real-world help threads 1. From example, inferred program 2. Checked program against informal description 3. If program was good: done else: extend example, repeat

30. Conclusion Motivated the problem of inferring table- layout transformations Sketched a language that describes practical transformations over table layouts Sketched an algorithm that infers programs in the language

31. Questions? Layout Program = (Filter Programs, Associative Programs) 31

32. Extra Slides

33. Spreadsheet Table Transformations from Examples William Harris Sumit Gulwani

34. Our Approach P P Input Output 34