Using join command in Miller

[san-r]

I'm trying to create a reference for join command with Miller (version 6 beta) equivalent to what is available in dplyr package in R, which can be seen in the top-right corner of second page at https://www.rstudio.com/wp-content/uploads/2015/02/data-wrangling-cheatsheet.pdf in the "Mutating joins" and "Filtering joins" sub-sections of "Combine Data Sets" section.

Suppose we have two two tables as under:

person.csv

id_p,gender
456,F
789,F
123,M

weight.csv

id_w,weight
123,200
999,160
789,155

Now the join commands:

inner join

R command:
inner_join(person, weight, by=c("id_p" = "id_w"))

id_p	gender	weight
789	F	155
123	M	200

Equivalent Miller command:
mlr --c2p join -j id -l id_p -r id_w -f person.csv weight.csv
R sorts the result in order of left file, whereas Miller sorts in order of right file.

anti-join filter on person table

R command:
anti_join(person, weight, by=c("id_p" = "id_w"))

id_p	gender
456	F

Equivalent Miller command:
mlr --c2p join --np --ul -j id -l id_p -r id_w -f person.csv weight.csv

anti-join filter on weight table

R command:
anti_join(weight, person, by=c("id_w" = "id_p"))

id_w	weight
999	160

Equivalent Miller command:
mlr --c2p join --np --ur -j id -l id_p -r id_w -f person.csv weight.csv

left-join

R command:
left_join(person, weight, by=c("id_p" = "id_w"))

id_p	gender	weight
456	F	NA
789	F	155
123	M	200

Equivalent Miller command:
?

right-join

R command:
right_join(person, weight, by=c("id_p" = "id_w"))

id_p	gender	weight
789	F	155
123	M	200
999	NA	160

Equivalent Miller command:
?

full-join

R command:
full_join(person, weight, by=c("id_p" = "id_w"))

id_p	gender	weight
456	F	NA
789	F	155
123	M	200
999	NA	160

Equivalent Miller command:
?

semi-join filter on person table

R command:
semi_join(person, weight, by=c("id_p" = "id_w"))

id_p	gender
789	F
123	M

Equivalent Miller command:
?

semi-join filter on weight table

R command:
semi_join(weight, person, by=c("id_w" = "id_p"))

id_w	weight
123	200
789	155

Equivalent Miller command:
?

Can someone please post the equivalent Miller commands for left-join, right-join, full-join and semi-join commands. The result should output the same rows even if they are in different sort order.

[aborruso]

Hi @san-r a rapid but probably not perfect first reply

left_join

mlr --c2p --barred join --ul -j id_p -l id_p -r id_w -f person.csv then unsparsify weight.csv

+------+--------+--------+
| id_p | gender | weight |
+------+--------+--------+
| 123  | M      | 200    |
| 789  | F      | 155    |
| 456  | F      | -      |
+------+--------+--------+

right_join

mlr --c2p --barred join --ur -j id_w -l id_p -r id_w -f person.csv then unsparsify weight.csv

+------+--------+--------+
| id_w | gender | weight |
+------+--------+--------+
| 123  | M      | 200    |
| 999  | -      | 160    |
| 789  | F      | 155    |
+------+--------+--------+

full_join

mlr --c2p --barred join --ul --ur -j id_p -l id_p -r id_w -f person.csv then unsparsify weight.csv

+------+--------+--------+------+
| id_p | gender | weight | id_w |
+------+--------+--------+------+
| 123  | M      | 200    | -    |
| -    | -      | 160    | 999  |
| 789  | F      | 155    | -    |
| 456  | F      | -      | -    |
+------+--------+--------+------+

semi_join

The first output table of

mlr --c2p --barred join --ul -j id_p -l id_p -r id_w -f person.csv weight.csv

+------+--------+--------+
| id_p | gender | weight |
+------+--------+--------+
| 123  | M      | 200    |
| 789  | F      | 155    |
+------+--------+--------+

+------+--------+
| id_p | gender |
+------+--------+
| 456  | F      |
+------+--------+

anti_join

mlr --c2p --barred join --np --ul -j id_p -l id_p -r id_w -f person.csv then unsparsify weight.csv

+------+--------+
| id_p | gender |
+------+--------+
| 456  | F      |
+------+--------+

[san-r]

Thanks for quick reply. Here is what I learnt.

left join

mlr --c2p --barred join --ul -j id_p -l id_p -r id_w -f person.csv then unsparsify weight.csv
Trick:

• -j parameter must be same as join column of LEFT file. Using anything else like "-j id" will make additional column which is not what we want.
• You also need to use "then unsparsify" after left file specification; or you will get multiple result tables.

right join

mlr --c2p --barred join --ur -j id_w -l id_p -r id_w -f person.csv then unsparsify weight.csv
Trick: Similar logic as left-join. -j parameter must be same as join column of RIGHT file. "then unsparsify" must also be used after left file specification.

full join

Current method gives an extra column, which is not what we want.

semi join

Current method gives an extra table, which is not what we want.

Interim conclusion:

Miller currently supports these type of joins: inner, left, right, anti
Miller needs to add support for these type of joins: full, semi

Hope to see intuitive support for all join methods soon. Also, not having to use workarounds like "unsparsify" for basic join commands will be nice. Thanks.

[masgo]

Maybe you should link this question in the documentation. Many people have some kind of SQL background and think in terms like left/right/full join, and this is quite useful.