search for string in column names and retrieve corresponding value for each row

excel find text in range and return cell reference
search for a text string and return adjacent value
search for a text string and return multiple adjacent values
excel find text in range of cells
pandas get row with max value in column
excel check if cell contains text from list and return value
index match
excel search column for text and return value

I have a df like this:

id <- c("defoo","ghfoo","abfoo")
abc <- c(.3,.1,.4)
ghi <- c(.4,.2,.2)
abc_dif <- c(.4,.3,.8)
def_dif <- c(.5,.7,.6)
ghi_dif <- c(.2,.1,.9)
df <- data.frame(id,abc,ghi,abc_dif,def_dif,ghi_dif)

I want to look for columns whose names contain the first two characters of the value in the id row and also include "dif," and create a new column containing the corresponding values in those columns for each row.

In this sample data, the new column would be

df$result <- c(.5,.1,.8)

My numerous attempts involved various versions of sapply and apply, like the following attempt to simply get the column index:

df$result <- apply(substr(df[,which(colnames(df)=="id")],1,2),1,function(x) grep(x,colnames(df[which(grepl("dif",colnames(df),fixed=TRUE))]),fixed = TRUE))

This gives the error:

"Error in apply(substr(df[, which(colnames(df) == "id")], 1, 2), 1, function(x) grep(x,  : 
  dim(X) must have a positive length"

What is the best way to go about doing this?


We could create a row/column index to get the values

df$result <- df[4:6][cbind(1:nrow(df), match( substr(df$id, 1, 2),
                substr(names(df)[4:6], 1, 2)))]

df$result
#[1] 0.5 0.1 0.8

Search for a text string in a data set and return , How do I filter rows that contain a string using an Excel defined Table? Click checkbox if your dataset contains headers for each column. Search for a text string in a single column and return multiple corresponding values. How to search the particular string in the excel sheet and getting the row and column value of that particular string in the excel sheet using xlrd in python? Can any one help me please? import xlrd workbook = xlrd.open_workbook("1234.xls") worksheet = workbook.sheet_by_name('firstpage') only this much i tried


You can try a tidyverse

library(tidyverse)

df %>% 
  gather(k,v, -id:-ghi) %>% 
  filter(str_sub(id,1,2) == str_sub(k,1,2)) %>% 
  select(1,result=v) %>% 
  left_join(df, .)
     id abc ghi abc_dif def_dif ghi_dif result
1 defoo 0.3 0.4     0.4     0.5     0.2 0.5
2 ghfoo 0.1 0.2     0.3     0.7     0.1 0.1
3 abfoo 0.4 0.2     0.8     0.6     0.9 0.8

Pandas Select rows by condition and String Operations, Select all rows containing a sub string; Select rows by list of values Slice the column values; Search for a String in Dataframe and replace with Let's create a Dataframe with following columns: name, Age, Grade, Zodiac, City, Pahun all the characters are split by underscore in their respective columns. Retrieving Values Using References Based on Row and Column Numbers in Microsoft Excel 2010 To retrieve the values using references based on row & column numbers, we will use a combination of " INDIRECT"& "ADDRESS" functions to get the output.


You could loop through the df$id and then for each one, select the relevant cell in df:

df$result <- sapply(df$id, function(x) df[df$id == x,
                                          grepl(paste0(substring(x,1,2),".*dif"), names(df))])

df$result
#[1] 0.5 0.1 0.8

Use the Column Header to Retrieve Values from an Excel Table , Use the Column Header to Retrieve Values from an Excel Table One thing to notice is that each customer may appear on many rows. If you are not familiar with the SUMIFS function, please check out Multiple If you need to retrieve a text string, rather than the sum of numbers, you'll probably want to  How do I Vlookup a value and return a value in corresponding, adjacent cell in Excel. Look up Value and Return Adjacent Cell Value Assuming that you have a list of data in range A1:B8, and you need to vlookup one product name and return the adjacent sale value of Sales column.


Pandas: Find maximum values & position in columns or rows of a , Let's use this to find the maximum value among rows and columns, Get maximum values in every row & column of the Dataframe Instead of passing a single column name we can pass the list of column Strings in python. 1. Select the column range you want to look up value, and click Data > Filter. See screenshot: 2. Then click the arrow button in the first cell of you selected range, and check the value you want to look up only in the drop-down list. See screenshot: 3. Click OK, now you see only the look-up value and its corresponding values are filtered out.


Get all rows in a Pandas DataFrame containing given substring , Get all rows in a Pandas DataFrame containing given substring. Let's see how to Code #1: Check the values PG in column Position. filter_none Code #4: Filter rows checking Team name contains 'Boston and Position must be PG. filter_none last_page Python | Finding strings with given substring in list. Writing code  Select multiple row & columns by Labels in DataFrame using loc[] To select multiple rows & column, pass lists containing index labels and column names i.e. It will return a subset DataFrame with given rows and columns i.e. Only Rows with index label ‘b’ & ‘c’ and Columns with names ‘Age’, ‘Name’ are in returned DataFrame object.


colnames: Row and Column Names, Retrieve or set the row or column names of a matrix-like object. to simple numbers, if there are no dimnames or the corresponding component of the dimnames is NULL . For a data frame, value for rownames should be a character vector of abbreviate: Abbreviate Strings agrep: Approximate String Matching (Fuzzy  Using an approximate match, searches for the value 1 in column A, finds the largest value less than or equal to 1 in column A, which is 0.946, and then returns the value from column C in the same row.