issues transforming string to date vega lite

user_12899567 0
Viewed 1573

I am trying to display some data in a line graph. However, my "Harvest_Year" data, which is a date in years, like 2017 or 2018, is being displayed as what I believe is a string

I imported by data from a .csv file, and the following are the steps I took to change the string to a date formate. I tired to do:

"Harvest_Year": "year"

But that did not work as it made all my values null. So I thought first I will make it into a int and then transform it into year. However in Vega-Lite all my years re displayed correctly in the table but when I display it on the line graph I only see 1970 which I am sure I don't have in the dataset, and it only displays that single year.

line graph displaying only one year

Where as in the image below, you can see I have all the years in my data:

correct data

{
  "$schema": "https://vega.github.io/schema/vega-lite/v4.json",
  "data": {
    "url": "https://raw.githubusercontent.com/DanStein91/Info-vis/master/CoffeeRobN.csv",
    "format": {
      "type": "csv",
            "parse": {
       "Number_of_Bags": "number",
        "Bag_weight": "number",
        "Harvest_Year": "number"
      }
    }
  },
  "transform": [
{
    "timeUnit": "year",
    "field": "Harvest_Year",
    "as": "Year"
  },
     {
      "calculate": "datum.Number_of_Bags * datum.Bag_Weight ",
      "as": "Total_Export"
    }
  ],  
      "width": 300,
      "height": 200,
  "mark": "line",
  "encoding": {
    
    "y": {
      "field": "Total_Export",
      "type": "quantitative"
    },
    "x": {
      "field": "Harvest_Year",
      "type": "temporal"
    }
  },
  "config": {}
}
vega-lite
1 Answers

When you tell vega-lite to interpret numbers as dates, it treats them as unix timestamps, i.e. milliseconds after January 1 1970. Each of your resulting dates is in the year 1970, which leads to the chart you are seeing.

Your dates appear to be in a non-standard format (e.g. "2017.0" means the year 2017) so you'll have to use vega expressions to manually parse them into date objects. Here is an example of this (view in editor):

{
  "data": {
    "url": "https://raw.githubusercontent.com/DanStein91/Info-vis/master/CoffeeRobN.csv",
    "format": {
      "type": "csv",
      "parse": {
        "Number_of_Bags": "number",
        "Bag_weight": "number",
        "Harvest_Year": "number"
      }
    }
  },
  "transform": [
    {"filter": "isValid(datum.Harvest_Year)"},
    {"calculate": "datetime(datum.Harvest_Year, 1)", "as": "Harvest_Year"},
    {
      "calculate": "datum.Number_of_Bags * datum.Bag_Weight ",
      "as": "Total_Export"
    }
  ],
  "mark": "point",
  "encoding": {
    "y": {"field": "Total_Export", "type": "quantitative"},
    "x": {"field": "Harvest_Year", "type": "ordinal", "timeUnit": "year"}
  },
  "width": 300,
  "height": 200
}

enter image description here

Another option is to avoid datetime and timeUnit logic altogether (since your data does not actually contain any dates), and just use the year numbers directly in your encoding; e.g.

{
  "data": {
    "url": "https://raw.githubusercontent.com/DanStein91/Info-vis/master/CoffeeRobN.csv",
    "format": {
      "type": "csv",
      "parse": {
        "Number_of_Bags": "number",
        "Bag_weight": "number",
        "Harvest_Year": "number"
      }
    }
  },
  "transform": [
    {"filter": "isValid(datum.Harvest_Year)"},
    {
      "calculate": "datum.Number_of_Bags * datum.Bag_Weight ",
      "as": "Total_Export"
    }
  ],
  "mark": "point",
  "encoding": {
    "y": {"field": "Total_Export", "type": "quantitative"},
    "x": {"field": "Harvest_Year", "type": "ordinal"}
  },
  "width": 300,
  "height": 200
}
user_29378310
Related