Multiple columns with near identical names

[Emily1]

Hi,

I have these columns: ID, measurename_1, measurename_2, measurename_3 ... measurename_1001, measure_1, measure_2, measure_3 ... measure1001, date_1, date_2 ... date1001, datetarget

I would like to make new columns based on measurename, meaure, date and datanow. So if "date_x = datetarget - 10" and "measurename_x = length" then a new column named "date-10" will be added showing the observation from measure_x.
I want the code to check alle 1001 different columns of measurename, measure and date.

I hope this makes sense, I can't give a more precise example of data as they are classified.

I hope you can help me,
Thanks

[nirgrahamuk]

Hi!

To help us help you, could you please prepare a reproducible example (reprex) illustrating your issue? Please have a look at this guide, to see how to create one:

FAQ: How to do a minimal reproducible example ( reprex ) for beginners

A minimal reproducible example consists of the following items: A minimal dataset, necessary to reproduce the issue The minimal runnable code necessary to reproduce the issue, which can be run on the given dataset, and including the necessary information on the used packages. Let's quickly go over each one of these with examples: Minimal Dataset (Sample Data) You need to provide a data frame that is small enough to be (reasonably) pasted on a post, but big enough to reproduce your issue. Let's say, as an example, that you are working with the iris data frame head(iris) #> Sepal.Length Sepal.Width Petal.Length Petal.Width Species #> 1 5.1 3.5 1.4 0.…

[Emily1]

Hi,

I am pretty new to R and have some issues creating a new column based on conditions.

I have following dataset:

#Dataset

id<-c(1,2,3,4,5)
measurename_1<-c("height","lenght","length","height","lenght")
measurename_2<-c("height","lenght","height","lenght","height")
measurename_3<-c("lenght","height","lenght","height","height")
measure_1<-c(10,40,41,6,100)
measure_2<-c(40,2,13,400,12)
measure_3<-c(19,719,32,2,129)
date_1<-as.Date(c("11-02-2001","04-04-2005","11-02-2001","02-05-2010","20-11-2019"),"%d-%m-%Y")
date_2<-as.Date(c("11-02-2001","05-06-2006","12-03-2004","19-01-2013","20-11-2019"),"%d-%m-%Y")
date_3<-as.Date(c("05-06-2006","04-04-2014","31-12-2018","29-10-2013","30-07-2019"),"%d-%m-%Y")
datetarget<-as.Date(c("21-02-2001","23-03-2006","10-01-2019","29-01-2013","30-11-2019"),"%d-%m-%Y")

data<-data.frame(id,measurename_1,measurename_2,measurename_3,measure_1,measure_2,measure_3,date_1,date_2,date_3,datetarget)

head(data)
id measurename_1 measurename_2 measurename_3 measure_1 measure_2 measure_3 date_1 date_2 date_3 datetarget
1 1 height height lenght 10 40 19 2001-02-11 2001-02-11 2006-06-05 2001-02-21
2 2 lenght lenght height 40 2 719 2005-04-04 2006-06-05 2014-04-04 2006-03-23
3 3 length height lenght 41 13 32 2001-02-11 2004-03-12 2018-12-31 2019-01-10
4 4 height lenght height 6 400 2 2010-05-02 2013-01-19 2013-10-29 2013-01-29
5 5 lenght height height 100 12 129 2019-11-20 2019-11-20 2019-07-30 2019-11-30

I want to make a new column called "day-10". I want this column to show measure_x when date_x = datetarget-10 (datetarget substracted 10 days) and measurename_x = length.

I want the code to search all 3 columns (_1, _2, _3) of measurename, measure and date. Is this possible?

I have no clue which package or function to do to accomplish this.

I hope you can help

Thank you

[nirgrahamuk]

not pretty but it works.

library(tidyverse)
library(glue)
data <- tibble::tribble(
  ~id, ~measurename_1, ~measurename_2, ~measurename_3, ~measure_1, ~measure_2, ~measure_3,      ~date_1,      ~date_2,      ~date_3,  ~datetarget,
  1,       "height",       "height",       "length",         10,         40,         19, "2001-02-11", "2001-02-11", "2006-06-05", "2001-02-21",
  2,       "length",       "length",       "height",         40,          2,        719, "2005-04-04", "2006-06-05", "2014-04-04", "2006-03-23",
  3,       "length",       "height",       "length",         41,         13,         32, "2001-02-11", "2004-03-12", "2018-12-31", "2019-01-10",
  4,       "height",       "length",       "height",          6,        400,          2, "2010-05-02", "2013-01-19", "2013-10-29", "2013-01-29",
  5,       "length",       "height",       "height",        100,         12,        129, "2019-11-20", "2019-11-20", "2019-07-30", "2019-11-30"
) %>% mutate(across(starts_with("date"),
                    as.Date)) %>% as.data.frame()
# So if "date_x = datetarget - 10" and "measurename_x = length"
# then a new column named "date-10" will be added showing the observation from measure_x.

mymake <- function(days,nums){
  walk(
    nums,
    ~ {
      data[, glue("d_{days}_{.x}")] <<-
        ifelse(data[, glue("date_{.x}")] == data[, "datetarget"] - days & 
                 data[, glue("measurename_{.x}")] == "length",
               data[, glue("measure_{.x}")], NA
        )
    }
  )
  data
}
mymake(10,1:3)
#coalescer
mycoal <- function(name,nums){
  
  data[, glue("{name}")] <<- eval(str2expression(
    glue('coalesce({paste0("data$",name,"_",nums,collapse = ",")})')
    ))
  
  eval(str2expression(paste0("data$",name,"_",nums,"<<- NULL",collapse = ";")
     ))
  data
  }
mycoal("d_10",1:3)

[Emily1]

This is great, thank you!!!