Abstract
Spreadsheets are often used as a simple way for representing tabular data. However, since they do not impose any restriction on their table structures and contents, their automatic processing and the integration with other information sources are particularly hard problems to solve. Many table understanding approaches have been proposed for extracting data from tables and transforming them in meaningful information. However, they require some regularities on the table contents.
Starting from CSV spreadsheets that present values of different types and errors, in this paper we introduce an approach for inferring the types of columns in CSV tables by exploiting a multi-label classification approach. By means of our approach, each column of the table can be associated with a simple datatype (such as integer, float, text), a domain-specific one (such as the name of a municipality, and address), or an “union” of types (that takes into account the frequency of the corresponding values). Since the automatically inferred types might not be accurate, graphical interfaces have been developed for supporting the user in fixing the mistakes. Experimental results are finally reported on real spreadsheets obtained by a debt collection agency.
This is a preview of subscription content, log in via an institution to check access.
Access this chapter
Tax calculation will be finalised at checkout
Purchases are for personal use only
Notes
- 1.
To distinguish terminal symbols from types, types are delimited by brackets.
- 2.
References
Abadi, M., et al.: TensorFlow: large-scale machine learning on heterogeneous systems (2015). https://www.tensorflow.org/
Abraham, R., Erwig, M.: Ucheck: a spreadsheet type checker for end users. J. Vis. Lang. Comput. 18, 71–95 (2007)
Arenas, M., Barcelo, P., Libkin, L., Murlak, F.: Relational and XML Data Exchange. Morgan and Claypool Publishers, San Rafael (2010)
Bellahsene, Z., Bonifati, A., Rahm, E.: Schema Matching and Mapping. Springer, Dordrecht (2011)
Ceritli, T., Williams, C.K.I., Geddes, J.: ptype: probabilistic type inference. Data Mining Knowl. Discov. 34(3), 870–904 (2020). https://doi.org/10.1007/s10618-020-00680-1
Chen, J., Jimenez-Ruiz, E., Horrocks, I., Sutton, C.: Colnet: embedding the semantics of web tables for column type prediction. In: Proceeding of AAAI Conference on Artificial Intelligence, vol. 33, pp. 29–36, July 2019
Chen, Z.: Spreadsheet property detection with rule-assisted active learning. In: Proceeding of the Conference on Information and Knowledge Management, pp. 999–1008 (2017)
Doan, A., Halevy, A., Ives, Z.: Principles of Data Integration. Morgan Kaufmann Publishers Inc., Waltham (2012)
Ermilov, I., Ngomo, A.-C.N.: Taipan: automatic property mapping for tabular data. In: Proceeding of International Conference Knowledge Engineering and Knowledge Management, pp. 163–179 (2016)
Fiorelli, M., et al.: Sheet2RDF: a flexible and dynamic spreadsheet import&lifting framework for RDF. In: Ali, M., Kwon, Y.S., Lee, C.-H., Kim, J., Kim, Y. (eds.) IEA/AIE 2015. LNCS (LNAI), vol. 9101, pp. 131–140. Springer, Cham (2015). https://doi.org/10.1007/978-3-319-19066-2_13
Fisher, K., Gruber, R.: Pads: a domain-specific language for processing ad hoc data. SIGPLAN Not. 40(6), 295–304 (2005)
Galkin, M., Mouromtsev, D., Auer, S.: Identifying web tables: supporting a neglected type of content on the web. In: Proceeding of International Conference Knowledge Engineering and Semantic Web, pp. 48–62, October 2015
Google. Openrefine: A free, open source, powerful tool for working with messy data (2020). https://openrefine.org/
Hulsebos, M., et al.: Sherlock: a deep learning approach to semantic data type detection. In: Proceeding of the 25th ACM SIGKDD International Conference on Knowledge Discovery & Data Mining, pp. 1500–1508. ACM (2019)
Hurst, M.: The Interpretation of Tables in Texts. PhD thesis, University of Edinburgh (2000)
Kandel, S., Paepcke, A., Hellerstein, J., Heer, J.: Wrangler: interactive visual specification of data transformation scripts. In: ACM Human Factors in Computing Systems (CHI), pp. 3363–3372 (2011)
Koci, E., Thiele, M., Romero, O., Lehner, W.: A genetic-based search for adaptive table recognition in spreadsheets. In: International Conference on Document Analysis and Recognition (ICDAR), pp. 1274–1279 (2019)
Langegger, A., Wöß, W.: XLWrap – querying and integrating arbitrary spreadsheets with SPARQL. In: Bernstein, A., Karger, D.R., Heath, T., Feigenbaum, L., Maynard, D., Motta, E., Thirunarayan, K. (eds.) ISWC 2009. LNCS, vol. 5823, pp. 359–374. Springer, Heidelberg (2009). https://doi.org/10.1007/978-3-642-04930-9_23
Limaye, G., Sarawagi, S., Chakrabarti, S.: Annotating and searching web tables using entities, types and relationships. Proc. VLDB Endow. 3(1–2), 1338–1347 (2010)
Limaye, G., Sarawagi, S., Chakrabarti, S.: Annotating and searching web tables using entities, types and relationships. Proc. VLDB 3(1–2), 1338–1347 (2010)
Lindenberg, F.: Messytables python library (2020). https://messytables.readthedocs.io/
Milosevic, N., Gregson, C., Hernandez, R., Nenadic, G.: Disentangling the structure of tables in scientific literature. In: Proceeding of International Conference on Applications of Natural Language to Information Systems, pp. 162–174 (2016)
Mulwad, V., Finin, T., Joshi, A.: A domain independent framework for extracting linked semantic data from tables. In: Ceri, S., Brambilla, M. (eds.) Search Computing. LNCS, vol. 7538, pp. 16–33. Springer, Heidelberg (2012). https://doi.org/10.1007/978-3-642-34213-4_2
Pedregosa, F., et al.: Scikit-learn: machine learning in Python. J. Mach. Learn. Res. 12, 2825–2830 (2011)
Petricek, T., Guerra, G., Syme, D.: Types from data: making structured data first-class citizens in f#. In: Proceeding of 37th ACM SIGPLAN Conference on Programming Language Design and Implementation, pp. 477–490. ACM (2016)
Pinto, D., McCallum, A., Wei, X., Croft, W.B.: Table extraction using conditional random fields. In: Proceeding of the 26th International ACM SIGIR Conference on Research and Development in Informaion Retrieval, pp. 235–242 (2003)
Raman, V., Hellerstein, J.: Potter’s wheel: an interactive data cleaning system. In: Proceeding of International Conference Very Large Data Bases, pp. 381–390, September 2001
Shigarov, A.: Table understanding using a rule engine. Expert Syst. Appl. 42, 929–937 (2015)
Shigarov, A., Khristyuk, V., Mikhailov, A., Paramonov, V.: TabbyXL: rule-based spreadsheet data extraction and transformation. In: Damaševičius, R., Vasiljevienė, G. (eds.) ICIST 2019. CCIS, vol. 1078, pp. 59–75. Springer, Cham (2019). https://doi.org/10.1007/978-3-030-30275-7_6
Taheriyan, M., Knoblock, C.A., Szekely, P., Ambite, J.L.: Learning the semantics of structured data sources. J. Web Semant. 37, 152–169 (2016)
Trifacta. Trifacta wrangler (2020). https://www.trifacta.com/
Valera, I., Ghahramani, Z.: Automatic discovery of the statistical types of variables in a dataset. Proc. Mach. Learn. Res. 70, 3521–3529 (2017)
Zhang, M., Zhou, Z.: A review on multi-label learning algorithms. IEEE Trans. Knowl. Data Eng. 26(8), 1819–1837 (2014)
Acknowledgements
The authors wish to thank Giovanni Mancinelli and Carlo Angelucci for working on the implementation of the tool and also the anonymous reviewers for their useful suggestions.
Author information
Authors and Affiliations
Corresponding author
Editor information
Editors and Affiliations
Rights and permissions
Copyright information
© 2021 Springer Nature Switzerland AG
About this paper
Cite this paper
Bonfitto, S., Cappelletti, L., Trovato, F., Valentini, G., Mesiti, M. (2021). Semi-automatic Column Type Inference for CSV Table Understanding. In: Bureš, T., et al. SOFSEM 2021: Theory and Practice of Computer Science. SOFSEM 2021. Lecture Notes in Computer Science(), vol 12607. Springer, Cham. https://doi.org/10.1007/978-3-030-67731-2_39
Download citation
DOI: https://doi.org/10.1007/978-3-030-67731-2_39
Published:
Publisher Name: Springer, Cham
Print ISBN: 978-3-030-67730-5
Online ISBN: 978-3-030-67731-2
eBook Packages: Computer ScienceComputer Science (R0)