Cleaning data using R

Question

I'm trying to clean a dataset. This is my dataset:

"id","gender","age","category1","category2","category3","category4","category5","category6","category7","category8","category9","category10"
1,"Male",22,"movies","music","travel","cloths","grocery",,,,,
2,"Male",28,"travel","books","movies",,,,,,,
3,"Female",27,"rent","fuel","grocery","cloths",,,,,,
4,"Female",22,"rent","grocery","travel","movies","cloths",,,,,
5,"Female",22,"rent","online-shopping","utiliy",,,,,,,

I want it to be in a tabular form something like this:

id gender age category            rank
1 Male    22  movies               1
1 Male    22  music                2
1 Male    22  travel               3
1 Male    22  cloths               4
1 Male    22  grocery              5
1 Male    22  books                NA
1 Male    22  rent                 NA
1 Male    22  fuel                 NA
1 Male    22  utility              NA
1 Male    22  online-shopping      NA
...................................
5 Female    22  movies             NA
5 Female    22  music              NA
5 Female    22  travel             NA
5 Female    22  cloths             NA
5 Female    22  grocery            NA
5 Female    22  books              NA
5 Female    22  rent               1
5 Female    22  fuel               NA
5 Female    22  utility            NA
5 Female    22  online-shopping    2

How do I achieve it?

Answer 1

Try something like this:

text1='"id","gender","age","category1","category2","category3","category4","category5","category6","category7","category8","category9","category10"
1,"Male",22,"movies","music","travel","cloths","grocery",,,,,
2,"Male",28,"travel","books","movies",,,,,,,
3,"Female",27,"rent","fuel","grocery","cloths",,,,,,
4,"Female",22,"rent","grocery","travel","movies","cloths",,,,,
5,"Female",22,"rent","online-shopping","utiliy",,,,,,,'

d1 <- read.table(text=text1, sep=",", head=T, as.is=T)

library(reshape2)
d2 <- melt(d1, id.vars=c("id","gender","age"))
names(d2)[5] <- "category"
names(d2)[4] <- "rank"
d2$rank <- gsub("category", "", d2$rank)
head(d2)
#   id gender age rank category
# 1  1   Male  22    1   movies
# 2  2   Male  28    1   travel
# 3  3 Female  27    1     rent
# 4  4 Female  22    1     rent
# 5  5 Female  22    1     rent
# 6  1   Male  22    2    music