Merge two datasets of different sizes

Question

I have two data tables formatted as DataSets.

The first one has five keys: date, origin, destination, type, value (sample below).

{<|"date" -> 2008, "origine" -> "Terre-Neuve-et-Labrador", 
  "destination" -> "Terre-Neuve-et-Labrador", 
  "type" -> "produits de base", 
  "valeur" -> 28452.|>, <|"date" -> 2009, 
  "origine" -> "Terre-Neuve-et-Labrador", 
  "destination" -> "Terre-Neuve-et-Labrador", 
  "type" -> "produits de base", 
  "valeur" -> 29029.|>, <|"date" -> 2011, 
  "origine" -> "Ile-du-Prince-edouard", 
  "destination" -> "Nouvelle-ecosse", "type" -> "produits de base", 
  "valeur" -> 305.3|>, <|"date" -> 2016, 
  "origine" -> "Ile-du-Prince-edouard", 
  "destination" -> "Nouveau-Brunswick", "type" -> "services", 
  "valeur" -> 151.5|>, <|"date" -> 2016, 
  "origine" -> "Nouvelle-ecosse", "destination" -> "Quebec", 
  "type" -> "produits de base", 
  "valeur" -> 1327.8|>, <|"date" -> 2009, 
  "origine" -> "Nouvelle-ecosse", "destination" -> "Quebec", 
  "type" -> "services", "valeur" -> 597.2|>}

The second on has four keys: date, origin, type, value (sample below). This one is very much shorter than the first one.

{<|"date" -> 2008, "origine" -> "Terre-Neuve-et-Labrador", 
  "type" -> "produits de base", 
  "valeur" -> 52657.2|>, <|"date" -> 2011, 
  "origine" -> "Ile-du-Prince-edouard", "type" -> "services", 
  "valeur" -> 6738.1|>, <|"date" -> 2013, 
  "origine" -> "Nouvelle-ecosse", "type" -> "services", 
  "valeur" -> 48082.8|>}

I am looking to divide value of the first dataset by value of the second one, while matching the other keys.

Put differently, for each row of the first dataset, I need to find a match for date, origin, and type, and divide the values.

Thanks a lot for your help

dataset

Rolf Mertig · Answer

    ds1 = {<|"date" -> 2008, "origine" -> "Terre-Neuve-et-Labrador", 
    "destination" -> "Terre-Neuve-et-Labrador", 
    "type" -> "produits de base", "valeur" -> 28452.|>, <|
    "date" -> 2009, "origine" -> "Terre-Neuve-et-Labrador", 
    "destination" -> "Terre-Neuve-et-Labrador", 
    "type" -> "produits de base", "valeur" -> 29029.|>, <|
    "date" -> 2011, "origine" -> "Ile-du-Prince-edouard", 
    "destination" -> "Nouvelle-ecosse", "type" -> "produits de base", 
    "valeur" -> 305.3|>, <|"date" -> 2016, 
    "origine" -> "Ile-du-Prince-edouard", 
    "destination" -> "Nouveau-Brunswick", "type" -> "services", 
    "valeur" -> 151.5|>, <|"date" -> 2016, 
    "origine" -> "Nouvelle-ecosse", "destination" -> "Quebec", 
    "type" -> "produits de base", "valeur" -> 1327.8|>, <|
    "date" -> 2009, "origine" -> "Nouvelle-ecosse", 
    "destination" -> "Quebec", "type" -> "services", 
    "valeur" -> 597.2|>};

ds2 = {<|"date" -> 2008, "origine" -> "Terre-Neuve-et-Labrador", 
    "type" -> "produits de base", "valeur" -> 52657.2|>, <|
    "date" -> 2011, "origine" -> "Ile-du-Prince-edouard", 
    "type" -> "services", "valeur" -> 6738.1|>, <|"date" -> 2013, 
    "origine" -> "Nouvelle-ecosse", "type" -> "services", 
    "valeur" -> 48082.8|>};

Divide @@ JoinAcross[ds1, ds2, Key["date"]][[All, "valeur"]]

gives 93.1936

Answered by Rolf Mertig on July 4, 2021

MelaGo · Answer

data1 = {<|"date" -> 2008, "origine" -> "Terre-Neuve-et-Labrador", "destination" -> "Terre-Neuve-et-Labrador", "type" -> "produits de base", "valeur" -> 28452.|>, 
<|"date" -> 2009, "origine" -> "Terre-Neuve-et-Labrador", "destination" -> "Terre-Neuve-et-Labrador", "type" -> "produits de base", "valeur" -> 29029.|>, 
<|"date" -> 2011, "origine" -> "Ile-du-Prince-edouard", "destination" -> "Nouvelle-ecosse", "type" -> "produits de base", "valeur" -> 305.3|>, 
<|"date" -> 2016, "origine" -> "Ile-du-Prince-edouard", "destination" -> "Nouveau-Brunswick", "type" -> "services", "valeur" -> 151.5|>,
<|"date" -> 2016, "origine" -> "Nouvelle-ecosse", "destination" -> "Quebec", "type" -> "produits de base", "valeur" -> 1327.8|>,
<|"date" -> 2009, "origine" -> "Nouvelle-ecosse", "destination" -> "Quebec", "type" -> "services", "valeur" -> 597.2|>};

data2 = {<|"date" -> 2008, "origine" -> "Terre-Neuve-et-Labrador", "type" -> "produits de base", "valeur" -> 52657.2|>,
<|"date" -> 2011, "origine" -> "Ile-du-Prince-edouard", "type" -> "services", "valeur" -> 6738.1|>, 
<|"date" -> 2013, "origine" -> "Nouvelle-ecosse", "type" -> "services", "valeur" -> 48082.8|>};

To get more matches from the sample data provided, I relaxed the match criteria to not include the data (commented out)

div = Table[
  match = Cases[data2, 
    KeyValuePattern[{
      (*"date"[Rule]row["date"],*) 
      "origine" -> row["origine"], 
      "type" -> row["type"]}]];
  If[Length[match] >= 1,
    row["valeur"] = row["valeur"]/match[[1]]["valeur"],
    row["valeur"] = "no match"];
  row,
  {row, data1}]

Dataset[div]

Merge two datasets of different sizes

2 Answers

Add your own answers!

Ask a Question