How to import normalised json data from several files into a pandas dataframe?
<p>I have json datafiles in several directories that I want to import into Pandas to do some data analysis. The format of the json depends on the type defined in the directory name. For example,</p>

<pre><code>dir1_typeA/
  file1
  file2
  ...
dir1_typeB/
  file1
  file2
  ...
dir2_typeB/
  file1
  ...
dir2_typeA/
  file1
  file2
</code></pre>

<p>Each <code>file</code> contains a complex nested json string that will be a row of the DataFrame. I will have two data frames for each TypeA and TypeB. Later on I will append them if needed. </p>

<p>So, far I've got all the files paths I need with os.walk and am trying to go through </p>

<pre><code>    import os
    from glob import glob

    PATH = 'dir/filepath'
    files = [y for x in os.walk(PATH) for y in glob(os.path.join(x[0], 'file*'))]

    for file in files:
        with open(issuefile, 'r') as f:
            data = f.read()

        data_json = json_normalize(json.loads(data))
        type = ' '.join(issuefile.split('/')[3]
        data_json['type'] = type
        # append to data frame for typeA and typeB
        if 'typeA' in type:
            # append to typeA dataframe
        else:
            # append to typeB dataframe
</code></pre>

<p>There is one added issue, which is files inside a directory may have slightly different fields. For example, <code>file1</code> may have a few more fields that <code>file2</code> in <code>dir1_typeA</code>. So, I need to accommodate that dynamic nature in data frame for each type as well.</p>

<p>How do I create these two dataframes? </p>

<p>I think you should concatenate the files together first before you read them into pandas, here is how you'd do it in bash (you could also do it in Python):</p>

<pre><code>cat `find *typeA` &gt; typeA
cat `find *typeB` &gt; typeB
</code></pre>

<p>Then you can import it into pandas using <code>io.json.json_normalize</code>:</p>

<pre><code>import json
with open('typeA') as f:
    data = [json.loads(l) for l in f.readlines()]
    dfA = pd.io.json.json_normalize(data)

dfA

#          that this.first this.second
# 0  otherthing      thing       thing
# 1  otherthing      thing       thing
# 2  otherthing      thing       thing
</code></pre>

python json pandas dataframe