How to transpose this Likert data ?

Andrzej · July 29, 2023, 4:20pm

Hi,
I have done it before but somehow forgot when I need it.
I have this data:

df <- structure(list(
  Levels_of_LS = c(
    "not_applicable", "not_stresful",
    "slightly_stresful", "moderately_sresful", "very_ stresful"
  ),
  DES1 = c(69, 73, 49, 37, 15), DES10 = c(10, 54, 84, 59, 36), DES11 = c(78, 77, 48, 27, 13), DES12 = c(
    5, 11, 49, 67,
    111
  ), DES13 = c(197, 26, 12, 3, 5), DES14 = c(
    9, 71, 68,
    49, 46
  ), DES15 = c(5, 67, 85, 50, 36), DES16 = c(
    167, 49,
    14, 11, 2
  ), DES17 = c(5, 41, 75, 66, 56), DES18 = c(
    12, 94,
    71, 46, 20
  ), DES19 = c(13, 94, 70, 44, 22), DES2 = c(
    15,
    83, 65, 49, 31
  ), DES40 = c(31, 13, 50, 58, 91), DES5 = c(
    13,
    51, 75, 56, 48
  ), DES6 = c(8, 84, 78, 48, 25)
), class = c(
  "tbl_df",
  "tbl", "data.frame"
), row.names = c(NA, -5L))

and:

want to transpose this data so I have individual values (not counts) and column Levels_of_LS in cells with values like:
Levels_of_LS = c( "not_applicable", "not_stresful", "slightly_stresful", "moderately_sresful", "very_ stresful")
). Basically I think, I would like to uncount this table. Any help will be much appreciated.
Move columns DES2 to DES9 after DES1 so sorting will look more natural.
Finally and for second table I would like "not_applicable", "not_stresful", "slightly_stresful", "moderately_stresful", "very_ stresful" to be coded as: 1, 2, 3 , 4, 5.

technocrat · July 30, 2023, 12:11am

I'm unsure of your intent. How does this look?

d <- structure(list(
  Levels_of_LS = c(
    "not_applicable", "not_stresful",
    "slightly_stresful", "moderately_sresful", "very_ stresful"
  ),
  DES1 = c(69, 73, 49, 37, 15), DES10 = c(10, 54, 84, 59, 36), DES11 = c(78, 77, 48, 27, 13), DES12 = c(
    5, 11, 49, 67,
    111
  ), DES13 = c(197, 26, 12, 3, 5), DES14 = c(
    9, 71, 68,
    49, 46
  ), DES15 = c(5, 67, 85, 50, 36), DES16 = c(
    167, 49,
    14, 11, 2
  ), DES17 = c(5, 41, 75, 66, 56), DES18 = c(
    12, 94,
    71, 46, 20
  ), DES19 = c(13, 94, 70, 44, 22), DES2 = c(
    15,
    83, 65, 49, 31
  ), DES40 = c(31, 13, 50, 58, 91), DES5 = c(
    13,
    51, 75, 56, 48
  ), DES6 = c(8, 84, 78, 48, 25)
), class = c(
  "tbl_df",
  "tbl", "data.frame"
), row.names = c(NA, -5L))
colnames(d)
#>  [1] "Levels_of_LS" "DES1"         "DES10"        "DES11"        "DES12"       
#>  [6] "DES13"        "DES14"        "DES15"        "DES16"        "DES17"       
#> [11] "DES18"        "DES19"        "DES2"         "DES40"        "DES5"        
#> [16] "DES6"
levs <- d$Levels_of_LS 
dt <- as.data.frame(matrix(as.integer(t(d[,-1])), nrow = 15, ncol = 5))
colnames(dt) <- levs
dt$des <- colnames(d)[-1]
dt <- dt[c(1,12,14:15,2:11),c(6,1:5)]
dt
#>      des not_applicable not_stresful slightly_stresful moderately_sresful
#> 1   DES1             69           73                49                 37
#> 12  DES2             15           83                65                 49
#> 14  DES5             13           51                75                 56
#> 15  DES6              8           84                78                 48
#> 2  DES10             10           54                84                 59
#> 3  DES11             78           77                48                 27
#> 4  DES12              5           11                49                 67
#> 5  DES13            197           26                12                  3
#> 6  DES14              9           71                68                 49
#> 7  DES15              5           67                85                 50
#> 8  DES16            167           49                14                 11
#> 9  DES17              5           41                75                 66
#> 10 DES18             12           94                71                 46
#> 11 DES19             13           94                70                 44
#>    very_ stresful
#> 1              15
#> 12             31
#> 14             48
#> 15             25
#> 2              36
#> 3              13
#> 4             111
#> 5               5
#> 6              46
#> 7              36
#> 8               2
#> 9              56
#> 10             20
#> 11             22

^{Created on 2023-07-29 with reprex v2.0.2}

Andrzej · July 30, 2023, 7:05am

Thank you, this looks good as another possibility of showing Likert data.
My intentions were like this however presented below:

obraz

Levels_of_LS, and values from this column:
"not_applicable", "not_stresful", "slightly_stresful", "moderately_stresful", "very_ stresful"
to be transfered into other columns according to theirs frequency and form like uncounted dataframe
with first new ID column which will be representing individual respondents.
Apparently this is probably impossible to achieve as it could be many combinations of Likert responses and
we might end up with different data like this:

So apparently knowing and having got summary table like my original df here (my first post) or yours d @technocrat dataframe,
will not get us back to source df with respondents replies given in Likert scale structure.
But I could be mistaken.

jrkrideau · July 30, 2023, 2:25pm

I cannot see any way to recreate raw scores from summary data alone. If you have more information it might be possible but I doubt it.

technocrat · July 30, 2023, 5:12pm

Entropy. It’s possible for each question individually, but there’s nothing to connect questions.

Andrzej · July 30, 2023, 5:28pm

Can you elaborate a bit how to do it or what do you mean, please ?

technocrat · July 31, 2023, 1:59am

For each of the questions, the totals can be expanded. And the answers are mutually exclusive. So each row represents one subject. I don't think, though, that the results can be lined up.

d <- structure(list(
  Levels_of_LS = c(
    "not_applicable", "not_stresful",
    "slightly_stresful", "moderately_sresful", "very_ stresful"
  ),
  DES1 = c(69, 73, 49, 37, 15), DES10 = c(10, 54, 84, 59, 36), DES11 = c(78, 77, 48, 27, 13), DES12 = c(
    5, 11, 49, 67,
    111
  ), DES13 = c(197, 26, 12, 3, 5), DES14 = c(
    9, 71, 68,
    49, 46
  ), DES15 = c(5, 67, 85, 50, 36), DES16 = c(
    167, 49,
    14, 11, 2
  ), DES17 = c(5, 41, 75, 66, 56), DES18 = c(
    12, 94,
    71, 46, 20
  ), DES19 = c(13, 94, 70, 44, 22), DES2 = c(
    15,
    83, 65, 49, 31
  ), DES40 = c(31, 13, 50, 58, 91), DES5 = c(
    13,
    51, 75, 56, 48
  ), DES6 = c(8, 84, 78, 48, 25)
), class = c(
  "tbl_df",
  "tbl", "data.frame"
), row.names = c(NA, -5L))

dt <- as.data.frame(matrix(as.integer(t(d[,-1])), nrow = 15, ncol = 5))

dt$des <- colnames(d)[-1]
head(dt)
#>    V1 V2 V3 V4  V5   des
#> 1  69 73 49 37  15  DES1
#> 2  10 54 84 59  36 DES10
#> 3  78 77 48 27  13 DES11
#> 4   5 11 49 67 111 DES12
#> 5 197 26 12  3   5 DES13
#> 6   9 71 68 49  46 DES14
example <- unlist(dt[,1])
head(example)
#> [1]  69  10  78   5 197   9
enlarge <- function(x) rep(1,x)
sapply(example,enlarge)
#> [[1]]
#>  [1] 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
#> [39] 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
#> 
#> [[2]]
#>  [1] 1 1 1 1 1 1 1 1 1 1
#> 
#> [[3]]
#>  [1] 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
#> [39] 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
#> [77] 1 1
#> 
#> [[4]]
#> [1] 1 1 1 1 1
#> 
#> [[5]]
#>   [1] 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
#>  [38] 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
#>  [75] 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
#> [112] 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
#> [149] 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
#> [186] 1 1 1 1 1 1 1 1 1 1 1 1
#> 
#> [[6]]
#> [1] 1 1 1 1 1 1 1 1 1
#> 
#> [[7]]
#> [1] 1 1 1 1 1
#> 
#> [[8]]
#>   [1] 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
#>  [38] 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
#>  [75] 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
#> [112] 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
#> [149] 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
#> 
#> [[9]]
#> [1] 1 1 1 1 1
#> 
#> [[10]]
#>  [1] 1 1 1 1 1 1 1 1 1 1 1 1
#> 
#> [[11]]
#>  [1] 1 1 1 1 1 1 1 1 1 1 1 1 1
#> 
#> [[12]]
#>  [1] 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
#> 
#> [[13]]
#>  [1] 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
#> 
#> [[14]]
#>  [1] 1 1 1 1 1 1 1 1 1 1 1 1 1
#> 
#> [[15]]
#> [1] 1 1 1 1 1 1 1 1

system · September 11, 2023, 1:59am

This topic was automatically closed 42 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.