Clash Royale CLAN TAG#URR8PPP

Print the first and last values of 2 consecutive data entries of a dataset in order to plot the differences - in R

First time poster.

I have a dataset of monthly survey answers from individuals starting in Month2 and ending in M13. I want to plot the difference from M2 to M3, M3 to M4, M4 to M5, etc. Ideally, I will be able to create new columns to easily use to plot the data.

An example of the dataset is this

ID M2 M3 M4 M5 M6 M7 M8 M9 M10 M11 M12 M13
1 5 15 2 20 . . . 4 2 7 8 .
2 2 8 7 6 5 4 7 4 7 9 9 8
3 . . . . . . . . . . 7 7
4 6 7 8 6 . . . . . 7 6 5


ID M2 M3 M4 M5 M6 M7 M8 M9 M10 M11 M12 M13
1 5 15 2 20 . . . 4 2 7 8 .
2 2 8 7 6 5 4 7 4 7 9 9 8
3 . . . . . . . . . . 7 7
4 6 7 8 6 . . . . . 7 6 5


So the ideal output for ID 1 would be

First Last
5 15
15 2
2 20
4 2
2 7
7 8


I will eventually want to capture the first and last value for anyone with 3 consecutive months all the way up the 11 consecutive months.

Any thoughts on where I would start with this?

Thanks

1 Answer
1

df = read.table(text = "
ID M2 M3 M4 M5 M6 M7 M8 M9 M10 M11 M12 M13
1 5 15 2 20 . . . 4 2 7 8 .
2 2 8 7 6 5 4 7 4 7 9 9 8
3 . . . . . . . . . . 7 7
4 6 7 8 6 . . . . . 7 6 5
", header=T, stringsAsFactors=F)

library(tidyverse)

df %>%
gather(M,First,-ID) %>% # reshape data
group_by(ID) %>% # for each ID
mutate(Last = lead(First)) %>% # get the next values in a new column
ungroup() %>% # forget the grouping
filter(First != "." & Last != ".") %>% # exclude rows with . as value
arrange(ID) %>% # order ID (needed for visualisation purposes only)
select(-M) # remove unnecessary column


# # A tibble: 23 x 3
# ID First Last
# <int> <chr> <chr>
# 1 1 5 15
# 2 1 15 2
# 3 1 2 20
# 4 1 4 2
# 5 1 2 7
# 6 1 7 8
# 7 2 2 8
# 8 2 8 7
# 9 2 7 6
# 10 2 6 5
# # ... with 13 more rows


Columns First and Last are character because you had . as a value. You can update them to be numeric if you want.

First

Last

character

.

numeric

Assuming that this was the case of 2 consecutive months, you can use the above code as a function that takes as input the number of consecutive months you want to consider (as the dataset df is always the same):

df

# function that gets as input the number of consecutive months you want to consider
f = function(x)

df %>%
gather(M,First,-ID) %>% # reshape data
group_by(ID) %>% # for each ID
mutate(Last = lead(First, x-1)) %>% # get the next values in a new column
ungroup() %>% # forget the grouping
filter(First != "." & Last != ".") %>% # exclude rows with . as value
arrange(ID) %>% # order ID (needed for visualisation purposes only)
select(-M) # remove unnecessary column



You can run f(2), f(3), ... f(11) and check results, or you can create a big dataset with all combinations, like this:

f(2)

f(3)

f(11)

# create a series of consecutive months (2 to 11) and apply the function to each value
data.frame(consec_months = 2:11) %>%
mutate(d = map(consec_months, ~f(.))) %>%
unnest() %>%
tbl_df() # only for visualisation purposes

# # A tibble: 114 x 4
# consec_months ID First Last
# <int> <int> <chr> <chr>
# 1 2 1 5 15
# 2 2 1 15 2
# 3 2 1 2 20
# 4 2 1 4 2
# 5 2 1 2 7
# 6 2 1 7 8
# 7 2 2 2 8
# 8 2 2 8 7
# 9 2 2 7 6
# 10 2 2 6 5
# # ... with 104 more rows


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.