Is there any way to merge 2 json files programmatically?

Question

There is the US json for the current situation in US, yet the json only shows the States while I'd also need the coordinates to place them in a map.

us data

On here we have the long and lat for each State, not sure how could I merge the 2 to combine them into a single file adding the coordinates for each State.

US data json is:

[
  {
    "date": 20200421,
    "state": "AK",
    ...
  },
  {
    "date": 20200421,
    "state": "AL",
    "positive": 5231,
    "negative": 43295,...

For each state I am looking at adding:

[
  {
    "date": 20200421,
    "state": "AK",
    "lat": "63.588753",
    "long": "-154.493062",

Then be able to download it. Or is there any other way I could programmatically get the coords instead of adding them each time? Not sure how to approach it.

UPDATE

Added php tag too as this could be done via backend.

You're asking about a four-step process: 1. getting the US state data into a variable, 2. getting the location data into a variable, 3. combining them into a new variable, and 4. moving that result into a format/file that's useful to you. Which (or all?) of the four steps are you unsure about? You should also say what language you prefer because this is equally possible in NodeJs, a browser or PHP. — Simon Jacobs, Apr 22 '20 at 00:23
@SimonJacobs the whole logic to get all the four steps i guess. Not looking for NodeJs, would be fine with browser or php as per the tags. — rob.m, Apr 22 '20 at 00:26

score 1 · Accepted Answer · edited Mar 19 '21 at 23:06

If you compose a dictionary of information about latitude and longitude in format:

{'AK': {lat:"63.588753", long: "-154.493062"},
 'AL': {lat:"32.318231", long: "-86.902298"}}

To get the info into dictionary you can use scraper script on site you've provided (run in browser console):

JSON.stringify(Array.from(document.querySelector('.devsite-table-wrapper').children[0].children[0].children).reduce((acc,item) => ({...acc, [item.children[0].textContent]: {lat: item.children[1].textContent, long : item.children[2].textContent} })))

It iterates through all table rows and build a dictionary

Results are:

{
   "AK":{
      "lat":"63.588753",
      "long":"-154.493062"
   },
   "AL":{
      "lat":"32.318231",
      "long":"-86.902298"
   },
   "AR":{
      "lat":"35.20105",
      "long":"-91.831833"
   },
   "AZ":{
      "lat":"34.048928",
      "long":"-111.093731"
   },
   "CA":{
      "lat":"36.778261",
      "long":"-119.417932"
   },
   "CO":{
      "lat":"39.550051",
      "long":"-105.782067"
   },
   "CT":{
      "lat":"41.603221",
      "long":"-73.087749"
   },
   "DC":{
      "lat":"38.905985",
      "long":"-77.033418"
   },
   "DE":{
      "lat":"38.910832",
      "long":"-75.52767"
   },
   "FL":{
      "lat":"27.664827",
      "long":"-81.515754"
   },
   "GA":{
      "lat":"32.157435",
      "long":"-82.907123"
   },
   "HI":{
      "lat":"19.898682",
      "long":"-155.665857"
   },
   "IA":{
      "lat":"41.878003",
      "long":"-93.097702"
   },
   "ID":{
      "lat":"44.068202",
      "long":"-114.742041"
   },
   "IL":{
      "lat":"40.633125",
      "long":"-89.398528"
   },
   "IN":{
      "lat":"40.551217",
      "long":"-85.602364"
   },
   "KS":{
      "lat":"39.011902",
      "long":"-98.484246"
   },
   "KY":{
      "lat":"37.839333",
      "long":"-84.270018"
   },
   "LA":{
      "lat":"31.244823",
      "long":"-92.145024"
   },
   "MA":{
      "lat":"42.407211",
      "long":"-71.382437"
   },
   "MD":{
      "lat":"39.045755",
      "long":"-76.641271"
   },
   "ME":{
      "lat":"45.253783",
      "long":"-69.445469"
   },
   "MI":{
      "lat":"44.314844",
      "long":"-85.602364"
   },
   "MN":{
      "lat":"46.729553",
      "long":"-94.6859"
   },
   "MO":{
      "lat":"37.964253",
      "long":"-91.831833"
   },
   "MS":{
      "lat":"32.354668",
      "long":"-89.398528"
   },
   "MT":{
      "lat":"46.879682",
      "long":"-110.362566"
   },
   "NC":{
      "lat":"35.759573",
      "long":"-79.0193"
   },
   "ND":{
      "lat":"47.551493",
      "long":"-101.002012"
   },
   "NE":{
      "lat":"41.492537",
      "long":"-99.901813"
   },
   "NH":{
      "lat":"43.193852",
      "long":"-71.572395"
   },
   "NJ":{
      "lat":"40.058324",
      "long":"-74.405661"
   },
   "NM":{
      "lat":"34.97273",
      "long":"-105.032363"
   },
   "NV":{
      "lat":"38.80261",
      "long":"-116.419389"
   },
   "NY":{
      "lat":"43.299428",
      "long":"-74.217933"
   },
   "OH":{
      "lat":"40.417287",
      "long":"-82.907123"
   },
   "OK":{
      "lat":"35.007752",
      "long":"-97.092877"
   },
   "OR":{
      "lat":"43.804133",
      "long":"-120.554201"
   },
   "PA":{
      "lat":"41.203322",
      "long":"-77.194525"
   },
   "PR":{
      "lat":"18.220833",
      "long":"-66.590149"
   },
   "RI":{
      "lat":"41.580095",
      "long":"-71.477429"
   },
   "SC":{
      "lat":"33.836081",
      "long":"-81.163725"
   },
   "SD":{
      "lat":"43.969515",
      "long":"-99.901813"
   },
   "TN":{
      "lat":"35.517491",
      "long":"-86.580447"
   },
   "TX":{
      "lat":"31.968599",
      "long":"-99.901813"
   },
   "UT":{
      "lat":"39.32098",
      "long":"-111.093731"
   },
   "VA":{
      "lat":"37.431573",
      "long":"-78.656894"
   },
   "VT":{
      "lat":"44.558803",
      "long":"-72.577841"
   },
   "WA":{
      "lat":"47.751074",
      "long":"-120.740139"
   },
   "WI":{
      "lat":"43.78444",
      "long":"-88.787868"
   },
   "WV":{
      "lat":"38.597626",
      "long":"-80.454903"
   },
   "WY":{
      "lat":"43.075968",
      "long":"-107.290284"
   }
}

Then you can compose data using map function and spread operator and add coordinates to every item of array, e.g.

    const data = [{
        "date": 20200421,
        "state": "AK"
      },
      {
        "date": 20200421,
        "state": "AL",
        "positive": 5231,
        "negative": 43295
       }
       ]

       const dictionary = {'AK': {lat:"63.588753", long: "-154.493062"},
       'AL': {lat:"32.318231", long: "-86.902298"}}

       const result = data.map(item => ({...item, lat: dictionary[item.state].lat, long: dictionary[item.state].long }))
       
       console.log(result)

that means building a dictionary manually tho or I should first run to like, loop the coords to create the dictionary then do the rest? — rob.m, Apr 22 '20 at 00:17
I do not see in your post format for coordinates information, If you add it, I can help you with building a dictionary from an array for example — elvira.genkel, Apr 22 '20 at 00:18
thanks elvira, the link is in the question, I could get that and convert it manually to json if you prefer https://developers.google.com/public-data/docs/canonical/states_csv — rob.m, Apr 22 '20 at 00:27
@rob.m I've added information about getting a dictionary using scrapping script from browser to answer. As for downloading file you can follow this instruction: https://stackoverflow.com/questions/19721439/download-json-object-as-a-file-from-browser/30800715 — elvira.genkel, Apr 22 '20 at 00:44
so I guess on const data I can just put the link right? https://covidtracking.com/api/v1/states/daily.json — rob.m, Apr 22 '20 at 00:48
No, you should fetch the data from server, using any preferred library: e.g. `const getData = async () => { const data = await fetch('https://covidtracking.com/api/v1/states/daily.json') console.log(await data.json()) } ` — elvira.genkel, Apr 22 '20 at 01:06
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/212218/discussion-between-rob-m-and-elvira-genkel). — rob.m, Apr 22 '20 at 01:13
so basically to male it simple I could create data.js and place that last bit of code you gave and launch the script from the browser but that would be async tho with the other script in the answer right? — rob.m, Apr 22 '20 at 01:21

Is there any way to merge 2 json files programmatically?

1 Answers1