Chapter 17: Cleaning Up on the Way In

Dr. Reyes had had enough of typing vaccine names. “Canigard”, “canigard DHP”, “Canigard D.H.P.”: the clinic’s records held every spelling of every product. “The national index lists every authorised product,” she said. “Can’t we just load that?”

Dr. Portbridge downloaded catalogue.csv. Every product was there: horses, cattle, worming tablets, products withdrawn twenty years ago, and somewhere in the middle, the vaccines Happy Paws actually used.

The problem

The mappings in Chapters 14 to 16 turned every record of a file into facts. Those files were written for the clinic. This one is not: it is a national list, and most of it is of no use to a small-animal practice. Its values are not tidy either. One person wrote Dog, another dog , a third CAT, and the doses are bare numbers whose unit is only in the column name.

Four tools clean data up on the way in, without touching the file:

Tool	What it does
`where:`	keeps only the rows the clinic needs
`case`	turns the file’s text into the clinic’s own values
`{slug(?x)}`	turns a name into a safe identifier
`as quantity(mL)`	gives a bare number its unit

The file

product,holder,category,target_species,dose_ml,status
Rabivet Pro,Vetolab SA,Vaccine,Dog,1,authorised
Rabivet Pro,Vetolab SA,Vaccine,CAT,1,authorised
Canigard DHP,Vetolab SA,Vaccine,dog ,1,authorised
Canigard DHP (1998 formula),Vetolab SA,Vaccine,Dog,1,withdrawn
Félishield 3,Animalia Pharma,Vaccine,Cat,1,authorised
Kennel-Cough Intranasal,Animalia Pharma,Vaccine,Dog,0.4,authorised
Myxo-Lapin,Cuniculab,Vaccine,Rabbit,0.5,authorised
Psittavax,Avian Health,Vaccine,Bird,0.2,authorised
Equiflu,Equisan,Vaccine,Horse,2,authorised
Bovishield BVD,Agrivet,Vaccine,Cattle,2,authorised
Wormex Plus,Vetolab SA,Antiparasitic,Dog,,authorised

Eleven products. Happy Paws wants seven of them: the authorised vaccines for dogs, cats, rabbits and birds.

The whole mapping


prefix <http://happypaws.com/clinic/vaccines#> as hp

concept Species:
  one of:
    Dog
    Cat
    Bird
    Rabbit
    Reptile
    Other

concept Manufacturer:
  has name: one string

concept VaccineProduct:
  has productName: one string
  has manufacturer: one Manufacturer
  has forSpecies: some Species
  has dose: one float  # a volume: quantity(1 mL)

mapping from <package://catalogue.csv> as CSV
  map:
    product        as ?product
    holder         as ?holder
    category       as ?category
    target_species as ?species
    dose_ml        as ?dose
    status         as ?status
  where:
    ?status [ = "authorised" ]
    ?category [ = "Vaccine" ]
    ?species [ != "Horse", != "Cattle" ]
  to:
    fact hp:maker_{slug(?holder)} a Manufacturer
      name {?holder}
    fact hp:vaccine_{slug(?product)} a VaccineProduct
      productName {?product}
      manufacturer hp:maker_{slug(?holder)}
      forSpecies case ?species:
        "dog" -> Dog
        "cat" -> Cat
        "rabbit" -> Rabbit
        "bird" -> Bird
      dose {?dose} as quantity(mL)

The map: block is the one you know from earlier chapters. The new parts come after it.

1. `where:`: keeping the rows you want


  where:
    ?status [ = "authorised" ]
    ?category [ = "Vaccine" ]
    ?species [ != "Horse", != "Cattle" ]

Each line takes a variable from map: and puts conditions on it, in the same [ ... ] brackets as the constraint blocks of a rule. A row is kept only if every condition holds. The others are dropped silently: they are not errors, just rows you did not ask for.

So the withdrawn 1998 formula goes, the worming tablets go, and the horse and cattle vaccines go.

These comparisons are exact. [ != "Horse" ] would let horse through. That is fine here, because the index writes its large animals one way only. If it did not, starts with and the numeric comparisons ([ >= 0.5 ]) are there too; the syntax reference lists them.

2. `case`: translating the file’s values


      forSpecies case ?species:
        "dog" -> Dog
        "cat" -> Cat
        "rabbit" -> Rabbit
        "bird" -> Bird

forSpecies needs a Species member, from the enumeration of Chapter 4, not a piece of text. case gives the table: on the left the text as the file writes it, on the right the value to use.

Unlike where:, case is forgiving. It ignores spaces around the cell, upper and lower case, and accents. Dog, dog and DOG all give Dog; CAT gives Cat. One arm per meaning is enough, however many ways the file writes it.

A cell that matches no arm gives no value. For an optional property the line is just left out. forSpecies is some, so the row would be skipped with a warning instead. An else -> Other arm would catch everything else. Here where: has already removed the species the clinic does not treat.

3. `slug()`: a safe name


    fact hp:vaccine_{slug(?product)} a VaccineProduct

A product name is not an identifier: Félishield 3 has an accent and a space, Kennel-Cough Intranasal a dash. {slug(?product)} lowercases the name, drops the accents and turns every run of other characters into a single _:

Product	Fact
`Rabivet Pro`	`hp:vaccine_rabivet_pro`
`Félishield 3`	`hp:vaccine_felishield_3`
`Kennel-Cough Intranasal`	`hp:vaccine_kennel_cough_intranasal`

The same name always gives the same identifier, and that does real work here. The index lists Rabivet Pro twice, once for dogs and once for cats. Both rows build hp:vaccine_rabivet_pro, so they add up to one product for two species. Vetolab SA makes two of them and is still a single manufacturer, hp:maker_vetolab_sa, which both point to.

4. `as quantity(mL)`: putting the unit back


      dose {?dose} as quantity(mL)

The dose_ml column holds 1, 0.4, 0.2: numbers whose unit lives only in the column’s name. In Chapter 11 a weight became quantity(45 kg). as quantity(mL) does the same for every row: the cell 0.4 becomes quantity(0.4 mL). From then on the dose is a volume, and compares with doses written in litres, or in any other volume unit.

A cell that is not a number, such as 0.4 mL or n/a, cannot be read this way. Its row is skipped with a warning, as for any failed cast.

What comes out

The last product of the file, with the unit definition the build adds at the end:

hp:maker_avian_health rdf:type owl:NamedIndividual
  , vaccines:Manufacturer
  ; rdfs:isDefinedBy <http://happypaws.com/clinic/vaccines>
  .
hp:maker_avian_health vaccines:name "Avian Health" .

hp:vaccine_psittavax rdf:type owl:NamedIndividual
  , vaccines:VaccineProduct
  ; rdfs:isDefinedBy <http://happypaws.com/clinic/vaccines>
  .
hp:vaccine_psittavax vaccines:productName "Psittavax" .
hp:vaccine_psittavax vaccines:manufacturer hp:maker_avian_health .
hp:vaccine_psittavax vaccines:forSpecies vaccines:Species_Bird .
hp:vaccine_psittavax vaccines:dose "0.2"^^<http://qudt.org/vocab/unit/MilliL> .


# Quantity unit definitions
<http://qudt.org/vocab/unit/MilliL> a qudt:Unit, rdfs:Datatype ; qudt:conversionMultiplier 0.000001 ; qudt:hasDimensionVector <http://qudt.org/vocab/dimensionvector/A0E0L3I0M0H0T0D0> ; qudt:symbol "mL" ; qudt:hasQuantityKind quantitykind:Volume .

Psittavax became hp:vaccine_psittavax, Bird the enumeration member Species_Bird, and 0.2 a typed volume.

Seven rows survived where:. They describe six products from four manufacturers: the two Rabivet Pro rows are one product.

Dr. Reyes scrolled through the new list: six vaccines, one spelling each, every dose in millilitres. “And next year?” she asked. “Next year we download the new index and rebuild,” said Dr. Portbridge. “The cleaning is in the mapping. Nobody has to do it again.”

Keyboard shortcuts

Dolfin Documentation