Transpose an list

[Dan1el]

Hey there.

I have the following data list (or df) in an excel sheet:

a 1 b 2 c 3 d 4 ...

And I want to get something like that:

a 1
b 2
c 3
d 4
...

Is there a way to group transpose two or more?

[technocrat]

your_new_data_frame <- data.frame(your_var_name = your_data)

[M_AcostaCH]

Hi @Dan1el , if I understand well, check this link:

http://home.cc.umanitoba.ca/~psgendb/birchhomedir/R/x86_64-redhat-linux-gnu-library/3.4/purrr/html/transpose.html

x <- list("a" = 2.5, "b" = TRUE, "c" = 1:3)

x
#> $a
#> [1] 2.5
#> 
#> $b
#> [1] TRUE
#> 
#> $c
#> [1] 1 2 3

t(x)
#>      a   b    c        
#> [1,] 2.5 TRUE integer,3
Created on 2023-01-11 by the reprex package (v2.0.1)

[AlexisW]

If I understand well, you have an Excel file containing nothing except these "a 1 b 2 ...", in the first row, one value per cell?

i.e. the cell A1 is "a", the cell A2 is "1" and so on? In that case, we'll need to torture the data a bit (it's not the way R typically uses data), but it's doable.

First we load the data:

horizontal <- readxl::read_excel("horizontal_list.xlsx",
                   col_names = FALSE)
#> New names:
#> • `` -> `...1`
#> • `` -> `...2`
#> • `` -> `...3`
#> • `` -> `...4`
#> • `` -> `...5`
#> • `` -> `...6`
#> • `` -> `...7`
#> • `` -> `...8`

horizontal
#> # A tibble: 1 × 8
#>   ...1   ...2 ...3   ...4 ...5   ...6 ...7   ...8
#>   <chr> <dbl> <chr> <dbl> <chr> <dbl> <chr> <dbl>
#> 1 a         1 b         2 c         3 d         4

Note that readxl does not like columns with no names, so it's creating arbitrary names. We will ignore them here.

The dataframe structure doesn't make any sense here, so we'll convert to list instead:

horizontal <- as.list(horizontal)
head(horizontal, n = 2)
#> $...1
#> [1] "a"
#> 
#> $...2
#> [1] 1

One way to extract the data is to say that cell A1, A3, A5, ... have names, whereas A2, A4, A6, ... have values. So we need to find the odd columns and the even columns:

n <- length(horizontal)

even_cols <- 2*(1:(n/2))
odd_cols <- 2*(1:(n/2))-1

names <- horizontal[odd_cols]
values <- horizontal[even_cols]

and finally we can create a new data frame with the correct values. Because the names and values are lists which have names I don't care about, I use unlist() to destroy its structure and only keep the values in it.

data.frame(letter_name = unlist(names),
           numeric_content = unlist(values),
           row.names = NULL)
#>   letter_name numeric_content
#> 1           a               1
#> 2           b               2
#> 3           c               3
#> 4           d               4

^{Created on 2023-01-16 by the reprex package (v2.0.1)}