Sorry if this has been answered. I did look through other posted questions, which helped me get to where I am, but I am now struggling. I am new to R/ computers, and any help would be massively appreciated!

I have a dataset on which each row represents a subject. The data set is very "wide" with roughly 200 variables (columns). I also have a "long" data set with laboratory testing results performed on samples collected from each subject. I have merged these data sets so that now each subject can appear multiple times, i.e, have multiple rows depending on how many lab samples were received and tested.

I now want to try to analyse how many samples of each sample type were sent for each subject. Below is a simplified example that I hope helps explain.

#example data frame
sample_type <- c("blood", "blood", "sputum", "blood", "csf", "blood", "csf", "sputum", "sputum", "sputum", "sputum", "blood", "csf", "csf")
id <- c(1,1,1,2,3,4,4,5,5,5,6,6,7,7)
example_data <- data.frame(id, sample_type)

   id sample_type
1   1       blood
2   1       blood
3   1      sputum
4   2       blood
5   3         csf
6   4       blood
7   4         csf
8   5      sputum
9   5      sputum
10  5      sputum
11  6      sputum
12  6       blood
etc.

Here, I have tried to create new variables. This works ok if I omit the "distinct" function at the end; however, when the distinct function collapses the output, it appears to select only one sample_type and then omits data in the other columns

example_data %>% 
  add_count(id, sample_type, name = "test_freq") %>% 
  mutate(blood_freq = case_when(sample_type == "blood" ~ test_freq),
         sputum_freq = case_when(sample_type == "sputum" ~ test_freq),
         csf_freq = case_when(sample_type == "csf" ~ test_freq)) %>% 
  distinct(id, .keep_all = 
             T)

Without the distinct function, patient (id) no. 1, for example, has 2 in the blood_freq column and a 1 in the sputum_freq column

   id sample_type test_freq blood_freq sputum_freq csf_freq
1   1       blood         2          2          NA       NA
2   1       blood         2          2          NA       NA
3   1      sputum         1         NA           1       NA
4   2       blood         1          1          NA       NA
5   3         csf         1         NA          NA        1
6   4       blood         1          1          NA       NA

with the distinct function added, the blood_freq is correct, but the sputum_freq is now NA

  id sample_type test_freq blood_freq sputum_freq csf_freq
1  1       blood         2          2          NA       NA
2  2       blood         1          1          NA       NA
3  3         csf         1         NA          NA        1
4  4       blood         1          1          NA       NA

Below is the closest I have come; however, the output is slightly odd as the values from the "test_freq" column are not unique. I am also worried that I will have to define what I want to do with the rest of my data in the many other columns if I start pivoting longer. But perhaps this is not something I should be worried about?

example_data %>% 
  add_count(id, sample_type, name = "test_freq")  %>% 
  pivot_wider(names_from = sample_type, values_from = test_freq) %>% 
  distinct(id, .keep_all = TRUE)

     id blood     sputum    csf      
              
1     1      
2     2         
3     3         
4     4      
5     5         
6     6      
7     7         

Ultimately, I would like to say that x many subjects had y many sputum tests sent, and x many had y many blood and z many sputums etc.

I found that this code gives a good overview of tests sent but does not allow me to see how many patients had one blood and one sputum, for example, so I am trying got transform the data as shown in these examples to better analyse it, but just can't quite get there!

> example_data %>% group_by(id) %>% count(sample_type) %$% table(sample_type,n)
           n
sample_type 1 2 3
     blood  3 1 0
     csf    2 1 0
     sputum 2 0 1

I hope this makes sense. I am open to all suggestions or even any other ideas that achieve this in a better way.

Many thanks!!