Wrangling messy CSV files by detecting row and type patterns

Abstract

It is well known that data scientists spend the majority of their time on preparing data for analysis. One of the first steps in this preparation phase is to load the data from the raw storage format. Comma-separated value (CSV) files are a popular format for tabular data due to their simplicity and ostensible ease of use. However, formatting standards for CSV files are not followed consistently, so each file requires manual inspection and potentially repair before the data can be loaded, an enormous waste of human effort for a task that should be one of the simplest parts of data science. The first and most essential step in retrieving data from CSV files is deciding on the dialect of the file, such as the cell delimiter and quote character. Existing dialect detection approaches are few and non-robust. In this paper, we propose a dialect detection method based on a novel measure of data consistency of parsed data files. Our method achieves 97% overall accuracy on a large corpus of realworld CSV files and improves the accuracy on messy CSV files by almost 22% compared to existing approaches, including those in the Python standard library.CSV is a textbook example of how not to design a textual file format.-The Art of Unix Programming, Raymond (2003).

GitTables: A Large-Scale Corpus of Relational Tables

“…Given the proximity of such users to actual databases, GitHub is a rich source for heterogeneous tables. Prior analyses of CSV files from GitHub also found that these files have diverse formatting and the tables extracted from them have relatively large dimensions [35,19]. These properties are common across database contexts [35,20], so that we consider CSV files from GitHub a suitable resource for database-like tables (C2).…”

AI Assistants: A Framework for Semi-Automated Data Wrangling

“…While parsing CSV files in the standard format [29] is easy, parsing a file with non-standard column separators and other formatting parameters often requires human insight. CleverCSV [5] is an automatic tool that uses a data consistency measure to determine formatting parameters, called a "dialect", consisting of the delimiter (e.g., ,), quote (e.g., ") and escape characters (e.g., \). We adapt CleverCSV into an interactive AI assistant that allows the analyst to guide the tool in case the automatic detection fails.…”

“…Objective function optimization. The objective function Q H for the AI assistant does not depend on user interactions and uses the consistency measure of non-interactive CleverCSV [5]. The measure is calculated by parsing the input file using a potential dialect and taking the product of two scores: the "pattern score" that captures how regular the structure of the parsed data is (i.e., does the resulting table have the same number of cells in each row?…”

“…Example of using CleverCSV. While the automatic dialect detection proposed in [5] achieves 97% accuracy, one type of failure arises when there are two delimiters that result in consistent row lengths and interpretable cells:…”

“…Jupyter and RStudio are popular environments used for programmatic data cleaning. They are used alongside libraries that implement specific functionality such as parsing CSV files or merging datasets [5], [6] and general data transformation functions provided, e.g., by Pandas [7] and Tidyverse [8].…”