Python file matching and appending

Question

This is one file result.csv:

M11251TH1230 
M11543TH4292 
M11435TDS144

This is another file sample.csv:

M11435TDS144,STB#1,Router#1 
M11543TH4292,STB#2,Router#1 
M11509TD9937,STB#3,Router#1
M11543TH4258,STB#4,Router#1

Can I write a Python program to compare both the files and if line in result.csv matches with the first word in the line in sample.csv, then append 1 else append 0 at every line in sample.csv?

*append 1 else append 0* - should it look like `M11435TDS144,STB#1,Router#11 M11543TH4292,STB#2,Router#11` after appending ? — RomanPerekhrest, Sep 28 '16 at 05:50
No it should look like M11435TDS144,STB#1,Router#1,1 and M11543TH4258,STB#4,Router#1,0 since M11543TH4258 is not found in result.csv — Krishna Prasad, Sep 28 '16 at 05:53
Yes, you can write a program. Please at least *try* and write some code yourself, and then post it. It does not really matter if it still has issues or if you have a question about it, we can answer that. Stack Overflow is not here to write your code for you. — MichielB, Sep 28 '16 at 08:14
Possible duplicate of [Python rewriting instead of appending](http://stackoverflow.com/questions/39751095/python-rewriting-instead-of-appending) — e4c5, Nov 04 '16 at 03:01

Gopi Vinod Avvari · Answer 1 · 2016-09-28T11:45:36.660

0

The following snippet of code will work for you

import csv

with open('result.csv', 'rb') as f:
    reader = csv.reader(f)
    result_list = []
    for row in reader:
        result_list.extend(row)
with open('sample.csv', 'rb') as f:
    reader = csv.reader(f)
    sample_list = []
    for row in reader:
        if row[0] in result_list:
            sample_list.append(row + [1])
        else:
            sample_list.append(row + [0]
with open('sample.csv', 'wb') as f:
    writer = csv.writer(f)
    writer.writerows(sample_list)

edited Sep 28 '16 at 11:45

answered Sep 28 '16 at 06:14

Gopi Vinod Avvari

264
1
6

`f.close()` is not required and indeed will be an error as `with` itself will take care of closing the file pointer :-) – thiruvenkadam Sep 28 '16 at 10:36

score 0 · Answer 2 · answered Sep 28 '16 at 06:36

import pandas as pd

d1 = pd.read_csv("1.csv",names=["Type"])
d2 = pd.read_csv("2.csv",names=["Type","Col2","Col3"])
d2["Index"] = 0

for x in d1["Type"] :
    d2["Index"][d2["Type"] == x] = 1

d2.to_csv("3.csv",header=False)

Considering "1.csv" and "2.csv" are your csv input files and "3.csv" is the result you needed

score 0 · Answer 3 · answered Sep 28 '16 at 08:06

0

The solution using csv.reader and csv.writer (csv module):

import csv

newLines = []
# change the file path to the actual one
with open('./data/result.csv', newline='\n') as csvfile:
    data = csv.reader(csvfile)
    items = [''.join(line) for line in data]

with open('./data/sample.csv', newline='\n') as csvfile:
    data = list(csv.reader(csvfile))
    for line in data:
        line.append(1 if line[0] in items else 0)
        newLines.append(line)

with open('./data/sample.csv', 'w', newline='\n') as csvfile:
    writer = csv.writer(csvfile)
    writer.writerows(newLines)

The sample.csv contents:

M11435TDS144,STB#1,Router#1,1
M11543TH4292,STB#2,Router#1,1
M11509TD9937,STB#3,Router#1,0
M11543TH4258,STB#4,Router#1,0

answered Sep 28 '16 at 08:06

RomanPerekhrest

88,541
4
65
105

Hi Roman, Thank you for the reply. Every time i execute the code, It keeps on appendning. Instead of appending to sample.csv, can i rewrite the last column again and again when the code is executed. – Krishna Prasad Sep 28 '16 at 13:14
hello, my code will overwrite the `sample.csv` file contents every time it runs. No appending. Check your code for correct file opening in "write" mode. The crucial line is `with open('./data/sample.csv', 'w', newline='\n') as csvfile:` – RomanPerekhrest Sep 28 '16 at 13:25
Hi roman, Thank you for the reply. It is giving "TypeError: 'newline' is an invalid keyword argument for this function" and every time it is appending to a sample.csv file instead of rewriting it – Krishna Prasad Sep 28 '16 at 13:38
check you Python version, is it lower than v3 ? I'm using v.3.4 – RomanPerekhrest Sep 28 '16 at 13:41
I am using python 2.7. – Krishna Prasad Sep 28 '16 at 13:42
I have updated to a newer version. Even now the sample.csv is being appended and growing in size instead of replacing the last columns with 1's and 0's – Krishna Prasad Sep 28 '16 at 14:16

score 0 · Answer 4 · answered Sep 28 '16 at 10:36

With only one column, I wonder why you made it as a result.csv. If it is not going to have any more columns, a simple file read operation would suffice. Along with converting the data from result.csv to dictionary will help in quick run as well.

result_file = "result.csv"
sample_file = "sample.csv"

with open(result_file) as fp:
    result_data = fp.read()
    result_dict = dict.fromkeys(result_data.split("\n"))
    """
    You can change the above logic, in case you have very few fields on csv like this:
    result_data = fp.readlines()
    result_dict = {}
    for result in result_data:
        key, other_field = result.split(",", 1)
        result_dict[key] = other_field.strip()
    """

#Since sample.csv is a real csv, using csv reader and writer
with open(sample_file, "rb") as fp:
    sample_data = csv.reader(fp)
    output_data = []
    for data in sample_data:
        output_data.append("%s,%d" % (data, data[0] in result_dict))

with open(sample_file, "wb") as fp:
    data_writer = csv.writer(fp)
    data_writer.writerows(output_data)

Python file matching and appending

4 Answers4