=, >, " />
logo
down
shadow

Matching values between data frames based on overlapping dates


Matching values between data frames based on overlapping dates

By : peleg
Date : November 21 2020, 03:00 PM
This might help you You may consider data.table which allows for "non-equi joins", i.e. joins based on >=, >, <= and <. In the same call, aggregate operations may be performed on the groups in the LHS data set that each row in the RHS data set (i) matches (by = .EACHI).
code :
d1[d2, on = .(id = id, end >= begin),
         .(i.begin, i.end, val_str = toString(val)), by = .EACHI]

#    id        end    i.begin      i.end    val_str
# 1:  5 2017-03-03 2017-03-03 2017-03-05 Cat3, Cat1
# 2:  6 2017-05-03 2017-05-03 2017-05-05         NA
# 3:  8 2017-03-03 2017-03-03 2017-03-05         NA
# 4: 10 2017-12-05 2017-12-05 2017-12-06       Cat4
d1 <- data.frame(id = c(5, 10, 5, 10),
                 begin = as.Date(c('1990-3-1','1993-12-1','1991-3-1','1995-12-5')),
                 end = as.Date(c('2017-3-10','2017-12-2','2017-3-3','2017-12-10')),
                 val = c("Cat1", "Cat2", "Cat3", "Cat4"))

d2 <- data.frame(id = c(5, 6, 8, 10),
                 begin = as.Date(c('2017-3-3','2017-5-3','2017-3-3','2017-12-5')),
                 end = as.Date(c('2017-3-5','2017-5-5','2017-3-5','2017-12-6')))

library(data.table)
setDT(d1)
setDT(d2)


Share : facebook icon twitter icon
How to merge two data frames with non overlapping dates?

How to merge two data frames with non overlapping dates?


By : swen
Date : March 29 2020, 07:55 AM
help you fix your problem Since you mentioned that your final data set should have 17568 (=4608+12960) observations/rows, I assume you want to stack the two data.frames over each other (and possibly order them by date afterwards). This is done by using rbind().
code :
finaldata <- rbind(weekdays, weekend)
finaldata$X <- NULL
finaldata$date <- as.Date(finaldata$date, format="%Y-%m-%d")
finaldata <- finaldata[order(finaldata$date),]
Matching factor levels from seperate data frames based on dates

Matching factor levels from seperate data frames based on dates


By : Shahbaz Hussain
Date : March 29 2020, 07:55 AM
wish helps you I'm sure there is an easy solution to this (I can't seem to find a cut.date solution that works). I have one dataframe whereby monthly values have been classified into discrete categories. , Try this solution using merge:
code :
out<-merge(df,Monthly,by.x=substr(as.character("daily"),1,7),by.y=substr(as.character("Date"),1,7))
head(out,10)
        daily values Category
1  2010-01-01     29        A
2  2010-02-01    132        A
3  2010-03-01     60        A
4  2010-04-01     96        A
5  2010-05-01     69        A
6  2010-06-01     18        A
7  2010-07-01     47        A
8  2010-08-01    133        B
9  2010-09-01    121        B
10 2010-10-01     73        B
df$id<-substr(as.character(df$daily),1,7)
Monthly$id<-substr(as.character(Monthly$Date),1,7)
library(plyr)
out<-join(df, Monthly, by="id",type="full")
head(out[,c(2,1,5)])
daily values Category
1 2010-03-12     94        A
2 2010-03-12     73        A
3 2010-03-12    132        A
4 2010-03-12     94        A
5 2010-03-12    164        A
6 2010-03-12     81        A
Is there a way to compare two data frames of a different length, based on matching column values?

Is there a way to compare two data frames of a different length, based on matching column values?


By : user2709019
Date : March 29 2020, 07:55 AM
Hope this helps Here's an example of doing a join between two tables to see if they match in score.x where the other columns match.
The "left_join" takes each row from the first table and outputs a row for each match that is found in the second table, defined by the columns named in the by = c("id", "name", "year") part. Since you'll now have two versions of score.x, the original from df1 is renamed score.x.x and the one from df2 is renamed score.x.y.
code :
library(dplyr)
df1 %>%          # (Note, I've modified df1 to be "oranges" so we'll have matches)
  left_join(     # Keep everything in df1 and connect to each matching row in...
    df2,                          # df2, defined by matching...
    by = c("id", "name", "year")  # id, name, and year
  ) %>%
  mutate(match = score.x.x == score.x.y)  # ...and say whether they match

# Here's the output
  id   name score.x.x year score.x.y match
1  1 orange         1 2000         2 FALSE
2  2 orange         3 2001         3  TRUE
3  3 orange         2 2002         1 FALSE
df1 <- data.frame(
  "id" = 1:3,
  "name" = c('orange', 'orange', 'orange'),  # Changed to make matches
  "score.x" = c(1, 3, 2),
  "year" = c(2000, 2001, 2002)
)

df2 <- data.frame(
  "id" = c(1, 2, 3, 1, 2, 3, 1, 2, 3, 1, 2, 3),
  "name" = c('orange', 'orange', 'orange', 'melon', 'melon', 'melon', 'grapes', 'grapes', 'grapes', 'lemon', 'lemon', 'lemon'),
  "score.x" = c(2, 3, 1, 1, 1, 2, 3, 3, 2, 1, 1, 1),
  "year" = c(2000, 2001, 2002, 2000, 2001, 2002, 2000, 2001, 2002, 2000, 2001, 2002)
)
Matching values by nearest POSIXct in two data frames keeping both dates

Matching values by nearest POSIXct in two data frames keeping both dates


By : user3341901
Date : March 29 2020, 07:55 AM
hope this fix your issue Here are some approaches to match each row in DF1 to the closest row in DF2 by date.
1) sqldf
code :
library(sqldf)
sqldf("select *, min(abs(date2 - date1)) AbsDiff, date2 - date1 TimeDiff
       from DF1 a join DF2 b group by a.rowid")
       date1 Num1      date2 Num2 AbsDiff TimeDiff
1 2013-08-03    1 2013-08-06    1  259200   259200
2 2013-09-04    2 2013-09-12    3  691200   691200
3 2013-09-08    3 2013-09-12    3  345600   345600
4 2013-09-12    4 2013-09-12    3       0        0
5 2013-11-01    5 2013-09-20    2 3628800 -3628800
abs.min <- function(d1) which.min(abs(d1 - DF2$date2))
transform(cbind(DF1, DF2[sapply(DF1$date1, abs.min), ]), 
          TimeDiff = as.numeric(date2 - date1))
         date1 Num1      date2 Num2 TimeDiff
1   2013-08-03    1 2013-08-06    1   259200
3   2013-09-04    2 2013-09-12    3   691200
3.1 2013-09-08    3 2013-09-12    3   345600
3.2 2013-09-12    4 2013-09-12    3        0
2   2013-11-01    5 2013-09-20    2 -3628800
R - conditionally matching variable values between data frames based on occurrence of regular expression

R - conditionally matching variable values between data frames based on occurrence of regular expression


By : Aaron Sloman
Date : March 29 2020, 07:55 AM
help you fix your problem I've hit a bit of a mental block trying to match values between two datasets. Here's an extract of what my data looks like: , First of all, build correctly your data.frames! It should simply be:
Related Posts Related Posts :
  • Authentication failure with rdrop2
  • DT data table display error
  • Issue when adding new rows (with nested dataframes within) to a dataframe
  • R-How to compare two dataframe and update list column value
  • Series vector for approximating pi
  • what is difference between "variance explained " in Random Forest and "merror" in XGBoost
  • R - Cast dataframe on unique rows - reshape2
  • ggplot2: plot correct proportions using geom_bar
  • Speedup query for R data.table - can this two-argument function be applied by group more quickly?
  • apply a function to several columns at once with mutate
  • R 'cowplot' neatly produce gridded plot with shared (common) legends and unique legends
  • Repeat R script for many times and save results to text file
  • How to negative lookbehind for special characters
  • data.table inner join produces error when no match is found
  • Create a new column base on existing column, but row above
  • Is there a way to visualize the process of source() in RStudio?
  • google places api consumes 10 request but I am doing only 1
  • Statistical mode of a categorical variable in R (using mlv)
  • Using for-loop to mutate a data.frame in r
  • Make plot with regression line for mixed model
  • Shortcut to select matces cases in R studio
  • vectoriced norm/matrix multiplication
  • Negative log10 transformation in R
  • Plot data with duplicate points
  • Visualizing crosstab tables with a plot in R - changing colours
  • How to manually modify automated numbers and labels in plot
  • How can I follow any redirections of a url in R?
  • Add jitter to box plot using markers in plotly
  • Adding an extra item to the legend
  • ggplot fills in data in the wrong order
  • Convert list to data frame
  • R: filtering by list(s) of strings and returning all results that start with the content of the lists
  • R:How to attach parts of a data frame with different headers and/or an overflowing piece of the dat frame
  • How to use 'par' for manipulating plot margins?
  • Can dplyr::case_when return mix of NAs and non-NAs?
  • Text preprocessing and topic modelling using text2vec package
  • Uploading multiple files in Shiny, process the files, rbind the results and return a download
  • R levelplot: color green-white-red (white on 0) according to one variable, but show the values of another variable
  • Why [i] doesn't point to the starting point in a vector
  • In R after generating a mvrnorm distribution, Y, what does Y[,1] do?
  • expand a data frame to have as many rows as range of two columns in original row
  • Getting started with R and CFA
  • Re order x-axis in ggplot so time goes from 12AM to 11PM in R
  • R - Automatically stack every nth column of a data frame and save them as new objects
  • How to format dplyr output in R into doubles (or other workable format)?
  • Dataframe to matrix conversion using tapply turns zeros to NAs
  • Smallest multiple of 1:20 - How can I make it quicker?
  • How to specify the size of a graph in ggplot2 independent of axis labels
  • How can I find the number of a vector's elements in another vector?
  • ROC curve from train/test set in caret R package
  • Random Forest for a mixture of categorical,numeric and "unwanted" variables which include missing values
  • extract certain data from multiple excel files with R
  • Matrix with counts of wins and losses between methods in R
  • Grouping string variables from a dataframe by best string match to make subsets
  • Reorder does not work after adding second geom_points
  • cover POS data formate to the one can apply Arules (Apriori)
  • Grouped bar chart turns into stacked bar chart ggplot
  • R: How to fill in NA Values within a Column based on grouping?
  • Two action buttons, but only the first one, that is written in the server file, works?
  • Barchart grouped by variable both count up to 100 percent
  • shadow
    Privacy Policy - Terms - Contact Us © voile276.org