Reputation: 47
I have a table like below, would like to crate suggestions based on row value in R studio.
This is what I have :
id | A | B | C | D | E | F | G |
---|---|---|---|---|---|---|---|
A | 0.98 | 0.48 | 0.21 | 0.97 | 0.47 | 0.20 | 0.19 |
B | 0.22 | 0.31 | 0.41 | 0.11 | 0.42 | 0.32 | 0.23 |
C | 0.70 | 0.81 | 0.61 | 0.21 | 0.82 | 0.71 | 0.62 |
I would like to have names of the column for top1 top2.. top7 .
id | A | B | C | D | E | F | G | top1name | top2name | top3name | top4name | top5name | top6name | top7name |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | 0.98 | 0.48 | 0.21 | 0.97 | 0.47 | 0.20 | 0.19 | A | D | B | E | C | F | G |
B | 0.22 | 0.31 | 0.41 | 0.11 | 0.42 | 0.32 | 0.23 | E | C | F | B | G | A | D |
C | 0.70 | 0.81 | 0.61 | 0.21 | 0.82 | 0.71 | 0.62 | E | B | F | A | G | C | D |
Upvotes: 0
Views: 518
Reputation: 30494
In base R you can try this, with df
as your data.frame. Use apply
to row-wise go through your data. You can sort
each row and include the corresponding column names
. The setNames
allows you to rename these new resultant columns.
cbind(
df,
setNames(
data.frame(t(apply(df[-1], 1, function(x) names(sort(x, decreasing = T))))),
paste0("top", 1:(ncol(df) - 1), "name")
)
)
Upvotes: 0
Reputation: 1430
There's a better solution that can be solved by using across - someone will post that answer soon.
library(tidyverse)
df <- tibble::tribble(
~id, ~A, ~B, ~C, ~D, ~E, ~F, ~G,
"A", 0.98, 0.48, 0.21, 0.97, 0.47, 0.2, 0.19,
"B", 0.22, 0.31, 0.41, 0.11, 0.42, 0.32, 0.23,
"C", 0.7, 0.81, 0.61, 0.21, 0.82, 0.71, 0.62
)
df %>% left_join (df %>%
pivot_longer(cols = A:G,
names_to = "letters",
values_to = "values") %>%
group_by(id) %>%
mutate(rank = dense_rank(desc(values))) %>%
arrange(id, rank) %>%
mutate(rank = glue::glue("top{rank}name") %>% as.character()) %>%
select(id, rank, letters) %>%
pivot_wider(names_from = "rank", values_from = "letters")
)
#> Joining, by = "id"
#> # A tibble: 3 x 15
#> id A B C D E F G top1name top2name top3name
#> <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <chr> <chr> <chr>
#> 1 A 0.98 0.48 0.21 0.97 0.47 0.2 0.19 A D B
#> 2 B 0.22 0.31 0.41 0.11 0.42 0.32 0.23 E C F
#> 3 C 0.7 0.81 0.61 0.21 0.82 0.71 0.62 E B F
#> # ... with 4 more variables: top4name <chr>, top5name <chr>, top6name <chr>,
#> # top7name <chr>
Created on 2021-08-16 by the reprex package (v1.0.0)
Upvotes: 0