r code - left join or merge between data frames based on 2 criteria: numerical match and date range evaluation
Budget: $30 – $250 CAD
I have two data frames: summary and hauled (complete df’s attached to posting). I want to create a new column in hauled that returns the values from summary$rfp_id based on 2 matching criteria:
1. Match summary$customer_id_num with hauled$customer_id_num
2. Compare hauled$transaction_date with summary$start_date and summary$end_date and return whichever rfp_id most closely matches based on the below explanation of possible outcomes.
summary dataframe example:
~rfp_id, ~start_date, ~end_date, ~customer_id_num
1, 12/30/2022, 2/28/2023, 7
2, 4/1/2022, 6/30/2022, 8
3, 7/1/2022, 8/30/2022, 8
4, 1/16/2022, 1/16/2023, 9
5, 1/1/2023, 2/6/2023, 9
hauled dataframe example (rfp_id column is the desired result):
~unique_id, ~customer_id_num, ~transaction_date, ~rfp_id
11, 7, 1/14/2023, 1
12, 8, 7/2/2022, 3
13, 8, 3/20/2022, 2
14, 8, 9/1/2022, 3
15, 9, 1/15/2023, 5
16 , 2, 5/1/2022, x
The first example (unique_id = 11) returns rfp_id = 1 since company 7 only appears once in summary and hauled$trans_date of 1/14/2023 falls between the start/end dates of 12/30/2022 and 2/28/2023.
The second example (unique_id = 12) returns rfp_id = 3 since the transaction_date of 7/2/2022 falls between 7/1/2022-8/30/2022 (and not between 4/1/2022-6/30/2022 of the other customer_id_num match).
The third example (unique_id = 13 ) returns rfp_id = 2 because the transaction_date of 3/20/2022 falls outside both start/end date ranges, however it is closest to the start_date of 4/1/2022.
The fourth example (unique_id = 14 ) returns rfp_id = 3 because the transaction_date of 9/1/2022 falls outside both start/end date ranges, however it is closest to the end_date of 8/30/2022.
The fifth example (unique_id = 15) returns rfp_id = 5 because when a transaction_date falls between 2 or more start/end date ranges then the rfp_id to return is whichever has the latest end_date
The sixth example (unique_id = 16) returns 'x' to rfp_id since hauled$customer_id_num = 2 does not exist in summary$customer_id_num.
----
The code-block that achieves this must be annotated line-by-line explaining what each line is doing to achieve the result.
I do not care which package(s) or approach(es) you use to achieve this provided they are
1) available for free through CRAN
2) able to quickly and efficiently process up to 200,000 rows in the hauled df
----
Freelancer project description:
I am looking for an R code expert who can help me with a project involving joining or merging data frames based on two criteria: numerical match and date range evaluation.
Purpose:
- The main purpose of this project is to identify matching records from multiple sources.
Preferred Method:
- There is no specific preference for the merging method, so the freelancer can choose the most suitable method (e.g., left join or merge) but code must be able to efficiently process extremely large dataframes (up to 200K rows)
Desired Output:
- The desired output of this project is to have the original data frames updated with the merged information.
Skills and Experience:
- Proficiency in R programming language
- Strong knowledge of data manipulation and merging techniques
- Experience with handling numerical data and date ranges (lubridate; dplyr; tidyverse; etc)
- Attention to detail to ensure accurate matching records
- Ability to efficiently update original data frames
If you have the required skills and experience, please submit your proposal.
1. Match summary$customer_id_num with hauled$customer_id_num
2. Compare hauled$transaction_date with summary$start_date and summary$end_date and return whichever rfp_id most closely matches based on the below explanation of possible outcomes.
summary dataframe example:
~rfp_id, ~start_date, ~end_date, ~customer_id_num
1, 12/30/2022, 2/28/2023, 7
2, 4/1/2022, 6/30/2022, 8
3, 7/1/2022, 8/30/2022, 8
4, 1/16/2022, 1/16/2023, 9
5, 1/1/2023, 2/6/2023, 9
hauled dataframe example (rfp_id column is the desired result):
~unique_id, ~customer_id_num, ~transaction_date, ~rfp_id
11, 7, 1/14/2023, 1
12, 8, 7/2/2022, 3
13, 8, 3/20/2022, 2
14, 8, 9/1/2022, 3
15, 9, 1/15/2023, 5
16 , 2, 5/1/2022, x
The first example (unique_id = 11) returns rfp_id = 1 since company 7 only appears once in summary and hauled$trans_date of 1/14/2023 falls between the start/end dates of 12/30/2022 and 2/28/2023.
The second example (unique_id = 12) returns rfp_id = 3 since the transaction_date of 7/2/2022 falls between 7/1/2022-8/30/2022 (and not between 4/1/2022-6/30/2022 of the other customer_id_num match).
The third example (unique_id = 13 ) returns rfp_id = 2 because the transaction_date of 3/20/2022 falls outside both start/end date ranges, however it is closest to the start_date of 4/1/2022.
The fourth example (unique_id = 14 ) returns rfp_id = 3 because the transaction_date of 9/1/2022 falls outside both start/end date ranges, however it is closest to the end_date of 8/30/2022.
The fifth example (unique_id = 15) returns rfp_id = 5 because when a transaction_date falls between 2 or more start/end date ranges then the rfp_id to return is whichever has the latest end_date
The sixth example (unique_id = 16) returns 'x' to rfp_id since hauled$customer_id_num = 2 does not exist in summary$customer_id_num.
----
The code-block that achieves this must be annotated line-by-line explaining what each line is doing to achieve the result.
I do not care which package(s) or approach(es) you use to achieve this provided they are
1) available for free through CRAN
2) able to quickly and efficiently process up to 200,000 rows in the hauled df
----
Freelancer project description:
I am looking for an R code expert who can help me with a project involving joining or merging data frames based on two criteria: numerical match and date range evaluation.
Purpose:
- The main purpose of this project is to identify matching records from multiple sources.
Preferred Method:
- There is no specific preference for the merging method, so the freelancer can choose the most suitable method (e.g., left join or merge) but code must be able to efficiently process extremely large dataframes (up to 200K rows)
Desired Output:
- The desired output of this project is to have the original data frames updated with the merged information.
Skills and Experience:
- Proficiency in R programming language
- Strong knowledge of data manipulation and merging techniques
- Experience with handling numerical data and date ranges (lubridate; dplyr; tidyverse; etc)
- Attention to detail to ensure accurate matching records
- Ability to efficiently update original data frames
If you have the required skills and experience, please submit your proposal.