Check differences of various DATE inside one variables R

Question

I want to split the line when the variable contain different YEAR, also split the col : "Price" with evenly divided by the numbers of date appear --> count (" ; ") +1

There is a table with the variable that is not yet be splitted.

# Dataset call df 

Price   Date 
500     2016-01-01
400     2016-01-03;2016-01-09
1000    2016-01-04;2017-09-01;2017-08-10;2018-01-01
25      2016-01-04;2017-09-01
304     2015-01-02
238     2018-01-02;2018-02-02

Desire Outlook

# Targeted df
Price   Date 
500     2016-01-01
400     2016-01-03;2016-01-09
250     2016-01-04
250     2017-09-01
250     2017-08-10
250     2018-01-01
12.5    2016-01-04
12.5    2017-09-01
304     2015-01-02
238     2018-01-02;2018-02-02

Once the variable contains different year is defined , below is the operation have to do .(It is just a example .)

mutate(Price = ifelse(DIFFERENT_DATE_ROW,
                     as.numeric(Price) / (str_count(Date,";")+1),
                     as.numeric(Price)),
       Date = ifelse(DIFFERENT_DATE_ROW,
                     strsplit(as.character(Date),";"),
                     Date)) %>%
 unnest()

I meet some constraints that cannot use dplyr's function "if_else" because else NO operation cannot be recognized .Only ifelse work properly.

How to find out there is differences of the year in one variables to PROVOKE the split line & split price calculations ?

so far the operation to split the element like

unlist(lapply(unlist(strsplit(df1$noFDate[8],";")),FUN = year))

cannot solve the problem.

I am beginner of coding , please feel free to change all operation above with considering the real data have over 2 million rows and 50 cols.

Yes , Your understanding is correct. I have to split to the numbers according to the numbers of the Date hv shown in one variables. — rane, Apr 04 '18 at 08:43

score 2 · Accepted Answer · answered Apr 04 '18 at 08:25

This might not be the most efficient one but can be used to get the required answer.

#Get the row indices which we need to separate
inds <- sapply(strsplit(df$Date, ";"), function(x) 
#Format the date into year and count number of unique values
#Return TRUE if number of unique values is greater than 1
    length(unique(format(as.Date(x), "%Y"))) > 1
)

library(tidyverse)
library(stringr)

#Select those indices 
df[inds, ] %>%
   # divide the price by number of dates in that row 
    mutate(Price = Price / (str_count(Date,";") + 1)) %>%
   # separate `;` delimited values in separate rows
    separate_rows(Date, sep = ";") %>%
   # bind the remaining rows as it is 
    bind_rows(df[!inds,])


# Price                  Date
#1  250.0            2016-01-04
#2  250.0            2017-09-01
#3  250.0            2017-08-10
#4  250.0            2018-01-01
#5   12.5            2016-01-04
#6   12.5            2017-09-01
#7  500.0            2016-01-01
#8  400.0 2016-01-03;2016-01-09
#9  304.0            2015-01-02
#10 238.0 2018-01-02;2018-02-02

Ronak , This approach is awesome. Clean and Simple , simple help avoid many shit problem in my real huge data . — rane, Apr 04 '18 at 09:23

count · Answer 2 · 2018-04-04T10:13:57.757

1

A bit cumbersome but you could do:

d_new = lapply(1:nrow(dat),function(x) {
  a = dat[x,]
  b = unlist(strsplit(as.character(a$Date),";"))
  l = length(b)
  if (l==1) check = 0 else check = ifelse(var(as.numeric(strftime(b,"%Y")))==0,0,1)

  if (check==0) {
      a
  } else {
      data.frame(Date = b, Price = rep(a$Price / l,l))
  }
})

do.call(rbind,d_new)

edited Apr 04 '18 at 10:13

answered Apr 04 '18 at 08:26

count

1,328
9
16

Thankyou count. I think the approach like above could bring me to another level to handling code . As independently try run b <- "2016-01-04;2017-09-01;2017-08-10;2018-01-01 " , var(as.numeric(strftime(b,"% Y"))) , [1] 0.6666667 , then how ==0,0,1 could be check with this result ? Apologize in advance if there is some misunderstanding . – rane Apr 04 '18 at 09:54
I am simply checking in variance in the data (dates). If all years are the same, the variance is 0 and the Dates are not split. If the variance is different from zero (different years) we split the dates and divide the price. – count Apr 04 '18 at 10:15
Understood . A smart approach . Thank you . – rane Apr 06 '18 at 00:43

Check differences of various DATE inside one variables R

2 Answers2