Python: Create file from list of dictionaries
<p>I have about 5000 .gz files from which I have to extract the data which is in form of "list of dictionaries". </p>

<p>Sample Source Data :</p>

<pre><code>{"user" : "J101", "ip" : "192.0.0.0", "usage" : "1000", "Location" : "CA", 
"time" : "12038098048"}

{"user" : "M101", "ip" : "192.0.0.1", "usage" : "5000", 
"time" : "12038098048", "Device" : "iOS" , "user_type" : "Premium"}

{"user" : "T101", "usage" : "10", "Location" : "AK","time" : "12038098048"}

{"user" : "A101", "ip" : "192.0.0.3", "usage" : "2000", 
"time" : "12038098048", "user_type" : "Platinum" }

{"user" : "T101", "usage" : "10", "Location" : "AK","time" : "12038098048"}

{"user" : "J101", "ip" : "192.0.0.0", "usage" : "1000", "Location" : "CA", 
"time" : "12038098048" }
</code></pre>

<p>Each line above represents data for a particular event; users <code>J101</code> and <code>T101</code> reported data twice, so they each have 2 rows. </p>

<p>I am in the initial phase of writing code this, so I started by extracting data from 1 .gz and trying to see if I can parse the data of interest and create a .txt or .csv file. </p>

<p>My requirement is to get only few attributes from these files, like <code>user</code>, <code>ip</code>, <code>time</code> and <code>usage</code>.</p>

<p>Below is the code I wrote to extract the data from .gz file and to store the data in form of list of dictionaries.</p>

<pre><code>import gzip
from collections import defaultdict
import json
import csv

e_dict = { 'userid' : { 'e_name' : 'user'},
        'ipaddr' : { 'e_name' : 'ip' },
        'event_time' : { 'e_name' : 'time' },
        'usage_in_mb' : { 'e_name' : 'usage' }
}

dict_list = []
inputdict = defaultdict(lambda: None)
count_valueerror = 0

class parser(object):
    def read_entries(self):
        count = 0
        with gzip.open('testfile.gz', 'r') as test:
            for row in test:
                try:
                  # Few rows are empty in the source file and have a new line character
                    if row == "\n":
                        continue
                    else:
                        # Changing the type of each row in file to string type for parsing dictionary
                        row_new = json.loads(row)
                        for key, val in e_dict.iteritems():
                            if val['e_name'] in row_new:
                                inputdict[key] = row_new[val['e_name']]
                except ValueError:
                    count_valueerror += 1
                dict_list.append(inputdict)

    def create_csv(self):
        with open('dict.csv', 'wb') as csv_file:
             for row in dict_list:
                 for key, val in row:
                    csvwriter = csv.DictWriter(csv_file, fieldnames= row.keys(), extrasaction='raise', dialect='excel')
                    csvwriter.writeheader()
                    csvwriter.writerows(val)
        return csv_file
</code></pre>

<p>The <code>create_csv</code> method isn't working correctly. I am not sure how to parse the <code>dict_list</code> and take each dictionary object to write it in csv/text file.</p>

<p>I am getting this error 
<code>ValueError: dict contains fields not in fieldnames: 'p</code> for <code>create_csv</code> method.</p>

<p>Modify...</p>

<p>Two list, one dictionary generation(<strong>cveFieldName</strong>, <strong>eventFieldName</strong>, <strong>inputdict</strong>) </p>

<pre><code>inputdict = {}
e_list = ('userid', 'user'), ('ipaddr', 'ip'),\
         ('event_time', 'time'), ('usage_in_mb', 'usage'),\
         ('test_1', 'test1'), ('test_2', 'test2'),\
         ('test_3', 'test4'), ('test_5', 'test6')

cveFieldName, eventFieldName = zip(*e_list)
</code></pre>

<p><strong>eventFieldName</strong> list use, <strong>inputdict.clear()</strong> delete</p>

<pre><code>def read_entries(self):
    count_valueerror = 0
    with gzip.open('test.gz', 'r') as test:
        for row in test:
            try:
                # Few rows are empty in the source file
                # and have a new line character
                if row == "\n":
                    continue
                else:
                    # Changing the type of each row in file
                    # to string type for parsing dictionary

                    row_new = json.loads(row)
                    for idx, x in enumerate(eventFieldName):
                        inputdict[cveFieldName[idx]] = row_new[x] if x in row_new else ''
            except ValueError as e:
                print e
                count_valueerror += 1
            dict_list.append(dict(inputdict))
            # inputdict.clear()
</code></pre>

<p><strong>cveFieldName</strong> use</p>

<pre><code>def create_csv(self):
        with open('dict.csv', 'wb') as csv_file:
            csvwriter = csv.DictWriter(
                csv_file,
                fieldnames=cveFieldName,
                extrasaction='raise',
                dialect='excel')
            csvwriter.writeheader()
            for row in dict_list:
                try:
                    csvwriter.writerow(row)
                except Exception as e:
                    print e

        return csv_file
</code></pre>

<p>dict.csv</p>

<pre><code>userid,ipaddr,event_time,usage_in_mb,test_1,test_2,test_3,test_5
J101,192.0.0.0,12038098048,1000,,,,
M101,192.0.0.1,12038098048,5000,,,,
T101,,12038098048,10,,,,
A101,192.0.0.3,12038098048,2000,,,,
T101,,12038098048,10,,,,
J101,192.0.0.0,12038098048,1000,,,,
</code></pre>

<p><s><strong>inputdict.clear()</strong>   &lt;= Required instruction</p>

<pre><code>def read_entries(self):
    count_valueerror = 0
    with gzip.open('test.gz', 'r') as test:
        for row in test:
            # import pdb; pdb.set_trace()

            try:
                # Few rows are empty in the source file
                # and have a new line character
                if row == "\n":
                    continue
                else:
                    # Changing the type of each row in file
                    # to string type for parsing dictionary

                    row_new = json.loads(row)
                    for key, val in e_dict.iteritems():
                        if val['e_name'] in row_new:
                            inputdict[key] = row_new[val['e_name']]
            except ValueError as e:
                print e
                count_valueerror += 1
            dict_list.append(dict(inputdict))
            inputdict.clear()  # &lt;==== very important

def create_csv(self):
    with open('dict.csv', 'wb') as csv_file:
        csvwriter = csv.DictWriter(
            csv_file,
            fieldnames=['userid', 'ipaddr', 'event_time', 'usage_in_mb'],
            extrasaction='raise',
            dialect='excel')
        csvwriter.writeheader()
        for row in dict_list:
            try:
                csvwriter.writerow(row)
            except Exception as e:
                print e

    return csv_file
</code></pre>

<p>Without the instruction : inputdict.clear()</p>

<pre><code>userid,ipaddr,event_time,usage_in_mb
J101,192.0.0.0,12038098048,1000
M101,192.0.0.1,12038098048,5000
T101,192.0.0.1,12038098048,10            &lt;===  M101 ip address
A101,192.0.0.3,12038098048,2000
T101,192.0.0.3,12038098048,10            &lt;===  A101 ip address
J101,192.0.0.0,12038098048,1000
</code></pre>

<p></s></p>

python json csv dictionary