Edit csv file with Python

Question

acrocephalus 0 Learning

14 Years Ago

Hello!
I am trying to edit a csv file. This is my original csv file

"TINAMIFORMES",,,,
,"Tinamidae",,,
,,"Tinamus",,
,,,"tao","Grey Tinamou"
,,,"solitarius","Solitary Tinamou"
,,,"osgoodi","Black Tinamou"
,,,"major","Great Tinamou"
,,,"guttatus","White-throated Tinamou"
,,"Nothocercus",,
,,,"bonapartei","Highland Tinamou"
,,,"julius","Tawny-breasted Tinamou"
,,,"nigrocapillus","Hooded Tinamou"
,,"Crypturellus",,
,,,"berlepschi","Berlepsch's Tinamou"
,,,"cinereus","Cinereous Tinamou"
,,,"soui","Little Tinamou"
,,,"ptaritepui","Tepui Tinamou"
,,,"obsoletus","Brown Tinamou"
,,,"undulatus","Undulated Tinamou"
,,,"transfasciatus","Pale-browed Tinamou"
,,,"strigulosus","Brazilian Tinamou"
,,,"duidae","Grey-legged Tinamou"
,,,"erythropus","Red-legged Tinamou"
,,,"noctivagus","Yellow-legged Tinamou"
,,,"atrocapillus","Black-capped Tinamou"
,,,"cinnamomeus","Thicket Tinamou"
,,,"boucardi","Slaty-breasted Tinamou"
,,,"kerriae","Choco Tinamou"
,,,"variegatus","Variegated Tinamou"
,,,"brevirostris","Rusty Tinamou"
,,,"bartletti","Bartlett's Tinamou"
,,,"parvirostris","Small-billed Tinamou"
,,,"casiquiare","Barred Tinamou"
,,,"tataupa","Tataupa Tinamou"
,,"Rhynchotus",,
,,,"rufescens","Red-winged Tinamou"
,,,"maculicollis","Huayco Tinamou"
,,"Nothoprocta",,
,,,"taczanowskii","Taczanowski's Tinamou"
,,,"ornata","Ornate Tinamou"
,,,"perdicaria","Chilean Tinamou"
,,,"cinerascens","Brushland Tinamou"
,,,"pentlandii","Andean Tinamou"
,,,"curvirostris","Curve-billed Tinamou"
,,"Nothura",,
,,,"boraquira","White-bellied Nothura"
,,,"minor","Lesser Nothura"
,,,"darwinii","Darwin's Nothura"
,,,"maculosa","Spotted Nothura"
,,,"chacoensis","Chaco Nothura"
,,"Taoniscus",,
,,,"nanus","Dwarf Tinamou"
,,"Eudromia",,
,,,"elegans","Elegant Crested Tinamou"
,,,"formosa","Quebracho Crested Tinamou"
,,"Tinamotis",,
,,,"pentlandii","Puna Tinamou"
,,,"ingoufi","Patagonian Tinamou"

And this is how I would like it to be

"ORDRE TINAMIFORMES",,
"Family Tinamidae",,
"Tinamus","tao","Grey Tinamou"
"Tinamus","solitarius","Solitary Tinamou"
"Tinamus","osgoodi","Black Tinamou"
"Tinamus","major","Great Tinamou"
"Tinamus","guttatus","White-throated Tinamou"
"Nothocercus","bonapartei","Highland Tinamou"
"Nothocercus","julius","Tawny-breasted Tinamou"
"Nothocercus","nigrocapillus","Hooded Tinamou"
"Crypturellus","berlepschi","Berlepsch's Tinamou"
"Crypturellus","cinereus","Cinereous Tinamou"
"Crypturellus","soui","Little Tinamou"
"Crypturellus","ptaritepui","Tepui Tinamou"
"Crypturellus","obsoletus","Brown Tinamou"
"Crypturellus","undulatus","Undulated Tinamou"
"Crypturellus","transfasciatus","Pale-browed Tinamou"
"Crypturellus","strigulosus","Brazilian Tinamou"
"Crypturellus","duidae","Grey-legged Tinamou"
"Crypturellus","erythropus","Red-legged Tinamou"
"Crypturellus","noctivagus","Yellow-legged Tinamou"
"Crypturellus","atrocapillus","Black-capped Tinamou"
"Crypturellus","cinnamomeus","Thicket Tinamou"
"Crypturellus","boucardi","Slaty-breasted Tinamou"
"Crypturellus","kerriae","Choco Tinamou"
"Crypturellus","variegatus","Variegated Tinamou"
"Crypturellus","brevirostris","Rusty Tinamou"
"Crypturellus","bartletti","Bartlett's Tinamou"
"Crypturellus","parvirostris","Small-billed Tinamou"
"Crypturellus","casiquiare","Barred Tinamou"
"Crypturellus","tataupa","Tataupa Tinamou"
"Rhynchotus","rufescens","Red-winged Tinamou"
"Rhynchotus","maculicollis","Huayco Tinamou"
"Nothoprocta","taczanowskii","Taczanowski's Tinamou"
"Nothoprocta","ornata","Ornate Tinamou"
"Nothoprocta","perdicaria","Chilean Tinamou"
"Nothoprocta","cinerascens","Brushland Tinamou"
"Nothoprocta","pentlandii","Andean Tinamou"
"Nothoprocta","curvirostris","Curve-billed Tinamou"
"Nothura","boraquira","White-bellied Nothura"
"Nothura","minor","Lesser Nothura"
"Nothura","darwinii","Darwin's Nothura"
"Nothura","maculosa","Spotted Nothura"
"Nothura","chacoensis","Chaco Nothura"
"Taoniscus","nanus","Dwarf Tinamou"
"Eudromia","elegans","Elegant Crested Tinamou"
"Eudromia","formosa","Quebracho Crested Tinamou"
"Tinamotis","pentlandii","Puna Tinamou"
"Tinamotis","ingoufi","Patagonian Tinamou"

Basically, I would like to add the string "Order" in all the fields with a string in column A; add the string "Family" in all the fields with a string in column B; copy the first field in column C in the fields below until it finds a non empty field, then delete the first fild; and finally move the columns C, D and E to A, B and C. I am trying to use it with the csv module, but I am quite confused. Can anyone help?
Cheers!

Dani

python

3 Contributors
11 Replies
5K Views
1 Day Discussion Span
Latest Post 14 Years Ago Latest Post by acrocephalus

TrustyTony 888 pyMod

14 Years Ago

Like:

with open('mixorder.csv') as inp, open('order.csv','w') as outp :

    inplist = [[word.strip('"\n') for word in line.split(',')]
           for line in inp
           ]

    for ln, line in enumerate(inplist):
        if line[0]:
            line[2]="ORDRE "+ line[0]
        elif line[1]:
            line[2]="Family " + line[1]
        elif line[2]:
            default2=line[2]
        else:
            line[2]=default2
        inplist[ln] = line[2:]
    outp.write('\n'.join(',\t'.join(repr(w)
                               for w in line)
                    for line in inplist))
print open('order.csv').read()

EDIT: @Beat_Slayer: Don't worry , you posted little less Pythonized version. Give choice of style for acrocephalus. I left the file part open, you did that. Maybe with solution for files would be little more stylish. I edited my solution to use with and same file names as you used.

Edited 14 Years Ago by TrustyTony because: n/a

Beat_Slayer 17 Posting Pro in Training

14 Years Ago

f_in = open('mixorder.csv')

fields = []

for line in f_in.readlines():
    fields.append([item.strip('\n') for item in line.split(',')])

f_in.close()

f_out = open('order.csv', 'w')

for i in range(len(fields)):
    if fields[i][0] != '':
        f_out.write('ORDRE ' + fields[i][0] + ',,\n')
    elif fields[i][1] != '':    
        f_out.write('Family ' + fields[i][1] + ',,\n')
    elif fields[i][2] != '':
        pre = fields[i][2]
    else:
        infos = [item for item in fields[i] if item]
        f_out.write('%s,%s,%s\n' % (pre, infos[0], infos[1]))

f_out.close()

Cheers and Happy coding

EDIT: I didn't saw your posted solution tonyjv.

Edited 14 Years Ago by Beat_Slayer because: n/a

TrustyTony 888 pyMod

14 Years Ago

Fixed my code more according to wish of poster:

with open('mixorder.csv') as inp, open('order.csv','w') as outp :
    for ln, line in enumerate([word.strip('"\n')
                for word in line.split(',')]
               for line in inp):
        if line[0]:
            line[2]="ORDRE "+ line[0]
        elif line[1]:
            line[2]="Family " + line[1]
        elif line[2] and not (line[3] and line[4]):
            default2=line[2]
            continue
        else:
            line[2]=default2
        outp.write(','.join('"%s"' % w if w else '' for w in line[2:])+'\n')
    
print(open('order.csv').read())

Edited 14 Years Ago by TrustyTony because: n/a

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

acrocephalus 0 Learning · Answer 1 · 2010-08-27T15:05:16+00:00

Hello,
Thank you for your replies. Beat_Slayer, your code does not exactly what I need, but I'll try to understand it before asking for more help. Tonyjv, your code is giving me a syntax error in line 1.
Cheers!

Dani

acrocephalus 0 Learning · Answer 2 · 2010-08-27T15:15:45+00:00

Beat-Slayer, I was wrong, your code does exactly what I need. It was my fault: I was using a wrong test file. I am studying your code but, could you please comment it?
Cheers!

Dani

acrocephalus 0 Learning · Answer 3 · 2010-08-27T15:30:14+00:00

Beat_Slayer, I understand your code but lines 19-21 are a little bit confusing for me. Can you coment them?
Cheers,
Dani

TrustyTony 888 pyMod Team Colleague Featured Poster · Answer 4 · 2010-08-27T16:30:51+00:00

Hello,
Thank you for your replies. Beat_Slayer, your code does not exactly what I need, but I'll try to understand it before asking for more help. Tonyjv, your code is giving me a syntax error in line 1.
Cheers!
Dani

You must have earlier version of Python (most recent 2 version is 2.7). You can use with inside with instead:

with open('mixorder.csv') as inp:
    with open('order.csv','w') as outp :
        # generate lines from inp file splitting to words from ' and dropping " and \n from left and right
        for line in ([word.strip('"\n') for word in thisline.split(',')] for thisline in inp):
            if line[0]:
                line[2]="ORDRE "+ line[0]
            elif line[1]:
                line[2]="Family " + line[1]
            elif line[2] and not (line[3] and line[4]):
                default2=line[2]
                continue
            else:
                line[2]=default2
            # write out words, except first two, joined by , inside ", leave out quotes for empty words
            outp.write(','.join('"%s"' % w if w else '' for w in line[2:])+'\n')
    
print(open('order.csv').read())

enumerate was left over from previous version, which replaced the line under iteration, and I removed that.

acrocephalus 0 Learning · Answer 5 · 2010-08-27T17:03:11+00:00

You're right, I have python 2.6.5. I should upgrade.

Dani

Beat_Slayer 17 Posting Pro in Training · Answer 6 · 2010-08-27T20:21:37+00:00

else:
    infos = [item for item in fields[i] if item]
    f_out.write('%s,%s,%s\n' % (pre, infos[0], infos[1]))

This is for lines like: ,,,"bonapartei","Highland Tinamou"

it extracts the two fields and writes them together with the prefix, that is get by lines like: ,,"Tinamus",,

Cheers and Happy coding

acrocephalus 0 Learning · Answer 7 · 2010-08-27T20:38:40+00:00

And how can I do it if I want the final code to be like

"TINAMIFORMES","Tinamidae","Tinamus","tao","Grey Tinamou"
"TINAMIFORMES","Tinamidae","Tinamus","solitarius","Solitary Tinamou"
"TINAMIFORMES","Tinamidae","Tinamus","osgoodi","Black Tinamou"
"TINAMIFORMES","Tinamidae","Tinamus","major","Great Tinamou"
"TINAMIFORMES","Tinamidae","Tinamus","guttatus","White-throated Tinamou"
"TINAMIFORMES","Tinamidae","Nothocercus","bonapartei","Highland Tinamou"
"TINAMIFORMES","Tinamidae","Nothocercus","julius","Tawny-breasted Tinamou"
"TINAMIFORMES","Tinamidae","Nothocercus","nigrocapillus","Hooded Tinamou"
"TINAMIFORMES","Tinamidae","Crypturellus","berlepschi","Berlepsch's Tinamou"
"TINAMIFORMES","Tinamidae","Crypturellus","cinereus","Cinereous Tinamou"
"TINAMIFORMES","Tinamidae","Crypturellus","soui","Little Tinamou"
"TINAMIFORMES","Tinamidae","Crypturellus","ptaritepui","Tepui Tinamou"
"TINAMIFORMES","Tinamidae","Crypturellus","obsoletus","Brown Tinamou"
"TINAMIFORMES","Tinamidae","Crypturellus","undulatus","Undulated Tinamou"
"TINAMIFORMES","Tinamidae","Crypturellus","transfasciatus","Pale-browed Tinamou"
"TINAMIFORMES","Tinamidae","Crypturellus","strigulosus","Brazilian Tinamou"
"TINAMIFORMES","Tinamidae","Crypturellus","duidae","Grey-legged Tinamou"
"TINAMIFORMES","Tinamidae","Crypturellus","erythropus","Red-legged Tinamou"
"TINAMIFORMES","Tinamidae","Crypturellus","noctivagus","Yellow-legged Tinamou"
"TINAMIFORMES","Tinamidae","Crypturellus","atrocapillus","Black-capped Tinamou"
"TINAMIFORMES","Tinamidae","Crypturellus","cinnamomeus","Thicket Tinamou"
"TINAMIFORMES","Tinamidae","Crypturellus","boucardi","Slaty-breasted Tinamou"
"TINAMIFORMES","Tinamidae","Crypturellus","kerriae","Choco Tinamou"
"TINAMIFORMES","Tinamidae","Crypturellus","variegatus","Variegated Tinamou"
"TINAMIFORMES","Tinamidae","Crypturellus","brevirostris","Rusty Tinamou"
"TINAMIFORMES","Tinamidae","Crypturellus","bartletti","Bartlett's Tinamou"
"TINAMIFORMES","Tinamidae","Crypturellus","parvirostris","Small-billed Tinamou"
"TINAMIFORMES","Tinamidae","Crypturellus","casiquiare","Barred Tinamou"
"TINAMIFORMES","Tinamidae","Crypturellus","tataupa","Tataupa Tinamou"
"TINAMIFORMES","Tinamidae","Rhynchotus","rufescens","Red-winged Tinamou"
"TINAMIFORMES","Tinamidae","Rhynchotus","maculicollis","Huayco Tinamou"
"TINAMIFORMES","Tinamidae","Nothoprocta","taczanowskii","Taczanowski's Tinamou"
"TINAMIFORMES","Tinamidae","Nothoprocta","ornata","Ornate Tinamou"
"TINAMIFORMES","Tinamidae","Nothoprocta","perdicaria","Chilean Tinamou"
"TINAMIFORMES","Tinamidae","Nothoprocta","cinerascens","Brushland Tinamou"
"TINAMIFORMES","Tinamidae","Nothoprocta","pentlandii","Andean Tinamou"
"TINAMIFORMES","Tinamidae","Nothoprocta","curvirostris","Curve-billed Tinamou"
"TINAMIFORMES","Tinamidae","Nothura","boraquira","White-bellied Nothura"
"TINAMIFORMES","Tinamidae","Nothura","minor","Lesser Nothura"
"TINAMIFORMES","Tinamidae","Nothura","darwinii","Darwin's Nothura"
"TINAMIFORMES","Tinamidae","Nothura","maculosa","Spotted Nothura"
"TINAMIFORMES","Tinamidae","Nothura","chacoensis","Chaco Nothura"
"TINAMIFORMES","Tinamidae","Taoniscus","nanus","Dwarf Tinamou"
"TINAMIFORMES","Tinamidae","Eudromia","elegans","Elegant Crested Tinamou"
"TINAMIFORMES","Tinamidae","Eudromia","formosa","Quebracho Crested Tinamou"
"TINAMIFORMES","Tinamidae","Tinamotis","pentlandii","Puna Tinamou"
"TINAMIFORMES","Tinamidae","Tinamotis","ingoufi","Patagonian Tinamou"
"STRUTHIONIFORMES","Struthionidae","Struthio","camelus","Common Ostrich"
"STRUTHIONIFORMES","Struthionidae","Struthio","molybdophanes","Somali Ostrich"

I have been trying some thing but no success.
Cheers!

Dani

acrocephalus 0 Learning · Answer 8 · 2010-08-27T20:46:09+00:00

Done! This is my final python code

f_in = open('tables/ioc-names-2.5.csv')
 
fields = []
 
for line in f_in.readlines():
	fields.append([item.strip('\n') for item in line.split(',')])

f_in.close()
 
f_out = open('tables/species.csv', 'w')
 
for i in range(len(fields)):
	if fields[i][0] != '':
		order = (fields[i][0])
	elif fields[i][1] != '':    
		family = (fields[i][1])
	elif fields[i][2] != '':
		pre = (fields[i][2])
	else:
		infos = [item for item in fields[i] if item]
		f_out.write('%s,%s,%s,%s,%s\n' % (order, family, pre, infos[0], infos[1]))

 
f_out.close()

Cheers!

Dani