Transfer unique values per column into rows - a maximum of 10 values per row

user_14825243 0
Viewed 66

my problem in brief: For this I would simply use the standard mtcars data frame.

head(mtcars)
                   mpg cyl disp  hp drat    wt  qsec vs am gear carb
Mazda RX4         21.0   6  160 110 3.90 2.620 16.46  0  1    4    4
Mazda RX4 Wag     21.0   6  160 110 3.90 2.875 17.02  0  1    4    4
Datsun 710        22.8   4  108  93 3.85 2.320 18.61  1  1    4    1
Hornet 4 Drive    21.4   6  258 110 3.08 3.215 19.44  1  0    3    1
Hornet Sportabout 18.7   8  360 175 3.15 3.440 17.02  0  0    3    2
Valiant           18.1   6  225 105 2.76 3.460 20.22  1  0    3    1

Now I want to transfer all properties/columns into rows and transfer all of the unique values into (maximum) 10 value columns. If there are more than 10 unique values, these should be included in a further line.

The expected dataframe looks like:

Prop    Value1  Value2  Value3  Value4  Value5  Value6  Value7  Value8  Value9  Value10
mpg     21.0    22.8    21.4    18.7    18.1    14.3    24.4    19.2    17.8    16.4
mpg     17.3    15.2    10.4    14.7    32.4    30.4    33.9    21.5    15.5    13.3 
mpg     27.3    26.0    15.8    19.7    15.0    NA      NA      NA      NA      NA
cyl     ...
...

thank you very much for your help.

dplyr transpose r
3 Answers

How about this method using a for loop

df = matrix(ncol = 11)[-1,]
for(i in 1:ncol(mtcars)){
 a = unique(mtcars[,i])
 b = length(a)%%10
 if(b!=0){
 c = matrix(c(unique(mtcars[,i]), rep(NA,10- b)), ncol=10, byrow = T)
 }
 if(b==0){
   c = matrix(unique(mtcars[,i]), ncol=10, byrow = T)
 }
 c= cbind(rep(colnames(mtcars)[i], nrow(c)),c)
 df=  rbind(df,c)
}
df=as.data.frame(df)

the output looks like this

     V1    V2    V3    V4    V5    V6    V7    V8    V9   V10   V11
1   mpg    21  22.8  21.4  18.7  18.1  14.3  24.4  19.2  17.8  16.4
2   mpg  17.3  15.2  10.4  14.7  32.4  30.4  33.9  21.5  15.5  13.3
3   mpg  27.3    26  15.8  19.7    15  <NA>  <NA>  <NA>  <NA>  <NA>
4   cyl     6     4     8  <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA>
5  disp   160   108   258   360   225 146.7 140.8 167.6 275.8   472
6  disp   460   440  78.7  75.7  71.1 120.1   318   304   350   400
7  disp    79 120.3  95.1   351   145   301   121  <NA>  <NA>  <NA>
8    hp   110    93   175   105   245    62    95   123   180   205
9    hp   215   230    66    52    65    97   150    91   113   264
10   hp   335   109  <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA>
11 drat   3.9  3.85  3.08  3.15  2.76  3.21  3.69  3.92  3.07  2.93
12 drat     3  3.23  4.08  4.93  4.22   3.7  3.73  4.43  3.77  3.62
13 drat  3.54  4.11  <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA>
14   wt  2.62 2.875  2.32 3.215  3.44  3.46  3.57  3.19  3.15  4.07
15   wt  3.73  3.78  5.25 5.424 5.345   2.2 1.615 1.835 2.465  3.52
16   wt 3.435  3.84 3.845 1.935  2.14 1.513  3.17  2.77  2.78  <NA>
17 qsec 16.46 17.02 18.61 19.44 20.22 15.84    20  22.9  18.3  18.9
18 qsec  17.4  17.6    18 17.98 17.82 17.42 19.47 18.52  19.9 20.01
19 qsec 16.87  17.3 15.41 17.05  16.7  16.9  14.5  15.5  14.6  18.6
20   vs     0     1  <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA>
21   am     1     0  <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA>
22 gear     4     3     5  <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  <NA>
23 carb     4     1     2     3     6     8  <NA>  <NA>  <NA>  <NA>
user_114922970 
library(tidyverse)
n <- as.integer(max(map_int(mtcars, ~length(unique(.x))), na.rm = T))
n
#> [1] 30

map_dfc(mtcars, ~c(unique(.x), rep(NA, n - length(unique(.x))))) %>%
  mutate(val = paste0('value', 1 + (row_number() -1) %% 10),
         row_seq = 1 + (row_number() -1) %/% 10) %>%
  pivot_longer(!c(val, row_seq), values_drop_na = T) %>%
  pivot_wider(id_cols = c(name, row_seq), names_from = val, values_from = value) %>%
  arrange(name, row_seq)
#> # A tibble: 23 x 12
#>    name  row_seq value1 value2 value3 value4 value5 value6 value7 value8 value9
#>    <chr>   <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>
#>  1 am          1   1      0     NA     NA     NA     NA     NA     NA     NA   
#>  2 carb        1   4      1      2      3      6      8     NA     NA     NA   
#>  3 cyl         1   6      4      8     NA     NA     NA     NA     NA     NA   
#>  4 disp        1 160    108    258    360    225    147.   141.   168.   276.  
#>  5 disp        2 460    440     78.7   75.7   71.1  120.   318    304    350   
#>  6 disp        3  79    120.    95.1  351    145    301    121     NA     NA   
#>  7 drat        1   3.9    3.85   3.08   3.15   2.76   3.21   3.69   3.92   3.07
#>  8 drat        2   3      3.23   4.08   4.93   4.22   3.7    3.73   4.43   3.77
#>  9 drat        3   3.54   4.11  NA     NA     NA     NA     NA     NA     NA   
#> 10 gear        1   4      3      5     NA     NA     NA     NA     NA     NA   
#> # ... with 13 more rows, and 1 more variable: value10 <dbl>

Created on 2021-05-20 by the reprex package (v2.0.0)

user_28848590

You could do it like this:

library(tidyverse)

N_COLS <- 10

imap_dfr(mtcars,
     ~enframe(.x, name = NULL, value = "value") %>%
       distinct() %>%
       #arrange(value) %>%
       mutate(Prop = .y,
              colid = rep(seq(N_COLS), length.out = n()),
              rowid = cumsum(colid - lag(colid, default = 0) < 0)) ) %>%
  pivot_wider(values_from = value, names_from = colid, names_prefix = "Value") %>%
  select(-rowid) 

## A tibble: 23 x 11
#Prop  Value1 Value2 Value3 Value4 Value5 Value6 Value7 Value8 Value9 Value10
#<chr>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>   <dbl>
#1 mpg     21     22.8   21.4   18.7   18.1   14.3   24.4   19.2   17.8        16.4
#2 mpg     17.3   15.2   10.4   14.7   32.4   30.4   33.9   21.5   15.5    13.3
#3 mpg     27.3   26     15.8   19.7   15     NA     NA     NA     NA      NA  
#4 cyl      6      4      8     NA     NA     NA     NA     NA     NA      NA  
#5 disp   160    108    258    360    225    147.   141.   168.   276.    472  
#6 disp   460    440     78.7   75.7   71.1  120.   318    304    350     400  
#7 disp    79    120.    95.1  351    145    301    121     NA     NA      NA  
#8 hp     110     93    175    105    245     62     95    123    180     205  
#9 hp     215    230     66     52     65     97    150     91    113     264  
#10 hp     335    109     NA     NA     NA     NA     NA     NA     NA      NA  
## ... with 13 more rows
user_61620110
Related