CODEWITHSUNDEEP
rdataframe
Hanbin Go
Hanbin Go

Reputation: 91

reshaping multiple columns in R, based on name values

Here is a snippet of the data frame

Df <- data.frame(prop1 = c(NA, NA, NA, "French", NA, NA,NA, "-29 to -20", NA, NA, NA, "Pop", NA, NA, NA, "French", "-29 to -20", "Pop"),
             prop1_rank = c(NA, NA, NA, 0, NA, NA,NA, 11, NA, NA, NA, 1, NA, NA, NA, 40, 0, 2),
             prop2 = c(NA, NA, NA, "Spanish", NA, NA,NA, "-19 to -10", NA, NA, NA, "Rock", NA, NA, NA, "Spanish", "-19 to -10", "Rock"),
             prop2_rank = c(NA, NA, NA, 10, NA, NA,NA, 4, NA, NA, NA, 1, NA, NA, NA, 1, 0, 2),
             initOSF1 = c(NA, NA, NA, NA, NA, "French", NA,NA,NA, "-29 to -20", NA, NA, NA, "Pop", NA, NA, NA, NA),
             initOSF1_freq = c(NA, NA, NA, NA, NA, 66, NA,NA,NA, 0, NA, NA, NA, 14, NA, NA, NA, NA),
             initOSF2 = c(NA, NA, NA, NA, NA, "Spanish", NA,NA,NA, "-19 to -10", NA, NA, NA, "Rock", NA, NA, NA, NA),
             initOSF2_freq = c(NA, NA, NA, NA, NA, 0, NA,NA,NA, 6, NA, NA, NA, 14, NA, NA, NA, NA))

Df

I would like to organize this into

3 columns consisting: c("propositions", "ranks", "freqs"),

where,

Propositions column has the values: "French", "Spanish", "-29 to -20", "19 to -10", "Pop", "Rock", and having a separate columns for the rank values e.g., 0 for French, 10 for Spanish, etc., and frequency values e.g., 66 for French, 0 for Spanish, etc.

This is the final product I am trying to achieve

Upvotes: 0

Views: 54

Answers (1)

Onyambu
Onyambu

Reputation: 79338

This is not an easy one. Probably a better solution exists:

library(tidyverse)
library(data.table)

setDT(Df) %>%
  select(contains(c('prop', 'rank', 'freq'))) %>%
  filter(!if_all(everything(), is.na)) %>%
  melt(measure.vars = patterns(c('prop.$', 'rank$', 'freq'))) %>%
  group_by(gr=cumsum(!is.na(value1)))%>%
  summarise(across(-variable, ~if(length(.x)>1) na.omit(.x) else .x))


# A tibble: 12 x 4
      gr value1     value2 value3
   <int> <chr>       <dbl>  <dbl>
 1     1 French          0     66
 2     2 -29 to -20     11      0
 3     3 Pop             1     14
 4     4 French         40     NA
 5     5 -29 to -20      0     NA
 6     6 Pop             2     NA
 7     7 Spanish        10      0
 8     8 -19 to -10      4      6
 9     9 Rock            1     14
10    10 Spanish         1     NA
11    11 -19 to -10      0     NA
12    12 Rock            2     NA

Upvotes: 1

Related Questions