Understanding data column types
In step 2: Check & Describe, Datawrapper tries to automatically detect the data type you upload: text, number or date. Occasionally, you might want to change the format. This tutorial covers how to do this without needing to go back to your spreadsheet software like Excel, and which date format Datawrapper supports.
How does Datawrapper detect my data format automatically?
Datawrapper understands three different column types: text, number, and date. The types are auto-detected by analyzing the content of each column. This works basically like this: if most of the values in a column look like valid dates (we’ll get into this in a moment) we assume the type is a date. Otherwise, we check if most of the values look like valid numbers and assume the type number if this is the case. If neither date or number type has been detected we assume that the column contains just texts.
Automatic detection of column type after uploading: The column type detection is smart about mis-parsed values. So instead of requiring all the values to be in the right type, we accept an error rate of 10%. That means that if at least 90% of the values look like valid dates the tool will detect those as date columns.
How do I know what type is detected?
To make the detected types easily recognizable we gave each type a unique styling. The colors actually indicate right away what Datawrapper has detected.
- Text columns are shown in the default text format which is: black color and left alignment.
- Number columns are shown in blue color and right alignment (as you would expect it from other spreadsheet applications).
- Date columns are in green color and with center alignment.
Datawrapper detected the wrong type. How can I fix that?
We included the auto-detection of the column type to simplify the work with Datawrapper, but of course, this cannot be perfect for all datasets. For instance, if a column happens to contain a lot of values between 1800 and 2100 the auto-detection will interpret them as years.
Changing date types is simple: To set the column type to numbers you just select the column (by clicking on the column index letter above it) and select the right column type in the type select box that appears in the left sidebar. By selecting multiple columns you can also set several column types at one time.
What date formats are detected?
To make it possible for Datawrapper to automatically detect your date, try to stick to one of the formats below for quick results. We now detect: full years (2013), half years (2013 H1), quarters (2013 Q3), months (2013-08), dates (2013-08-27) and even time values (2013-08-27 09:47). The date format not only accepts the recommended ISO format but also tries to identify a few other local variants (e.g. 8/27/2013).
You find the full list of date formats in our Github wiki.
|Full year||YYYY||1955, 2013|
|Half year||YYYY-H||2001 H1, 1984-H2|
|Quarter year||YYYY-Q||2002Q1, 2002/Q2|
|Month||YYYY-M|| 2013-1, 2013/02, 2013 3
|M-YYYY||11-2004, 12 2004|
|ISO week||YYYY-W||2013-W37, 2013W38, 2013 w39|
|ISO weekday||YYYY-W-d||2013-W37-3, 2013W37 4, 2013W375|
|Full date (ISO)||YYYY-MM-DD||1999-11-29, 1999/11/29|
|Full date (US)||MM/DD/YYYY||11/29/1999, 11-30-1999|
|Full date (EU)||DD.MM.YYYY||29.11.1999, 30-11-1999|
|Full time||YYYY-MM-DD H:M:S||1999-11-29 12:00, 1999-11-29 12:00:12|
Note that parsing dates is sometimes ambiguous. For instance, the date 3/4/2012 can either be the third of April (EU) or the fourth of March (US). To avoid confusion, you should try to convert your dates to the ISO date format.