generate rows for each id by date sequence

my dataframe looks someway like this

df <- read.table(text="

                 id          start           end

    1            2      2018-10-01    2018-12-01

    2            3      2018-01-01    2018-04-01

", header=TRUE)

What I trying to achieve is get difference between start and day in months for each id and then generate new dataframe with each month for particular id. Result should be

result <- read.table(text="

                 id          date           

    1            2      2018-10-01

    2            2      2018-11-01

    3            2      2018-12-01     

    4            3      2018-01-01

    5            3      2018-02-01    

    6            3      2018-03-01    

    7            3      2018-04-01        

", header=TRUE)

edited Jan 2 at 16:23

Ronak Shah

43.3k104266

asked Jan 2 at 16:14

Sklenicka

526

Related: Expand ranges defined by “from” and “to” columns, but it doesn't cover the by = month aspect in seq.

– Henrik
Jan 2 at 16:30

add a comment |

my dataframe looks someway like this

df <- read.table(text="

                 id          start           end

    1            2      2018-10-01    2018-12-01

    2            3      2018-01-01    2018-04-01

", header=TRUE)

What I trying to achieve is get difference between start and day in months for each id and then generate new dataframe with each month for particular id. Result should be

result <- read.table(text="

                 id          date           

    1            2      2018-10-01

    2            2      2018-11-01

    3            2      2018-12-01     

    4            3      2018-01-01

    5            3      2018-02-01    

    6            3      2018-03-01    

    7            3      2018-04-01        

", header=TRUE)

edited Jan 2 at 16:23

Ronak Shah

43.3k104266

asked Jan 2 at 16:14

Sklenicka

526

Related: Expand ranges defined by “from” and “to” columns, but it doesn't cover the by = month aspect in seq.

– Henrik
Jan 2 at 16:30

add a comment |

my dataframe looks someway like this

df <- read.table(text="

                 id          start           end

    1            2      2018-10-01    2018-12-01

    2            3      2018-01-01    2018-04-01

", header=TRUE)

What I trying to achieve is get difference between start and day in months for each id and then generate new dataframe with each month for particular id. Result should be

result <- read.table(text="

                 id          date           

    1            2      2018-10-01

    2            2      2018-11-01

    3            2      2018-12-01     

    4            3      2018-01-01

    5            3      2018-02-01    

    6            3      2018-03-01    

    7            3      2018-04-01        

", header=TRUE)

edited Jan 2 at 16:23

Ronak Shah

43.3k104266

asked Jan 2 at 16:14

Sklenicka

526

my dataframe looks someway like this

df <- read.table(text="

                 id          start           end

    1            2      2018-10-01    2018-12-01

    2            3      2018-01-01    2018-04-01

", header=TRUE)

What I trying to achieve is get difference between start and day in months for each id and then generate new dataframe with each month for particular id. Result should be

result <- read.table(text="

                 id          date           

    1            2      2018-10-01

    2            2      2018-11-01

    3            2      2018-12-01     

    4            3      2018-01-01

    5            3      2018-02-01    

    6            3      2018-03-01    

    7            3      2018-04-01        

", header=TRUE)

r dataframe row sequence

edited Jan 2 at 16:23

Ronak Shah

43.3k104266

asked Jan 2 at 16:14

Sklenicka

526

edited Jan 2 at 16:23

Ronak Shah

43.3k104266

asked Jan 2 at 16:14

Sklenicka

526

edited Jan 2 at 16:23

Ronak Shah

43.3k104266

edited Jan 2 at 16:23

Ronak Shah

43.3k104266

edited Jan 2 at 16:23

Ronak Shah

43.3k104266

asked Jan 2 at 16:14

Sklenicka

526

asked Jan 2 at 16:14

Sklenicka

526

asked Jan 2 at 16:14

Sklenicka

526

Related: Expand ranges defined by “from” and “to” columns, but it doesn't cover the by = month aspect in seq.

– Henrik
Jan 2 at 16:30

add a comment |

Related: Expand ranges defined by “from” and “to” columns, but it doesn't cover the by = month aspect in seq.

– Henrik
Jan 2 at 16:30

Related: Expand ranges defined by “from” and “to” columns, but it doesn't cover the by = month aspect in seq.

– Henrik
Jan 2 at 16:30

add a comment |

2 Answers
2

active

oldest

votes

Most straightforward way using base R functions is to create a sequence of monthly dates for each row, create a dataframe and rbind them together

do.call(rbind, with(df,lapply(1:nrow(df), function(i) 

   data.frame(id = id[i], date = seq(as.Date(start[i]), as.Date(end[i]), by = "month")))))



#  id       date

#1  2 2018-10-01

#2  2 2018-11-01

#3  2 2018-12-01

#4  3 2018-01-01

#5  3 2018-02-01

#6  3 2018-03-01

#7  3 2018-04-01

answered Jan 2 at 16:21

Ronak Shah

43.3k104266

add a comment |

We can do this easily with Map. Pass the Date converted 'start' and 'end' columnd of the dataset as arguments to Map, get the sequence of 'month's as a list and expand the 'id' based on the lengths of list as well as concatenate the list elements to create the expanded dataframe

lst1 <- Map(seq, MoreArgs = list(by = 'month'), as.Date(df$start), as.Date(df$end))

data.frame(id = rep(df$id, lengths(lst1)), date = do.call(c, lst1))

#  id       date

#1  2 2018-10-01

#2  2 2018-11-01

#3  2 2018-12-01

#4  3 2018-01-01

#5  3 2018-02-01

#6  3 2018-03-01

#7  3 2018-04-01

Or using tidyverse, we mutate the class of the 'start', 'end' columns to Date, using map2 (from purrr), get the sequence of dates from 'start' to 'end' by the 'month' and expand the data by unnesting the dataset

library(tidyverse)

df %>% 

  mutate_at(2:3, as.Date) %>% 

  transmute(id = id, date = map2(start, end,  ~ seq(.x, .y, by = 'month'))) %>% 

  unnest

#  id       date

#1  2 2018-10-01

#2  2 2018-11-01

#3  2 2018-12-01

#4  3 2018-01-01

#5  3 2018-02-01

#6  3 2018-03-01

#7  3 2018-04-01

edited Jan 2 at 16:33

answered Jan 2 at 16:25

akrun

417k13205278

I can't reproduce the error with purrr 0.2.5 and dplyr 0.7.6

– akrun
Jan 2 at 16:35

Try it on a fresh session in case there are some function masking

– akrun
Jan 2 at 16:45

Not sure what was wrong. Worked on Mac now but I had issue yesterday on Windows.

– Ronak Shah
Jan 3 at 3:50

I am also using Mac. So, it could be some issue in windows

– akrun
Jan 3 at 4:55

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54009625%2fgenerate-rows-for-each-id-by-date-sequence%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

Most straightforward way using base R functions is to create a sequence of monthly dates for each row, create a dataframe and rbind them together

do.call(rbind, with(df,lapply(1:nrow(df), function(i) 

   data.frame(id = id[i], date = seq(as.Date(start[i]), as.Date(end[i]), by = "month")))))



#  id       date

#1  2 2018-10-01

#2  2 2018-11-01

#3  2 2018-12-01

#4  3 2018-01-01

#5  3 2018-02-01

#6  3 2018-03-01

#7  3 2018-04-01

answered Jan 2 at 16:21

Ronak Shah

43.3k104266

add a comment |

Most straightforward way using base R functions is to create a sequence of monthly dates for each row, create a dataframe and rbind them together

do.call(rbind, with(df,lapply(1:nrow(df), function(i) 

   data.frame(id = id[i], date = seq(as.Date(start[i]), as.Date(end[i]), by = "month")))))



#  id       date

#1  2 2018-10-01

#2  2 2018-11-01

#3  2 2018-12-01

#4  3 2018-01-01

#5  3 2018-02-01

#6  3 2018-03-01

#7  3 2018-04-01

answered Jan 2 at 16:21

Ronak Shah

43.3k104266

add a comment |

Most straightforward way using base R functions is to create a sequence of monthly dates for each row, create a dataframe and rbind them together

do.call(rbind, with(df,lapply(1:nrow(df), function(i) 

   data.frame(id = id[i], date = seq(as.Date(start[i]), as.Date(end[i]), by = "month")))))



#  id       date

#1  2 2018-10-01

#2  2 2018-11-01

#3  2 2018-12-01

#4  3 2018-01-01

#5  3 2018-02-01

#6  3 2018-03-01

#7  3 2018-04-01

answered Jan 2 at 16:21

Ronak Shah

43.3k104266

Most straightforward way using base R functions is to create a sequence of monthly dates for each row, create a dataframe and rbind them together

do.call(rbind, with(df,lapply(1:nrow(df), function(i) 

   data.frame(id = id[i], date = seq(as.Date(start[i]), as.Date(end[i]), by = "month")))))



#  id       date

#1  2 2018-10-01

#2  2 2018-11-01

#3  2 2018-12-01

#4  3 2018-01-01

#5  3 2018-02-01

#6  3 2018-03-01

#7  3 2018-04-01

answered Jan 2 at 16:21

Ronak Shah

43.3k104266

answered Jan 2 at 16:21

Ronak Shah

43.3k104266

answered Jan 2 at 16:21

Ronak Shah

43.3k104266

answered Jan 2 at 16:21

Ronak Shah

43.3k104266

add a comment |

lst1 <- Map(seq, MoreArgs = list(by = 'month'), as.Date(df$start), as.Date(df$end))

data.frame(id = rep(df$id, lengths(lst1)), date = do.call(c, lst1))

#  id       date

#1  2 2018-10-01

#2  2 2018-11-01

#3  2 2018-12-01

#4  3 2018-01-01

#5  3 2018-02-01

#6  3 2018-03-01

#7  3 2018-04-01

library(tidyverse)

df %>% 

  mutate_at(2:3, as.Date) %>% 

  transmute(id = id, date = map2(start, end,  ~ seq(.x, .y, by = 'month'))) %>% 

  unnest

#  id       date

#1  2 2018-10-01

#2  2 2018-11-01

#3  2 2018-12-01

#4  3 2018-01-01

#5  3 2018-02-01

#6  3 2018-03-01

#7  3 2018-04-01

edited Jan 2 at 16:33

answered Jan 2 at 16:25

akrun

417k13205278

I can't reproduce the error with purrr 0.2.5 and dplyr 0.7.6

– akrun
Jan 2 at 16:35

Try it on a fresh session in case there are some function masking

– akrun
Jan 2 at 16:45

Not sure what was wrong. Worked on Mac now but I had issue yesterday on Windows.

– Ronak Shah
Jan 3 at 3:50

I am also using Mac. So, it could be some issue in windows

– akrun
Jan 3 at 4:55

add a comment |

lst1 <- Map(seq, MoreArgs = list(by = 'month'), as.Date(df$start), as.Date(df$end))

data.frame(id = rep(df$id, lengths(lst1)), date = do.call(c, lst1))

#  id       date

#1  2 2018-10-01

#2  2 2018-11-01

#3  2 2018-12-01

#4  3 2018-01-01

#5  3 2018-02-01

#6  3 2018-03-01

#7  3 2018-04-01

library(tidyverse)

df %>% 

  mutate_at(2:3, as.Date) %>% 

  transmute(id = id, date = map2(start, end,  ~ seq(.x, .y, by = 'month'))) %>% 

  unnest

#  id       date

#1  2 2018-10-01

#2  2 2018-11-01

#3  2 2018-12-01

#4  3 2018-01-01

#5  3 2018-02-01

#6  3 2018-03-01

#7  3 2018-04-01

edited Jan 2 at 16:33

answered Jan 2 at 16:25

akrun

417k13205278

I can't reproduce the error with purrr 0.2.5 and dplyr 0.7.6

– akrun
Jan 2 at 16:35

Try it on a fresh session in case there are some function masking

– akrun
Jan 2 at 16:45

Not sure what was wrong. Worked on Mac now but I had issue yesterday on Windows.

– Ronak Shah
Jan 3 at 3:50

I am also using Mac. So, it could be some issue in windows

– akrun
Jan 3 at 4:55

add a comment |

lst1 <- Map(seq, MoreArgs = list(by = 'month'), as.Date(df$start), as.Date(df$end))

data.frame(id = rep(df$id, lengths(lst1)), date = do.call(c, lst1))

#  id       date

#1  2 2018-10-01

#2  2 2018-11-01

#3  2 2018-12-01

#4  3 2018-01-01

#5  3 2018-02-01

#6  3 2018-03-01

#7  3 2018-04-01

library(tidyverse)

df %>% 

  mutate_at(2:3, as.Date) %>% 

  transmute(id = id, date = map2(start, end,  ~ seq(.x, .y, by = 'month'))) %>% 

  unnest

#  id       date

#1  2 2018-10-01

#2  2 2018-11-01

#3  2 2018-12-01

#4  3 2018-01-01

#5  3 2018-02-01

#6  3 2018-03-01

#7  3 2018-04-01

edited Jan 2 at 16:33

answered Jan 2 at 16:25

akrun

417k13205278

lst1 <- Map(seq, MoreArgs = list(by = 'month'), as.Date(df$start), as.Date(df$end))

data.frame(id = rep(df$id, lengths(lst1)), date = do.call(c, lst1))

#  id       date

#1  2 2018-10-01

#2  2 2018-11-01

#3  2 2018-12-01

#4  3 2018-01-01

#5  3 2018-02-01

#6  3 2018-03-01

#7  3 2018-04-01

library(tidyverse)

df %>% 

  mutate_at(2:3, as.Date) %>% 

  transmute(id = id, date = map2(start, end,  ~ seq(.x, .y, by = 'month'))) %>% 

  unnest

#  id       date

#1  2 2018-10-01

#2  2 2018-11-01

#3  2 2018-12-01

#4  3 2018-01-01

#5  3 2018-02-01

#6  3 2018-03-01

#7  3 2018-04-01

edited Jan 2 at 16:33

answered Jan 2 at 16:25

akrun

417k13205278

edited Jan 2 at 16:33

answered Jan 2 at 16:25

akrun

417k13205278

answered Jan 2 at 16:25

akrun

417k13205278

answered Jan 2 at 16:25

akrun

417k13205278

I can't reproduce the error with purrr 0.2.5 and dplyr 0.7.6

– akrun
Jan 2 at 16:35

Try it on a fresh session in case there are some function masking

– akrun
Jan 2 at 16:45

Not sure what was wrong. Worked on Mac now but I had issue yesterday on Windows.

– Ronak Shah
Jan 3 at 3:50

I am also using Mac. So, it could be some issue in windows

– akrun
Jan 3 at 4:55

add a comment |

I can't reproduce the error with purrr 0.2.5 and dplyr 0.7.6

– akrun
Jan 2 at 16:35

Try it on a fresh session in case there are some function masking

– akrun
Jan 2 at 16:45

Not sure what was wrong. Worked on Mac now but I had issue yesterday on Windows.

– Ronak Shah
Jan 3 at 3:50

I am also using Mac. So, it could be some issue in windows

– akrun
Jan 3 at 4:55

I can't reproduce the error with purrr 0.2.5 and dplyr 0.7.6

– akrun
Jan 2 at 16:35

Try it on a fresh session in case there are some function masking

– akrun
Jan 2 at 16:45

Not sure what was wrong. Worked on Mac now but I had issue yesterday on Windows.

– Ronak Shah
Jan 3 at 3:50

I am also using Mac. So, it could be some issue in windows

– akrun
Jan 3 at 4:55

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

Search This Blog

Ufyukyu