Compare two dataframes and delete not same dates

Question

I have two dataframes and want to compare them and delete the days in the df2 which are not the same as in df1. I tried to use:

df2[~df2.Date.isin(df1.Date)]

but this does not work and getting an empty dataframe. df2 should look like df1. The dataframe's looks like the following:

df1
        Date
0    20-12-16
1    21-12-16
2    22-12-16
3    23-12-16
4    27-12-16
5    28-12-16
6    29-12-16
7    30-12-16
8    02-01-17
9    03-01-17
10   04-01-17
11   05-01-17
12   06-01-17

df2

         Date
0    20-12-16
1    21-12-16
2    22-12-16
3    23-12-16
4    24-12-16
5    25-12-16
6    26-12-16
7    27-12-16
8    28-12-16
9    29-12-16
10   30-12-16
11   31-12-16
12   01-01-17
13   02-01-17
14   03-01-17
15   04-01-17
16   05-01-17
17   06-01-17

Answer 1

It seems dtypes are different. For comparing need same.

Check it by:

print (df1.Date.dtype)
print (df2.Date.dtype)

and then convert if necessary:

df1['Date'] = pd.to_datetime(df1['Date'])
df2['Date'] = pd.to_datetime(df2['Date'])

I add another 2 solutions - first with numpy.in1d and second with merge, because need default inner join:

 df = df2[np.in1d(df2.Date, df1.Date)]
print (df)
         Date
0  2016-12-20
1  2016-12-21
2  2016-12-22
3  2016-12-23
7  2016-12-27
8  2016-12-28
9  2016-12-29
10 2016-12-30
13 2017-01-02
14 2017-01-03
15 2017-01-04
16 2017-01-05
17 2017-01-06

---

df = df1.merge(df2, on='Date')
print (df)
         Date
0  2016-12-20
1  2016-12-21
2  2016-12-22
3  2016-12-23
7  2016-12-27
8  2016-12-28
9  2016-12-29
10 2016-12-30
13 2017-01-02
14 2017-01-03
15 2017-01-04
16 2017-01-05
17 2017-01-06

Sample:

d1 = {'Date': ['20-12-16', '21-12-16', '22-12-16', '23-12-16', '27-12-16', '28-12-16', '29-12-16', '30-12-16', '02-01-17', '03-01-17', '04-01-17', '05-01-17', '06-01-17']}
d2 = {'Date': ['20-12-16', '21-12-16', '22-12-16', '23-12-16', '24-12-16', '25-12-16', '26-12-16', '27-12-16', '28-12-16', '29-12-16', '30-12-16', '31-12-16', '01-01-17', '02-01-17', '03-01-17', '04-01-17', '05-01-17', '06-01-17']}
df1 = pd.DataFrame(d1)
df2 = pd.DataFrame(d2)

---

print (df1.Date.dtype)
object

print (df2.Date.dtype)
object

df1['Date'] = pd.to_datetime(df1['Date'], format='%d-%m-%y')
df2['Date'] = pd.to_datetime(df2['Date'], format='%d-%m-%y')

Answer 2

Your mistake is from logic. You want to select the df2 date that are df1. So you should write

df2[df2.Date.isin(df1.Date)]

not the contrary of the boolean where comparison/inclusion in df1 is true

You could also obtain the same result with

set(b.Date)-(set(b.Date)-set(a.Date))

Which should then be used through:

pd.DataFrame(sorted((set(b.Date)-(set(b.Date)-set(a.Date)))), columns=["Date"] )   

While the sorting is not optimal and you may change it in pandas by better logic.

 df = pd.DataFrame(list((set(b.Date)-(set(b.Date)-set(a.Date)))), columns=["Date"] ) 
 df.Date = [date.date() for date in df.Date]

or df.Date.dt.date

(see How do I convert dates in a Pandas data frame to a 'date' data type?)