Python CSV read-> write; remove and replace PLUS: end of line is JSON format

Question

I am having problems getting my Python script to do what I want. It does not appear to be modifying my file.

I want to:

Read in a *.csv file that has the following format PropertyName::PropertyValue,…,PropertyName::PropertyValue,{ExtPropertyName::ExtPropertyValue},…,{ExtPropertyName:: ExtPropertyValue}
I want to remove PropertyName:: and leave behid just a column of the PropertyValue
I want to add a header line

I was trying to step through replacing the :: values with a comma, but cant seem to get this to work:

fin = csv.reader(open('infile', 'rb'), delimiter=',')
fout = open('outfile', 'w')
for row in fin:
   fout.write(','.join(','.join(item.split()) for item in row) + '::')
fout.close()

Any advice, whether on my first step problem, or to a bigger picture resolution is always appreciated. Thanks.

UPDATE/EDIT asked for by a person nice enough to review for me!

Here is the first line of the *.csv file (INPUT)

InnerDiameterOrWidth::0.1,InnerHeight::0.1,Length2dCenterToCenter::44.6743867864386,Length3dCenterToCenter::44.6768028159989,Length2dToInsideEdge::44.2678260053526,Length3dToInsideEdge::44.2717800813466,Length2dToOutsideEdge::44.6743867864386,Length3dToOutsideEdge::44.6768028159989,MinimumCover::0,MaximumCover::0,StartConnection::ImmxGisUtilityNetworkCommon.Connection,

In a perfect world here is what I would like my text file to look like (OUTPUT)

InnerDiameterOrWidth, InnerHeight, Length2dCenterToCenter,,,,,,,,,,,
0.1,0.1,44.6743867864386

so one header line and the values in column

UPDATED JSON Info

The end of each line has JSON formatted text:

{StartPoint::7858.35924983374[%2C]1703.69341358077[%2C]-3.075},{EndPoint::7822.85045874375[%2C]1730.80294308742[%2C]-3.53962362760298}

WHich I need to split into X Y Z and X Y Z with headers

Could you please update you question with some proper input and the expected output? BTW, welcome to SO! — Fredrik Pihl
– Fredrik Pihl, Commented Mar 3, 2013 at 20:19
Thanks Fredrik, long time reader- first time poster! Will update shortly. — MfM
– MfM, Commented Mar 3, 2013 at 20:34
What's your problem? And why you don't write the file with csv.writer? — wRAR
– wRAR, Commented Mar 3, 2013 at 20:37
the file has multiple delimiters and I cant seem to get the above code to work (response to wRAR). Thank you for looking. — MfM
– MfM, Commented Mar 3, 2013 at 20:40
wRAR, sorry, I dont think I addressed your question. Why not csv.writer? Because I am a newbie and just learning Python for the first time...... Will look into it. — MfM
– MfM, Commented Mar 3, 2013 at 20:47

DSM · Accepted Answer · 2013-03-03 20:54:29Z

1

Maybe something like this (assuming that each line has the same keys, and in the same order):

import csv

with open("diam.csv", "rb") as fin, open("diam_out.csv", "wb") as fout:
    reader = csv.reader(fin)
    writer = csv.writer(fout)
    for i, line in enumerate(reader):
        split = [item.split("::") for item in line if item.strip()]
        if not split: # blank line
            continue
        keys, vals = zip(*split)
        if i == 0:
            # first line: write header
            writer.writerow(keys)
        writer.writerow(vals)

which produces

localhost-2:coding $ cat diam_out.csv 
InnerDiameterOrWidth,InnerHeight,Length2dCenterToCenter,Length3dCenterToCenter,Length2dToInsideEdge,Length3dToInsideEdge,Length2dToOutsideEdge,Length3dToOutsideEdge,MinimumCover,MaximumCover,StartConnection
0.1,0.1,44.6743867864386,44.6768028159989,44.2678260053526,44.2717800813466,44.6743867864386,44.6768028159989,0,0,ImmxGisUtilityNetworkCommon.Connection

I think most of that code should make sense, except maybe the zip(*split) trick: that basically transposes a sequence, i.e.

>>> s = [['a','1'],['b','2']]
>>> zip(*s)
[('a', 'b'), ('1', '2')]

so that the elements are now grouped together by their index (the first ones are all together, the second, etc.)

answered Mar 3, 2013 at 20:54

DSM

355k67 gold badges606 silver badges504 bronze badges

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

4 Comments

MfM Over a year ago

Thanks DSM. I appreciate your taking the time to look at this. WIll try it, and may take a while for me to digest it. But above all, thank you for you time.

MfM Over a year ago

DSM, wow. that was fast. Seems to work great. A question on this (I have 5 files that follow this format, and one that deviates from it only slightly in that it has the same format except for the last string of text, there is a second delimiter '[%2C]' Can I add a second line of split = [item.split("[%2C]" for item in line if item.strip()] ??

MfM Over a year ago

{StartPoint::7858.35924983374[%2C]1703.69341358077[%2C]-3.075},{EndPoint::7822.85045874375[%2C]1730.80294308742[%2C]-3.53962362760298} here is the end of the file, it has coordinates separated by [%2C] and need to have X Y Z header

MfM Over a year ago

Looks as though it is similar to the JSON format: docs.python.org/2/library/json.html

Collectives™ on Stack Overflow

Python CSV read-> write; remove and replace PLUS: end of line is JSON format

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related