Append data in csv file with robot framework-CodePudding

I have a csv file as:

col1;col2;col3;col4
val1;val1;;val1
val2;val2;;val2
val3;val3;;val3

i need to append a value for each line for col3

i have the index of col3 "indexcol3" and all ligne values in file.txt:

val1
val2
val3

i don't know how to append the lines with file value? can you help!

CodePudding user response：

Using pandas, you could read both files into dataframes and then replace column C3 in the main df with the C3 column from the day...txt dataframe

import pandas as pd

df1 = pd.read_csv('main.csv',sep=';')
df2 = pd.read_csv('day_1.txt',sep=';',header=None,Names=['C3'])
df1.C3 = df2.C3
df1.to_csv('updated_main.csv',sep=';')

CodePudding user response：

I think you're saying that you have your main CSV:

C1;C2;C3;C4
a;A;;1
b;B;;2
c;C;;3

and you'll have daily values that you'll want update main with, like:

day_1.txt
=========
foo
bar
baz

day_2.txt
=========
FOO
BAR
BAZ

If you run the program like, main.py main.csv day_1.txt, you'd get:

C1;C2;C3;C4
a;A;foo;1
b;B;bar;2
c;C;baz;3

run main.py main.csv day_2.txt, and you'd get:

C1;C2;C3;C4
a;A;FOO;1
b;B;BAR;2
c;C;BAZ;3

If so, you need to be able iterate day_X.txt as you're iterating the rows of the main CSV, and insert the txt value into the CSV.

Every time we open a text file in Python, we get an iterator. We normally iterate the lines with a for-loop, but we can "manully step over" the lines by calling next() on the file:

with open("day_1.txt") as f:
    print(next(f).strip())
    print(next(f).strip())
    print(next(f).strip())

Python doesn't strip the line endings, so we have to, and we get:

foo
bar
baz

Now we can see how to step through the text file and get a value per line, at a time, with control. On to merging that idea with reading the CSV.

The csv module lets us read and write CSV files fairly easily. Here's a simple program for reading the input CSV, and just writing it out again, no modifications... boring, but just to get familiar with the basics:

rows = []
with open("main.csv", newline="") as f_csv:
    reader = csv.reader(f_csv, delimiter=";")

    header = next(reader)  # we have the same control to manually iterate the CSV
    rows.append(header)

    for row in reader:  # or hand over iteration to a for-loop
        rows.append(row)

with open("output.csv", "w", newline="") as f_out:
    writer = csv.writer(f_out, delimiter=";")
    writer.writerows(rows)

We can see that we open the file as normal, pass it to the csv reader (specifying the semi-colon delimiter). The reader object we get back is a lot like the text file object from earlier. We're stepping over complete, parsed, "rows" of data, and we have the same mechanisms, next() and the for-loop. The csv reader doesn't know about headers, so we have to account for that ourselves, copying it to the rows list.

Finally, we can add in reading the text file in lock step with the CSV rows:

rows = []
with open("main.csv", newline="") as f_csv, open("day_1.txt") as f_txt:
    reader = csv.reader(f_csv, delimiter=";")
    rows.append(next(reader))
    for row in reader:
        col3_val = next(f_txt).strip()
        row[2] = col3_val
        rows.append(row)

and I get:

C1;C2;C3;C4
a;A;foo;1
b;B;bar;2
c;C;baz;3

Change day_1.txt to day_2.txt, and I get:

C1;C2;C3;C4
a;A;FOO;1
b;B;BAR;2
c;C;BAZ;3