0

I have two dataframes df1 & df2 listed below

ID  Age Weight
1   30  62
2   40  80
3   28  77
4   35  70

ID  Age Weight
1   30  62
2   40  80
3   28  97
4   35  87
5   25  65
6   33  70

i want to compare Weight variable in df1 with df2 using ID field and i need the difference showing below

ID  Weight_df1  Weight_df2
3     77             97
4     70             87
user96564
  • 1,578
  • 5
  • 24
  • 42
Raju
  • 11
  • 1
  • It has been answered before for R: https://stackoverflow.com/questions/28702960/find-complement-of-a-data-frame-anti-join – Sotos Nov 14 '19 at 14:25
  • 3
    Does this answer your question? [Compare two data.frames to find the rows in data.frame 1 that are not present in data.frame 2](https://stackoverflow.com/questions/3171426/compare-two-data-frames-to-find-the-rows-in-data-frame-1-that-are-not-present-in) – Clemsang Nov 14 '19 at 14:25

2 Answers2

2

Pandas version, use merge() with query():

(df1[['ID','Weight']].merge(df2[['ID','Weight']],on='ID',suffixes=('_df1','_df2')) 
                             .query('Weight_df1!=Weight_df2'))

   ID  Weight_df1  Weight_df2
2   3          77          97
3   4          70          87
anky
  • 74,114
  • 11
  • 41
  • 70
0

Here is a tidyverse approach in R that will do it all.

library(dplyr)
library(purrr)
library(tidyr)

map_dfr(list(df1, df2), I, .id = "df") %>%
  group_by(ID, Age, Weight) %>%
  filter(n() == 1) %>%
  group_by(ID) %>%
  filter(n() > 1) %>%
  pivot_wider(id_cols = "ID", values_from = "Weight", names_from = "df", names_prefix = "Weight_df")

# A tibble: 2 x 3
# Groups:   ID [2]
     ID Weight_df1 Weight_df2
  <dbl>      <dbl>      <dbl>
1     3         77         97
2     4         70         87

data

library(tibble)

df1 <- tribble(~ID,  ~Age, ~Weight,
               1,   30,  62,
               2,   40,  80,
               3,   28,  77,
               4,   35,  70)

df2 <- tribble(~ID,  ~Age, ~Weight,
               1,   30,  62,
               2,   40,  80,
               3,   28,  97,
               4,   35,  87,
               5,   25,  65,
               6,   33,  70)