In Python, how to compare two csv files based on values in one column and output records from first file that do not match second

user_14047455 0
Viewed 10181

Pretty new to python and coding in general. I've been searching for several csv comparison questions and answers and couldn't find anything that helped with this specific comparison problem.

I have two files that contain network asset info. Some devices have multiple IP addresses in one file, and only 1 address in another. Also they don't seem to share uppercase or lowercase format. I'm interested in their hostname values.

(files don't have headers)

file 1:

HOSTNAME1,10.0.0.1
HOSTNAME2,10.0.0.2
HOSTNAME3,10.19.0.3
hostname4,10.19.0.4,10.19.17.31,10.19.17.32,10.19.17.33,10.19.17.34
hostname5,10.19.0.40,10.19.17.51,10.19.17.52,10.19.17.53,10.19.17.54
hostname6,10.19.0.55,10.19.17.56,10.19.17.57,10.19.17.58,10.19.17.59

File 2

HOSTNAME4,10.19.0.4
HOSTNAME5,10.19.0.40
HOSTNAME6,10.19.0.55
hostname7,192.168.0.1
hostname8,192.168.0.2
hostname9,192.168.0.3

I'd like to compare these files based on hostname (column 0) and output to a third file that contains the rows in file1 that are NOT in file2, ignoring case, and ignoring if they have multiple IP's in file1 or file2.

desired output:

file3:

HOSTNAME1,10.0.0.1
HOSTNAME2,10.0.0.2
HOSTNAME3,10.19.0.3

I tried a simple comm command in bash to try and see if I could generate the desired result and had no luck, so I decided to try this in python

comm -23 --nocheck-order file1.csv file2.csv > file3.csv

Here's what i've tried in python:

with open('file1.csv', 'r') as f1, open('file2.csv', 'r') as f2:
    fileone = f1.readlines()
    filetwo = f2.readlines()

with open('file3.csv', 'w') as outFile:
    for line in fileone:
        if line not in filetwo:
            outFile.write(line)

Problem is it isn't writing the rows where the IP list don't match exactly. Even if in column 1 they share a hostname, if the row has multiple ips in one file it isn't counted.

I'm not sure my code above is ignore case and it seems to be trying to match the entire string from a row, rather than "contains."

willing to try pandas package if that makes more sense for this kind of comparison

pandas compare csv python
3 Answers

Your own code is not too far away from what you need to do.

Step 1 : Create a set from the list of hostnames in file2.csv. Here the hostnames are changed to uppercase.

with open('file2.csv') as check_file:
    check_set = set([row.split(',')[0].strip().upper() for row in check_file])

Step 2 : Iterate through the lines of file1.csv and check if the hostname is in the set.

with open('file1.csv', 'r') as in_file, open('file3.csv', 'w') as out_file:
    for line in in_file:
        if line.split(',')[0].strip().upper() not in check_set:
            out_file.write(line)

Generated file file3.csv contents:

HOSTNAME1,10.0.0.1
HOSTNAME2,10.0.0.2
HOSTNAME3,10.19.0.3
user_22020180

Since you are interested to use Pandas I would suggest this.

Use read_csv to read the csv file and merge to join both and identify the mismatch. But for this the number of columns in both files should be same(or use names to define columns). Having said that,if you fine with only the first column comparison you can try this.

import pandas as pd

#Read the 2 csv files and take only the first column
file1_df = pd.read_csv('filename1.csv',usecols=[0],names=['Name'])
file2_df = pd.read_csv('filename2.csv',usecols=[0],names=['Name'])

#Converting both the files first column to uppercase to make it case insensitive
file1_df['Name'] = file1_df['Name'].str.upper()
file2_df['Name'] = file2_df['Name'].str.upper()

#Merging both the Dataframe using left join
comparison_result = pd.merge(file1_df,file2_df,on='Name',how='left',indicator=True)

#Filtering only the rows that are available in left(file1)
comparison_result = comparison_result.loc[comparison_result['_merge'] == 'left_only']

print(comparison_result)

As I told, Since the number of columns are different(if separated by comma) in both csv, i'm reading only the first column. Hence output also will be only one column as shown below.

HOSTNAME1
HOSTNAME2
HOSTNAME3
user_42957630

you need to compare the first column only , try something like below

filetwo=[val.split(',')[0].strip().lower() for val in filetwo]
for line in fileone:
  if line.split(',')[0].strip().lower() not in filetwo:
    print(line)

run the code as list

user_141487120
Related