Subset Columns based on partial matching of column names in the same data frame

Question

I would like to understand how to subset multiple columns from same data frame by matching the first 5 letters of the column names with each other and if they are equal then subset it and store it in a new variable.

Here is a small explanation of my required output. It is described below,

Lets say the data frame is eatable

fruits_area   fruits_production  vegetable_area   vegetable_production 

12             100                26               324
33             250                40               580
66             510                43               581

eatable <- data.frame(c(12,33,660),c(100,250,510),c(26,40,43),c(324,580,581))
names(eatable) <- c("fruits_area", "fruits_production", "vegetables_area",
          "vegetable_production")

I was trying to write a function which will match the strings in a loop and will store the subset columns after matching first 5 letters from the column names.

checkExpression <- function(dataset,str){
    dataset[grepl((str),names(dataset),ignore.case = TRUE)]
}

checkExpression(eatable,"your_string")

The above function checks the string correctly but I am confused how to do matching among the column names in the dataset.

Edit:- I think regular expressions would work here.

Using `dplyr`, I believe you just want `select(eatable, starts_with("fruit"))`. — aichao, Oct 23 '16 at 12:55
@aichao I tried that. Your suggestion is good but what I want is automatic checking of column names in the data frame if they match, subset. — WaterRocket8236, Oct 23 '16 at 12:57
Then you can use `grepl` as you did and use the result from that to `subset` your columns `subset(dataset,select=colnames(dataset)[cols])` where `cols` is output from the `grepl` — aichao, Oct 23 '16 at 13:04

Steven Beaupré · Accepted Answer · 2018-07-17T11:39:43.183

4

You could try:

v <- unique(substr(names(eatable), 0, 5))
lapply(v, function(x) eatable[grepl(x, names(eatable))])

Or using map() + select_()

library(tidyverse)
map(v, ~select_(eatable, ~matches(.)))

Which gives:

#[[1]]
#  fruits_area fruits_production
#1          12               100
#2          33               250
#3         660               510
#
#[[2]]
#  vegetables_area vegetable_production
#1              26                  324
#2              40                  580
#3              43                  581

Should you want to make it into a function:

checkExpression <- function(df, l = 5) {
  v <- unique(substr(names(df), 0, l))
  lapply(v, function(x) df[grepl(x, names(df))])
}

Then simply use:

checkExpression(eatable, 5)

edited Jul 17 '18 at 11:39

answered Oct 23 '16 at 13:06

Steven Beaupré

21,343
7
57
77

Nice idea! I thought `substr` is vectorized :) – Sotos Oct 23 '16 at 13:10
Indeed. No need for dplyr for just one functtion – Sotos Oct 23 '16 at 13:15
1

@Sotos Yup. I guess I'll just leave it as an alternative to base R. It might come in handy if part of a more complex pipeline. – Steven Beaupré Oct 23 '16 at 13:16
1

@StevenBeaupré Thank you. It is clear now from your answer and aichao 's answer. – WaterRocket8236 Oct 23 '16 at 13:59

score 1 · Answer 2 · answered Oct 23 '16 at 13:07

I believe this may address your needs:

checkExpression <- function(dataset,str){
  cols <- grepl(paste0("^",str),colnames(dataset),ignore.case = TRUE)
  subset(dataset,select=colnames(dataset)[cols])
}

Note the addition of "^" to the pattern used in grepl.

Using your data:

checkExpression(eatable,"fruit")
##  fruits_area fruits_production
##1          12               100
##2          33               250
##3         660               510
checkExpression(eatable,"veget")
##  vegetables_area vegetable_production
##1              26                  324
##2              40                  580
##3              43                  581

Thank you. It works. But Steven's answer is more automated. – WaterRocket8236 Oct 23 '16 at 14:06 — WaterRocket8236, Oct 23 '16 at 14:06

score 0 · Answer 3 · answered Oct 23 '16 at 13:11

0

Your function does exactly what you want but there was a small error:

checkExpression <- function(dataset,str){
  dataset[grepl((str),names(dataset),ignore.case = TRUE)]
}

Change the name of the object from which your subsetting from obje to dataset.

checkExpression(eatable,"fr")
#  fruits_area fruits_production
#1          12               100
#2          33               250
#3         660               510

checkExpression(eatable,"veg")
#  vegetables_area vegetable_production
#1              26                  324
#2              40                  580
#3              43                  581

answered Oct 23 '16 at 13:11

cimentadaj

1,414
10
23

Actually I corrected it in my local R script and getting similar output as yours but there was a mistake while typing it while asking the question. However I edited it and correct the same thing in question. Thank you for noticing. But my question starts after getting the similar output as yours. :) – WaterRocket8236 Oct 23 '16 at 13:26
Sorry, I misunderstood the question. – cimentadaj Oct 23 '16 at 13:41

Subset Columns based on partial matching of column names in the same data frame

3 Answers3