Hello there, I have a list.txt (big file) contains 2000 samples and 18000 coordinates (same as below file 1).
Coordinates Sample Values
chr1:110238914-110324454 SampleB 1
chr1:110238914-110324454 SampleC 3
chr1:110238914-110324454 SampleD 1
chr5:65562670-65627908 SampleD 1
chr5:65562670-65627908 SampleA 1
chr5:65562670-65627908 SampleB 4
chr5:65562670-65627908 SampleC 1
chr2:158248715-158335919 SampleB 1
chr2:158248715-158335919 SampleA 0
chr2:158248715-158335919 SampleC 1
Actually I want to make a matrix by the above file. Whereas coordinates to be as rows name and samples as columns name, then if the coordinate has related sample put the related value in the matrix, if the coordinate does not the value for the sample just put 2 in the matrix, the result should be same the below.
Coordinates SampleA SampleB SampleC SampleD
chr1:110238914-110324454 2 1 3 1
chr5:65562670-65627908 1 4 1 1
chr2:158248715-158335919 0 1 1 2
I would really appreciate it , if I can receive any scripts for linux,bash (preferably) or R to get this result?
)
•
written
by
hosin •
You can use tidyr::pivot_wider to get to what you need. Your problem here is the simplest case, so figuring out the exact usage from the manual should be easy enough.
In case the OP runs into trouble, here's the exact code for their data. df <- tidyr::pivot_wider(df, names_from="Sample", values_from="Values").
If I understand you correctly you can add the argument values_fill=2.
Can you post your current code here?
What code are you using to define df?
I just follow this manual: "https://tidyr.tidyverse.org/reference/pivot_wider.html" as mentioned above, that is the exact code. I did not consider data frame. This is our input file:
Coordinates Sample Values
chr1:110238914-110324454 SampleB 1
chr1:110238914-110324454 SampleC 3
chr1:110238914-110324454 SampleD 1
chr5:65562670-65627908 SampleD 1
chr5:65562670-65627908 SampleA 1
chr5:65562670-65627908 SampleB 4
chr5:65562670-65627908 SampleC 1
chr2:158248715-158335919 SampleB 1
chr2:158248715-158335919 SampleA 0
chr2:158248715-158335919 SampleC 1
> mydat=read.table(file.choose())
> df <- read.table("list.txt", sep="\t", header=TRUE, stringsAsFactors=FALSE)
Error in file(file, "rt") : cannot open the connection
In addition: Warning message:
In file(file, "rt") :
cannot open file 'list.txt': No such file or directory
You need to read the file into a data.frame named 'df' first. df <- read.table("file.txt", sep="\t", header=TRUE, stringsAsFactors=FALSE). Change the file name and delimiter as appropriate.
Sorry, previously I got this error
> mydat=read.table(file.choose())
> df <- read.table("list.txt", sep="\t", header=TRUE, stringsAsFactors=FALSE)
Error in file(file, "rt") : cannot open the connection
In addition: Warning message:
In file(file, "rt") :
cannot open file 'list.txt': No such file or directory
Log in to answer this question.
Relevant post from SO - "reshape long to wide":