Is there an R function to make subset by part of column name?

user_9923957 0
Viewed 146

I'm looking for a function in order to split a dataframe in several dataframe by the end of column names. To take an example : 

Year | hour | LOT | S123_AA | S135_AA | S1763_BB | S173_BB | ...

So I want to split it into 2 dataframes as:

Year | hour | LOT | S123_AA | S135_AA |

and 

Year | hour | LOT | S1763_BB | S173_BB |

My key point is to conserve the first 3 columns and append all columns where the end names is _AA and _BB.

Thanks for your time

dataframe r
5 Answers

You can just take the right subset using grep.

df_AA = df[,c(1:3, grep("_AA$", colnames(df)))]
df_BB = df[,c(1:3, grep("_BB$", colnames(df)))]
user_47526750

Here's a basic answer, your use case may require more sophisticated regular expressions inside the grepl() call, but this should get you on the right path:

#make some sample data
x <- data.frame(Year = rnorm(3), hour = rnorm(3), LOT =  rnorm(3),S123_AA =  rnorm(3),S135_AA =  rnorm(3),S1763_BB =   rnorm(3),S173_BB =  rnorm(3))

#list the common columns
common_cols <- c("Year", "hour", "LOT")
#use grepl() to subset the columns that contain AA or BB
aa_cols <- names(x)[grepl("AA", names(x))]
bb_cols <- names(x)[grepl("BB", names(x))]

#create two new data frames
x_a <- x[, c(common_cols, aa_cols)]
x_b <- x[, c(common_cols, bb_cols)]
user_4156350

One way is to exclude the columns you do not want.

i <- grep("_AA$", names(df1))
j <- grep("_BB$", names(df1))

dfA <- df1[, -j]    # Exclude the 'BB' columns
dfB <- df1[, -i]    # Exclude the 'AA' columns

Using the same exclusion principle, but with the tidyverse.

library(tidyverse)

df1 %>%
  select(names(.)[!grepl("_BB$", names(.))])

df1 %>%
  select(names(.)[!grepl("_AA$", names(.))])

This can become more the tidyverse way with the suggestion in user NColl's comment.

df1 %>% select(-ends_with('_BB'))

df1 %>% select(-ends_with('_AA'))

Data. 

df1 <- as.data.frame(matrix(1:49, ncol = 7))
nms <- scan(what = character(), sep = "|",
            text = "Year | hour | LOT | S123_AA | S135_AA | S1763_BB | S173_BB ")
names(df1) <- trimws(nms)
user_82454060

If you have a bunch of groups (as the ... in your question suggests), you might want to use lapply to avoid supplying the regex for each group:

# Sample data
df <- data.frame(
  Year = rnorm(3), 
  hour = rnorm(3), 
  LOT = rnorm(3), 
  S123_AA = rnorm(3), 
  S135_AA = rnorm(3), 
  S1763_BB = rnorm(3), 
  S173_BB = rnorm(3)
)

# Our groups
groups <- unique(gsub(".*_", "", names(df[grep("_", colnames(df))])))
groups
#> [1] "AA" "BB"

# Our group regex's
groupx <- paste0("_", groups, "$")
groupx
#> [1] "_AA$" "_BB$"

lapply(groupx, function(x) df[, c(1:3, grep(x, colnames(df)))])
#> [[1]]
#>          Year       hour       LOT   S123_AA     S135_AA
#> 1  0.07940092 -1.2628189  1.629389 -1.376438 -0.94292025
#> 2 -2.04122298  0.7471061  0.291170 -2.126642  0.24355149
#> 3  0.11448519  0.1710263 -0.736140 -1.087515 -0.07720119
#> 
#> [[2]]
#>          Year       hour       LOT   S1763_BB    S173_BB
#> 1  0.07940092 -1.2628189  1.629389 -0.3593335 0.64176748
#> 2 -2.04122298  0.7471061  0.291170  1.7928938 0.36021859
#> 3  0.11448519  0.1710263 -0.736140 -0.7853338 0.01439278

Created on 2018-12-31 by the reprex package (v0.2.1)

user_73620460

Use ! and grepl() to filter the columns.

  • NOT BB means AA and columns 1~3 will be retained
  • NOT AA means BB and columns 1~3 will be retained
A <- ! grepl("BB", names(df))
B <- ! grepl("AA", names(df))

df[, A]
#   Year hour LOT S123_AA S135_AA
# 1    1    8  15      22      29
# 2    2    9  16      23      30
# 3    3   10  17      24      31

df[, B]
#   Year hour LOT S1763_BB S173_BB
# 1    1    8  15       36      43
# 2    2    9  16       37      44
# 3    3   10  17       38      45
user_100689850
Related