Intelligently creating and recommending reusable reformatting rules

ABSTRACT

When users combine data from multiple sources into a spreadsheet or dataset, the result is often a mishmash of different formats, since phone numbers, dates, course numbers and other string-like kinds of data can each be written in many different formats. Although spreadsheets provide features for reformatting numbers and a few specific kinds of string data, they do not provide any support for the wide range of other kinds of string data encountered by users. We describe a user interface where a user can describe the formats of each kind of data. We provide an algorithm that uses these formats to automatically generate reformatting rules that transform strings from one format to another. In effect, our system enables users to create a small expert system called a "tope" that can recognize and reformat instances of one kind of data. Later, as the user is working with a spreadsheet, our system recommends appropriate topes for validating and reformatting the data. With a recall of over 80% for a query time of under 1 second, this algorithm is accurate enough and fast enough to make useful recommendations in an interactive setting. A laboratory experiment shows that compared to manual typing, users can reformat sample spreadsheet data more than twice as fast by creating and using topes.

REFERENCES

• 1: Robin Abraham , Martin Erwig, Header and Unit Inference for Spreadsheets Through Spatial Analyses, Proceedings of the 2004 IEEE Symposium on Visual Languages - Human Centric Computing, p.165-172, September 26-29, 2004 [doi>10.1109/VLHCC.2004.29]
• 2: Alan F. Blackwell, SWYN: a visual representation for regular expressions, Your wish is my command: programming by example, Morgan Kaufmann Publishers Inc., San Francisco, CA, 2001
• 3: Chee-Yong Chan , Minos Garofalakis , Rajeev Rastogi, RE-tree: an efficient index structure for regular expressions, The VLDB Journal — The International Journal on Very Large Data Bases, v.12 n.2, p.102-119, August 2003 [doi>10.1007/s00778-003-0094-0]
• 4: Fisher II, M., and Rothermel, G. The EUSES Spreadsheet Corpus: A Shared Resource for Supporting Experimentation with Spreadsheet Dependability Mechanisms. Tech. Rpt. 04-12-03, Univ. Nebraska-Lincoln, 2004.
• 5: Maria Jean Johnstone Hall, A Risk and Control Oriented Study of the Practices of Spreadsheet Application Developers, Proceedings of the 29th Hawaii International Conference on System Sciences Volume 2: Decision Support and Knowledge-Based Systems, p.364, January 03-06, 1996
• 6: David F. Huynh , Robert C. Miller , David R. Karger, Potluck: Data mash-up tool for casual users, Web Semantics: Science, Services and Agents on the World Wide Web, v.6 n.4, p.274-282, November, 2008 [doi>10.1016/j.websem.2008.09.005]
• 7: Henry Lieberman , Bonnie A. Nardi , David J. Wright, Training Agents to Recognize Text by Example, Autonomous Agents and Multi-Agent Systems, v.4 n.1-2, p.79-92, March-June 2001 [doi>10.1023/A:1010018830260]
• 8: Marsh, E., and Perzanowski, D. MUC-7 Evaluation of IE Technology: Overview of Results. 7th Message Understanding Conf., 2001.
• 9: Robert C. Miller , Brad A. Myers, Outlier finding: focusing user attention on possible errors, Proceedings of the 14th annual ACM symposium on User interface software and technology, November 11-14, 2001, Orlando, Florida [doi>10.1145/502348.502361]
• 10: Mosteller, F., and Youtz, C. Quantifying Probabilistic Expressions. Statistical Science, 5, 1, 1990, 2--12.
• 11: Robert P. Nix, Editing by example, ACM Transactions on Programming Languages and Systems (TOPLAS), v.7 n.4, p.600-621, Oct. 1985 [doi>10.1145/4472.4476]
• 12: Scaffidi, C. Unsupervised Inference of Data Formats in Human-Readable Notation. Proc. 9th Intl. Conf. Enterprise Information Systems, 2007, 236--241.
• 13: Scaffidi, C., Myers, B., and Shaw, M. Fast, Accurate Creation of Data Validation Formats by End-User Developers. Submitted to 2nd Intl. Symp. End-User Development.
• 14: Christopher Scaffidi , Brad Myers , Mary Shaw, Topes: reusable abstractions for validating data, Proceedings of the 30th international conference on Software engineering, May 10-18, 2008, Leipzig, Germany [doi>10.1145/1368088.1368090]
• 15: Zadeh, L. Fuzzy Logic. Tech Rpt. CSLI-88-116, Stanford University, 1988.

INDEX TERMS

Primary Classification:
  D. Software
  D.2 SOFTWARE ENGINEERING
      D.2.6 Programming Environments
          Subjects: Integrated environments

General Terms:
Human Factors

Keywords:
consistent data format, end-user programming, spreadsheets