check two column values of two csv files and if match found write to a another csv file

Question

i have two csv files named 1.csv and 2.csv with 1.csv of 50 lines and 2.csv of 75 lines. now i am trying to findout if username and feature is present in both files then write to a new file.

my code so far is :

with open('1.csv') as a:
    c=pd.read_csv(a)
    with open('2.csv') as b:
        d=pd.read_csv(b)
        if (c['User'] == d['User'] and c['Feature'] == d['Feature'] and c['ipaddress'] == d['ipaddress']):
         c.to_csv('3.csv')

but with this code i am getting the following error.

Traceback (most recent call last):
  File "path/main.py", line 181, in <module>
    if (c['User'] == d['User'] and c['Feature'] == d['Feature'] and c['ipaddress'] == d['ipaddress']):
  File "path\lib\site-packages\pandas\core\ops.py", line 1190, in wrapper
    raise ValueError("Can only compare identically-labeled "
ValueError: Can only compare identically-labeled Series objects

Any help would be very much appreciated.

1.csv :
name feature start_date 
aaaa  apple  2018-02-10 
bbbb  mango  2018-03-11 
cccc  orange 2018-04-12 
dddd  guava  2018-05-13 

2.csv : 

name feature  end_date
aaaa  apple   2018-02-13
bbbb  mango   2018-03-16
cccc  orange  2018-04-15
dddd  guava   2018-05-18
eeee  Avocado 2018-06-14
ffff  Banana  2018-07-13
gggg  Bilberry 2018-08-09

Expected output 3.csv

name feature start_date end_date   difference
aaaa  apple  2018-02-10 2018-02-13 3days.
bbbb  mango  2018-03-11 2018-03-16 5days.
cccc  orange 2018-04-12 2018-04-15 3days.
dddd  guava  2018-05-13 2018-05-18 5days.

Answer 1

You can easily do this in few lines of code:

import pandas as pd

# Uncomment to read from file and comment out a, b vars from pd.DataFrame
#a = pd.read_csv('1.csv')
#b = pd.read_csv('2.csv')
a = pd.DataFrame({'name': ['aaaa', 'bbbb', 'cccc', 'dddd'],
                  'feature': ['apple', 'mango', 'orange', 'guava'],
                  'start_date': ['2018-02-10','2018-03-11','2018-04-12','2018-05-13',]})

b = pd.DataFrame({'name': ['aaaa', 'bbbb', 'cccc', 'dddd', 'eeee', 'ffff', 'gggg'],
                  'feature': ['apple', 'mango', 'orange', 'guava', 'Avocado', 'Banana','Bilberry',],
                  'end_date': ['2018-02-13','2018-03-16','2018-04-15','2018-05-18','2018-06-14','2018-07-13','2018-08-09']})

# replace to on=['name', 'feature', 'ipaddress'] if needed.
# In example you don't have 'ipaddress', but in your code you have it
c = pd.merge(a, b, how='inner', on=['name', 'feature'])
c['difference'] = pd.to_datetime(c['end_date']) - pd.to_datetime(c['start_date'])
print(c)
#Uncomment to save to file
#c.to_csv('3.csv')

Check variables. It completely same as in your example.

print(a)

   name feature  start_date
0  aaaa   apple  2018-02-10
1  bbbb   mango  2018-03-11
2  cccc  orange  2018-04-12
3  dddd   guava  2018-05-13

print(b)

   name   feature    end_date
0  aaaa     apple  2018-02-13
1  bbbb     mango  2018-03-16
2  cccc    orange  2018-04-15
3  dddd     guava  2018-05-18
4  eeee   Avocado  2018-06-14
5  ffff    Banana  2018-07-13
6  gggg  Bilberry  2018-08-09

print(c)

   name feature  start_date    end_date difference
0  aaaa   apple  2018-02-10  2018-02-13     3 days
1  bbbb   mango  2018-03-11  2018-03-16     5 days
2  cccc  orange  2018-04-12  2018-04-15     3 days
3  dddd   guava  2018-05-13  2018-05-18     5 days

Hope this helps!

Answer 2

Use merge.

df1 = pd.read_csv('1.csv')
df2 = pd.read_csv('2.csv')
df3 = df1.merge(df2, on = ['name','feature'],how = 'left')

Then you can perform the subtraction for dates, depending on the data type of the timestamp columns.