title: Pandas - Join two JSON in different structure the right way
body: <p>I know there are many similar problems, but still I have the feeling I'm doing so many things wrong here, I'm sure some of you can help me and give me some advise for the future, I tried many different ways but still doesn't work properly.</p>
  
  <p>I have the two following data sources that I'd like to join with pandas. </p>
  
  <p>First json with properties - let's call it <strong>json1</strong>:</p>
  
  <pre><code>[
  {
      "propertyDetails": {
          "accountId": 102864,
          "availableFromFormatted": "On request",
          "cityName": "Derendingen",
          "contactFormTypeId": 3,
          "countryId": 1,
          "description": "Mehr erfahren: F\u00fcr weitere Bilder, Informationen zu Raumangebot, Gemeinde und Lage besuchen sie uns auf www.immostema.ch und geben Sie oben rechts im Suchfeld die Objektnummer I0398 ein. Wir freuen uns auf Sie!  \r\n  \r\n\r\n\r\n**Schauen Sie sich das Kurzvideo an:**  \r\n[https://youtu.be/xyD9oX4nmDQ](https://youtu.be/xyD9oX4nmDQ)  \r\n  \r\nIn Derendingen im Kanton Solothurn, verkaufen wir an ruhiger Wohnlage dieses **5\u00bd-Zimmer-Einfamilienhaus**(Doppeleinfamilienhaush\u00e4lfte). Das Haus, im Jahr 2013 erbaut, wurde mit qualitativ hochwertigen Materialen Innen ausgebaut. Das Haus eignet sich sehr gut f\u00fcr Familien und bietet sehr sch\u00f6nes und ruhiges Wohnen.   \r\nZum Haus geh\u00f6ren auch ein Autounterstand mit Ger\u00e4teraum sowie ein zus\u00e4tzlicher Parkplatz.   \r\n  \r\n**Hier die Eckdaten:**\r\n\r\n\r\n- Einfamilienhaus mit 5\u00bd - Zimmer\r\n- Grundst\u00fcck: 221 m\u00b2\r\n- Wohnfl\u00e4che: 141 m\u00b2\r\n- Nutzfl\u00e4che: 216 m\u00b2\r\n- Hochwertiger Innenausbau\r\n- W\u00e4rmepumpe\r\n- Fussbodenheizung\r\n- Grosse gedeckte Terrasse\r\n- Grosser Balkon\r\n- 1 Autounterstand mit grossem Ger\u00e4teraum zur Eigennutzung\r\n- 1 Parkplatz\r\n\r\n\r\n\r\nSchulen, Einkaufsm\u00f6glichkeiten, Banken, Post, erreicht man in wenigen Minuten.   \r\nBusbetrieb Solothurn und Umgebung (BSU) verkehrt im 15 Minutentakt. Die Bushaltestelle befindet sich nur 50m vom Haus entfernt.  \r\n  \r\nVerlangen Sie die detaillierte Verkaufsdokumentation.\r\n\r\n",
          "geoAccuracy": 8,
          "id": 4998203
      },
      "propertyDetailsView": {
          "accountPropertiesUrl": "/en/agency-properties/immostema-ag/102864"
      }
  },
  {
      "propertyDetails": {
          "accountId": 98465,
          "attributesSize": {
              "numberOfFloors": 4
          },
          "attributesSurrounding": {
              "distanceShop": 100,
              "distanceShopFormatted": "Approx. 100 m",
              "distanceKindergarten": 100,
              "distanceKindergartenFormatted": "Approx. 100 m",
              "distanceSchool1": 100,
              "distanceSchool1Formatted": "Approx. 100 m",
              "distanceSchool2": 200,
              "distanceSchool2Formatted": "Approx. 200 m",
              "distancePublicTransport": 200,
              "distancePublicTransportFormatted": "Approx. 200 m",
              "distanceMotorway": 6000,
              "distanceMotorwayFormatted": "Approx. 6000 m"
          },
          "attributes": {
              "yearBuilt": 1846
          },
          "availableFrom": "2018-05-30T00:00:00",
          "availableFromFormatted": "Immediately",
          "cityName": "Koppigen",
          "contactFormTypeId": 3,
          "countryId": 1,
          "description": " \r\n\r\n**Was es scho het**\r\n\r\n \r\n\r\nEin altehrw\u00fcrdiges Bauernhaus nahe dem Dorfzentrum.\r\n\r\n \r\n\r\n**Was es darf g\u00e4**\r\n\r\n \r\n\r\nGeplant ist ein umfassender Umbau des Bauernhauses mit der Erstellung von vier 3.5-Zimmer- und zwei 4.5-Zimmer-Wohnungen.\r\n\r\n \r\n\r\n**Me cha grad loslege, muess aber nid**\r\n\r\n \r\n\r\nDie Baubewilligung wurde bereits erteilt und ist g\u00fcltig bis Ende Dezember 2018. Der Kauf der Parzelle, inkl. aller bestehenden Bauten, ist auch ohne Realisierung des Projekts m\u00f6glich.\r\n\r\n \r\n\r\n**Meh gits hie**\r\n\r\n \r\n\r\nUnsere Verkaufsdokumentation mit detailliertem Baubeschrieb, umfassenden Planunterlagen\u00a0und vielem mehr liegt f\u00fcr Sie bereit. Gerne empfangen wir Sie auch bei uns im B\u00fcro, um Ihnen die gesamte \u00dcberbauung detailliert zu erkl\u00e4ren.\r\n\r\n \r\n\r\nIn diesem Sinne: Wir freuen uns, von Ihnen zu h\u00f6ren - bis gli!\r\n\r\n ",
          "geoAccuracy": 8,
          "id": 4998826            
      },
      "propertyDetailsView": {
          "accountPropertiesUrl": "/en/agency-properties/immosky-ag---aargau/238044"          
      }
  }
  ]
  </code></pre>
  
  <p>Second dictionary file that I created myself - lets call it <strong>json2</strong>:
  *remark A: if you generate JSON-files, do you put the id as a key in the dictionary or do you add the key as "id" as a column if you use them later with pandas? I figured it is better as a column as it is easier to set_index() in pandas when it is a column, but not sure?</p>
  
  <pre><code>  {
    "4998203": {
    "id": 4998203,
    "routeDepartureCity": "47.20959, 7.5326",
    "routeDestinationCity": "solothurn Hauptbahnhof",
    "routeDurationSecCity": 455,
    "routeDistanceMetersCity": 1044,
    "transitsAllCity": 2,
    "transitsNoWalkingCity": 1,
    "routeDepartureWork": "47.20959, 7.5326",
    "routeDestinationWork": "swissqual, Allmendweg 8, 4528 Zuchwil",
    "routeDurationSecWork": 1129,
    "routeDistanceMetersWork": 3060,
    "transitsAllWork": 3,
    "transitsNoWalkingWork": 1
  }
  </code></pre>
  
  <p>}</p>
  
  <p>Before I can load them to pandas dataframe I flattern <strong>json1</strong> with the following function I found on the net:</p>
  
  <pre><code>def flatten_json(y):
  out = {}
  
  def flatten(x, name=''):
      if type(x) is dict:
          for a in x:
              if a not in ('images'):
                  flatten(x[a], name + a + '_')
      elif type(x) is list:
          i = 0
          for a in x:
              flatten(a, name + str(i) + '_')
              i += 1
      else:
          out[name[:-1]] = x
  
  flatten(y)
  return out
  </code></pre>
  
  <p>That looks like this:</p>
  
  <pre><code>import ijson, json
  import pandas as pd
  
  with open(json1, 'r') as f:
      #using ijson as this file might be big
      objects = ijson.items(f, 'item.propertyDetails')
      props = list(objects)
  
  p = []
  for prop in props:
      #flatten all dics/arrays inside
      p.append(flatten_json(prop))
  
  #load into pandas including setting index:
  dfprop = pd.DataFrame(p).set_index('id')
  </code></pre>
  
  <p>This works fine and also index seems to be correctly set. Now I load the second <strong>json2</strong> file:</p>
  
  <pre><code>d = json.load(open(json2, 'rb'))
  dfWayCalc = pd.DataFrame.from_dict(d)
  
  dfWayCalc = dfWayCalc.transpose()  # switch cols and rows
  dfWayCalc.set_index('id')
  </code></pre>
  
  <p>If I want to join it with pandas, it doesn't give me an error, but I can't find the correct values from json2 for id=4998203. It is always NaN instead of the actual values. This is how I tried (one of many versions):</p>
  
  <pre><code>m = dfprop.join(dfWayCalc, how='left')
  
  m.loc[4998203] #doesn't show the values from json2
  
  #if i print json2, i see the values:
  print(dfWayCalc.loc['4998203'])
  </code></pre>
  
  <p>What am I doing wrong? What is the correct way to do this join based on index?
  I appreciate any help.</p>