Having issues with melt in R

Question

So the data looks like this at present. The mean column is average hourly wage. The meanann is average annual wage hence the ann at the end. Entry follows the same logic. However, trying to make it look like the intended result below

   ID      mean    meanann    entry     entryann
  32100     100      5200       99         5148
  32101     101      5252      100         5200

Intended result

    ID      mean    entry
  32100     100      99
  32100     5200     5148
  32101     101      100
  32101     5252     5200

Been using this (https://ademos.people.uic.edu/Chapter8.html) as a teaching tool but not finding anything that does it as such. I know that you would do a melt and specify your ID variables but the issues is that it stacks all of my mean observations first and then all of the meanann and so on.

Allan Cameron · Accepted Answer · 2020-11-18T18:07:36.873

3

You can do this more easily with pivot_longer from tidyr:

tidyr::pivot_longer(df, 2:5, names_pattern = "^(mean|entry)", names_to = ".value")
#> # A tibble: 4 x 3
#>      ID  mean entry
#>   <int> <int> <int>
#> 1 32100   100    99
#> 2 32100  5200  5148
#> 3 32101   101   100
#> 4 32101  5252  5200

edited Nov 18 '20 at 18:07

answered Nov 18 '20 at 17:57

Allan Cameron

147,086
7
49
87

Thanks. However, some clarifying questions. The {4}.. does this represent the number of columns in the original? Also, the 2:5, what does this refer to? – Tim Wilcox Nov 18 '20 at 18:02
1

`"(mean|entry)"` might be a nicer and clearer `names_pattern`. – Gregor Thomas Nov 18 '20 at 18:04
@TimWilcox the 2:5 are the column numbers containing the values. The names pattern is a regular expression for lumping together the relevant columns you wish to merge – Allan Cameron Nov 18 '20 at 18:06
@GregorThomas You're absolutely right. Updated, with thanks. – Allan Cameron Nov 18 '20 at 18:08

score 1 · Answer 2 · answered Nov 18 '20 at 21:43

melt from data.table can make use of the patterns in measure

library(data.table)
melt(setDT(df), measure = patterns("^mean", "^entry"),
    value.name = c("mean", "entry"))[, variable := NULL][]
#      ID mean entry
#1: 32100  100    99
#2: 32101  101   100
#3: 32100 5200  5148
#4: 32101 5252  5200

data

df <- structure(list(ID = 32100:32101, mean = 100:101, meanann = c(5200L, 
5252L), entry = 99:100, entryann = c(5148L, 5200L)), 
class = "data.frame", row.names = c(NA, 
-2L))

Having issues with melt in R

2 Answers2

data