how to create a calculated column from another data table using script

[Ahmed Ali Shah]

I have 2 Data Tables.

Table 1: Table 2:

Days StartTime EndTime value

9/2/2020 00:00:00 9/2/2020 00:01:00 9/2/2020 00:03:00 123

9/2/2020 00:01:00 9/2/2020 00:04:00 9/2/2020 00:09:00 456

9/2/2020 00:02:00 9/2/2020 02:01:00 9/2/2020 03:03:00 789

9/2/2020 00:03:00 9/2/2020 11:33:00 9/2/2020 23:59:00 987

9/2/2020 00:04:00

9/2/2020 00:05:00

.

.

.

9/2/2020 23:59:00

 

I want to create another column 'Val1' in Table 1 based on condition"StartTime = EndTime"and if condition is satisfied then fill with Column "Values".

 

Expected Answer is

Table1:

Days Val1

9/2/2020 00:00:00 123

9/2/2020 00:01:00 123

9/2/2020 00:02:00 123

9/2/2020 00:03:00 123

9/2/2020 00:04:00 456

9/2/2020 00:05:00 456

.

.

.

9/2/2020 23:59:00 987

[Fabian Duerr]

Be careful with your condition: "StartTime <= Days >= EndTime" should be changed to "StartTime < Days >= EndTime" or to "StartTime <= Days > EndTime". Furthermore you should not get any match for your first data 9/2/2020 00:00:00 because it doesn't fulfill the condition.

 

Do you need an entire script Or is it sufficient if I explain the approach

[Ahmed Ali Shah]

Searching

[Fabian Duerr]

Have you worked with R data functions so far If yes then you can use the following approach:

 

Inputs: your two tables

 

Output: a new table

 

Here's an example that you can run with RStudio

 

#input: table1
df1 <- data.frame (ColA = seq(10))

#input: table2
df2 <- data.frame (Start = c(1,5,9), End = c(4,8,10), Val = c(11,13,15))

#output: table3. Initialized with column from table one and dummy entry 1 
df3 <- data.frame (ColA = df1$ColA, Val = c(1))

#lenght of table1
cnt <- nrow(df1)

library(dplyr)

#loop thru all values of table1 and use them to filter table2. store output to table3
for (i in seq(cnt)){
 #get value of 1st column
 x <- df1[i,1]
 
 #filter table2 to this value with your condition
 temp <- df2 %>% filter(x >= Start & x <= End)
 
 #make sure that condition returns some value. Else return NA
 if (nrow(temp) > 0){
   Val <- temp %>% select(Val)
 } else {
   Val <- NA
 }
 
 #store values to table3
 df3[i,1] <- x
 df3[i,2] <- Val
}
# end of script

[Ahmed Ali Shah]

Any alternative to this,as I dont have permission to deply the package dplyr. By using native packages in TERR or pssibly python or using a calculated column.

[Ahmed Ali Shah]

Is there any R script for this Or any other way also works

Date table 1

Col1

 

11/14/2018 5:00:00 AM

 

11/14/2018 5:01:00 AM

 

11/14/2018 5:02:00 AM

 

11/14/2018 5:03:00 AM

 

11/14/2018 5:04:00 AM

 

11/14/2018 5:05:00 AM

 

11/14/2018 5:06:00 AM

 

11/14/2018 5:07:00 AM

 

11/14/2018 5:08:00 AM

 

11/14/2018 5:09:00 AM

 

11/14/2018 5:10:00 AM

 

11/14/2018 5:11:00 AM

 

11/14/2018 5:12:00 AM

 

11/14/2018 5:13:00 AM

 

11/14/2018 5:14:00 AM

 

11/14/2018 5:15:00 AM

 

11/14/2018 5:16:00 AM

 

11/14/2018 5:17:00 AM

 

11/14/2018 5:18:00 AM

 

11/14/2018 5:19:00 AM

 

11/14/2018 5:20:00 AM

 

11/14/2018 5:21:00 AM

 

11/14/2018 5:22:00 AM

 

11/14/2018 5:23:00 AM

 

11/14/2018 5:24:00 AM

 

11/14/2018 5:25:00 AM

 

11/14/2018 5:26:00 AM

 

11/14/2018 5:27:00 AM

 

11/14/2018 5:28:00 AM

 

11/14/2018 5:29:00 AM

 

11/14/2018 5:30:00 AM

 

.

 

.

 

.

 

11/15/2018 4:59:00 AMDataTable2

 

StartTime                               EndTime                       Value

 

11/14/2018 5:00:00 AM 11/14/2018 5:00:20 AM 3218.248

 

11/14/2018 5:00:22 AM 11/14/2018 5:39:30 AM 3257.966

 

11/14/2018 5:39:32 AM 11/14/2018 5:39:58 AM 3257.973

 

11/14/2018 5:40:00 AM 11/14/2018 5:49:28 AM 3257.973

 

11/14/2018 5:49:30 AM 11/14/2018 5:50:28 AM 3257.973

 

11/14/2018 5:50:30 AM 11/14/2018 5:52:58 AM 3193.992

 

11/14/2018 5:53:00 AM 11/14/2018 5:53:36 AM 3198.542

 

11/14/2018 5:53:38 AM 11/14/2018 6:02:52 AM 3198.558

 

11/14/2018 6:02:54 AM 11/14/2018 6:04:58 AM 3101.826

 

11/14/2018 6:05:00 AM 11/14/2018 6:10:22 AM 3104.928

 

11/14/2018 6:10:24 AM 11/14/2018 6:12:24 AM 3009.105

 

11/14/2018 6:12:26 AM 11/14/2018 6:12:54 AM 3011.392

 

11/14/2018 6:12:56 AM 11/14/2018 6:17:52 AM 3011.878

 

11/14/2018 6:17:54 AM 11/14/2018 6:21:36 AM 2915.363

 

11/14/2018 6:21:38 AM 11/14/2018 6:22:54 AM 2925.756

 

11/14/2018 6:22:56 AM 11/14/2018 6:24:20 AM 2915.662

 

11/14/2018 6:24:34 AM 11/14/2018 6:27:32 AM 2917.891

 

.

 

.

 

.Expected Output:Hour Min    Value

 

5        0    3260.347

 

5       1     3260.347

 

5       2    3260.347

 

5       3   3260.347

 

5       4   3260.347

 

5       5   3260.347

 

5       6   3260.347

 

5       7   3260.347

 

5       8   3260.347

 

5       9    3260.347

 

5      10   3260.347

 

5       11     3260.347

 

5       12    3260.347

 

5       13 3260.347

 

5           14 3260.347

 

5           15 3260.347

 

5      16 3260.347

 

5      17 3260.347 

[Fabian Duerr]

When you work with dates you might have to use the POSIXct format for all date columns to get the comparison work correctly. 

 

You can set the format like this: data.frame$column <- as.POSIXct(data.frame$column, format = ...)

 

Example: as.POSIXct("11/14/2018 6:10:24 AM", format = "%m/%d/%Y %I:%M:%S %p")