3
$\begingroup$

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

$\endgroup$
5
  • $\begingroup$ It looks like your strings aren't properly quoted in your code, which makes it hard to copy-paste. Could you try and add that code again? Also, it's not clear from your comment if you mean Dataset the function, or just that you have two datasets, perhaps you could clarify. $\endgroup$ – Carl Lange Jan 21 at 21:24
  • $\begingroup$ @CarlLange Thank you, is this better? $\endgroup$ – EBassal Jan 21 at 21:29
  • 1
    $\begingroup$ What should happen when there is no match? $\endgroup$ – MelaGo Jan 21 at 23:26
  • $\begingroup$ @MelaGo There is always a match, due to the structure of the data $\endgroup$ – EBassal Jan 22 at 15:18
  • $\begingroup$ The questions says to find a match for date, origine, and type, but rows 2, 4, 5, and 6 from the first dataset have no match in the second... $\endgroup$ – MelaGo Jan 23 at 10:57
7
$\begingroup$
    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

$\endgroup$
1
  • $\begingroup$ Thank you for your time. I think my question was not clear enough. Ultimately, I don't want a single digit. Right now, in ds1, for every <date>, and every <origine>, I have 12 <destination> and values for each of them. In ds2, for every <date> and every <origine>, I have only one value (not 12). So the goal is to divide, in ds1, every value of same <date> and <origine> by the value of ds2. I don't know If this is clearer? Best $\endgroup$ – EBassal Jan 22 at 15:23
1
$\begingroup$
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]

enter image description here

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.