Join or merge organization

tidyverse
, ,
1

#Hello friends,

#Please, about this simple DF:

DF1 <- data.frame(id = c(1,1,1,1,1,1,1,1,1,1, "", "", "", "", "", 
                         2,2,2,2,2),
                  group = c(1,1,1,2,2,3,3,3,4,4,"", "", "", "", "", 
                        5,5,5,7,8),
                  product = c(11, 22, 33, 44,55,66,77,88,99,1010, "", "", "", "", "", 
                        1111, 1212, 1313, 1414, 1515),
                  frecuency = c(2,2,2,3,3,3,4,4,4,5,"","","","","", 6,7,7,3,2))

DF1

#How it is possible to insert this continuous data by ID like?

DF2 <- data.frame(id = c(1,1,1,1,1,1,1,1,1,1,2,2,2,2,2,2,2,2,2,2),
                  k = c(1,1,1,1,1,1,1,1,1,1,2,2,2,2,2,2,2,2,2,2),
                  group = c(1111,1212,1313,1414,1515,1616,1717, 1818, 1919,2020, 2121, 2222,2323, 2424, 2525, 2626, 2727, 2828, 2929, 3030),
                  frecuency = c(2,2,2,2,2,2,4,4,4,5,2,2,3,4,5,4,4,4,3,2))

DF2

#Obtaining this output or like this? by descending frequency

id group	product	frecuency
1	  4	1010	5
1	  3	77	  4
1	  3	88	  4
1	  4	99	  4
1	  2	44	  3
1	  2	55	  3
1	  3	66	  3
1	  1	11	  2
1	  1	22	  2
1	  1	33	  2
1	  1	2020	5
1	  1	1717	4
1	  1	1818	4
1	  1	1919	4
1	  1	1111	2
1	  1	1212	2
1	  1	1313	2
1	  1	1414	2
1	  1	1515	2
1	  1	1616	2
2	  5	1212	7
2	  5	1313	7
2	  5	1111	6
2	  7	1414	3
2	  8	1515	2
2	  2	2525	5
2	  2	2424	4
2	  2	2626	4
2	  2	2727	4
2	  2	2828	4
2	  2	2929	3
2	  2	2323	3
2	  2	2121	2
2	  2	2222	2
2	  2	3030	2

#Equal or similar merge or join, priorizing the group and frequency

#Thank you very much!!

Here is a spreadsheet maybe can help. SPREADSHEET

Equal or similar output
Thanks!!

2

maybe ...

DF1 <- data.frame(id = c(1,1,1,1,1,1,1,1,1,1, "", "", "", "", "", 
                         2,2,2,2,2),
                  group = c(1,1,1,2,2,3,3,3,4,4,"", "", "", "", "", 
                            5,5,5,7,8),
                  product = c(11, 22, 33, 44,55,66,77,88,99,1010, "", "", "", "", "", 
                              1111, 1212, 1313, 1414, 1515),
                  frecuency = c(2,2,2,3,3,3,4,4,4,5,"","","","","", 6,7,7,3,2))

DF2 <- data.frame(id = c(1,1,1,1,1,1,1,1,1,1,2,2,2,2,2,2,2,2,2,2),
                  k = c(1,1,1,1,1,1,1,1,1,1,2,2,2,2,2,2,2,2,2,2),
                  group = c(1111,1212,1313,1414,1515,1616,1717, 1818, 1919,2020, 2121, 2222,2323, 2424, 2525, 2626, 2727, 2828, 2929, 3030),
                  frecuency = c(2,2,2,2,2,2,4,4,4,5,2,2,3,4,5,4,4,4,3,2))

library(tidyverse)

bind_rows(
     DF1 |> mutate_all(as.numeric),
     DF2 |> set_names(names(DF1))) |> 
  na.omit() |> 
arrange(id,
       desc(frecuency))
3

Thanks Nirgrahamuk

but It appears this message

Error in bind_rows():
! Can't combine ..1$RUT and ..2$RUT .
Run rlang::last_error() to see where the error occurred.
Warning message:
Problem while computing RUT = .Primitive("as.double")(RUT).
:information_source: NAs introduced by coercion

(About RUT it´s the id column and in the real data it´s an observation as 12121212-4 for that it´s character)

4

can you provide a reprex ?

5

I don´t know reprex......

6
1 Like
7

The real context it´s:

id = client
group = kmeans the group
product = recomendation

DF1 it´s the recomendation
DF2 it´s the client kmeans, the group of the client.

I want to recommend to a client 1-1 the recomendation, that is 11 and 22, and after, or in the line 3rd, the popular of his kmedias 1, in this case the product 55.

  DF1 <- data.frame(id = c("1-1","1-1","1-1","1-2"),
                    group = c(1,1,2,2),
                    product = c(11, 22, 33, 44))
  
  DF1


  DF2 <- data.frame(id = c("1-1","1-2"),
                    group = c(1,2),
                    product = c(55, 66))
  
  DF2

OUTPUT example:
id group product CORRESPOND TO
1 1-1 1 11 Dataframe 1
2 1-1 1 22 Dataframe 1
3 1-1 1 55 Dataframe 2
4 1-2 2 44 Dataframe 1

Now id it´s like my real data:

  DF1 <- data.frame(id = c("1-1","1-1","1-1","1-1","1-1","1-1","1-1","1-1","1-1","1-1", "", "", "", "", "", 
                           "2-2","2-2","2-2","2-2","2-2"),
                    group = c(1,1,1,2,2,3,3,3,4,4,"", "", "", "", "", 
                              5,5,5,7,8),
                    product = c(11, 22, 33, 44,55,66,77,88,99,1010, "", "", "", "", "", 
                                1111, 1212, 1313, 1414, 1515),
                    frecuency = c(2,2,2,3,3,3,4,4,4,5,"","","","","", 6,7,7,3,2))

  DF2 <- data.frame(id = c("1-1","1-1","1-1","1-1","1-1","1-1","1-1","1-1","1-1","1-1","2-2","2-2","2-2","2-2","2-2","2-2","2-2","2-2","2-2","2-2"),
                    k = c(1,1,1,1,1,1,1,1,1,1,2,2,2,2,2,2,2,2,2,2),
                    group = c(1111,1212,1313,1414,1515,1616,1717, 1818, 1919,2020, 2121, 2222,2323, 2424, 2525, 2626, 2727, 2828, 2929, 3030),
                    frecuency = c(2,2,2,2,2,2,4,4,4,5,2,2,3,4,5,4,4,4,3,2))

Thank you Nirgramhamuk!

8

in the new short DF1 and DF2 , the major differences that all the data types agree, so there is no need to mutate the types. there is no frecuency column so dropping that

bind_rows(
    DF1 ,
    DF2 |> set_names(names(DF1)) |> 
    na.omit() )

and your new DF1/DF2 works as before.

you then provide yet another pair of DF1/DF2, this time all DF1 is character, while DF2 has id as character and the rest numeric...
in this case we need the numeric mutates back, we just dont change id

bind_rows(
  DF1 |> mutate(across(-id,as.numeric)),
  DF2 |> set_names(names(DF1))) |> 
  na.omit() |> 
  arrange(id,
          desc(frecuency))
1 Like
9

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.