Converting embedded JSON into flat table

Question

I am new Python user, who decided to use Python to create simple application that allows for converting json files into flat table and saving the output in cvs format. I was wondering if you could give me some advice how I could improve my code to make it work in more efficient way. I am asking since if I convert relatively small files everything works just fine but when I try to convert ~200 MB file it starts to take a while. I am afraid that when I begin to work with bigger files it might take quite some time to convert my datasets.

Here is my code, which I created with help of this great blog post about flattening json objects :

import sys, os, json, tkFileDialog, tkMessageBox
from Tkinter import *
from pandas.io.json import json_normalize

def openFile():
    currdir = os.getcwd()
    filename = tkFileDialog.askopenfilename(
        initialdir = currdir,
        title='Please select a file',
        filetypes=[('JSON file','.json')])

    return filename

def loading_file(path):
    #File path
    file_path = path

    #Loading json file
    json_data = open(file_path)
    data = json.load(json_data)
    return data

#Function that recursively extracts values out of the object into a flattened dictionary
def flatten_json(data):
    flat = [] #list of flat dictionaries
    def flatten(y):
        out = {}

        def flatten2(x, name=''):
            if type(x) is dict:
                for a in x:
                    if a == "name":
                            flatten2(x["value"], name + x[a] + '_')
                    else:
                        flatten2(x[a], name + a + '_')
            elif type(x) is list:
                for a in x:
                    flatten2(a, name + '_')
            else:
                out[name[:-1]] = x

        flatten2(y)
        return out

#Loop needed to flatten multiple objects
    for i in range(len(data)):
        flat.append(flatten(data[i]).copy())

    return json_normalize(flat)




#Outputing normalized data into csv
def csv_out(data, path):
    #creating csv file name
    name = '~/Desktop/' + os.path.basename(os.path.splitext(path)[0]) + '.csv'
    #converting to the csv
    data.to_csv(name, encoding='utf-8') #'~/Desktop/out.csv'

def done():
   tkMessageBox.showinfo('json2csv',"DONE!")

def main():
    filepath = openFile()
    data_file = loading_file(filepath)
    table = flatten_json(data_file)
    csv_out(table, filepath)
    done()

### Application Interface ###
tk = Tk()

#Creating window:
tk.geometry('250x150+600+300')
tk.title('JSON2CSV')

#Creating convert button
convertbutton = Button(tk, text = 'Convert to .csv', command = main)
convertbutton.place(x = 25, y = 50)



tk.mainloop()

Here you will find short example of the json structure I work with:

[{
 "_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"
   }]
 }]
}]

Answer 1

I don't know anything about tkinter, so I'm just going to look at your flatten function.

I'm going to start with some overarching comments. When checking for the type of something, use isinstance instead of type(x) is some_type. Secondly, when you have a trivial loop of the form

list_ = []
for x in iterable:
    list_.append(function(x))

You almost always want to turn it into a list comprehension

list_ = [function(x) for x in iterable]

You have an unnecessary dict.copy which is definitely going to slow things down if you start having very large files.

You can get rid of the for i in range(len(data)) and just use for item in data.

I don't like that out isn't a local variable/parameter, and I find it a bit hacky that it is modified inside of flatten2 without being passed as a parameter. I think a better idea is to use an input parameter. This might also help speed things up, as Python accesses locals faster than nonlocals.

Now you also have a minor correctness issue - you only handle dict and list, but what if there were a tuple inside? Some user defined type that quacks like a dict? Admittedly if you're parsing json that is unlikely, but this would be a fairly useful general utility to flatten all sorts of nested mappings. There are all sorts of ways to handle this, but the easiest might be to ask forgiveness instead of permission

try:
    for key, value in x.items():
        # your dict stuff here
except (ValueError, AttributeError): # oh this doesn't support unpacking/the items() method
    for item in x: # treat it like a list

Not necessarily a perfect solution, but worth considering.

I doubt this is going to help a ton with speed. Short of parallelising this (and I don't think Python would do that effectively with either threads or processes) I'm not seeing a good solution.

One last minor thing - if you have a large and deeply nested json file this might choke with a recursion error. You could fix that by using a list or a queue and keep adding items to it as they're discovered. Something like this (sketched, may not work right away) might work

to_flatten = [(item, '') for item in y]
for item, name in to_flatten:
    if isinstance(item, dict):
       for key, value in item.items():
            if key == "name":
                to_flatten.append((item["value"], name + item[key] + '_'))
            else:
                to_flatten.append((item[key], name + key + '_'))
    elif isinstance(x, (list, tuple)):
       for member in item:
           to_flatten.append((member, name + '_'))
    else:
        result_dict[name[:-1]] = item

This may also help with speed - I honestly don't know.

One last note - like with most languages, string concatenation is slow in Python. In CPython, the peephole optimizer does some magic with just 2 strings (I wish I could find the source for this) but doesn't for more than that. And according to PEP8,

For example, do not rely on CPython's efficient implementation of in-place string concatenation for statements in the form a += b or a = a + b. This optimization is fragile even in CPython ... In performance sensitive parts of the library, the ''.join() form should be used instead.

(emphasis and code-formatting added)

You might be able to get a speedup by changing your string concatenation method.

to_flatten.append((item["value"], ''.join([name, item[key], '_'])))