create a new column based on multiple date entries

Question

i have a data frame df

date        tss
2020-07-03  76
2020-07-03  49
2020-07-04  NA
2020-07-05  NA
2020-07-06  100
2020-07-06  76
2020-07-07  37
2020-07-08  104
2020-07-09  53
2020-07-10  116
2020-07-10  79

What I'm trying to achieve is to create a new column (tss2) that containts the value in the tss column if it's repeated twice for the same date.

i would imagine I would need to use Mutate with an if statement ?

example output i would like to achieve:

date        tss     tss2
2020-07-03           76
2020-07-03   49
2020-07-04           NA
2020-07-05   NA

date is classified as a date within the df

thank you

What if 3 are values for the same date in `tss` column? Will you have 3 columns? Can you show the complete expected output? — Ronak Shah, Jul 24 '20 at 04:38
there will only be 2 values for the same date. Sorry i noticed that NA was not put across into tss2 — BigBird, Jul 24 '20 at 05:10

score 1 · Accepted Answer · answered Jul 24 '20 at 04:50

You can try reshaping the data so that the solution is flexible enough for more number of columns.

library(dplyr)

df %>%
  mutate(row = row_number()) %>%
  group_by(date) %>%
  mutate(colname = paste0('tss', rev(row_number()))) %>%
  tidyr::pivot_wider(names_from = colname, values_from = tss) %>%
  select(-row)

#  date        tss2  tss1
#   <chr>      <int> <int>
# 1 2020-07-03    76    NA
# 2 2020-07-03    NA    49
# 3 2020-07-04    NA    NA
# 4 2020-07-05    NA    NA
# 5 2020-07-06   100    NA
# 6 2020-07-06    NA    76
# 7 2020-07-07    NA    37
# 8 2020-07-08    NA   104
# 9 2020-07-09    NA    53
#10 2020-07-10   116    NA
#11 2020-07-10    NA    79

data

df <- structure(list(date = c("2020-07-03", "2020-07-03", "2020-07-04", 
"2020-07-05", "2020-07-06", "2020-07-06", "2020-07-07", "2020-07-08", 
"2020-07-09", "2020-07-10", "2020-07-10"), tss = c(76L, 49L, 
NA, NA, 100L, 76L, 37L, 104L, 53L, 116L, 79L)), 
class = "data.frame", row.names = c(NA, -11L))

create a new column based on multiple date entries

1 Answers1