# Creating a count table using R

I’m trying to develop a table from a series of lists generated using the Immunarch library to process TCR sequencing results. Each list is comprised of CDR3.aa (clone) information which are character strings and their count in a particular sample. The clones are short but vary between 7 and 20 (or more). Each list has a header that identifies the sample sequenced. I have a list of 66 samples. Each sample can have several thousand clone strings. Not every clone is contained in every sample so the number of clones listed in the samples varies. Here’s an example of the structure of a single sample list.

$PreAg_18_2 CDR3.aa Count CASSYGTAYTGELFF 1623 CASSRGDSDNSPLHF 1440 CASSREKAFF 1161 CSGMGALAKNIQYF 949 CSAYTGLSYEQYF 813 CASSLSLAVNSPLHF 634 CAIRDTPGSPQHF 574 CATGQVNTEAFF 555 CASSLKGQGGSPLHF 499 CASSYSRSPQPQHF 478  I want to combine the results in a single table showing the clone counts with all the clone strings listed on the y-axis and the sample ID listed on the x-axis. For example:  10_pep_10_1 preAg_10_2 Dec_2_18_1 …... CASSYGTAYTGELFF 1623 234 0 CASSRGDSDNSPLHF 1440 522 28 CASSREKAFF 1161 445 50 CSGMGALAKNIQYF 949 24 0 CASSYSRSPQPQHF 478 0 398 . .  Currently, I'm working with R because that's what Immunarch is in, but I wonder if a) There's a better way to do this in Python, or b) If it's doable in r, then how do I generate the matrix matches. The main problem is that not all the samples have the same number of clones, so I first have to separate each sample data before I can convert it to a data frame, and then how can I match the actual count with the actual clone. I can't just merge the data frames. Any suggestion is greatly appreciated. Here's what i have for code right now. Code: library(immunarch) library(stringr) library(plyr) immdata = repLoad("/mnt/data/Development/Analysis_Scripts/input_files/") all <- immdata$data

# Get list headers (names) and convert to df
sample.id <- names(all)
sample.id <- data.frame(sample.id)

# Get list of clones and filter for unique clones per list.
for (i in 1:length(all)){
all[[i]]$Sample.ID<-names(all)[i] all[[i]]<-all[[i]][,c("CDR3.aa", "Clones")] } # all is the variable that contains the list of samples and clones. all.split <- split(all, ) # make vector of all clones all.clones <- unlist(all, use.names=FALSE) # Removes clone repeats all.clones.u <- unique(all.clones) # convert list of clones to data frame all.clones.u <- data.frame(all.clones.u)  At this point I have both the list of sample ids and the list of clones. My problem is now matching each count with respective sample and clone in the table. Any suggestions? • It's hard to come up with a working code snippet if the workspace can't be recreated. Could you provide a minimal mock example of all that we can use? But based on what I think you want to do, I'd probably use a combination of left_join of the dplyr package combined with Reduce. Aug 10, 2019 at 13:58 • Thanks, Sebastian, I apologize for not providing a more clear picture of my current data, but I think you captured it correctly. I'll give it a try. Aug 12, 2019 at 13:03 ## 1 Answer You could give a try with dplyr's full_join() and purrr's reduce(). full_join() will make sure that you won't lose any data and reduce() will merge tables in your list from left to right, left being the first table of the list in the first iteration but would become merged table1+table2 in the second iteration and so on. This left to right "reduction" will also be helpful when renaming the columns of the resulting table. Without your workspace I have attempted to replicate your data: There is a list of 3 data frames, each with 2 columns: library(dplyr) library(purrr) df1 <- data.frame(CDR3.aa = c("A", "B", "C"), Count = c(10,20,100)) df2 <- data.frame(CDR3.aa = c("A", "B", "D"), Count = c(100,200,300)) df3 <- data.frame(CDR3.aa = c("D", "E", "F"), Count = c(10,20,100)) my_list <- list(df1 = df1, df2 = df2, df3 = df3) > my_list$df1
CDR3.aa Count
1       A    10
2       B    20
3       C   100

$df2 CDR3.aa Count 1 A 100 2 B 200 3 D 300$df3
CDR3.aa Count
1       D    10
2       E    20
3       F   100

# "by" argument specifies which column to use when merging
my_table <- reduce(my_list, full_join, by = "CDR3.aa")

names(my_table) <- c("CDR3.aa", names(my_list))

> my_table
CDR3.aa df1 df2 df3
1       A  10 100  NA
2       B  20 200  NA
3       C 100  NA  NA
4       D  NA 300  10
5       E  NA  NA  20
6       F  NA  NA 100


Clonotypes that are not present in table will be "NA".

• Sounds like it could work. I'll give it a try and report back. Thanks. Aug 12, 2019 at 13:02
• haci, it worked very well. I just had to convert the initial samples to data.frames so I could run reduce on it. It's a very clean elegant solution. Thank you very much for your help. Aug 12, 2019 at 18:33