1

There is the table to transform into long form from wide form. It contains +200 columns compose with multi columns like below :

Original data :

# dt
dt <- data.table("1" = c(NA,"Place","dan","uan","yan"),
                 "2" = c(NA,"Place_2","adan","duan","eyan"),
                 "3" = c("something","Male",1253,6643,4325),
                 "4" = c(1998,"Female",624,623,55),
                 "5" = c(NA,"Trans",13,51,51),
                 "6" = c("something2","Male",126,63643,725),
                 "7" = c(1999,"Female",284,243,557),
                 "8" = c(NA,"Trans",138,541,11))

Start from the 3rd column , every +3 column is the year value

dt[1,c(3:ncol(dt) %% 3 == 1),with = FALSE]

How to effectively transform the multi columns into single column for melt ?

Goal:

Place Place_2   Sex     Year    num
dan   adan      Male    1998    1253
dan   adan      Female  1998    624
dan   adan      Trans   1998    13
dan   adan      Male    1999    126
dan   adan      Female  1999    63643
dan   adan      Trans   1999    725
uan   duan      Female  1998    6643
....
jangorecki
  • 16,384
  • 4
  • 79
  • 160
rane
  • 901
  • 4
  • 12
  • 24
  • Yes , only 1st row contain year value , and the middle of three type . Like what we usually see in some excel table with double column name . – rane Jan 05 '20 at 09:12

2 Answers2

1

The structure of your data.table is uncommon. Here is one way to do it. Step 1-3 is to prepare the data.table for melt.

If you need the final output looks exactly as your expected output, you may need to create an ID column before melt and order dt5 based on multiple columns. Let me know if you need help with that.

library(data.table)

# Step 1: Get the year value
col_num <- which(c(3:ncol(dt) %% 3 == 2)) + 1
year_vec <- as.numeric(as.vector(t(dt[1, ..col_num])))

# Step 2: Create all combinations of year and Male, Female, and Trans
year_sex_dt <- CJ(as.vector(t(dt[2, 3:5])), year_vec)
year_sex_dt[, V1 := factor(V1, levels = c("Male", "Female", "Trans"))]
keycol <- c("year_vec", "V1")
setorderv(year_sex_dt, keycol)
new_name <- paste(year_sex_dt[, V1], year_sex_dt[, year_vec], sep = "_")

# Step 3: Assign column names
dt2 <- setnames(dt[c(-1, -2)], c(as.vector(t(dt[2, 1:2])), new_name)) 

# Step 4: melt the data.table 
dt3 <- melt(dt2, id.vars = 1:2, variable.name = "Sex_Year", value.name = "num")
dt4 <- dt3[, c("Sex", "Year") := tstrsplit(Sex_Year, "_", fixed = TRUE)]
dt4[, Sex_Year := NULL]
dt5 <- dt4[, c("Place", "Place_2", "Sex", "Year", "num")]

head(dt5)
#    Place Place_2    Sex Year  num
# 1:   dan    adan   Male 1998 1253
# 2:   uan    duan   Male 1998 6643
# 3:   yan    eyan   Male 1998 4325
# 4:   dan    adan Female 1998  624
# 5:   uan    duan Female 1998  623
# 6:   yan    eyan Female 1998   55
www
  • 38,575
  • 12
  • 48
  • 84
  • Thanks! Perfect . Can you kindly explain what t() and CJ() exactly is ? base on the approach t() is perform split 1 result into elements (straight form ?) and CJ() multiply each two columns all occurrences (work on more than 2 columns as well ?) . Since without adding `,unique = T ` argument , CJ() repeated the real data with +200 cols into +19k rows . – rane Jan 05 '20 at 10:58
1

Here is what I tried. I thought arranging column names is the key here. I provided explanation in the code below.

library(data.table)

# Creat new column names. Get the 1st row, search for years, repeat each year
# three times, and paste them with three levels of sex.

unlist(dt[1,]) %>% 
grep(pattern = "\\d{4}", value = TRUE) %>% 
rep(each = 3) %>% 
paste(., c("Male", "Female", "Trans"), sep = "_") -> foo

# Set new column names.
setnames(dt, c("Place_1", "Place_2", foo))

# Then, transform the data into a long-format data. Create two new columns
# (i.e., year and sex), and remove the column, variable.

melt(dt[-(1:2)], id.vars = 1:2, measure = patterns("^\\d{4}"))[,
        c("year", "sex") := tstrsplit(variable, "_", fixed = TRUE)][, -"variable"] -> out

# Sort the result with Place_1 and Place_2. (This is for showing the result). 
out[order(Place_1, Place_2)][]

#    Place_1 Place_2 value year    sex
# 1:     dan    adan  1253 1998   Male
# 2:     dan    adan   624 1998 Female
# 3:     dan    adan    13 1998  Trans
# 4:     dan    adan   126 1999   Male
# 5:     dan    adan   284 1999 Female
# 6:     dan    adan   138 1999  Trans
# 7:     uan    duan  6643 1998   Male
# 8:     uan    duan   623 1998 Female
# 9:     uan    duan    51 1998  Trans
#10:     uan    duan 63643 1999   Male
#11:     uan    duan   243 1999 Female
#12:     uan    duan   541 1999  Trans
#13:     yan    eyan  4325 1998   Male
#14:     yan    eyan    55 1998 Female
#15:     yan    eyan    51 1998  Trans
#16:     yan    eyan   725 1999   Male
#17:     yan    eyan   557 1999 Female
#18:     yan    eyan    11 1999  Trans
jazzurro
  • 23,179
  • 35
  • 66
  • 76