Extracting concatenated metadata from a column in R

Question

I've downloaded some ChIP-seq data from www.chip-atlas.org and I want to use this to conduct some enrichment tests with genomic regions from my experiment. The .bed file generated by that website is set up so that each row represents a genomic region from a specific ChIP-seq experiment. I have the data set up as a dataframe in R.

Each row has a single column with all of the metadata, including such important information as the target transcription factor, model system, and tissue type. These are delimited by semi-colons, but the number of pieces of information varies between rows so it's impossible to simply split by the delimiter.

The metadata is structured something like this:

name=CTCF; strain=B6; age=adult; tissue=brain
name=Mecp2; age=embryo; tissue=5t3 cells; passages=5-10

So within each row, the order and number of metadata terms varies. I want to reshape this column into something like this:

name; strain; age; tissue; passages
CTCF; B6; adult; brain; NA
Mecp2; NA; embryo; 5t3 cells; 5-10

How can I extract and reshape this data in R? It seems like it should be possible, but the normal approaches to splitting columns by delimiters are failing me since the rows all have different numbers of items and order.

Here's a slice of the real data which is much more messy than my little example.

test_df <- data.frame(pos=c(3002983, 3002881, 3010946, 3021775), metadata=c("ID=SRX661585;Name=Ctcf%20(@%20Brain);Title=GSM1446329:%20CTCF%20brain%20ChIPSeq%3B%20Mus%20musculus%20x%20Mus%20spretus%3B%20ChIP-Seq;Cell%20group=Neural;<br>source_name=Mouse%20adult%20hybrid%20brain;tissue=Adult%20whole%20brain;strain=BL6/spretus;chip%20antibody=CTCF,%20MillIpore%2007-729;", "ID=SRX2957352;Name=Smc1a%20(@%20AtT-20);Title=GSM2684855:%20SMC1%20ChIPseq%20in%20AtT-20%3B%20Mus%20musculus%3B%20ChIP-Seq;Cell%20group=Neural;<br>source_name=AtT-20%20cells;cell%20line=AtT-20%20cells;cell%20type=pituitary%20corticotroph%20cell;passages=10-20;chip%20antibody=Bethyl%20A300-055A;", "ID=SRX5287318;Name=Mecp2%20(@%20Olfactory%20Nerve);Title=GSM3577864:%20GSM1827604%20MeCP2%20ChIP%20WT%20rep1%3B%20Mus%20musculus%3B%20ChIP-Seq;Cell%20group=Neural;<br>source_name=Olfactory%20epithelium;strain/background=C57BL/6J;genotype/variation=Wild-type,%20MeCP2(%2B/y);age=8%20weeks;cell%20type=Olfactory%20sensory%20neuron;chip%20antibody=MeCP2%20antibody%20(Diagenode,%20pAb-052-050);", "ID=SRX5287318;Name=Mecp2%20(@%20Olfactory%20Nerve);Title=GSM3577864:%20GSM1827604%20MeCP2%20ChIP%20WT%20rep1%3B%20Mus%20musculus%3B%20ChIP-Seq;Cell%20group=Neural;<br>source_name=Olfactory%20epithelium;strain/background=C57BL/6J;genotype/variation=Wild-type,%20MeCP2(%2B/y);age=8%20weeks;cell%20type=Olfactory%20sensory%20neuron;chip%20antibody=MeCP2%20antibody%20(Diagenode,%20pAb-052-050);"))

Thank you for your help!

Answer 1

In general you want to:

1. Clean your text
2. Split the features by semicolons
3. Split keys and values by '='
4. Create a temporary data frame with keys and value
5. Reshape

library(tidyverse)
library(urltools)
library(textclean)
test_df <- data.frame(pos=c(3002983, 3002881, 3010946, 3021775), metadata=c("ID=SRX661585;Name=Ctcf%20(@%20Brain);Title=GSM1446329:%20CTCF%20brain%20ChIPSeq%3B%20Mus%20musculus%20x%20Mus%20spretus%3B%20ChIP-Seq;Cell%20group=Neural;<br>source_name=Mouse%20adult%20hybrid%20brain;tissue=Adult%20whole%20brain;strain=BL6/spretus;chip%20antibody=CTCF,%20MillIpore%2007-729;", "ID=SRX2957352;Name=Smc1a%20(@%20AtT-20);Title=GSM2684855:%20SMC1%20ChIPseq%20in%20AtT-20%3B%20Mus%20musculus%3B%20ChIP-Seq;Cell%20group=Neural;<br>source_name=AtT-20%20cells;cell%20line=AtT-20%20cells;cell%20type=pituitary%20corticotroph%20cell;passages=10-20;chip%20antibody=Bethyl%20A300-055A;", "ID=SRX5287318;Name=Mecp2%20(@%20Olfactory%20Nerve);Title=GSM3577864:%20GSM1827604%20MeCP2%20ChIP%20WT%20rep1%3B%20Mus%20musculus%3B%20ChIP-Seq;Cell%20group=Neural;<br>source_name=Olfactory%20epithelium;strain/background=C57BL/6J;genotype/variation=Wild-type,%20MeCP2(%2B/y);age=8%20weeks;cell%20type=Olfactory%20sensory%20neuron;chip%20antibody=MeCP2%20antibody%20(Diagenode,%20pAb-052-050);", "ID=SRX5287318;Name=Mecp2%20(@%20Olfactory%20Nerve);Title=GSM3577864:%20GSM1827604%20MeCP2%20ChIP%20WT%20rep1%3B%20Mus%20musculus%3B%20ChIP-Seq;Cell%20group=Neural;<br>source_name=Olfactory%20epithelium;strain/background=C57BL/6J;genotype/variation=Wild-type,%20MeCP2(%2B/y);age=8%20weeks;cell%20type=Olfactory%20sensory%20neuron;chip%20antibody=MeCP2%20antibody%20(Diagenode,%20pAb-052-050);"))

tbl <- apply(test_df, 1, function(row){
  # Split by smicolons and clean html tags
  n <- replace_html(unlist(strsplit(row[2], ";")))
  # Decode urlencoded characters, mostly space
  n <- url_decode(n)
  # Split into keys and values
  s <- do.call(rbind, str_split(n, "="))
  # Get keys and values into vectors and clean up leading and trailing whitespace
  v <- s[, 2]
  v <- str_replace_all(v, "(^ +)|( +$)", "")
  k <- s[, 1]
  k <- str_replace_all(k, "(^ +)|( +$)", "")
  # Create final data frame with original columns (pos), and also columns from
  # keys and values
  tibble(pos = row[1], key = k, value = v)
})

# Concatenate list into tibble
(tbl <- do.call(rbind, tbl))

This tibble looks like:

# A tibble: 37 x 3
   pos     key           value                                                               
   <chr>   <chr>         <chr>                                                               
 1 3002983 ID            SRX661585                                                           
 2 3002983 Name          Ctcf (@ Brain)                                                      
 3 3002983 Title         GSM1446329: CTCF brain ChIPSeq; Mus musculus x Mus spretus; ChIP-Seq
 4 3002983 Cell group    Neural                                                              
 5 3002983 source_name   Mouse adult hybrid brain                                            
 6 3002983 tissue        Adult whole brain                                                   
 7 3002983 strain        BL6/spretus                                                         
 8 3002983 chip antibody CTCF, MillIpore 07-729                                              
 9 3002881 ID            SRX2957352                                                          
10 3002881 Name          Smc1a (@ AtT-20)                                                    
# … with 27 more rows

Using this, you can:

# Pivot wider
tbl %>% 
  pivot_wider(names_from = key, values_from = value)

Which nets you:

# A tibble: 4 x 15
  pos   ID    Name  Title `Cell group` source_name tissue strain `chip antibody` `cell line` `cell type`
  <chr> <chr> <chr> <chr> <chr>        <chr>       <chr>  <chr>  <chr>           <chr>       <chr>      
1 3002… SRX6… Ctcf… GSM1… Neural       Mouse adul… Adult… BL6/s… CTCF, MillIpor… NA          NA         
2 3002… SRX2… Smc1… GSM2… Neural       AtT-20 cel… NA     NA     Bethyl A300-05… AtT-20 cel… pituitary …
3 3010… SRX5… Mecp… GSM3… Neural       Olfactory … NA     NA     MeCP2 antibody… NA          Olfactory …
4 3021… SRX5… Mecp… GSM3… Neural       Olfactory … NA     NA     MeCP2 antibody… NA          Olfactory …
# … with 4 more variables: passages <chr>, `strain/background` <chr>, `genotype/variation` <chr>, age <chr>