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.

Name Pattern Examples
Full year YYYY 19552013
Half year YYYY-H 2001 H11984-H2
H-YYYY H1/1999
Quarter year YYYY-Q 2002Q12002/Q2
Q-YYYY Q3-2002Q4/2004
Month YYYY-M 2013-12013/022013 3
M-YYYY 11-200412 2004
ISO week YYYY-W 2013-W372013W382013 w39
ISO weekday YYYY-W-d 2013-W37-32013W37 42013W375
Full date (ISO) YYYY-MM-DD 1999-11-291999/11/29
Full date (US) MM/DD/YYYY 11/29/199911-30-1999
Full date (EU) DD.MM.YYYY 29.11.199930-11-1999
Full time YYYY-MM-DD H:M:S 1999-11-29 12:001999-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.