Converts JSON to CSV

convert_json_csv.py

#!/bin/python
# -*- coding: utf-8 -*-

"""
Converts JSON to CSV.

"""

import json
import csv
import datetime

WEEKDAYS = ['DOM', 'SEG', 'TER', 'QUA', 'QUI', 'SEX', 'SAB']

def load_data():
    """Loads json file"""
    
    json_data = open("file3.json", "r").read()
    data = json.loads(json_data)
    return data

def convert_date(timestamp):
    return datetime.datetime.utcfromtimestamp(int((timestamp/1000)-2*60*60)).strftime('%Y-%m-%d %H:%M:%S') 

def convert_hour(timestamp):
    return datetime.datetime.utcfromtimestamp(int((timestamp/1000)-2*60*60)).strftime('%H')

def convert_month(timestamp):
    return datetime.datetime.utcfromtimestamp(int((timestamp/1000)-2*60*60)).strftime('%m')

def convert_year(timestamp):
    return datetime.datetime.utcfromtimestamp(int((timestamp/1000)-2*60*60)).strftime('%Y')

def convert_weekday(timestamp):
    return WEEKDAYS[int(datetime.datetime.utcfromtimestamp(int((timestamp/1000)-2*60*60)).strftime('%w'))]

def transactions():
    """Converts transactions to csv.
    """
    data = load_data()
    transactions_list = []
    with open('transactions_done.csv', 'wb') as csv_file:
        writer = csv.writer(csv_file, delimiter=";", quotechar='|')
        # reading
        line = 'key,date,hour,dtCreated,userEmail,uid,productKey,productName,bank,taxbuy,taxsell,canceled,approved,type,amount,in,out,month,year,weekday,free40,tic,chips,profit,profit10'
        writer.writerow(line.split(','))
        
        for k, transaction in data.items():
            item = {'id': k, 'key': k}

            if 'dtCreated' in transaction:
                date = convert_date(transaction['dtCreated'])
            else:
                #skip
                continue

            # building fields
            for field, value in transaction.items():
                item[field] = value
                item['date'] = convert_date(transaction['dtCreated'])
                item['hour'] = convert_hour(transaction['dtCreated'])
                item['month'] = convert_month(transaction['dtCreated'])
                item['year'] = convert_year(transaction['dtCreated'])
                item['weekday'] = convert_weekday(transaction['dtCreated'])
            

            #dtCreated, userEmail, uid, productKey, productName, taxbuy, taxsell, bank, canceled, approved, amount,    
            item['canceled'] = item['canceled'] if 'canceled' in item else False
            item['approved'] = item['approved'] if 'approved' in item else False 
            item['bank'] = str(item['bank'].encode('UTF-8')) if 'bank' in item else '' 
            
            if item['type'] == 'buy':
                item['in'] = item['amount']
                item['free40'] = float(item['amount']) - (float(item['amount']) * 0.40)
                item['tic'] = item['free40'] * 0.10
                item['chips'] = float(item['amount']) / (float(item['taxbuy']) / 100.0)
            else:
                item['in'] = 0
                item['free40'] = 0
                item['tic'] = 0
                item['chips'] = 0
            
            if item['type'] == 'sell':
                item['out'] = item['amount']
                #item['chips'] = float(item['amount']) / (float(item['taxsell']) / 100.0)
            else:
                item['out'] = 0
            
            if item['approved'] == False:
                continue
                
            item['profit'] = item['chips'] * 0.40
            item['profit10'] = item['profit'] * 0.10
            
            line = '{key},{date},{hour},{dtCreated},{userEmail},{uid},{productKey},{productName},{bank},{taxbuy},{taxsell},{canceled},{approved},{type},{amount},{in},{out},{month},{year},{weekday},{free40},{tic},{chips},{profit},{profit10}'.format(**item)
            writer.writerow(line.split(','))

if __name__ == "__main__":
    transactions()