Menu
I have two data.frames with multiple common columns (here: date, city, ctry, and (other_)number).
I would now like to merge them on the above columns but tolerate some level of difference:
threshold.numbers <- 3
threshold.date <- 5 # in days
If the difference between the date entries is > threshold.date (in days) or > threshold.numbers, I don't want the lines to be merged.
Similarly, if the entry in city is a substring of the other df's entry in the city column, I want the lines to be merged. [If anyone has a better idea to test for actual city names' similarity, I'd be happy to hear about it.] (And keep the first df's entries of date, city and country but both (other_)number columns and all other columns in the df.
Consider the following example:
df1 <- data.frame(date = c("2003-08-29", "1999-06-12", "2000-08-29", "1999-02-24", "2001-04-17",
"1999-06-30", "1999-03-16", "1999-07-16", "2001-08-29", "2002-07-30"),
city = c("Berlin", "Paris", "London", "Rome", "Bern",
"Copenhagen", "Warsaw", "Moscow", "Tunis", "Vienna"),
ctry = c("Germany", "France", "UK", "Italy", "Switzerland",
"Denmark", "Poland", "Russia", "Tunisia", "Austria"),
number = c(10, 20, 30, 40, 50, 60, 70, 80, 90, 100),
col = c("apple", "banana", "pear", "banana", "lemon", "cucumber", "apple", "peach", "cherry", "cherry"))
df2 <- data.frame(date = c("2003-08-29", "1999-06-12", "2000-08-29", "1999-02-24", "2001-04-17", # all identical to df1
"1999-06-29", "1999-03-14", "1999-07-17", # all 1-2 days different
"2000-01-29", "2002-07-01"), # all very different (> 2 weeks)
city = c("Berlin", "East-Paris", "near London", "Rome", # same or slight differences
"Zurich", # completely different
"Copenhagen", "Warsaw", "Moscow", "Tunis", "Vienna"), # same
ctry = c("Germany", "France", "UK", "Italy", "Switzerland", # all the same
"Denmark", "Poland", "Russia", "Tunisia", "Austria"),
other_number = c(13, 17, 3100, 45, 51, 61, 780, 85, 90, 101), # slightly different to very different
other_col = c("yellow", "green", "blue", "red", "purple", "orange", "blue", "red", "black", "beige"))
Now, I would like to merge the data.frames and receive a df where lines are merged if the above conditions are met.
(The first column is only for your convenience: behind the first digit, which indicates the original case, it shows whether the lines where merged (.) or whether the lines is from df1 (1) or df2 (2).
date city ctry number other_col other_number other_col2 #comment
1. 2003-08-29 Berlin Germany 10 apple 13 yellow # matched on date, city, number
2. 1999-06-12 Paris France 20 banana 17 green # matched on date, city similar, number - other_number == threshold.numbers
31 2000-08-29 London UK 30 pear <NA> <NA> # not matched: number - other_number > threshold.numbers
32 2000-08-29 near London UK <NA> <NA> 3100 blue #
41 1999-02-24 Rome Italy 40 banana <NA> <NA> # not matched: number - other_number > threshold.numbers
42 1999-02-24 Rome Italy <NA> <NA> 45 red #
51 2001-04-17 Bern Switzerland 50 lemon <NA> <NA> # not matched: cities different (dates okay, numbers okay)
52 2001-04-17 Zurich Switzerland <NA> <NA> 51 purple #
6. 1999-06-30 Copenhagen Denmark 60 cucumber 61 orange # matched: date difference < threshold.date (cities okay, dates okay)
71 1999-03-16 Warsaw Poland 70 apple <NA> <NA> # not matched: number - other_number > threshold.numbers (dates okay)
72 1999-03-14 Warsaw Poland <NA> <NA> 780 blue #
81 1999-07-16 Moscow Russia 80 peach <NA> <NA> # not matched: number - other_number > threshold.numbers (dates okay)
82 1999-07-17 Moscow Russia <NA> <NA> 85 red #
91 2001-08-29 Tunis Tunisia 90 cherry <NA> <NA> # not matched: date difference < threshold.date (cities okay, dates okay)
92 2000-01-29 Tunis Tunisia <NA> <NA> 90 black #
101 2002-07-30 Vienna Austria 100 cherry <NA> <NA> # not matched: date difference < threshold.date (cities okay, dates okay)
102 2002-07-01 Vienna Austria <NA> <NA> 101 beige #
I tried different implementations of merging them but cannot get the threshold implemented.
EDIT Apologies for unclear formulation - I would like to retain all rows and receive an indicator whether the row is matched, unmatched and from df1 or unmatched and from df2.
the pseudo-code is:
if there is a case where abs("date_df2" - "date_df1") <= threshold.date:
if "ctry_df2" == "ctry_df1":
if "city_df2" ~ "city_df1":
if abs("number_df2" - "number_df1") <= threshold.numbers:
merge and go to next row in df2
else:
add row to df1```
447
You can pass two DataFrame to be merged to the pandas. merge() method. This collects all common columns in both DataFrames and replaces each common column in both DataFrame with a single one.
It is possible to join the different columns is using concat() method. DataFrame: It is dataframe name. axis: 0 refers to the row axis and1 refers the column axis. join: Type of join.
I first turned the city names into character vectors, since (if I understood correctly) you want to include city names that are contained within df2.
df1$city<-as.character(df1$city)
df2$city<-as.character(df2$city)
Then merge them by country:
df = merge(df1, df2, by = ("ctry"))
> df
ctry date.x city.x number col date.y city.y other_number other_col
1 Austria 2002-07-30 Vienna 100 cherry 2002-07-01 Vienna 101 beige
2 Denmark 1999-06-30 Copenhagen 60 cucumber 1999-06-29 Copenhagen 61 orange
3 France 1999-06-12 Paris 20 banana 1999-06-12 East-Paris 17 green
4 Germany 2003-08-29 Berlin 10 apple 2003-08-29 Berlin 13 yellow
5 Italy 1999-02-24 Rome 40 banana 1999-02-24 Rome 45 red
6 Poland 1999-03-16 Warsaw 70 apple 1999-03-14 Warsaw 780 blue
7 Russia 1999-07-16 Moscow 80 peach 1999-07-17 Moscow 85 red
8 Switzerland 2001-04-17 Bern 50 lemon 2001-04-17 Zurich 51 purple
9 Tunisia 2001-08-29 Tunis 90 cherry 2000-01-29 Tunis 90 black
10 UK 2000-08-29 London 30 pear 2000-08-29 near London 3100 blue
The library stringr will allow you to see if city.x is within city.y here (see last column):
library(stringr)
df$city_keep<-str_detect(df$city.y,df$city.x) # this returns logical vector if city.x is contained in city.y (works one way)
> df
ctry date.x city.x number col date.y city.y other_number other_col city_keep
1 Austria 2002-07-30 Vienna 100 cherry 2002-07-01 Vienna 101 beige TRUE
2 Denmark 1999-06-30 Copenhagen 60 cucumber 1999-06-29 Copenhagen 61 orange TRUE
3 France 1999-06-12 Paris 20 banana 1999-06-12 East-Paris 17 green TRUE
4 Germany 2003-08-29 Berlin 10 apple 2003-08-29 Berlin 13 yellow TRUE
5 Italy 1999-02-24 Rome 40 banana 1999-02-24 Rome 45 red TRUE
6 Poland 1999-03-16 Warsaw 70 apple 1999-03-14 Warsaw 780 blue TRUE
7 Russia 1999-07-16 Moscow 80 peach 1999-07-17 Moscow 85 red TRUE
8 Switzerland 2001-04-17 Bern 50 lemon 2001-04-17 Zurich 51 purple FALSE
9 Tunisia 2001-08-29 Tunis 90 cherry 2000-01-29 Tunis 90 black TRUE
10 UK 2000-08-29 London 30 pear 2000-08-29 near London 3100 blue TRUE
Then you can get the difference in days between dates:
df$dayDiff<-abs(as.POSIXlt(df$date.x)$yday - as.POSIXlt(df$date.y)$yday)
and the difference in numbers:
df$numDiff<-abs(df$number - df$other_number)
Here was what the resulting dataframe looks like:
> df
ctry date.x city.x number col date.y city.y other_number other_col city_keep dayDiff numDiff
1 Austria 2002-07-30 Vienna 100 cherry 2002-07-01 Vienna 101 beige TRUE 29 1
2 Denmark 1999-06-30 Copenhagen 60 cucumber 1999-06-29 Copenhagen 61 orange TRUE 1 1
3 France 1999-06-12 Paris 20 banana 1999-06-12 East-Paris 17 green TRUE 0 3
4 Germany 2003-08-29 Berlin 10 apple 2003-08-29 Berlin 13 yellow TRUE 0 3
5 Italy 1999-02-24 Rome 40 banana 1999-02-24 Rome 45 red TRUE 0 5
6 Poland 1999-03-16 Warsaw 70 apple 1999-03-14 Warsaw 780 blue TRUE 2 710
7 Russia 1999-07-16 Moscow 80 peach 1999-07-17 Moscow 85 red TRUE 1 5
8 Switzerland 2001-04-17 Bern 50 lemon 2001-04-17 Zurich 51 purple FALSE 0 1
9 Tunisia 2001-08-29 Tunis 90 cherry 2000-01-29 Tunis 90 black TRUE 212 0
10 UK 2000-08-29 London 30 pear 2000-08-29 near London 3100 blue TRUE 0 3070
But we want to drop things where city.x was not found within city.y, where the day difference is greater than 5 or the number difference is greater than 3:
df<-df[df$dayDiff<=5 & df$numDiff<=3 & df$city_keep==TRUE,]
> df
ctry date.x city.x number col date.y city.y other_number other_col city_keep dayDiff numDiff
2 Denmark 1999-06-30 Copenhagen 60 cucumber 1999-06-29 Copenhagen 61 orange TRUE 1 1
3 France 1999-06-12 Paris 20 banana 1999-06-12 East-Paris 17 green TRUE 0 3
4 Germany 2003-08-29 Berlin 10 apple 2003-08-29 Berlin 13 yellow TRUE 0 3
What is left are the three rows that you had above (which contained dots in column 1).
Now we can drop the three columns we created, and the date and city from df2:
> df<-subset(df, select=-c(city.y, date.y, city_keep, dayDiff, numDiff))
> df
ctry date.x city.x number col other_number other_col
2 Denmark 1999-06-30 Copenhagen 60 cucumber 61 orange
3 France 1999-06-12 Paris 20 banana 17 green
4 Germany 2003-08-29 Berlin 10 apple 13 yellow
Step 1: Merge the data based on "city" and "ctry":
df = merge(df1, df2, by = c("city", "ctry"))
Step 2: Remove rows if the difference between the date entries is > threshold.date (in days):
date_diff = abs(as.numeric(difftime(strptime(df$date.x, format = "%Y-%m-%d"),
strptime(df$date.y, format = "%Y-%m-%d"), units="days")))
index_remove = date_diff > threshold.date
df = df[-index_remove,]
Step 3: Remove rows if the difference between the numbers is > threshhold.number:
number_diff = abs(df$number - df$other_number)
index_remove = number_diff > threshold.numbers
df = df[-index_remove,]
The data should be merged before applying conditions, in case the rows are not matching.
35
An option using data.table (explanations inline):
library(data.table)
setDT(df1)
setDT(df2)
#dupe columns and create ranges for non-equi joins
df1[, c("n", "ln", "un", "d", "ld", "ud") := .(
number, number - threshold.numbers, number + threshold.numbers,
date, date - threshold.date, date + threshold.date)]
df2[, c("n", "ln", "un", "d", "ld", "ud") := .(
other_number, other_number - threshold.numbers, other_number + threshold.numbers,
date, date - threshold.date, date + threshold.date)]
#perform non-equi join using ctry, num, dates in both ways
res <- rbindlist(list(
df1[df2, on=.(ctry, n>=ln, n<=un, d>=ld, d<=ud),
.(date1=x.date, date2=i.date, city1=x.city, city2=i.city, ctry1=x.ctry, ctry2=i.ctry, number, col, other_number, other_col)],
df2[df1, on=.(ctry, n>=ln, n<=un, d>=ld, d<=ud),
.(date1=i.date, date2=x.date, city1=i.city, city2=x.city, ctry1=i.ctry, ctry2=x.ctry, number, col, other_number, other_col)]),
use.names=TRUE, fill=TRUE)
#determine if cities are substrings of one and another
res[, city_match := {
i <- mapply(grepl, city1, city2) | mapply(grepl, city2, city1)
replace(i, is.na(i), TRUE)
}]
#just like SQL coalesce (there is a version in dev in rdatatable github)
coalesce <- function(...) Reduce(function(x, y) fifelse(!is.na(y), y, x), list(...))
#for rows that are matching or no matches to be found
ans1 <- unique(res[(city_match), .(date=coalesce(date1, date2),
city=coalesce(city1, city2),
ctry=coalesce(ctry1, ctry2),
number, col, other_number, other_col)])
#for rows that are close in terms of dates and numbers but are diff cities
ans2 <- res[(!city_match), .(date=c(.BY$date1, .BY$date2),
city=c(.BY$city1, .BY$city2),
ctry=c(.BY$ctry1, .BY$ctry2),
number=c(.BY$number, NA),
col=c(.BY$col, NA),
other_number=c(NA, .BY$other_number),
other_col=c(NA, .BY$other_col)),
names(res)][, seq_along(names(res)) := NULL]
#final desired output
setorder(rbindlist(list(ans1, ans2)), date, city, number, na.last=TRUE)[]
output:
date city ctry number col other_number other_col
1: 1999-02-24 Rome Italy 40 banana NA <NA>
2: 1999-02-24 Rome Italy NA <NA> 45 red
3: 1999-03-14 Warsaw Poland NA <NA> 780 blue
4: 1999-03-16 Warsaw Poland 70 apple NA <NA>
5: 1999-06-12 East-Paris France 20 banana 17 green
6: 1999-06-29 Copenhagen Denmark 60 cucumber 61 orange
7: 1999-07-16 Moscow Russia 80 peach NA <NA>
8: 1999-07-17 Moscow Russia NA <NA> 85 red
9: 2000-01-29 Tunis Tunisia NA <NA> 90 black
10: 2000-08-29 London UK 30 pear NA <NA>
11: 2000-08-29 near London UK NA <NA> 3100 blue
12: 2001-04-17 Bern Switzerland 50 lemon NA <NA>
13: 2001-04-17 Zurich Switzerland NA <NA> 51 purple
14: 2001-08-29 Tunis Tunisia 90 cherry NA <NA>
15: 2002-07-01 Vienna Austria NA <NA> 101 beige
16: 2002-07-30 Vienna Austria 100 cherry NA <NA>
17: 2003-08-29 Berlin Germany 10 apple 13 yellow
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
