CODEWITHSUNDEEP
pythonjsoncsv
DaveC
DaveC

Reputation: 106

How to preserve trailing zeros with Python CSV Writer

I am trying to convert a json file with individual json lines to csv. The json data has some elements with trailng zeros that I need to maintain (ex. 1.000000). When writing to csv the value is changed to 1.0, removing all trailing zeros except the first zero following the decimal point. How can I keep all trailing zeros? The number of trailing zeros may not always static.

Updated the formatting of the sample data.

Here is a sample of the json input:

{"ACCOUNTNAMEDENORM":"John Smith","DELINQUENCYSTATUS":2.0000000000,"RETIRED":0.0000000000,"INVOICEDAYOFWEEK":5.0000000000,"ID":1234567.0000000000,"BEANVERSION":69.0000000000,"ACCOUNTTYPE":1.0000000000,"ORGANIZATIONTYPEDENORM":null,"HIDDENTACCOUNTCONTAINERID":4321987.0000000000,"NEWPOLICYPAYMENTDISTRIBUTABLE":"1","ACCOUNTNUMBER":"000-000-000-00","PAYMENTMETHOD":12345.0000000000,"INVOICEDELIVERYTYPE":98765.0000000000,"DISTRIBUTIONLIMITTYPE":3.0000000000,"CLOSEDATE":null,"FIRSTTWICEPERMTHINVOICEDOM":1.0000000000,"HELDFORINVOICESENDING":"0","FEINDENORM":null,"COLLECTING":"0","ACCOUNTNUMBERDENORM":"000-000-000-00","CHARGEHELD":"0","PUBLICID":"xx:1234346"}

Here is a sample of the output:

ACCOUNTNAMEDENORM,DELINQUENCYSTATUS,RETIRED,INVOICEDAYOFWEEK,ID,BEANVERSION,ACCOUNTTYPE,ORGANIZATIONTYPEDENORM,HIDDENTACCOUNTCONTAINERID,NEWPOLICYPAYMENTDISTRIBUTABLE,ACCOUNTNUMBER,PAYMENTMETHOD,INVOICEDELIVERYTYPE,DISTRIBUTIONLIMITTYPE,CLOSEDATE,FIRSTTWICEPERMTHINVOICEDOM,HELDFORINVOICESENDING,FEINDENORM,COLLECTING,ACCOUNTNUMBERDENORM,CHARGEHELD,PUBLICID
John Smith,2.0,0.0,5.0,1234567.0,69.0,1.0,,4321987.0,1,000-000-000-00,10012.0,10002.0,3.0,,1.0,0,,0,000-000-000-00,0,bc:1234346

Here is the code:

import json
import csv

f=open('test2.json') #open input file

outputFile = open('output.csv', 'w', newline='') #load csv file

output = csv.writer(outputFile) #create a csv.writer

i=1
for line in f:
    try:
        data = json.loads(line) #reads current line into tuple
    except:
        print("Can't load line {}".format(i))
    if i == 1:
        header = data.keys()
        output.writerow(header) #Writes header row
    i += 1
    output.writerow(data.values()) #writes values row

f.close() #close input file

The desired output would look like:

ACCOUNTNAMEDENORM,DELINQUENCYSTATUS,RETIRED,INVOICEDAYOFWEEK,ID,BEANVERSION,ACCOUNTTYPE,ORGANIZATIONTYPEDENORM,HIDDENTACCOUNTCONTAINERID,NEWPOLICYPAYMENTDISTRIBUTABLE,ACCOUNTNUMBER,PAYMENTMETHOD,INVOICEDELIVERYTYPE,DISTRIBUTIONLIMITTYPE,CLOSEDATE,FIRSTTWICEPERMTHINVOICEDOM,HELDFORINVOICESENDING,FEINDENORM,COLLECTING,ACCOUNTNUMBERDENORM,CHARGEHELD,PUBLICID
John Smith,2.0000000000,0.0000000000,5.0000000000,1234567.0000000000,69.0000000000,1.0000000000,,4321987.0000000000,1,000-000-000-00,10012.0000000000,10002.0000000000,3.0000000000,,1.0000000000,0,,0,000-000-000-00,0,bc:1234346

Upvotes: 1

Views: 1512

Answers (3)

Veera Balla Deva
Veera Balla Deva

Reputation: 788

Use format but here need to give static decimal precision.

>>> '{:.10f}'.format(10.0)
'10.0000000000'

Upvotes: 0

blhsing
blhsing

Reputation: 106445

The decoder of the json module parses real numbers with float by default, so trailing zeroes are not preserved as they are not in Python. You can use the parse_float parameter of the json.loads method to override the constructor of a real number for the JSON decoder with the str constructor instead:

data = json.loads(line, parse_float=str)

Upvotes: 3

epsilonmajorquezero
epsilonmajorquezero

Reputation: 599

I've been trying and I think this may solve your problem:

Pass the str function to the parse_float argument in json.loads :)

data = json.loads(line, parse_float=str)

This way when json.loads() tries to parse a float it will use the str method so it will be parsed as string and maintain the zeroes. Tried doing that and it worked:

i=1
for line in f:
    try:
        data = json.loads(line, parse_float=str) #reads current line into tuple
    except:
        print("Can't load line {}".format(i))
    if i == 1:
        header = data.keys()
        print(header) #Writes header row
    i += 1
    print(data.values()) #writes values row

More information here: Json Documentation

PS: You could use a boolean instead of i += 1 to get the same behaviour.

Upvotes: 3

Related Questions