Fetching values from one column based on other column keys in long-formatted dataset
I have a long format dataset of 100,000+ individuals, capturing clinic visits at 5 different time points (not chronological). I've included an example dataset below that replicates the formatting of my data:
ID: participant ID visit_number: order of the clinic visits in the original datasetage_visit: age at the time of the visitclinic_number: the identifier for the specific clinic locationage_sorted: For each ID, age sorted in ascending order across the 5 clinic visitsage_sorted_index: For each ID, the visit number corresponding to the sorted age
I would like to create a new column (clinic_number_extracted) that fetches the clinic identifier (clinic_number) corresponding to each sorted age (age_sorted value) for each participant. I was thinking that it might be possible to use the age_sorted_index and visit_number variables to do so (generating key-value pairs?), but am not quite sure how to do this outside of data.table. A tidyverse solution would be preferred.
I've looked on R community and stack exchange for clues, but haven't been able to find exactly what I'm looking for (likely not using the correct search terms). I tried to play around with group_by(across()) and with_order(order_by()) functions without much success. I can potentially create a new variable with a few case_when() conditions but might run into issues if there are repeated age_assessment_sorted values.
set.seed(42)
# Beginning dataset
das <- data.frame(id = rep(letters[1:3], each = 5),
visit_number = rep(1:5, times = 3),
age_visit = c(50, rep(NA_real_, times = 7), 34, 40, 72, rep(NA_real_, times = 3), 87),
clinic_number = sample(30:50, 15, replace=TRUE),
age_sorted = c(50, rep(NA_real_, times = 4), 34, 40,rep(NA_real_, times = 3), 72, 87, rep(NA_real_, times = 3)),
age_sorted_index = c(rep(1:5), 4, 5, rep(1:3), 1, 5, 2, 3, 4))
# Print out dataset
das
#> id visit_number age_visit clinic_number age_sorted age_sorted_index
#> 1 a 1 50 46 50 1
#> 2 a 2 NA 34 NA 2
#> 3 a 3 NA 30 NA 3
#> 4 a 4 NA 39 NA 4
#> 5 a 5 NA 33 NA 5
#> 6 b 1 NA 47 34 4
#> 7 b 2 NA 46 40 5
#> 8 b 3 NA 44 NA 1
#> 9 b 4 34 36 NA 2
#> 10 b 5 40 33 NA 3
#> 11 c 1 72 34 72 1
#> 12 c 2 NA 43 87 5
#> 13 c 3 NA 49 NA 2
#> 14 c 4 NA 47 NA 3
#> 15 c 5 87 44 NA 4
Desired data:
das_final <- cbind(das,
clinic_number_extracted = c(46, rep(NA_real_, times = 4), 36, 33, rep(NA_real_, times = 3), 34, 44, rep(NA_real_, times = 3)))
# Print out final dataset
das_final
#> id visit_number age_visit clinic_number age_sorted age_sorted_index
#> 1 a 1 50 46 50 1
#> 2 a 2 NA 34 NA 2
#> 3 a 3 NA 30 NA 3
#> 4 a 4 NA 39 NA 4
#> 5 a 5 NA 33 NA 5
#> 6 b 1 NA 47 34 4
#> 7 b 2 NA 46 40 5
#> 8 b 3 NA 44 NA 1
#> 9 b 4 34 36 NA 2
#> 10 b 5 40 33 NA 3
#> 11 c 1 72 34 72 1
#> 12 c 2 NA 43 87 5
#> 13 c 3 NA 49 NA 2
#> 14 c 4 NA 47 NA 3
#> 15 c 5 87 44 NA 4
#> clinic_number_extracted
#> 1 46
#> 2 NA
#> 3 NA
#> 4 NA
#> 5 NA
#> 6 36
#> 7 33
#> 8 NA
#> 9 NA
#> 10 NA
#> 11 34
#> 12 44
#> 13 NA
#> 14 NA
#> 15 NA
Created on 2022-05-06 by the reprex package (v2.0.1)
2 answers
-
answered 2022-05-06 18:23
akrun
We may use
matchbetween the 'age_sorted' and 'age_visit', use that index to subset the 'clinic_number' and replace the elements where they are NA in 'age_sorted' toNAlibrary(dplyr) das_final %>% group_by(id) %>% mutate(clinic_new = clinic_number[match(age_sorted, age_visit)] * NA^is.na(age_sorted)) %>% ungroup-output
# A tibble: 15 × 8 id visit_number age_visit clinic_number age_sorted age_sorted_index clinic_number_extracted clinic_new <chr> <int> <dbl> <int> <dbl> <dbl> <dbl> <dbl> 1 a 1 50 46 50 1 46 46 2 a 2 NA 34 NA 2 NA NA 3 a 3 NA 30 NA 3 NA NA 4 a 4 NA 39 NA 4 NA NA 5 a 5 NA 33 NA 5 NA NA 6 b 1 NA 47 34 4 36 36 7 b 2 NA 46 40 5 33 33 8 b 3 NA 44 NA 1 NA NA 9 b 4 34 36 NA 2 NA NA 10 b 5 40 33 NA 3 NA NA 11 c 1 72 34 72 1 34 34 12 c 2 NA 43 87 5 44 44 13 c 3 NA 49 NA 2 NA NA 14 c 4 NA 47 NA 3 NA NA 15 c 5 87 44 NA 4 NA NA -
answered 2022-05-06 18:30
jpsmith
Could you just do this without needing to group? It seems to solve the issue in the sample data, but perhaps I am missing something:
das$clinic_number_extracted <- ifelse(!is.na(das$age_sorted), das$clinic_number, NA)Output:
# id visit_number age_visit clinic_number age_sorted age_sorted_index clinic_number_extracted 1 a 1 50 46 50 1 46 #2 a 2 NA 34 NA 2 NA #3 a 3 NA 30 NA 3 NA #4 a 4 NA 39 NA 4 NA #5 a 5 NA 33 NA 5 NA #6 b 1 NA 47 34 4 47 #7 b 2 NA 46 40 5 46 #8 b 3 NA 44 NA 1 NA #9 b 4 34 36 NA 2 NA #10 b 5 40 33 NA 3 NA #11 c 1 72 34 72 1 34 #12 c 2 NA 43 87 5 43 #13 c 3 NA 49 NA 2 NA #14 c 4 NA 47 NA 3 NA #15 c 5 87 44 NA 4 NATesting against desired data provided:
all.equal(das[,"clinic_number_extracted"], das_final[,"clinic_number_extracted"]) #[1] TRUE
do you know?
how many words do you know
See also questions close to this topic
-
pivot_wider does not keep all the variables
I would like to keep the variable
cat(category) in the output of my function. However, I am not able to keep it. The idea is to apply a similar function tom <- 1 - (1 - se * p2)^df$nbased on the category. But in order to perform that step, I need to keep the variable category.Here's the code:
#script3 suppressPackageStartupMessages({ library(mc2d) library(tidyverse) }) sim_one <- function() { df<-data.frame(id=c(1:30),cat=c(rep("a",12),rep("b",18)),month=c(1:6,1,6,4,1,5,2,3,2,5,4,6,3:6,4:6,1:5,5),n=rpois(30,5)) nr <- nrow(df) df$n[df$n == "0"] <- 3 se <- rbeta(nr, 96, 6) epi.a <- rpert(nr, min = 1.5, mode = 2, max = 3) p <- 0.2 p2 <- epi.a*p m <- 1 - (1 - se * p2)^df$n results <- data.frame(month = df$month, m, df$cat) results %>% arrange(month) %>% group_by(month) %>% mutate(n = row_number(), .groups = "drop") %>% pivot_wider( id_cols = n, names_from = month, names_glue = "m_{.name}", values_from =m ) } set.seed(99) iters <- 1000 sim_list <- replicate(iters, sim_one(), simplify = FALSE) sim_list[[1]] #> # A tibble: 7 x 7 #> n m_1 m_2 m_3 m_4 m_5 m_6 #> <int> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> #> 1 1 0.970 0.623 0.905 0.998 0.929 0.980 #> 2 2 0.912 0.892 0.736 0.830 0.890 0.862 #> 3 3 0.795 0.932 0.553 0.958 0.931 0.798 #> 4 4 0.950 0.892 0.732 0.649 0.777 0.743 #> 5 5 NA NA NA 0.657 0.980 0.945 #> 6 6 NA NA NA 0.976 0.836 NA #> 7 7 NA NA NA NA 0.740 NACreated on 2022-05-07 by the reprex package (v2.0.1)
-
calculate weighted average over several columns with NA
I have a data frame like this one:
ID duration1 duration2 total_duration quantity1 quantity2 1 5 2 7 3 1 2 NA 4 4 3 4 3 5 NA 5 2 NAI would like to do a weighted mean for each subject like this:
df$weighted_mean<- ((df$duration1*df$quantity1) + (df$duration2*df$quantity2) ) / (df$total_duration)But as I have NA, this command does not work and it is not very nice....
The result would be this:
ID duration1 duration2 total_duration quantity1 quantity2 weighted_mean 1 5 2 7 3 1 2.43 2 NA 4 4 3 4 4 3 5 NA 5 2 NA 2Thanks in advance for the help
-
I am to extract data from netCDF file using R for specific loaction the code i've written as showen and I have an error at the end of the code
I need some help with extracting date from NetCDF files using R , I downloaded them from cordex (The Coordinated Regional climate Downscaling Experiment). In total I have some files. This files have dimensions of (longitude, latitude, time) and the variable is maximum temperature (tasmax). At specific location, I need to extract data of tasmax at different time. In total I have some files. This files have dimensions of (longitude, latitude, time) and variable maximum temperature (tasmax). At specific location, I need to extract data of tasmax at different time.I wrote the code using R but at the end of code, an error appeared. Error ( location subscript out of bounds)
getwd() setwd("C:/Users/20120/climate change/rcp4.5/tasmax")
dir() library ("ncdf4") libra,-ry(ncdf4.helpers) library ("chron") ncin <- nc_open("tasmax_AFR-44_ICHEC-EC-EARTH_rcp45_r1i1p1_KNMI-RACMO22T_v1_mon_200601-201012.nc") lat <- ncvar_get(ncin, "lat") lon <- ncvar_get(ncin, "lon") tori <- ncvar_get(ncin, "time") title <- ncatt_get(ncin,0,"title") institution <- ncatt_get(ncin,0,"institution") datasource <- ncatt_get(ncin,0,"source") references <- ncatt_get(ncin,0,"references") history <- ncatt_get(ncin,0,"history") Conventions <- ncatt_get(ncin,0,"Conventions") tustr <- strsplit(tunits$value,"") ncin$dim$time$units ncin$dim$time$calendar tas_time <- nc.get.time.series(ncin, v = "tasmax", time.dim.name = "time") tas_time[c(1:3, length(tas_time) - 2:0)] tmp.array <- ncvar_get(ncin,"tasmax") dunits <- ncatt_get(ncin,"tasmax","units") tmp.array <- tmp.array-273.15 tunits <- ncatt_get(ncin,"time","units") nc_close(ncin) which.min(abs(lat-28.9)) which.min(abs(lon-30.2)) tmp.slice <- tmp.array[126,32981,] tmp.sliceError in tmp.array[126, 32981, ] : subscript out of bounds
-
Can't group employees list without years
my issue is that when I want to group a set of employees according to the month of their birth date, Odoo separates the months by years, which I want to avoid because i wanna generate a list of people who have their birthday in the month.
This is code:
<filter name="birthday_groupby" string="Birthday month" domain="[]" context="{'group_by': 'birthday:week'}" />And this is result:
As you can see, it separates by months, but also by years. I think solution may be more easier than than I think, but i can't figure it out.
-
Group unique values in r
I have a following data:
I want o summarise the data in three columns by Subject type and other two columns sates number of schools and number of students by each subject type. I have used group_by function by it keeps giving the number of types subjects appeared rather then by the number of schools or students
TIA
-
Python - How to get value of a key from position of dictionary inside a list
If i have a list of coordinates such as follows:
coordinatestop = [{'x': 450, 'y': 0}, {'x': 630, 'y': -200}, {'x': 450, 'y': 200}, {'x': -450, 'y': 0}, {'x': -630, 'y': 200}, {'x': 450, 'y': -200}, {'x': -270, 'y': -200}, {'x': 270, 'y': -200}, {'x': -630, 'y': -200}]How would i go about getting or printing the value for the key 'x' of the dictionary in the first position of the list, in this case 450?
Thanks in advance.
-
Is there a way to pass keys into a JavaScript array where a a string might match multiple values of an object?
So, if I have only one key to match, then something like:
var str = "foo"; let [key,val] = Object.entries(obj).find(([key,val]) => val== str); return key;would work beautifully. But is there a way to add multiple keys if the value matches?
To give example of an object that might match the above string:
obj = { quay: "foo", key1: "blargh", yaya: "foo", idnet: "blat", blah: "foo", hahas: "blargh" }What I want to do is return all of the "foo" keys (
quay,yaya, andblah) based on the matching varstrfrom above.I'm sure the answer is something simple I'm overlooking.
-
case_when fails when condition checks for rows that don't exist
Consider this data:
df <- data.frame(group = c(1, 2, 2, 2), start = c(2, 7, 7, 7), stop = c(8, 7, 8, 9), unstop = c(10, 7, 9, 10))I now want to setup a more or less simple case_when per group in the form of "if first row do this, if second row do that". However, I'm getting an error. I assume it's because group 1 does only have one row so the condition can't be checked:
df |> group_by(group) |> mutate(n_rows = n(), split_weeks = case_when(n_rows == 1 ~ str_c(start:stop, collapse = ","), n_rows > 1 & row_number() == 1 ~ str_c(c(start:stop, unstop:lead(stop)), collapse = ","), TRUE ~ "fail")) Error in `mutate()`: ! Problem while computing `split_weeks = case_when(...)`. ℹ The error occurred in group 1: group = 1. Caused by error in `unstop:lead(stop)`: ! NA/NaN argument Run `rlang::last_error()` to see where the error occurred.Any idea what's going on here?
I assume it has to do with the
leadfunction, because if I remove that part, I "only get warnings, but at least I get a result.Expected output:
# A tibble: 4 × 6 # Groups: group [2] group start stop unstop n_rows split_weeks <dbl> <dbl> <dbl> <dbl> <int> <chr> 1 1 2 8 10 1 2,3,4,5,6,7,8 2 2 7 7 7 3 7,8 3 2 7 8 9 3 fail 4 2 7 9 10 3 fail -
Create and fill new columns based on range information from two other columns
I have the following data:
df <- data.frame(group = c(1, 1, 1, 2, 2, 2), start = c(2, 2, 2, 7, 7, 7), stop = c(4, 7, 8, 7, 8, 9), unstop = c(5, 7, 10, 7, 9, 10))I now want to do the following:
- Create new columns that have the names "week_1", "week_2" ... "week_10", "week_n".
- Within each group for the FIRST ROW, I check which weeks the row was "active" in, i.e. it started in week 2, and stopped in week 4, so the row was active in week 2, 3, 4. I now want to populate the respective week columns with a 1.
- Within each group for ALL OTHER EXCEPT THE LAST ROW, I do the same check, but now populate based on the unstop value of that row and the stop value of the next row.
- Within each group for the LAST ROW, I do the same check, but now populate based on the range from unstop to 10 (which is the last week in my case).
I have a theoretical way. The problem is that my real-life data has 80k rows (consisting of 60k groups) and I'd need to create ~200 of such week-columns. Even filtering on 10 rows only takes ~30s for the code below.
So I'm looking for a more elegent/smarter/FASTER solution.
Expected outcome:
# A tibble: 6 × 14 # Groups: group [2] group start stop unstop week_1 week_2 week_3 week_4 week_5 week_6 week_7 week_8 week_9 week_10 <dbl> <dbl> <dbl> <dbl> <int> <int> <int> <int> <int> <int> <int> <int> <int> <int> 1 1 2 4 5 0 1 1 1 0 0 0 0 0 0 2 1 2 7 7 0 0 0 0 0 0 1 1 0 0 3 1 2 8 10 0 0 0 0 0 0 0 0 0 1 4 2 7 7 7 0 0 0 0 0 0 1 0 0 0 5 2 7 8 9 0 0 0 0 0 0 0 1 1 0 6 2 7 9 10 0 0 0 0 0 0 0 0 0 1Below is how I would have approached it generally (of course not with manually defining each row_number. Apart from that, the code is also wrong and does not give the expected 0/1 values. It alsow throws many warnings. And finally, this code already runs a few seconds just for this small test data. It would run one month for my 80k/200col data set.
add_weeks <- as_tibble(as.list(setNames(rep(0L, 10), paste0("week_", 1:10)))) df |> bind_cols(add_weeks) |> group_by(group) |> mutate(across(num_range("week_", 1:10), ~ if_else(row_number() == 1 & str_extract(cur_column(), "\\d+$") %in% start:stop, 1L, .)), across(num_range("week_", 1:10), ~ if_else(row_number() == 2 & str_extract(cur_column(), "\\d+$") %in% unstop:lead(stop), 1L, .)), across(num_range("week_", 1:10), ~ if_else(row_number() == 3 & str_extract(cur_column(), "\\d+$") %in% unstop:10, 1L, .))) -
Annotate ggplot2 across multiple facets
I have recently started using the facet_nested function from the ggh4x package and I really like the look of the nested axis. I would like to annotate the plot to show stats that I have run. I have created a dummy dataset to illustrate my problem.
library(tidyverse) library(markdown) library(ggtext) library(ggh4x) df <- data.frame(pretreatment = c("10NA", "10NA","10NA", "NT", "NT", "NT"), timepoint = c("0 h", "6 h","6 h", "0 h", "6 h", "6 h"), treatment = c("baseline", "10NA", "NT","baseline", "10NA", "NT"), mean_copy_no = c(1000, 1500, 1200, 600, 700, 400), sample_id = c(1, 2, 3, 4, 5, 6)) df %>% ggplot(aes(x=sample_id, y = mean_copy_no, fill = treatment)) + geom_col(colour = "black") + facet_nested(.~ pretreatment + timepoint + treatment, scales = "free", nest_line = TRUE, switch = "x") + ylim(0,2000) + theme_bw() + theme(strip.text.x = element_text(size = unit(10, "pt")), legend.position = "none", axis.title.y = element_markdown(size = unit(13, "pt")), axis.text.y = element_text(size = 11), axis.text.x = element_blank(), axis.title.x = element_blank(), axis.ticks.x = element_blank(), strip.text = element_markdown(size = unit(12, "pt")), strip.background = element_blank(), panel.spacing.x = unit(0.05,"line"), panel.grid.major.x = element_blank(), panel.grid.minor.x = element_blank(), panel.border = element_blank())This generates the following plot.
Now my problem is that each of the bars is located within its own facet and not all on one x axis (if you run the code without the theme, it shows it more clearly).
I have drawn how I would like the plot to look.
I would like to add lines and stars to indicate significant differences.
I can easilly add the stars, however I am struggling to add the lines and I understand that this may not even be possible because I am using facets to generate the plot. I just wanted to post the question and see if someone has any suggestion on how to do it in R. Or if there is a way to achieve the nested look without using facets.
*Edited for clarity.
-
New variable calculation with input from multple groups in long format
I was wondering whether the following calculation is possible using
dplyrwithout transforming my data into wide format. My data looks like the following:data <- data.frame(ID = c(rep(1:2, 6)), Date = c(rep(as.Date('2022-03-01'), 4), rep(as.Date('2022-03-02'), 4), rep(as.Date('2022-03-03'), 4)), Type = rep(LETTERS[c(1,1,2,2)], 3), Value = c(1,2,101,102,3,4,103,104,5,6,105,106))My goal is to make a calculation, which involves the value of a certain day from type B, but as well the value from the previous day of type A AND type B. If the calculation would only be within one group, then
dplyr::lagis the way to go. But I do not see the way in this case. I'd like to avoid pivoting my data into wide format.So as an example, I'd like to calculate X = B(t) - A(t-1) * B(t-1), where t is denoting the date. My goal in this case would be something like the following dataframe:
data_goal <- data.frame(ID = c(rep(1:2, 3)), Date = c(rep(as.Date('2022-03-01'), 2), rep(as.Date('2022-03-02'), 2), rep(as.Date('2022-03-03'), 2)), X = c(NA, NA, 103 - 1 * 101, 104 - 2 * 102, 105 - 3 * 103, 106 - 6 * 104))If I would calculate the daily difference for each type on its own, my solution would be
data |> dplyr::arrange(Date) |> dplyr::group_by(ID, Type) |> dplyr::mutate(Diff = Value - dplyr::lag(Value, n = 1))But unfortunately I have no idea how I might extend this.
Any help is highly appreciated!
Thanks a lot!
Note that I am also glad to know, if this is not possible. Then I would move on to transforming the table into wide format and continue from there. My actual data has a lot more types, which is why I'd like to avoid that.
-
from long to wide format multiple variables in R
I have a table in long format like this:
gene tissue tpm A liver 5 A brain 2 B ovary 10 B brain 1 C brain 15 C liver 6I'd like to convert it into a wider format:
gene tissue1 tissue2 tpm1 tpm2 A liver brain 5 2 B ovary brain 10 1 C brain liver 15 6I have tried with
dcastandspreadbut I get this result:gene liver brain ovary A 5 2 NA B NA 1 10 C 6 15 NAWhich is NOT what I want.
Thank you!
-
How do I make sscanf ignore white spaces? C
I'm trying to read formatted data with sscanf in this format:
"%d,%d,%d"for example,
sscanf(buffer, "%d,%d,%d", n1, n2, n3)and it is critic that there is no white space between every number, because the input comes like this, for example:
109,304,249 194,204,482etc.
But when I give sscanf an input like:
13, 56, 89 145, 646, 75it still reads it even though it is not in the specifed format, and should be invalid
Is there is a way to write something such that sscanf will work as I expect it to work?
