In data-driven workflows, practicioners typically resort to a plethora of different systems:

•  Wrangling tools, such as Trifacta, can be used for the early preparation and cleaning stages;
•  Spreadsheet tools, such as Microsoft Excel, are often used to carry out data exploration and analysis;
•  Programming frameworks, such as Pandas, Tensorflow, PyTorch, allow users to perform statistical and machine-learning operations;
•  Business intelligence software, such as Tableau, to create interactive dashboards and data visualizations;
•  Database management systems, such as MySQL,  are traditionally used to efficiently organize and store data;

For its flexibility, ease of use and parsing, the de-facto standard file format for the data flow between heterogeneous systems is CSV.
Albeit the structure of CSV files is defined in the RFC4180 document, often times files have a different structure, for example due to different locale specifications (e.g. comma used for floating point delimiters in Germany).
Moreover, data files may contain metadata information such as preambles or footnotes that should not be considered as part of the tabular content of CSV files.

The results of our survey highlight how often real-world files do not follow the standard specification of the RFC4180 document with respect to different structural features.

The key question that arises from these results is: how do different tools handle loading files with such non-standard features?

We analyze loaded files at three levels.

•  Header: we consider a header to be loaded correctly, if the output files it contains all and only the columns of the input file.
•  Record: since relational tables are not order-sensitive with respect to records, but columns are ordered, we consider records to be equal if they contains the same values in the same order.
•  Cell: at the cell level, we do not consider record or column order, but consider two cells to be equal if their content is the same.

For every level we evaluate precision, recall, and F1-score comparing the input and the output of the loading. Additionally, we include the "success" measure, a binary measure of whether the system was able to load the file in memory.

The final benchmark score is obtained as the average of these scores across the original input file and the 2289 polluted versions of it, and finally sum across different scores.

As all the scores are in the range [0,1] with 1 being the hightest possible, the maximum score obtainable by a given system is 10.

We experiment with two weighting schemes for the average: a "simple" average, giving a weight of one to every polluted file, and a "weighted" average giving a weight to each polluted file depending on the frequency of the pollution in our real-world survey.

We used our benchmark to evaluate sixteen different real-world systems in four categories:

• CSV parsing module from three different languages: CleverCSV, Pandas, and the standard library parser for Python; OpenCSV, Univocity, and CSVCommons for Java; Hypoparser and the standard library parser for R.
• Relational databases: MariaDB, MySQL, PostgreSQL and SQLite.
• Spreadsheet software: LibreOffice Calc, a commercial software defined as SpreadDesktop, and a web-based software defined as SpreadWeb (real names omitted due to licensing).
• Business intelligence tools: a commercial tool defined as DataViz (real name omitted due to licensing).

The results of sixteen different real-world systems on the Pollock benchmark can be seen in the table below:

Pollock is an open source project. Its code is available on the project page at GitHub.

• G. Vitagliano, L. Reisener, M. Hameed, L. Jiang, E. Wu, F. Naumann: Pollock: A Data Loading Benchmark. PVLDB, 16(8):1870-1882, 2023. doi: 10.14778/3594512.3594518

This research project is conducted at the Data Preparation group. If you have any question, interesting idea, or request, do not hesitate to contact Gerardo Vitagliano.