Bringing together 2 databases

Community Help
1

Hello, I hope you’re well.
I have a question about a graph I want to create. I’d like to combine my ISO standards database with another database called continents.
I’d like to be able to make a graph comparing the regions (continents) on the X axis and the number of certificates per standard. I would like to combine the continents database, which has 3 columns (country = name, ISO3, and Region) with my database, which has 4 columns (country, standards, sectors and certificates).
My code is on the link below in the "Try to combine data" part**(last section).**

Does anyone know how I can do this simply?
Thanks in advance to anyone who can help me :slight_smile:

2

To “join” two datasets, you had a good idea to transform one of them (say the continents) into a Map where you will read the value for each country.

Now, to read the continent of a given country name, you can use:

Country => continentMap.get(Country)?.Region

The question mark is here to return undefined when the map doesn’t know the country, instead of generating an error.

Then you can plot directly from the original “master” dataset:

untitled (22)
untitled (22)1856×940 48.7 KB

Plot.plot({
  marginLeft: 60,
  color: { legend: true },
  marks: [
    Plot.barY(
      MasterFile,
      Plot.groupX(
        { y: "sum" },
        {
          x: ({ Country }) => continentMap.get(Country)?.Region,
          fill: ({ Norm }) => Norm,
          y: "Certificates",
          tip: true,
          order: "sum",
          sort: { color: "y" }
        }
      )
    )
  ]
})

I hope this gets you started. Can I suggest that in the future you reduce your question to the one or two cells you need help with? By sharing a huge notebook full of broken cells (is this your code, or something generated by AI?), you’re really asking a lot of effort from someone who wants to help.

1 Like
3

Note that I would actually recommend horizontal bars here: they would provide much more information in much less space. Just reverse x and y everywhere in the code above and you get this:

untitled (23)
untitled (23)1344×424 17.7 KB

Plot.plot({
  marginLeft: 60,
  marks: [
    Plot.barX(
      MasterFile,
      Plot.groupY(
        { x: "sum" },
        {
          y: ({ Country }) => continentMap.get(Country)?.Region ?? "N/A",
          fill: ({ Norm }) => Norm,
          x: "Certificates",
          tip: true,
          order: "sum",
          sort: { color: "x", y: {value:"x", reverse: true} }
        }
      )
    )
  ]
})

(in addition, I sorted y by x)

1 Like
4

You can try the arquero library. See the introduction documentation. Scroll to the Blend datasets with join() section.

1 Like
5

@Fil
Hello,
Thank you very much for your advice and help. :pray:
As far as my cell questions are concerned, I’ll be careful in the future, I promise. As for the code, I’m trying to do everything myself with the documentation on observable and with YouTube videos too. But I must admit that when I can’t do something I ask the AI. So sometimes I copy and paste code given to me by the AI. Unfortunately, however, it rarely works the first time…
So I try to combine all the resources at my disposal to try and learn.
In any case, you’ve helped me a lot and I’d like to thank you. :slight_smile:
Vanessa

@erdirstats
Hello,
Thank you very much for the documentation. I’ll go and check it all out :slight_smile: