R melt subcategories

Question

I'm trying to melt a table (csv) with subcategories but for some reason cannot get the desired result with melt.array

I have:

Region,Sender,Afghanistan,,,,
,,Adult,,Minor,,
,,Male ,Female,Male,Female,
EEA,Austria,165,8,6,10,
....

need:

Reg Send    Rec         Age   Gender  Value
EEA Austria Afghanistan Adult Male    165
EEA Austria Afghanistan Adult Female  8
EEA Austria Afghanistan Adult Male    6
EEA Austria Afghanistan Adult Female  10
....

and would be most grateful for some help!

Is the first table your csv file? Can you paste it in with commas as well? — chinsoon12, Apr 15 '16 at 11:44
I got a bit carried away there. Now we have it though. Any ideas? I am trying to map a large set of deportation data and am not the best at data wrangling! — Thomas, Apr 15 '16 at 13:31
Btw, if the table comes from an excel pivot table, you can use R to read in the raw data. Or you can double click on Grand Total and save down the data as csv — chinsoon12, Apr 15 '16 at 23:06

score 0 · Answer 1 · answered Apr 15 '16 at 13:58

dont have a very elegant way because of the structure but zoo::na.locf might be what you are looking for

library(zoo)
data <- structure(list(V1 = structure(c(3L, 1L, 1L, 2L), .Label = c("", 
                                                                    "EEA", "Region"), class = "factor"), V2 = structure(c(3L, 1L, 
                                                                                                                          1L, 2L), .Label = c("", "Austria", "Sender"), class = "factor"), 
                       V3 = structure(c(3L, 2L, 4L, 1L), .Label = c("165", "Adult", 
                                                                    "Afghanistan", "Male "), class = "factor"), V4 = structure(c(1L, 
                                                                                                                                 1L, 3L, 2L), .Label = c("", "8", "Female"), class = "factor"), 
                       V5 = structure(c(1L, 4L, 3L, 2L), .Label = c("", "6", "Male", 
                                                                    "Minor"), class = "factor"), V6 = structure(c(1L, 1L, 3L, 
                                                                                                                  2L), .Label = c("", "10", "Female"), class = "factor")), .Names = c("V1", 

data[1:3,][data[1:3,]==""] <- NA
data <- na.locf(zoo(data), fromLast=F)
df <- cbind(as.character(data$V1[4]), as.character(temp$V2[4]),
          data.frame(na.locf(zoo(t(data)), fromLast=F)[-(1:2),]))
setNames(df, c("Reg","Send","Rec","Age","Gender", "Value"))

R melt subcategories

1 Answers1