CODEWITHSUNDEEP
rpivottidyverse
PDog
PDog

Reputation: 143

Pivoting data.frame with multiple "columns" per column

Consider the following:

tribble(
  ~"1", ~"2", ~"3", ~"4",
  "bob", "sally", "fred","jim",
  "2011", "2012", "2013", "2014"
)

In the above, column "1" stores values that we really want to be to separate columns, such:

tribble(
  ~col, ~name, ~year,
  "1", "bob", "2011",
  "2", "sally", "2012",
  "3", "fred", "2013",
  "4", "jim", "2014"
)

At first, I tried pivot_longer(everything()) but that produces duplicate rows. 

  name  value
  <chr> <chr>
1 1     bob  
2 2     sally
3 3     fred 
4 4     jim  
5 1     2011 
6 2     2012 
7 3     2013 
8 4     2014

I considered then trying pivot_wider, but I can't partially select either the years or names in the value column above.

Any ideas? This is a toy example of course - the basic idea is to take multiple rows and covert them to columns without needing to repeat the code.

Upvotes: 0

Views: 218

Answers (3)

akrun
akrun

Reputation: 886938

We can do this directly with data.table::transpose

data.table::transpose(df1, keep.names = 'rn')
#  rn    V1   V2
#1  1   bob 2011
#2  2 sally 2012
#3  3  fred 2013
#4  4   jim 2014

Upvotes: 0

Ronak Shah
Ronak Shah

Reputation: 388817

The data is all mixed up. If we have more such values we need to specify a way in which we can identify each group.

Here I have considered values with only characters in them as name, values with only numbers in them as year, you can add more such conditions if needed. 

library(dplyr)
library(tidyr)

df %>%
  pivot_longer(cols = everything(), names_to = 'col') %>%
  mutate(col_name = case_when(grepl('^[A-Za-z]+$', value) ~ 'name', 
                              grepl('^[0-9]+$', value) ~'year')) %>%
  pivot_wider(names_from = col_name, values_from = value) %>%
  type.convert(as.is = TRUE)

# A tibble: 4 x 3
#    col name   year
#  <int> <chr> <int>
#1     1 bob    2011
#2     2 sally  2012
#3     3 fred   2013
#4     4 jim    2014

Upvotes: 1

Taufi
Taufi

Reputation: 1577

An easy solution is 

tibble::rownames_to_column(as.data.frame(t(A)))

with output 

  rowname    V1   V2
1       1   bob 2011
2       2 sally 2012
3       3  fred 2013
4       4   jim 2014

where 

> A = tribble(
+     ~"1", ~"2", ~"3", ~"4",
+     "bob", "sally", "fred","jim",
+     "2011", "2012", "2013", "2014"
+ )

Edit:

You can also use the pipe like this 

A %>% t() %>% as.data.frame() %>% tibble::rownames_to_column()

Upvotes: 5

Related Questions