Replace element in column with previous one in CSV file using python

Question

    3rd UPDATE: To describe the problem in precise:-
    ================================================

First post, so not able to format it well. Sorry for this.

I have a CSV file called sample.CSV. I need to add additional columns to this file, I could do it using below script. What is missing in my script

If present value in column named "row" is different from previous element. Then update the column named "value" with the previous row column value. If not, update it as zero in the "value" column.

Hope my question is clear. Thanks a lot for your support.

My script:
#!/usr/local/bin/python3 <bl
import csv, os, sys, time
inputfile='sample.csv'
with open(inputfile, 'r') as input, open('input.csv', 'w') as output:
        reader = csv.reader(input, delimiter = ';')
        writer = csv.writer(output, delimiter = ';')
        list1 = []
        header = next(reader)
        header.insert(1,'value')
        header.insert(2,'Id')
        list1.append(header)
        count = 0
        for column in reader:
                count += 1
                list1.append(column)
                myvalue = []
                myvalue.append(column[4])
                if count == 1:
                        firstmyvalue = myvalue
                if count > 2 and myvalue != firstmyvalue:
                        column.insert(0, myvalue[0])
                else:
                        column.insert(0, 0)
                if column[0] != column[8]:
                        del column[0]
                        column.insert(0,0)
                else:
                        del column[0]
                        column.insert(0,myvalue[0])
                column.insert(1, count)
                column.insert(0, 1)
        writer.writerows(list1)

sample.csv:-

    rate;sec;core;Ser;row;AC;PCI;RP;ne;net
    244000;262399;7;5;323;29110;163;-90.38;2;244
    244001;262527;6;5;323;29110;163;-89.19;2;244
    244002;262531;6;5;323;29110;163;-90.69;2;244
    244003;262571;6;5;325;29110;163;-88.75;2;244
    244004;262665;7;5;320;29110;163;-90.31;2;244
    244005;262686;7;5;326;29110;163;-91.69;2;244
    244006;262718;7;5;323;29110;163;-89.5;2;244
    244007;262753;7;5;324;29110;163;-90.25;2;244
    244008;277482;5;5;325;29110;203;-87.13;2;244

My expected output:-

rate;value;Id;sec;core;Ser;row;AC;PCI;RP;ne;net
1;0;1;244000;262399;7;5;323;29110;163;-90.38;2;244
1;0;2;244001;262527;6;5;323;29110;163;-89.19;2;244
1;0;3;244002;262531;6;5;323;29110;163;-90.69;2;244
1;323;4;244003;262571;6;5;325;29110;163;-88.75;2;244
1;325;5;244004;262665;7;5;320;29110;163;-90.31;2;244
1;320;6;244005;262686;7;5;326;29110;163;-91.69;2;244
1;326;7;244006;262718;7;5;323;29110;163;-89.5;2;244
1;323;8;244007;262753;7;5;324;29110;163;-90.25;2;244
1;324;9;244008;277482;5;5;325;29110;203;-87.13;2;244

You will have to store the previous one in a temporary variable, and compare to that as you go along. Can you post some of the code you have so far? (Also it's really not helpful to call a column "row"!) — TessellatingHeckler
– TessellatingHeckler, Commented Jul 16, 2014 at 11:54
Hi, row is picked from another CSV file. So it is populated randomly. If row value is changed from previous, I will have to update value column. Hope I clarified. — user3762807
– user3762807, Commented Jul 16, 2014 at 12:16
your generic vs hardcorded statement is not clear, if none of the suggested solutions work you need to clarify this — deinonychusaur
– deinonychusaur, Commented Jul 16, 2014 at 12:58
Yes. I will try to rephrase my question and give more clarify. Thanks — user3762807
– user3762807, Commented Jul 16, 2014 at 13:06
I would recommend you using at least one of the suggested solutions here and give an example output for where that solution goes wrong and what the expected output would have been. And add reasoning behind why the expected is what it is for where it deviates. All suggestions here would fulfill the requirements as they are stated now. — deinonychusaur
– deinonychusaur, Commented Jul 16, 2014 at 13:23

deinonychusaur · Accepted Answer · 2014-07-16 19:26:32Z

1

This will do the part you were asking for in a generic way, however your output clearly has more changes to it than the question asks for. I added in the Id column just to show how you can order the column output too:

df = pd.read_csv('sample.csv', sep=";")
df.loc[:,'value'] = None
df.loc[:, 'Id'] = df.index + 1
prev = None                                                                        
for i, row in df.iterrows():                                                       
    if prev is not None:                                                           
        if row.row == prev.row:                                                    
            df.value[i] = prev.value                                                 
        else:                                                                      
            df.value[i] = prev.row                                                   
    prev = row 
df.to_csv('output.csv', index=False, cols=['rate','value','Id','sec','core','Ser','row','AC','PCI','RP','ne','net'], sep=';')

edited Jul 16, 2014 at 19:26

answered Jul 16, 2014 at 12:05

deinonychusaur

7,3543 gold badges33 silver badges43 bronze badges

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

1 Comment

user3762807 Over a year ago

Hi, I dont have pandas module installed to check your solution. I tried to install it on my CentOS but it fails bez of numpy version is <1.6.1

Serbitar · Accepted Answer · 2014-07-16 12:01:05Z

0

previous = []
for i, entry in enumerate(csv.reader(test.csv)):
    if not i:                        # do this on first entry only
        previous = entry             # initialize here
        print(entry)
    else:                            # other entries
        if entry[2] != previous[2]:  # check if this entries row is equal to previous entries row
            entry[1] = previous[2]   # add previous entries row value to this entries var
        previous = entry
        print(entry)

edited Jul 16, 2014 at 12:01

answered Jul 16, 2014 at 11:54

Serbitar

2,22422 silver badges26 bronze badges

Comments

Ashoka Lella · Accepted Answer · 2014-07-16 12:03:29Z

0

import csv 
with open('test.csv') as f, open('output.csv','w') as o:
    out = csv.writer(o, delimiter='\t')
    out.writerow(["id", 'value', 'row'])      
    reader = csv.DictReader(f, delimiter="\t") #Assuming file is tab delimited
    prev_row = '100'                                          
    for line in reader:                                       
        if prev_row != line["row"]:
            prev_row = line["row"]  
            out.writerow([line["id"],prev_row,line["row"]])   
        else:                                                 
            out.writerow(line.values())  
o.close()

content of output.csv:

id      value   row
1       0       100
2       0       100
3       110     110
4       140     140

edited Jul 16, 2014 at 12:03

answered Jul 16, 2014 at 11:58

Ashoka Lella

6,7491 gold badge32 silver badges39 bronze badges

Collectives™ on Stack Overflow

Replace element in column with previous one in CSV file using python

sample.csv:-

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

sample.csv:-

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related