LaVOZs

The World’s Largest Online Community for Developers

'; r - Changing cell values in a row conditioned on values of another row - LavOzs.Com

I'm trying to change a data frame with several thousands of rows that each look like one of the following variations:

table, th, td {
  border: 1px solid black
}
<table>
  <tr>
    <th> a </th>
    <th> b </th>
    <th> c </th>
  </tr>
  <tr>
    <td> $10 x and $7 y </td>
    <td> NA </td>
    <td> NA </td>
  </tr>
  <tr>
    <td> $70 a; $95 b </td>
    <td> NA </td>
    <td> NA </td>
  </tr>
  <tr>
    <td> $6 j </td>
    <td> NA </td>
    <td> NA </td>
  </tr>
</table>

Don't try and write code to parse HTML, just call an HTML parser:

library(rvest)
library(tidyverse)

stage1 <- 
  "<table>
  <tr>
    <th> a </th>
    <th> b </th>
    <th> c </th>
  </tr>
  <tr>
    <td> $10 x and $7 y </td>
    <td> NA </td>
    <td> NA </td>
  </tr>
  <tr>
    <td> $70 a; $95 b </td>
    <td> NA </td>
    <td> NA </td>
  </tr>
  <tr>
    <td> $6 j </td>
    <td> NA </td>
    <td> NA </td>
  </tr>
</table>" %>% 
  rvest::minimal_html() %>% 
  rvest::html_node("table") %>% 
  rvest::html_table() %>% 
  as_tibble()

stage1

# A tibble: 3 x 3
  a              b     c    
  <chr>          <lgl> <lgl>
1 $10 x and $7 y NA    NA   
2 $70 a; $95 b   NA    NA   
3 $6 j           NA    NA   

Now clean up stage1 using separate and a regex

stage1 %>% 
  select(a) %>% 
  separate(col = "a", into = c("b", "c"), 
           sep = "(?ix) \\s* (and|;) \\s*",   # Perl stye regex, cases insensitive.
           remove = FALSE, 
           fill= "right")


  a              b     c    
  <chr>          <chr> <chr>
1 $10 x and $7 y $10 x $7 y 
2 $70 a; $95 b   $70 a $95 b
3 $6 j           $6 j  NA   

You can use str_split_fixed from stringr

stringr::str_split_fixed(df$a, '\\s*(;|and)\\s*', 2)

#       [,1]    [,2]   
#[1,] "$10 x" "$7 y" 
#[2,] "$70 a" "$95 b"
#[3,] "$6 j"  ""