title: Nested json to csv - generic approach
body: <p>I am very new to Python and I am struggling with converting nested <code>json</code> file into <code>cvs</code>. To do so I started with loading the <code>json</code> and then transformed it in a way that prints out nice output with json_normalize, then using pandas package I output the normalised parts into <code>cvs</code>. </p>
  
  <p>My example json:</p>
  
  <pre><code>[{
   "_id": {
     "id": "123"
   },
   "device": {
     "browser": "Safari",
     "category": "d",
     "os": "Mac"
   },
   "exID": {
     "$oid": "123"
   },
   "extreme": false,
   "geo": {
     "city": "London",
     "country": "United Kingdom",
     "countryCode": "UK",
     "ip": "00.000.000.0"
   },
   "viewed": {
     "$date": "2011-02-12"
   },
   "attributes": [{
     "name": "gender",
     "numeric": 0,
     "value": 0
   }, {
     "name": "email",
     "value": false
   }],
   "change": [{
     "id": {
       "$id": "1231"
     },
     "seen": [{
       "$date": "2011-02-12"
     }]
   }]
  }, {
   "_id": {
     "id": "456"
   },
   "device": {
     "browser": "Chrome 47",
     "category": "d",
     "os": "Windows"
   },
   "exID": {
     "$oid": "345"
   },
   "extreme": false,
   "geo": {
     "city": "Berlin",
     "country": "Germany",
     "countryCode": "DE",
     "ip": "00.000.000.0"
   },
   "viewed": {
     "$date": "2011-05-12"
   },
   "attributes": [{
     "name": "gender",
     "numeric": 1,
     "value": 1
   }, {
     "name": "email",
     "value": true
   }],
   "change": [{
     "id": {
       "$id": "1231"
     },
     "seen": [{
       "$date": "2011-02-12"
     }]
   }]
  }]
  </code></pre>
  
  <p>With following code (here I exclude the nested parts):</p>
  
  <pre><code>import json
  from pandas.io.json import json_normalize
  
  
  def loading_file():
      #File path
      file_path = #file path here
  
      #Loading json file
      json_data = open(file_path)
      data = json.load(json_data)
      return data
  
  #Storing avaliable keys
  def data_keys(data):
      keys = {}
      for i in data:
          for k in i.keys():
              keys[k] = 1
  
      keys = keys.keys()
  
  #Excluding nested arrays from keys - hard coded -&gt; IMPROVE
      new_keys = [x for x in keys if
      x != 'attributes' and
      x != 'change']
  
      return new_keys
  
  #Excluding nested arrays from json dictionary
  def new_data(data, keys):
      new_data = []
      for i in range(0, len(data)):
          x = {k:v for (k,v) in data[i].items() if k in keys }
          new_data.append(x)
      return new_data
  
   def csv_out(data):
       data.to_csv('out.csv',encoding='utf-8')
  
  def main():
       data_file = loading_file()
       keys = data_keys(data_file)
       table = new_data(data_file, keys)
       csv_out(json_normalize(table))
  
  main()
  </code></pre>
  
  <p>My current output looks something like this:</p>
  
  <pre><code>| _id.id | device.browser | device.category | device.os |  ... | viewed.$date |
  |--------|----------------|-----------------|-----------|------|--------------|
  | 123    | Safari         | d               | Mac       | ...  | 2011-02-12   |
  | 456    | Chrome 47      | d               | Windows   | ...  | 2011-05-12   |
  |        |                |                 |           |      |              |
  </code></pre>
  
  <p>My problem is that I would like to include the nested arrays into the cvs, so I have to flatten them. I cannot figure out how to make it generic so I do not use dictionary <code>keys</code> (<code>numeric, id, name</code>) and <code>values</code> while creating table. I have to make it generalisable because the number of keys in <code>attributes</code> and <code>change</code>. Therefore, I would like to have output like this:</p>
  
  <pre><code>| _id.id | device.browser | ... | attributes_gender_numeric | attributes_gender_value | attributes_email_value | change_id | change_seen |
  |--------|----------------|-----|---------------------------|-------------------------|------------------------|-----------|-------------|
  | 123    | Safari         | ... | 0                         | 0                       | false                  | 1231      | 2011-02-12  |
  | 456    | Chrome 47      | ... | 1                         | 1                       | true                   | 1231      | 2011-02-12  |
  |        |                |     |                           |                         |                        |           |             |
  </code></pre>
  
  <p>Thank you in advance! Any tips how to improve my code and make it more efficient are very welcome. </p>