0

Does anyone know how to merge rows from an excel file (or CSV) that sums values? I can use R Studio for this. I got a dataset with data per week in each row but need to merge it into another database containing fortnightly data per row. For instance, in one database, the row contains sales data for the week 24/04/2020, and the next row is sales data for the week 04/05/2020. The other dataset only contains sales data for the fortnight between 27/04/2020 and 11/05/2020 (other items). The datasets are not small. More or less 3000 rows in each one. The databases do not start with the same week (date), but I want to merge the common rows in both datasets, but I still need to sum data in one of the datasets. The databases contain data from 2018 to 2021. Then, I am unsure how to manage this in excel or R studio. I need to get all data per fortnight. Any help will be greatly appreciated. Thank you!

r2evans
  • 141,215
  • 6
  • 77
  • 149
Rick
  • 1
  • 1
    Welcome to SO, Rick! Questions on SO (especially in R) do much better if they are reproducible and self-contained. By that I mean including attempted code (please be explicit about non-base packages), sample representative data (perhaps via `dput(head(x))` or building data programmatically (e.g., `data.frame(...)`), possibly stochastically), perhaps actual output (with verbatim errors/warnings) versus intended output. Refs: https://stackoverflow.com/q/5963269, [mcve], and https://stackoverflow.com/tags/r/info. – r2evans Mar 04 '22 at 01:36
  • You could try `lubridate::floor_date` to convert all dates to a common timeframe. Then `dplyr::group_by` and `dplyr::summarize` to get the totals for each fortnight. Then use `dplyr::bind_rows` to merge the two data sets. (You could actually merge at the start...). – Michael Dewar Mar 04 '22 at 07:59

0 Answers0