recipe procedure: align

[semio]

Reason

The same entity may have different keys in different datasets. For example, the key for country entity, may be the iso code in one dataset, while in the other dataset it may be the alphanumeric form of the country name. When creating a new dataset we may want to use the datapoints from one and country key from other. so we need a procedure to change the country in datapoints to match the other dataset.

EDIT: I found this is an automate version of translate_column. translate_column takes a dictionary as input and align generate the dictionary on the fly. Not sure if it's good to combine this function into translate_column.

API

align procedure translate the index column in a ingredient to make it align with an other ingredient.

the align procedure accepts following options:

• ingredients: the target ingredient to translate
• search_cols: the columns to search for values in base ingredient
• to_find: the column in target ingredient contains values to search
• to_replace: after finishing search, replace the column with new value

[semio]

example:

https://github.com/semio/ddf--gapminder--systema_globalis/blob/feature/autogenerated/etl/recipes/recipe_gapminder.yaml

This recipe replace the country column in datapoints to align with the GW geo entity.

[jheeffer]

How could we clear up the API? I think this can be joined with translate_column in a clear API.

{
  procedure: 'translate_column',
  ingredient: 'wdi-countries',              // ingredient which will be translated
  column: 'country',                        // column in ingredient which will be translated
  target_column: 'geo',                     // column where translations will be written to. 
                                            // Overwrites when target_column already exists. 
                                            // Creates new column when target_column does not exist. 
                                            // Defaults to 'column' value.
  dictionary: { 
    ingredient: 'gw-entities',              // ingredient from which to build dictionary
    key/from: 'name' || ['name','alt1','alt2'],  // columns which form dictionary keys. May default to ingredient value columns?
    value/to: 'geo'                            // column which forms dictionary value. May default to ingredient key column?
  },
  dictionary: 'path/to/dictionary.json',    // path to a json file containing a dictionary
  dictionary: {                             // inline dictionary
    China: 'chn',
    Sweden: 'swe'
  },
  not_found: 'error' || 'drop' || 'include',  // action on row when column contains value not in dictionary keys. Defaults to include.
}

of course only one of the dictionaries can be given

Questions:

1. Does this seem a clear API to you? Does it allow for the same functionality as translate_column and align have now? Are there other problems with it?
   1. use key/value or from/to for dictionary? key/value is technical, from/to a bit more descriptive.
2. How to solve the ambiguity of dictionaries with an object value (inline/ingredient)
   1. Maybe three different dictionary properties? dictionary_from_file, dictionary_from_ingredient, dictionary_inline?
   2. dictionary: { type: 'inline' || 'file' || 'ingredient', .. }
   3. dictionary_type: 'inline' || 'file' || 'ingredient', dictionary: { }
   4. Assume ingredient dictionary when object contains only ingredient,key,value properties. Have iii as fallback for conflicting cases.
   5. Something else?

[jheeffer]

p.s.

here: #28
and in the recipe code I saw the base option for translate_column which seems to overlap with align. Is that correct?

[semio]

yes, @jheeffer your suggestion looks clear. I agree we can join this function to translation_column, and this will also cover what we need for #28

My suggestions:

1. I think we should put the options inside options, as what we do in other functions
2. using the type option is better than add 3 different options for 3 kinds of dictionary, for we can add other types and keep using the dictionary option

{
  procedure: 'translate_column',
  ingredient: 'wdi-countries',              // ingredient which will be translated
  options: {
    column: 'country',                      // column in ingredient which will be translated
    target_column: 'geo',                   // column where translations will be written to. 
                                            // Overwrites when target_column already exists. 
                                            // Creates new column when target_column does not exist. 
                                            // Defaults to 'column' value.
    type: 'ingredient'                      // type of dictionary
    dictionary: { 
      ingredient: 'gw-entities',              // ingredient from which to build dictionary
      key: 'name' || ['name','alt1','alt2'],  // columns which form dictionary keys
      value: 'geo'                            // column which forms dictionary value
  }
}

[jheeffer]

Alright, options is good then. I see you prefer key/value over from/to. I would call type dictionary_type, to make the link to dictionary clear.
Is dictionary_type mandatory or has it a default/is it smart depending on dictionary object's properties?

[semio]

Yes, dictionary_type can be smart depending on dictionary object. We can skip this parameter.

[jheeffer]

I think this is ready for implementation!