-2

enter image description here

In the attached image I would like to create and add the c variable rows rto the dataframe. Row c is row a - row b (by date etc). Have tried using dplyr but not getting there....

variable <- c(a,b,a,b) 
date <- c(jan 22,jan 22, feb 22,feb 22) 
amount <- c(100,20,100,20) 

DF:
variable   date   amount
--------   ----   ------
a         Jan 22   100
b         Jan 22   20
c         Jan 22   80
a         Jan 22   100
b         Jan 22   20
c         Jan 22   80
Konrad Rudolph
  • 530,221
  • 131
  • 937
  • 1,214
4agreements
  • 43
  • 6

1 Answers1

1

It is better to perform mathematical operations across columns than between rows, so the best way to do this is to first pivot_wider (or reshape) the data into a wide format, then do the mutate, and then pivot_longer back to the long format that you want. Using tidyverse:

DF |>
  pivot_wider(id_cols = "date" , names_from = "variable", values_from = "amount") |> 
  mutate(c = a-b) |>
  pivot_longer(c(a,b,c), names_to = "variable", values_to = "amount")


  date   variable amount
  <chr>  <chr>     <dbl>
1 jan 22 a           100
2 jan 22 b            20
3 jan 22 c            80
4 feb 22 a           100
5 feb 22 b            20
6 feb 22 c            80
George Savva
  • 4,152
  • 1
  • 7
  • 21
  • Does not quite work for me. In reality the dataset looks more like this: DF: variable date short name date long name city short city long amount -------- ------------------ ------------------- ---------- ---------- ----------- ab Jan 22 20 c Jan 22 80 a Jan 22 100 b Jan 22 20 c Jan 22 80 – 4agreements Apr 03 '23 at 12:13
  • my answer corresponds to the data in the question, if your real dataset has a different structure that affects the answer can you update the question details? – George Savva Apr 03 '23 at 12:18
  • variable date short name date long name city short city long amount -------- ------------------ ------------------- ---------- ---------- --------- a Jan 22 01/01-2022 LA Los Angeles 100 b Jan 22 01/01/2022 LA Los Angeles 20 c Jan 22 01/01-/2022 LA Los Angeles 80 – 4agreements Apr 03 '23 at 12:20
  • Thanks George. I should have made my example clearer, sorry. Basically, there is more than one variable in each section; value_from etc and I get a missing arguments error when I try? – 4agreements Apr 03 '23 at 12:29
  • without you properly updating the details in the question text it is difficult to update the answer. But in short you just need to add more elements to `id_cols` in the `pivot_wider` call. So `pivot_wider(id_cols=c("date", "city",...)...` etc – George Savva Apr 03 '23 at 12:32
  • Thanks George, I will try and re-post the question. Problem is the the variable column has a short name and a long name, so I think that craetes a problem in the mutate part? – 4agreements Apr 03 '23 at 12:39
  • I just reposted in clearer / better format (hopefully!) – 4agreements Apr 03 '23 at 13:44