python: adding and deleting rows - on action specified in another csv

Question

i am trying update a csv file (original.csv) from another csv file (first.csv) where i need to add or delete rows based action specified in first.csv column-0, i,e., add, delete.

import csv
file = 'first.csv'
with open(file) as csv_file:
    csv_reader = csv.reader(csv_file, delimiter=',')
    for row in csv_reader:
        if row[0] == 'add':
            with open('original.csv', 'a', newline='') as f:
                writer = csv.writer(f, quotechar="'")
                writer.writerow(row)

cat first.csv

add,apple,1,2,3
add,orange,1,2,3
delete,banana,1,2,3

cat original.csv

none,cherry,1,2,3
none,pineapple,1,2,3
none,banana,1,2,3


if column0 in first.csv is add, it will add that row in original file,
if column0 in first.csv is delete, it will delete that corresponding row (based on match of fields after `none`. delete,banana,1,2,3 == none,banana,1,2,3 both rows are same ignoring first field)

Output getting for above add method..

none,cherry,1,2,3
none,pineapple,1,2,3
none,banana,1,2,3
add,apple,1,2,3
add,orange,1,2,3

please help how to develop above script, so that row corresponding to delete is removed from original file.

expected output:

none,cherry,1,2,3 # already exist in this file
none,pineapple,1,2,3 # already exist in this file
add,apple,1,2,3 # added from first.csv file
add,orange,1,2,3 #added from first.csv file.

please help.

thanks in advance.

score 0 · Answer 1 · answered May 17 '20 at 06:25

0

The best solution to remove a row from the original file would be creating a new file and adding the rows from the original file except the row you want to delete. You can see it how it can be done here.

answered May 17 '20 at 06:25

Kishor

450
8
11

score 0 · Accepted Answer · answered May 17 '20 at 07:36

I have tried to reproduce your task, you can consider following dataframes as csv files i.e for first.csv df1 and original.csv is df2

import pandas as pd
data1 = [['add','apple',1,2,3],
['add','orange',1,2,3],
['delete','banana',1,2,3]]
data2 = [['none','cherry',1,2,3],
['none','pineapple',1,2,3],
['none','banana',1,2,3]]

df1 = pd.DataFrame(data1, columns= ['column1', 'column2', 'column3', 'column4', 'column5'])
df2 = pd.DataFrame(data2, columns= ['column1', 'column2', 'column3', 'column4', 'column5'] )

**df1 for first.csv **

    column1 column2 column3 column4 column5
0   none    cherry         1    2   3
1   none    pineapple      1    2   3
2   none    banana         1    2   3

df2 for original.csv

    column1 column2 column3 column4 column5
0   none    cherry         1    2   3
1   none    pineapple      1    2   3
2   none    banana         1    2   3

for i, val in enumerate(df1.column1):
    if val == 'add':
        df2 = df2.append(df1.iloc[i,:])

    if val == 'delete':
        for ind, val2 in enumerate(df2.column2):
            if val2 ==  'banana':
                df2.drop(ind, axis = 'rows', inplace= True)

Finally df2 for original.csv will look like

column1 column2 column3 column4 column5
0   none    cherry     1    2   3
1   none    pineapple  1    2   3
0   add     apple      1    2   3
1   add     orange     1    2   3

Note: use df2.reset_index() for proper indexing, after this you can use df2 as your final dataframe to make it as csv file.

Please let me know me if this works for you or not thanks

Since i am new to python, i am trying ways to convert "first.csv" into data1 format that you have defined above. Could you please help with that small piece of code too. — Maria628, May 17 '20 at 13:59
with open('first.csv', 'r') as f: X.append(f.readlines()) #just like this.. — Maria628, May 17 '20 at 13:59
you can use pandas read_csv() method, for example for first.csv or original.csv, dataframe1 = pd.read_csv('file_name.csv'). — The Guy, May 17 '20 at 18:44

python: adding and deleting rows - on action specified in another csv

2 Answers2