Search code examples
rreshape2

loop through df, grep, then split


I want to loop through several dataframes, and certain columns within each dataframe, and separate columns with numbers in brackets with asterisks into two columns. For instance, splitting [123]*** into a column with 123 (no brackets) and column with ***. Here's what I've tried:

require(reshape2)
# create dataframes
  df1 <- data.frame(var=c("item1", "item2"),
                    var.se.1=c("[123]**", "[456]"),
                    var.se.2=c("[1]***", "[45]*"))
  df2 <- data.frame(var=c("item3", "item4"),
                    var.se.1=c("[7]*", "[89]***"),
                    var.se.2=c("[34]**", "[2]"))

I have:

# df1
#     var var.se.1 var.se.2
# 1 item1  [123]**   [1]***
# 2 item2    [456]    [45]*

I want:

#     var var.se.1 var.se.1.ast var.se.2 var.se.2.ast
# 1 item1      123           **        1          ***
# 2 item2      456                    45            *

I tried:

# create list of dataframes
  dfs <- list(df1, df2)
# loop through dataframes
  for (i in 1:length(dfs)) {
    # index columns with .se in the name
      seCols <- grep(".se", names(dfs[[i]]))
    # loop through every column with .se in the name
      for (s in seCols) {
        # remove [, which will leave ] to split on
        # want to get rid of [] anyway
          dfs[[i]][,s] <- gsub("\\[", "", as.character(dfs[[i]][,s]))
        # split into two columns on ]
          dfs[[i]] <- cbind(dfs[[i]],
                            colsplit(dfs[[i]][,s],
                                     pattern = "\\]",
                                     names = c(names(dfs[[i]][s]),
                                               paste(names(dfs[[i]][s]),
                                                     "ast", sep="."))))
      }
  }

The code is mostly doing what I want it to do, but the results are not being stored in the dataframe. If I run the loop and then run dfs[[i]], so when i==2, I get the following:

# i == 2
dfs[[i]]
    var var.se.1 var.se.2 var.se.1 var.se.1.ast var.se.2 var.se.2.ast
1 item3      7]*    34]**        7            *       34           **
2 item4   89]***       2]       89          ***        2 

I need to delete columns 2 and 3, but other than that, it works. Just need to get columns 1, 4, 5, 6, and 7 (column naming is correct) into df1 and df2 (df2 in this case).

UPDATE

My actual use case is more complex than the MRE and breaks @Tyler's code. The problem seems to be related to my real dataframes having different numbers of observations. If we redefine df2 to have 3 rows, and leave df2 to have 2 rows, R will throw an error when running @Tyler's code: "Error in data.frame(..., check.names = FALSE) : arguments imply differing number of rows: 2, 3"

df2 <- data.frame(var=c("item3", "item4", "item5"),
                  var.se.1=c("[7]*", "[89]***", "new"),
                  var.se.2=c("[34]**", "[2]", "rows"))

Solution

  • Here's one approach using the qdap package. I made some tweaks based on the OP's comments. I'd operate out of the list but if you wanted them to be in the global environment I provide that as well.

    L1 <- setNames(list(df1, df2), c("df1", "df2"))
    
    library(qdap)
    
    bot_scum <- function(x) identical(character(0), x)
    FUN <- function(x) {
        y <- bracketXtract(x)
        y[sapply(y, bot_scum)] <- NA
        as.numeric(unlist(y))
    }
    FUN2 <- function(x) gsub("[^*]", "", x)
    FUN3 <- function(x) cbind.data.frame(FUN(x), FUN2(x))
    
    (L2 <- lapply(L1, function(x) {
        inds <- grep(".se", colnames(x), fixed=TRUE)
        ninds <- grep(".se", colnames(x), fixed=TRUE, invert=TRUE)
    
        out <- do.call(cbind.data.frame, lapply(inds, function(i) {
            setNames(FUN3(x[, i]), c(colnames(x)[i], paste0(colnames(x)[i], ".ast")))
        }))
    
        cbind.data.frame(x[, ninds, drop=FALSE],out)
    }))
    
    list2env(L2, envir = .GlobalEnv)
    df1
    df2
    
    ## $df1
    ##     var var.se.1 var.se.1.ast var.se.2 var.se.2.ast
    ## 1 item1      123           **        1          ***
    ## 2 item2      456                    45            *
    ## 
    ## $df2
    ##     var var.se.1 var.se.1.ast var.se.2 var.se.2.ast
    ## 1 item3        7            *       34           **
    ## 2 item4       89          ***        2