2

I have a data set (that has been imported from a .json file) that currently looks like this:

ID <- c(1)
Name <- c("John")
Name.1 <- c("Irene")
Address <- c("1 Brick Lane")
Address.1 <- c("2 Wood Street")

df <- data.frame(ID, Name, Name.1, Address, Address.1)

ID     |  Name |   Address    | Name.1 | Address.1
------ | ------|   ------     | ------ | ------
1      | John  | 1 Brick Lane | Irene  | 2 Wood Street

However, I would like to manipulate it to look like this:

ID <- c(1)
Name <- c("John", "Irene")
Address <- c("1 Brick Lane", "2 Wood Street")

df <- data.frame(ID, Name, Address)

ID     |  Name |   Address    
------ | ------|   ------     
1      | John  | 1 Brick Lane 
1      | Irene | 2 Wood Street

Also, not all people have the same information fields. For example, John's date of birth might be in the data but Irene's is not.

In my real data set there would be about 30 different columns in the final data frame. Your help would be greatly appreciated!

nspoljar
  • 41
  • 4

1 Answers1

1

We can use melt from data.table which can take multiple measure patterns

library(data.table)
melt(setDT(df), measure = patterns("^Name", "^Address"),
      value.name = c("Name", "Address"))[, variable := NULL][]
#   ID  Name       Address
#1:  1  John  1 Brick Lane
#2:  1 Irene 2 Wood Street
akrun
  • 674,427
  • 24
  • 381
  • 486
  • Thank you. This worked for my smaller example, but for my data set with more than 50 variables, I am now getting an error saying "Error in melt.data.table(setDT(df), id = ID, : Unsupported type 'NULL' ". Does anyone have any knowledge as to why? I have run it line by line and cannot seem to find the issue. Also, is there a way to not write out every single variable by name? Especially as each of my data sets may have different variable names. – nspoljar May 18 '17 at 01:11
  • @nspoljar Not clear about the error. Could you please update your post with a small example that shows the error – akrun May 18 '17 at 03:02
  • I have fixed the error and figured out the second part of my question. Thank you very much though! – nspoljar May 18 '17 at 05:46