-
|
Alrighty, I have a table that's going to require a lot of manipulation and I think I need some help/suggestions on how to approach it. The table is here: https://github.com/epigeneticstoocean/2018_L18-adult-methylation/blob/main/data/whole_tx_table.csv It's a large CSV with 67,891 rows. Here's an excerpt of what it looks like:
What I want:
Problems that I need to figure out how to handle:
If anyone can suggest an approach, I'd greatly appreciate. Feeling a bit overwhelmed when looking at this table and having to handle so many exceptions within the data. |
Beta Was this translation helpful? Give feedback.
Replies: 3 comments 8 replies
-
I would you create new column that takes Not super easy but idea.. would probably need to split, edit and stack back. |
Beta Was this translation helpful? Give feedback.
-
|
This is a killer idea. Thanks! I think it should be "easy" to replace |
Beta Was this translation helpful? Give feedback.
-
|
Again, thanks! Here's how I replaced the dots in the whole_tx_table <- whole_tx_table %>% mutate(gene_name = ifelse(gene_name == ".", t_name, gene_name)) |
Beta Was this translation helpful? Give feedback.
-
|
Making progress... Counting unique table(whole_tx_table[,"gene_name"]) |
Beta Was this translation helpful? Give feedback.
-
|
EDITED: This will not work. Leaving in discussion for reference, though. More progress... This will count all unique whole_tx_table_fpkm.s12m <- whole_tx_table %>% group_by(gene_name) %>% summarise(count = length(FPKM.S12M > 0)) |
Beta Was this translation helpful? Give feedback.
-
|
This will rename the column to have a sample name-specific count (albeit, not dynamically as will be needed/desired), but I'm just documenting the process: whole_tx_table %>% group_by(gene_name) %>% summarise(count = length(FPKM.S12M > 0)) %>% rename("trans_count_fpkm.s12m" = count) |
Beta Was this translation helpful? Give feedback.
-
|
Create vector of library names. Will be used for looping through columns. library_names <- paste("FPKM", sample_metadata_full$OldSample.ID, sep = ".") |
Beta Was this translation helpful? Give feedback.
-
|
I think I might be switching over to using the shell and Firstly, just creating a test file. Code below sets input field separator as a awk -F"," 'BEGIN {OFS"\t"} NR > 1 {print $10, $12, $14}' whole_tx_table.csv
| sort -k1
| head -n 50
| column -t
> unique_gene_name_test.txtThat gives this output: Then, using awk '{if ($2 > 0) gene_name[$1]++}; {if ($3 > 0) arr[$1]++}; END{ for (var in gene_name) print var, "\t", gene_name[var], arr[var]}' unique_gene_name_test.txtThe output looks correct... So, this seems to work! This is probably what I'll go with. Things I don't like, but probably won't take the time to fix:
The final command for to handle all 24 samples will be MESSY! |
Beta Was this translation helpful? Give feedback.
-
|
Guess I should've just gone to StackOverflow right away. Posted my problem and received a solution in Here's the solution, which is based off of the example table I posted above: library(dplyr)
desired_result = your_data %>%
group_by(name_of_first_column) %>%
summarize(across(everything(), ~sum(. > 0)))I'll have to figure out some usage of |
Beta Was this translation helpful? Give feedback.
-
|
Aaaaand, here's a "one-liner" to select appropriate columns and get the desired output from all of them: whole_tx_table %>% select(starts_with(c("gene_name", "FPKM"))) %>% group_by(gene_name) %>% summarise((across(everything(), ~sum(. > 0)))) |
Beta Was this translation helpful? Give feedback.
-
|
And, just like that, I have a final table with new column names and everything! Beautiful! # Create table of transcript counts per gene per sample
transcript_counts <- whole_tx_table %>%
select(starts_with(c("gene_name", "FPKM"))) %>%
group_by(gene_name) %>%
summarise((across(everything(), ~sum(. > 0))))
# Rename columns
names(transcript_counts) <- gsub(x = names(transcript_counts), pattern = "FPKM", replacement = "transcript_counts") |
Beta Was this translation helpful? Give feedback.
Guess I should've just gone to StackOverflow right away. Posted my problem and received a solution in
Rin less than 5 minutes (seriously).Here's the solution, which is based off of the example table I posted above:
I'll have to figure out some usage of
select()to get the full input table and I'll have to figure out a way to programmatically rename all the columns in the output data, but that seems much more feasible for my skillset.