Spreadsheet Tools for Data Analysts

155 Pages·2017·6.12 MB·English

UUnniivveerrssiittyy ooff MMaassssaacchhuusseettttss AAmmhheerrsstt
SScchhoollaarrWWoorrkkss@@UUMMaassss AAmmhheerrsstt
Doctoral Dissertations Dissertations and Theses
November 2017
SSpprreeaaddsshheeeett TToooollss ffoorr DDaattaa AAnnaallyyssttss
Daniel W. Barowy
University of Massachusetts Amherst
Follow this and additional works at: https://scholarworks.umass.edu/dissertations_2
Part of the Programming Languages and Compilers Commons
RReeccoommmmeennddeedd CCiittaattiioonn
Barowy, Daniel W., "Spreadsheet Tools for Data Analysts" (2017). Doctoral Dissertations. 1045.
https://doi.org/10.7275/10678415.0 https://scholarworks.umass.edu/dissertations_2/1045
This Open Access Dissertation is brought to you for free and open access by the Dissertations and Theses at
ScholarWorks@UMass Amherst. It has been accepted for inclusion in Doctoral Dissertations by an authorized
administrator of ScholarWorks@UMass Amherst. For more information, please contact
[email protected].
SPREADSHEET TOOLS FOR DATA ANALYSTS
A Dissertation Presented
by
DANIEL W. BAROWY
Submitted to the Graduate School of the
University of Massachusetts Amherst in partial fulfillment
of the requirements for the degree of
DOCTOR OF PHILOSOPHY
September 2017
College of Information and Computer Sciences
(cid:13)c Copyright by Daniel W. Barowy 2017
All Rights Reserved
SPREADSHEET TOOLS FOR DATA ANALYSTS
A Dissertation Presented
by
DANIEL W. BAROWY
Approved as to style and content by:
Emery D. Berger, Chair
Michael Ash, Member
Andrew McGregor, Member
Alexandra Meliou, Member
James Allan, Chair
College of Information and Computer Sciences
ACKNOWLEDGMENT
First, I must thank my parents, Bill and Judy. You instilled me with the desire
to be a scientist at an early age1. Second, thanks to my little brother, Adam. You
enrolled in a graduate program before me, engendering a great deal of envy on my
part. Clearly you were having more fun. Thanks to you, I realized that a major life
change was in order.
Many people helped me through the winding path to graduate school. Thanks
to Peter D’Errico and Alan Gaitenby of the UMass Legal Studies Department, and
Jae Young Lee, Wayne Snyder, and John Day of the Boston University Metropolitan
College Computer Science Department.
I am honored to consider many fellow graduate students my friends: Charlie
Curtsinger, John Altidor, Kaituo Li, John Vilk, Tongping Liu, Marianna Rapoport,
Emma Tosch, Rian Shambaugh, Bobby Powers, and Sam Baxter. I hope that all of
you appreciate the extent to which you made coming to work every day fun.
To Leeanne Leclerc and the UMass CS administrative staff: you rock! In addition
to ensuring that I always dotted my i’s and crossed my t’s, you endured my many
requests for equipment (e.g., projectors), frequently for questionable extracurricular
activities (e.g., “PLASMA Movie Night”).
Many thanks to my mentors and collaborators Ben Zorn, Sumit Gulwani, Ted
Hart, Eric Chung, Daniel Goldstein, Siddharth Suri, and Rodric Rabbah from whom
I learned how to be a professional computer scientist. Thanks to the faculty who
liberally offered me their advice and encouragement over the years, especially David
1Anybody needing proof may examine my marked up copy of Dr. Seuss’ My Book About Me.
iv
Jensen, Andrew McGregor, Yuriy Brun, Alexandra Meliou, Scott Kaplan, Steve
Freund, Margaret Robinson, Heather Pon-Barry, and Jack Wileden.
I am incredibly fortunate to have ended up with Emery Berger as my advisor.
Emery is much more than a research mentor. He pushed me to improve myself in
ways I was neither aware of nor fully appreciated until afterward. Emery, working
with you was fun and hard—exactly what I wanted from graduate school. Thank you.
Finally, my wife Karen deserves more thanks than anybody. Karen, you and I
dreamed up the “Dan and Karen plan” a long time ago, and we stuck with it. I think
that this is more of a testament to your character than it is to mine. For example,
you are serenely dealing with the fact as I write this that I neglected (again) to make
dinner. Newton once said “If I have seen further than others, it is by standing upon
the shoulders of giants.” Having never married, Newton had a poor selection of giants.
You are my giant. I honestly do not know how I could have done this without you.
v
ABSTRACT
SPREADSHEET TOOLS FOR DATA ANALYSTS
SEPTEMBER 2017
DANIEL W. BAROWY
B.Sc., BOSTON UNIVERSITY
M.Sc., UNIVERSITY OF MASSACHUSETTS AMHERST
Ph.D., UNIVERSITY OF MASSACHUSETTS AMHERST
Directed by: Professor Emery D. Berger
Spreadsheets are a natural fit for data analysis, combining a simple data storage
and presentation layer with a programming language and basic debugging tools.
Because spreadsheets are accessible and flexible, they are used by both novices and
experts. Consequently, spreadsheets are hugely popular, with more than 750 million
copies of Microsoft Excel installed worldwide. This popularity means that spreadsheets
are the most popular programming language on the planet and the de facto tool for
data analysis.
Nevertheless, spreadsheets do not address a number of important tasks in a typical
analyst’s pipeline, and their design frequently complicates them. This thesis describes
three key challenges for analysts using spreadsheets. 1) Data wrangling is the process
of converting or mapping data from a “raw” form into another form suitable for use
with automated tools. 2) Data cleaning is the process of locating and correcting
omitted or erroneous data. 3) Formula auditing is the process of finding and correcting
vi
spreadsheet program errors. These three tasks combined are estimated to occupy more
than three quarters of a data analyst’s time. Furthermore, errors not caught during
these steps have led to catastrophically bad decisions resulting in billions of dollars
in losses. Advances in automated techniques for these tasks may result in dramatic
savings in both time and money.
Three novel programming language-based techniques were created to address these
key tasks. The first, automatic layout transformation using examples, is a program
synthesis-based technique that lets spreadsheet users perform data wrangling tasks
automatically, at scale, and without programming. The second, data debugging, is
technique for data cleaning that combines program analysis and statistical analysis to
automatically find likely data errors. The third, spatio-structural program analysis
unifies positional and dependence information and finds spreadsheet errors using a
kind of anomaly analysis.
Each technique was implemented as an end-user tool—FlashRelate, Check-
Cell, and ExceLint respectively—in the form of a point-and-click plugin for Mi-
crosoftExcel. Ourevaluationdemonstratesthatthesetechniquessubstantiallyimprove
user efficiency. Finally, because these tools build on each other in a complementary
fashion, data analysts can run data wrangling, cleaning, and formula auditing tasks
together in a single analysis pipeline.
vii
TABLE OF CONTENTS
Page
ACKNOWLEDGMENT .............................................. iv
ABSTRACT..........................................................vi
LIST OF TABLES ...................................................xiv
LIST OF FIGURES.................................................. xv
CHAPTER
INTRODUCTION .....................................................1
0.0.1 Data wrangling ............................................ 1
0.0.2 Data cleaning..............................................2
0.0.3 Formula auditing...........................................2
0.0.4 Contributions..............................................2
1. BACKGROUND ...................................................4
1.1 Data wrangling: Automation is layout sensitive .......................5
1.1.1 Related Work..............................................8
1.1.1.1 Programming-By-Example ..........................9
1.1.1.2 Language Approaches...............................9
1.1.1.3 Algorithmic Approaches.............................9
1.1.1.4 Machine Learning .................................10
1.2 Data cleaning: Input errors .......................................10
1.2.1 Related Work............................................. 11
1.2.1.1 Database Cleaning ................................ 11
1.2.1.2 Statistical Outlier Analysis .........................12
1.2.1.3 Sensitivity Analysis ...............................13
viii
1.3 Formula auditing: Formula reference errors..........................13
1.3.1 Spreadsheet Error Detection ................................15
1.3.2 Related Work.............................................16
1.3.2.1 Microsoft Excel ...................................17
1.3.2.2 Spreadsheet “Smells” ..............................19
1.3.2.3 Type and Unit Checking ...........................20
1.3.2.4 Other Approaches to Spreadsheet Correctness.........20
1.3.2.5 Anomaly Analysis................................. 21
2. FLASHRELATE: EXTRACTING RELATIONAL DATA FROM
SEMI-STRUCTURED SPREADSHEETS USING
EXAMPLES................................................... 23
2.1 Approach.......................................................24
2.2 The Flare Transformation Language ..............................24
2.2.1 Example .................................................26
2.2.2 Constraints...............................................27
2.2.2.1 Cell Constraints ..................................27
2.2.2.2 Spatial Constraints ................................28
2.2.2.3 Geometric Descriptors .............................28
2.3 Algorithm ......................................................29
2.3.1 Synthesis Goals ...........................................30
2.4 FlashRelate Implementation.................................... 31
2.4.0.1 Ranking ......................................... 31
2.4.0.2 Pruning..........................................32
2.4.0.3 Data Structures...................................32
2.5 Evaluation......................................................32
2.5.0.1 Benchmark Selection ..............................33
2.5.0.2 Expressiveness ....................................33
2.5.0.3 Synthesizer Experiments ...........................33
2.5.1 Results ..................................................34
2.6 Conclusion .....................................................35
ix