Wide to long, inconsistent column names

user_7030705 0
Viewed 86

I have the following dataframe, which you can get as a CSV here: http://www.sharecsv.com/s/c4d94fd3a7dc43e3ec249bff373a0082/data.csv

My colnames, are as follows. As you can see, the names are not sequential (for example, Q1 is missing, no Q3 or Q4, and so on). I need to keep it that way. Check that some cases have an additional ".1" that is irrelevant.

  [1] "Q2_1"     "Q2_2"     "Q5_1"     "Q5_2"     "Q6_1"     "Q6_2"     "Q9_1"     "Q9_2"     "Q11_1"    "Q11_2"    "Q8_1"     "Q8_2"     "Q14_1"   
 [14] "Q14_2"    "Q16_1"    "Q16_2"    "Q10_1"    "Q10_2"    "Q11_1.1"  "Q11_2.1"  "Q19_1"    "Q19_2"    "Q20_1"    "Q20_2"    "Q21_1"    "Q21_2"   
 [27] "Q15_1"    "Q15_2"    "Q23_1"    "Q23_2"    "Q24_1"    "Q24_2"    "Q25_1"    "Q25_2"    "Q26_1"    "Q26_2"    "Q20_1.1"  "Q20_2.1"  "Q21_1.1" 
 [40] "Q21_2.1"  "Q29_1"    "Q29_2"    "Q30_1"    "Q30_2"    "Q35_1"    "Q35_2"    "Q36_1"    "Q36_2"    "Q37_1"    "Q37_2"    "Q38_1"    "Q38_2"   
 [53] "Q39_1"    "Q39_2"    "Q41_1"    "Q41_2"    "Q30_1.1"  "Q30_2.1"  "Q43_1"    "Q43_2"    "Q44_1"    "Q44_2"    "Q45_1"    "Q45_2"    "Q47_1"   
 [66] "Q47_2"    "Q48_1"    "Q48_2"    "Q36_1.1"  "Q36_2.1"  "Q37_1.1"  "Q37_2.1"  "Q51_1"    "Q51_2"    "Q52_1"    "Q52_2"    "Q53_1"    "Q53_2"   
 [79] "Q41_1.1"  "Q41_2.1"  "Q42_1"    "Q42_2"    "Q56_1"    "Q56_2"    "Q57_1"    "Q57_2"    "Q58_1"    "Q58_2"    "Q59_1"    "Q59_2"    "Q60_1"   
 [92] "Q60_2"    "Q61_1"    "Q61_2"    "Q62_1"    "Q62_2"    "Q63_1"    "Q63_2"    "Q64_1"    "Q64_2"    "Q65_1"    "Q65_2"    "Q53_1.1"  "Q53_2.1" 
[105] "Q54_1"    "Q54_2"    "Q68_1"    "Q68_2"    "Q75_1"    "Q75_2"    "Q57_1.1"  "Q57_2.1"  "Q58_1.1"  "Q58_2.1"  "Q59_1.1"  "Q59_2.1"  "Q60_1.1" 
[118] "Q60_2.1"  "Q61_1.1"  "Q61_2.1"  "Q81_1"    "Q81_2"    "Q82_1"    "Q82_2"    "Q83_1"    "Q83_2"    "Q87_1"    "Q87_2"    "Q88_1"    "Q88_2"   
[131] "Q89_1"    "Q89_2"    "Q90_1"    "Q90_2"    "Q91_1"    "Q91_2"    "Q92_1"    "Q92_2"    "Q93_1"    "Q93_2"    "Q94_1"    "Q94_2"    "Q95_1"   
[144] "Q95_2"    "Q74_1"    "Q74_2"    "Q75_1.1"  "Q75_2.1"  "Q76_1"    "Q76_2"    "Q77_1"    "Q77_2"    "Q100_1"   "Q100_2"   "Q101_1"   "Q101_2"  
[157] "Q102_1"   "Q102_2"   "Q103_1"   "Q103_2"   "Q104_1"   "Q104_2"   "Q105_1"   "Q105_2"   "Q106_1"   "Q106_2"   "Q107_1"   "Q107_2"   "Q108_1"  
[170] "Q108_2"   "Q113_1"   "Q113_2"   "Q114_1"   "Q114_2"   "Q117_1"   "Q117_2"   "Q96_1"    "Q96_2"    "Q97_1"    "Q97_2"    "Q98_1"    "Q98_2"   
[183] "Q121_1"   "Q121_2"   "Q103_1.1" "Q103_2.1" "Q104_1.1" "Q104_2.1" "Q127_1"   "Q127_2"   "Q128_1"   "Q128_2"   "Q129_1"   "Q129_2"

QUESTION: I need to convert this from WIDE to LONG, to obtain something as:

QUESTION CASE VALUE
Q2        1     5
Q2        2     5
Q5        1     1
Q5        2     2

I have tried to reshape as follows but keep getting the varying error, and I'm not sure I'm splitting this correctly either:

test <- reshape(data, sep = "_", times = c(1, 2), direction = "long", varying = colnames(data))
reshape dataframe r
2 Answers

You can use pivot_longer form tidyr, separating at the "_":

library(tidyr)

pivot_longer(data, cols=everything(), names_sep="_",
             names_to=c("Question","Case"))

# A tibble: 194 x 3
   Question Case  value
   <chr>    <chr> <int>
 1 Q2       1         5
 2 Q2       2         5
 3 Q5       1         1
 4 Q5       2         2
 5 Q6       1         4
 6 Q6       2         4
 7 Q9       1         4
 8 Q9       2         4
 9 Q11      1         5
10 Q11      2         3
# ... with 184 more rows

Attempt to use the reshape function results in an error due to inconsistent variable names in the wide format. For example, as you mentioned, some names have an additional ".1", which you say is irrelevant. The tidyr package agrees because it just gathers everything after the separator and puts whatever it finds into the "Case" variable (the second item in the names_to argument). The reshape function is more strict. That is, the additional ".1" is not irrelevant (according to the function) because the function will attempt to guess the values contained in the names (after the "_"), sees that the lengths are unequal, and fails with 'varying' arguments must be the same length.

An alternative using sep="" is given below...

user_75145270

Since you want to reshape just on one category "question" (i.e. not on more categories as e.g. "question" and "year"), all the Q* variables can be considered as unique (sub-)questions.

Hence you may want to separate by "",

tmp <- reshape(dat[-1], varying=1:194, direction="long", sep="")

which and gives you a long format with time variable including all the sub questions.

head(tmp, 3)
#       time Q id
# 1.2_1  2_1 5  1
# 1.2_2  2_2 5  1
# 1.5_1  5_1 1  1

Thereafter you simply may split the time variable at "_" using strsplit, give the result nice names using setNames, and order it.

res <- setNames(data.frame(do.call(rbind, strsplit(tmp$time, "_")), tmp[2:3],
                           stringsAsFactors=FALSE),
                c("question", "case", "value", "id"))
res <- res[order(as.numeric(res$question)), ]

Result

head(res, 18)
#          question case value id
# 1.2_1           2    1     5  1
# 1.2_2           2    2     5  1
# 1.5_1           5    1     1  1
# 1.5_2           5    2     2  1
# 1.6_1           6    1     4  1
# 1.6_2           6    2     4  1
# 1.8_1           8    1     5  1
# 1.8_2           8    2     3  1
# 1.9_1           9    1     4  1
# 1.9_2           9    2     4  1
# 1.10_1         10    1     5  1
# 1.10_2         10    2     4  1
# 1.11_1         11    1     5  1
# 1.11_2         11    2     3  1
# 1.11_1.1       11  1.1     5  1
# 1.11_2.1       11  2.1     4  1
# 1.14_1         14    1     3  1
# 1.14_2         14    2     2  1

Data

dat <- structure(list(X = 2L, Q2_1 = 5L, Q2_2 = 5L, Q5_1 = 1L, Q5_2 = 2L, 
    Q6_1 = 4L, Q6_2 = 4L, Q9_1 = 4L, Q9_2 = 4L, Q11_1 = 5L, Q11_2 = 3L, 
    Q8_1 = 5L, Q8_2 = 3L, Q14_1 = 3L, Q14_2 = 2L, Q16_1 = 5L, 
    Q16_2 = 4L, Q10_1 = 5L, Q10_2 = 4L, Q11_1.1 = 5L, Q11_2.1 = 4L, 
    Q19_1 = 5L, Q19_2 = 5L, Q20_1 = 5L, Q20_2 = 5L, Q21_1 = 5L, 
    Q21_2 = 5L, Q15_1 = 5L, Q15_2 = 5L, Q23_1 = 5L, Q23_2 = 4L, 
    Q24_1 = 5L, Q24_2 = 5L, Q25_1 = 5L, Q25_2 = 5L, Q26_1 = 4L, 
    Q26_2 = 5L, Q20_1.1 = 4L, Q20_2.1 = 5L, Q21_1.1 = 4L, Q21_2.1 = 5L, 
    Q29_1 = 5L, Q29_2 = 5L, Q30_1 = 5L, Q30_2 = 5L, Q35_1 = 4L, 
    Q35_2 = 5L, Q36_1 = 5L, Q36_2 = 5L, Q37_1 = 5L, Q37_2 = 5L, 
    Q38_1 = 5L, Q38_2 = 5L, Q39_1 = 5L, Q39_2 = 5L, Q41_1 = 5L, 
    Q41_2 = 5L, Q30_1.1 = 5L, Q30_2.1 = 5L, Q43_1 = 5L, Q43_2 = 5L, 
    Q44_1 = 5L, Q44_2 = 5L, Q45_1 = 5L, Q45_2 = 5L, Q47_1 = 5L, 
    Q47_2 = 5L, Q48_1 = 5L, Q48_2 = 5L, Q36_1.1 = 4L, Q36_2.1 = 5L, 
    Q37_1.1 = 5L, Q37_2.1 = 5L, Q51_1 = 3L, Q51_2 = 2L, Q52_1 = 3L, 
    Q52_2 = 2L, Q53_1 = 3L, Q53_2 = 2L, Q41_1.1 = 3L, Q41_2.1 = 2L, 
    Q42_1 = 3L, Q42_2 = 2L, Q56_1 = 5L, Q56_2 = 5L, Q57_1 = 5L, 
    Q57_2 = 5L, Q58_1 = 5L, Q58_2 = 5L, Q59_1 = 3L, Q59_2 = 2L, 
    Q60_1 = 4L, Q60_2 = 2L, Q61_1 = 3L, Q61_2 = 2L, Q62_1 = 3L, 
    Q62_2 = 2L, Q63_1 = 5L, Q63_2 = 4L, Q64_1 = 5L, Q64_2 = 4L, 
    Q65_1 = 5L, Q65_2 = 5L, Q53_1.1 = 5L, Q53_2.1 = 5L, Q54_1 = 3L, 
    Q54_2 = 2L, Q68_1 = 5L, Q68_2 = 4L, Q75_1 = 4L, Q75_2 = 4L, 
    Q57_1.1 = 4L, Q57_2.1 = 4L, Q58_1.1 = 3L, Q58_2.1 = 2L, Q59_1.1 = 5L, 
    Q59_2.1 = 5L, Q60_1.1 = 5L, Q60_2.1 = 5L, Q61_1.1 = 4L, Q61_2.1 = 3L, 
    Q81_1 = 5L, Q81_2 = 5L, Q82_1 = 5L, Q82_2 = 5L, Q83_1 = 5L, 
    Q83_2 = 5L, Q87_1 = 5L, Q87_2 = 5L, Q88_1 = 5L, Q88_2 = 5L, 
    Q89_1 = 5L, Q89_2 = 5L, Q90_1 = 4L, Q90_2 = 4L, Q91_1 = 5L, 
    Q91_2 = 5L, Q92_1 = 5L, Q92_2 = 5L, Q93_1 = 5L, Q93_2 = 5L, 
    Q94_1 = 5L, Q94_2 = 5L, Q95_1 = 5L, Q95_2 = 5L, Q74_1 = 5L, 
    Q74_2 = 5L, Q75_1.1 = 5L, Q75_2.1 = 5L, Q76_1 = 5L, Q76_2 = 5L, 
    Q77_1 = 5L, Q77_2 = 5L, Q100_1 = 5L, Q100_2 = 5L, Q101_1 = 5L, 
    Q101_2 = 5L, Q102_1 = 5L, Q102_2 = 5L, Q103_1 = 5L, Q103_2 = 5L, 
    Q104_1 = 5L, Q104_2 = 5L, Q105_1 = 5L, Q105_2 = 5L, Q106_1 = 5L, 
    Q106_2 = 5L, Q107_1 = 5L, Q107_2 = 5L, Q108_1 = 5L, Q108_2 = 5L, 
    Q113_1 = 5L, Q113_2 = 5L, Q114_1 = 4L, Q114_2 = 4L, Q117_1 = 5L, 
    Q117_2 = 5L, Q96_1 = 5L, Q96_2 = 5L, Q97_1 = 5L, Q97_2 = 5L, 
    Q98_1 = 5L, Q98_2 = 5L, Q121_1 = 5L, Q121_2 = 5L, Q103_1.1 = 5L, 
    Q103_2.1 = 5L, Q104_1.1 = 5L, Q104_2.1 = 5L, Q127_1 = 4L, 
    Q127_2 = 4L, Q128_1 = 5L, Q128_2 = 5L, Q129_1 = 4L, Q129_2 = 4L), class = "data.frame", row.names = c(NA, 
-1L))
user_65740380
Related