Tidyverse R: how to change a tuple-table into straight tibble?

Question

I want to do the following, changing tupple-style rows (without header) of data into a table with headers, with Tidyverse. The new command gather apparently can handle this.

How can I gather the wide table into a straight table or with some other modern Tidyverse tools?

library(tidyverse)
INLINE_LOAD ="Car,Merse,Speed,10,Other,ot
Car,Ferra,Speed,20,Other,ot2
Car,Volve,Speed,30,Other,ot3
Car,Miiss,Speed,40,Other,ot4"

Wanted Output

Car,Speed,Other
Merse,10,ot
Ferra,20,ot2
Volve,30,ot3
Miiss,40,ot4

[How to make a great R reproducible example?](http://stackoverflow.com/questions/5963269) — Sotos, Oct 05 '17 at 13:55
Do u have the column names or is it the first line that is the column name — akrun, Oct 05 '17 at 13:57
@akrun there is no column names as a header. Column names are on each row: each odd value is a column name. Column names are `Car`, `Speed` and `Other`. — hhh, Oct 05 '17 at 13:59
Not the downvoter but here are a couple of reasons: No reproducible example, No attempt to solve YOUR problem. People tend to get frustrated when all you post is *I have this and I want this* — Sotos, Oct 05 '17 at 14:04

h3rm4n · Answer 1 · 2017-10-05T14:46:19.137

1

With the tidyverse:

df %>% select(Car = 2, Speed = 4, Other = 6)

The result:

# A tibble: 4 x 3
     Car Speed  Other
  <fctr> <int> <fctr>
1  Merse    10     ot
2  Ferra    20    ot2
3  Volve    30    ot3
4  Miiss    40    ot4

With base R:

dfnew <- df[,c(FALSE,TRUE)]
names(dfnew) <- unlist(unique(df[,c(TRUE,FALSE)]))

The result:

> dfnew
    Car Speed Other
1 Merse    10    ot
2 Ferra    20   ot2
3 Volve    30   ot3
4 Miiss    40   ot4

edited Oct 05 '17 at 14:46

answered Oct 05 '17 at 13:59

h3rm4n

4,126
15
21

`library(tidyverse);df <- read_delim("Input.csv", ",");dfnew <- df[,c(FALSE,TRUE)]` firing the error `Error: Length of logical index vector must be 1 or 6 (the number of rows), not 2` – hhh Oct 05 '17 at 14:14
@hhh that is because tidyverse messes with the dataframe format; I added a tidyverse solution to my answer as well; for the base R you can do `dfnew <- as.data.frame(df)[,c(FALSE,TRUE)]` – h3rm4n Oct 05 '17 at 14:20
1

@hhh added another soltuion at the end, I hope that helps – h3rm4n Oct 05 '17 at 14:40
yes that is the most elegant so far +1, perhaps you could simplify the answer -- it is a bit hard reading and I can only get the last one working with the InlineLoad `INLINE_LOAD ="Car,Merse,Speed,10,Other,ot Car,Ferra,Speed,20,Other,ot2 Car,Volve,Speed,30,Other,ot3 Car,Miiss,Speed,40,Other,ot4"; df <- read_delim(INLINE_LOAD, ",", col_names=FALSE)` – hhh Oct 05 '17 at 14:42

hhh · Answer 2 · 2017-10-05T14:44:09.367

We first use inline load and then apply the following transformations

library(tidyverse)
INLINE_LOAD ="Car,Merse,Speed,10,Other,ot
Car,Ferra,Speed,20,Other,ot2
Car,Volve,Speed,30,Other,ot3
Car,Miiss,Speed,40,Other,ot4"

read_delim(INLINE_LOAD, ",", col_names=FALSE) %>% 
    select(c(2,4,6)) %>% 
    select(Car=X2,Speed=X4,Other=X6)

where renaming of the columns and selecting only the odd columns. There is a more elegant solution here with only one select.

This is not a very elegant solution when a large number of columns.

Tidyverse R: how to change a tuple-table into straight tibble?

2 Answers2