0

How to replace a column in csv file with a condition in python?

My csv file contains:

 34,2016-06-14 13:37:10,,1,,,t
 34,2016-06-15 13:48:20,,1,,,f
 34,2016-06-16 13:55:34,,1,,,t
 34,2016-06-17 13:48:40,,1,,,f
 34,2016-06-18 13:41:27,,1,,,t

I want to change the last column according to a condition. That is I want to change "t" to "1" and "f" to "0" using python program.

I have seen many examples but not with this condition and examples are little bit confusing.

I tried a little bit but it is not working. I also don't know if it is correct method or not 

f = open('/home/rck/Desktop/attendance/month.csv','rb')
fo = open('/home/rck/Desktop/attendance/demo.csv','wb')

for line in f:
    bits = line.split(',')

if bits[5] == '"t"':
    bits[5] = '"1"'    
elif bits[5] == '"f"':
    bits[5] = '"0"'
fo.write( ','.join(bits) )

f.close()
fo.close()

How can I achieve this?

Thanks....

Improve this question
2
  • 1
    Could you show your attempts, this is very trivial to do Commented Jul 7, 2016 at 10:10
  • Did you tried any csv modules in python? Commented Jul 7, 2016 at 10:11

3 Answers 3

Reset to default
1

Looking at your example there are 7 elements to the bits array, so you need to test bits[6] not bits[5]. If you know you always want the last one, bits[-1] would work.

Also, I don't think your quoting '"t"' is correct. I fared better with 't'.

Finally, the last element (for me anyway) contains the newline character. So to achieve a match I changed the tests to this form:

    if bits[-1] == 't\n':

This then worked for me. rafalmp's answer works as it does a search and replace on the whole line looking for the character 't' followed by a newline, which it is.

One design difference however is that when using code based on your example, if the code encounters an unknown character (not a 't' or an 'f') it would leave the character unaltered. rafalmp's code would always change it to a '0' if it were not a 't'. to have the same effect in your example, change the elif line to an else with no supplemental test.

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

Comments

0

You can use regular expression to do the job:

import re

for line in f:
    line = re.sub('\w$', lambda x: '"1"' if x.group(0) == 't' else '"0"', line)
    fo.write(line)
Improve this answer

4 Comments

Thanks for your answer. The code changes the value but it changes 't' as '0' not '1' I have changed the line to "line = re.sub('\w$', lambda x: '0' if x == 't' else '1', line)" now it changes the 't' to '1'.
Sorry, made a mistake in the lambda function - now it works as it should.
@sathish, please check the edit made by rafalmp. x.group(0) should be compared and not x
But it also changes the 'f' to '1'. It changes only the else part. If I give else as 0 it changes both the 't' and 'f' to 0 and it is same as for 1 both changes to 1
0

Unless you insist on python, it would easily be done with awk as following:

awk '{$7= $7=="t" ? "1" : "0"}1' FS=',' OFS=',' month.csv

where replace 7th column with 0/1 using ternary and FS (field separator) and OFS (output field separator) is ,.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.