Resolved: I need to copy numbers between two text strings in column, "transpose" it to columns next to the first text string

Question:

I need to copy numbers between two text strings in column, “transpose” it to columns next to the first text string and repeat through the rest of the column. Dataset has hundreds of rows.
The “alignment” (like putting NAs for correcting positions of numbers) is not necessary, transposing would be more than enough.
input:

df1 <- structure(list(column1 = c("STOUT", "18", "9341", "4", "0,2005", 
"STOUT", "1", "9341", "25", "0,2004", "STIN", "7", "9341", "0,2003", 
"OFF", "7", "L(1)", "9342", "0,2005")), class = "data.frame", row.names = c(NA, 
-19L))

> print(df)
       column1
    1    STOUT
    2       18
    3     9341
    4        4
    5   0,2005
    6    STOUT
    7        1
    8     9341
    9       25
    10  0,2004
    11    STIN
    12       7
    13    9341
    14  0,2003
    15     OFF
    16       7
    17    L(1)
    18    9342
    19  0,2005

desired output:

df2 <- structure(list(column1 = c("STOUT", "STOUT", "STIN", "OFF", "L(1)"
), column2 = c(18L, 1L, 7L, 7L, NA), column3 = c(9341L, 9341L, 
9341L, NA, 9342L), column4 = c(4L, 25L, NA, NA, NA), column5 = c(0.2005, 
0.2004, 0.2003, NA, 0.2005)), class = "data.frame", row.names = c(NA, 
-5L))

> print(df2)
  column1 column2 column3 column4 column5
1   STOUT      18    9341       4  0.2005
2   STOUT       1    9341      25  0.2004
3    STIN       7    9341      NA  0.2003
4     OFF       7      NA      NA      NA
5    L(1)      NA    9342      NA  0.2005

I was thinking along Extracting a string between other two strings in R
But did not make much progress :-/
Thanks in advance.

Answer:

Split on a regex-based logical:
(I defined the break points based on the presence of capital letters (i.e. [A-Z]); you may want to modify the pattern based on your expected break points in df1$column1.)

a <- split(df1$column1, cumsum(grepl('[A-Z]', df1$column1)))
a
$`1`
[1] "STOUT"  "18"     "9341"   "4"      "0,2005"

$`2`
[1] "STOUT"  "1"      "9341"   "25"     "0,2004"

$`3`
[1] "STIN"   "7"      "9341"   "0,2003"

$`4`
[1] "OFF" "7"  

$`5`
[1] "L(1)"   "9342"   "0,2005"
[/code]

</p>



Then <code>rbind()</code> and fill with <code>NA</code>:<br> 



(<code>plyr::rbind.fill()</code> expects a dataframe, so I'm using <code>lapply()</code> to call <code>as.data.frame()</code> to each list element.)<br> 


<p>[code]library(plyr)</p>
<p>plyr::rbind.fill(lapply(a,function(y){as.data.frame(t(y),stringsAsFactors=FALSE)}))<br />
     V1   V2     V3     V4     V5<br />
1 STOUT   18   9341      4 0,2005<br />
2 STOUT    1   9341     25 0,2004<br />
3  STIN    7   9341 0,2003   <NA><br />
4   OFF    7   <NA>   <NA>   <NA><br />
5  L(1) 9342 0,2005   <NA>   <NA>

If you have better answer, please add a comment about this, thank you!

Resolved: I need to copy numbers between two text strings in column, “transpose” it to columns next to the first text string

Resolved: navigate and redirect not redirecting to path in react router

Resolved: Time Complexity: Find the number of people who know a secret leetcode 2327

Resolved: Best way to create 3d matrix of variables in PULP

Leave A Reply