Match two column value in entire dataset [Specific Case]

user_13199674 0
Viewed 55

I have below-mentioned two dataframe in R, and I have tried various method but couldn't achieve the required output yet.

DF:

ID     Date                 city        code    uid
I-1    2020-01-01 10:12:15  New York     123    K-1
I-1    2020-01-01 10:12:15  Utha         103    K-1
I-2    2020-01-02 10:12:15  Washington   122    K-1
I-3    2020-02-01 10:12:15  Tokyo        123    K-2
I-3    2020-02-01 10:12:15  Osaka        193    K-2
I-4    2020-02-02 10:12:15  London       144    K-3
I-5    2020-02-04 10:12:15  Dubai        101    K-4
I-6    2019-11-01 10:12:15  Dubai        101    K-4
I-7    2019-11-01 10:12:15  London       144    K-3
I-8    2018-12-13 10:12:15  Tokyo        143    K-5
I-9    2019-05-17 10:12:15  Dubai        101    K-4
I-19   2020-03-11 10:12:15  Dubai        150    K-7

Dput:

structure(list(ID = c("I-1", "I-1", 
"I-2", "I-3", "I-3", "I-4", 
"I-5", "I-6", "I-7", "I-8", "I-9","I-19" 
), DATE = c("2020-01-01 11:49:40.842", "2020-01-01 09:35:33.607", 
"2020-01-02 06:14:58.731", "2020-02-01 16:51:27.190", "2020-02-01 05:35:46.952", 
"2020-02-02 05:48:49.443", "2020-02-04 10:00:41.616", "2019-11-01 09:10:46.536", 
"2019-11-01 11:54:05.655", "2018-12-13 14:24:31.617", "2019-05-17 14:24:31.617", "2020-03-11 14:24:31.617"), CITY = c("New York", 
"UTAH", "Washington", "Tokyo", 
"Osaka", "London", "Dubai", 
"Dubai", "London", "Tokyo", "Dubai", 
"Dubai"), CODE = c("221010", 
"411017", "638007", "583101", "560029", "643102", "363001", "452001", 
"560024", "509208"), UID = c("K-1", 
"K-1", "K-1", "K-2", "K-2", 
"K-3", "K-4", "K-4", "K-3", 
"K-5","K-4","K-7")), .Names = c("ID", "DATE", 
"CITY", "CODE", "UID"), row.names = c(NA, 
10L), class = "data.fram)

Using the above-mentioned two dataframe, I want to fetch records between 1st Jan 2020 to 29th Feb 2002 and compare those ID in entire database to check whether both city and code together match with other ID and categorize it further to check how many have the same uid and how many have different.

Where,

  • Match - combination of city and code match with other ID in database
  • Same_uid - classification of Match ids to identify how many ID have similar uid
  • different_uid - classification of Match ids to identify how many ID doesn't have similar uid
  • uid_count - count of similar uid of that particular ID in entire database
  • Match_with - This is basically to get those ID which has both city and code same against that particular ID

Note - I have more than 10M records in the dataframe.

Required Output

    ID      Date                 city        code  uid  Match   Same_uid   different_uid uid_count Match_with
    I-1     2020-01-01 10:12:15  New York    123   K-1  No      0          0              2        NA
    I-2     2020-01-02 10:12:15  Washington  122   K-1  No      0          0              2        NA
    I-3     2020-02-01 10:12:15  Tokyo       123   K-2  No      0          0              1        NA
    I-4     2020-02-02 10:12:15  London      144   K-3  Yes     1          0              2        I-7
    I-5     2020-02-04 10:12:15  Dubai       101   K-4  Yes     2          0              3        I-6, I-9
dplyr tidyverse dataframe r
1 Answers

This is just some headstart I still don't get all that you need maybe I got something wrong we can discuss further

library(tidyverse)
library(lubridate)
#> 
#> Attaching package: 'lubridate'
#> The following objects are masked from 'package:base':
#> 
#>     date, intersect, setdiff, union

df_example <- read_table("ID     Date                 city        code    uid
I-1    2020-01-01 10:12:15  New York     123    K-1
I-1    2020-01-01 10:12:15  Utha         103    K-1
I-2    2020-01-02 10:12:15  Washington   122    K-1
I-3    2020-02-01 10:12:15  Tokyo        123    K-2
I-3    2020-02-01 10:12:15  Osaka        193    K-2
I-4    2020-02-02 10:12:15  London       144    K-3
I-5    2020-02-04 10:12:15  Dubai        101    K-4
I-6    2019-11-01 10:12:15  Dubai        101    K-4
I-7    2019-11-01 10:12:15  London       144    K-3
I-8    2018-12-13 10:12:15  Tokyo        143    K-5
I-9    2019-05-17 10:12:15  Dubai        101    K-4
I-19   2020-03-11 10:12:15  Dubai        150    K-7")
#> Warning: Missing column names filled in: 'X3' [3]



df_example1 <- df_example %>%
  unite(Date:X3,col = "Date",sep = " ") %>% 
  mutate(Date = Date %>% ymd_hms() %>% as_date())


df_example1 %>%
  group_by(city,code) %>% 
  mutate(Match_with = toString(ID),
         different_uid = if_else(n_distinct(uid) - 1 == 0,0L,n()),
         uid_count = if_else(n_distinct(uid) - 1 == 0,n(),0L),
         same_uid = uid_count - 1)
#> # A tibble: 12 x 9
#> # Groups:   city, code [9]
#>    ID    Date       city   code uid   Match_with different_uid uid_count
#>    <chr> <date>     <chr> <dbl> <chr> <chr>              <int>     <int>
#>  1 I-1   2020-01-01 New ~   123 K-1   I-1                    0         1
#>  2 I-1   2020-01-01 Utha    103 K-1   I-1                    0         1
#>  3 I-2   2020-01-02 Wash~   122 K-1   I-2                    0         1
#>  4 I-3   2020-02-01 Tokyo   123 K-2   I-3                    0         1
#>  5 I-3   2020-02-01 Osaka   193 K-2   I-3                    0         1
#>  6 I-4   2020-02-02 Lond~   144 K-3   I-4, I-7               0         2
#>  7 I-5   2020-02-04 Dubai   101 K-4   I-5, I-6,~             0         3
#>  8 I-6   2019-11-01 Dubai   101 K-4   I-5, I-6,~             0         3
#>  9 I-7   2019-11-01 Lond~   144 K-3   I-4, I-7               0         2
#> 10 I-8   2018-12-13 Tokyo   143 K-5   I-8                    0         1
#> 11 I-9   2019-05-17 Dubai   101 K-4   I-5, I-6,~             0         3
#> 12 I-19  2020-03-11 Dubai   150 K-7   I-19                   0         1
#> # ... with 1 more variable: same_uid <dbl>

Created on 2020-08-05 by the reprex package (v0.3.0)

user_71999660
Related