How to preserve trailing zeros with Python CSV Writer

Question

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

Have you checked where are you "losing" the 0's? It's when reading the Json or when writing to Csv? — epsilonmajorquezero
– epsilonmajorquezero, Commented Feb 7, 2019 at 16:36
@Pradam That's what I was thinking as a solution but I believe que already loses the 0's when json.loads(line), so converting data.values() to string wouldn't help. — epsilonmajorquezero
– epsilonmajorquezero, Commented Feb 7, 2019 at 16:44

epsilonmajorquezero · Accepted Answer · 2019-02-07 16:51:14Z

3

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.

answered Feb 7, 2019 at 16:51

epsilonmajorquezero

5991 gold badge5 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

blhsing · Accepted Answer · 2019-02-07 16:51:58Z

3

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)

answered Feb 7, 2019 at 16:51

blhsing

109k9 gold badges88 silver badges132 bronze badges

Comments

Veera Balla Deva · Accepted Answer · 2019-02-07 17:00:05Z

0

Use format but here need to give static decimal precision.

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

answered Feb 7, 2019 at 17:00

Veera Balla Deva

7886 silver badges19 bronze badges

Collectives™ on Stack Overflow

How to preserve trailing zeros with Python CSV Writer

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related