I have two columns of data containing merged peak chr start, start, end, gene name data that looks like the following:
Essentially what I did was copy and pasted the values I obtained using a script that finds peaks that are similar between different chip-seq experiments. Column 1 contains peaks in protein 1, 2, and 3 and Column 2 contains peaks in protein 1, 2, 3, and 4.
What I am trying to do now is find all the duplicated values in Columns A that are also found in Column B ... not just by row but if there is a similar value in B2 and it is found in A4506 then I want it to find that value also and then spit all the duplicated values into Column C.
While this dataset isn't essentially "huge" ... only about 4.5k rows in either column, it's too much to go through by hand and I can't find a good answer on any internet topic. I've tried varieties of VLOOKUP, IFCOUNT and other functions but they only search the row 1 in column A to the row 1 in column B or simply do not work on the Mac version of Excel (for instance I found a add-in that could supposedly do this but the add-in was not able to be used by Mac, I got an error indicating it was not properly formatted for mac).
Can anyone help or point me to a correct source?
1 answer
Hi daily
The data looks easily transformable to bed-format. Afterwards, you can use intersectBed to find overlapping features.
Cheers,
Michael
not tested but something like this would work to create a bed file:
cat myFile.txt | tr ',' '\t' > myOutput.bed
since you have two cols you could try
cat myFile.txt | awk '{print $1}' | tr ',' '\t' > myOutput.bed
cat myFile.txt | awk '{print $2}' | tr ',' '\t' >> myOutput.bed
Was able to make this work, but it seems to have returned most of the coordinates which I find odd.
I used:
intersectBed -a fourproteins.bed -b threeproteins.bed > mergedproteins.bed
Do you know if intersectBed looks for only exact coordinates or for coordinates between a set distance of the coordinate on file?
it depends how you set it up, the documentation shows that you can consider even only one nt overlap.
Log in to answer this question.
-
Can someone help me with searching overlapping values between two files?
written by mxm189 •
-
Anova for proteomics
written by FF •
-
R object can allow duplicated column name and storing different types of values
written by cwwong13 •
-
How to write it in R language?
written by Farah
-
data prerprocessing coding
written by sskimvd •
-
how to sum up the columns to remove the duplicated row names in RSEM output?
written by John
-
Remove lines if duplicated values in column 1 has specific condition for column 2
written by waqasnayab
-
How to arrange the second column according to the first column?
written by Grunir112 •
-
How to identify a certain pattern for histone marks
written by dally
-
Using pamr.train to find Shrunken Centroid
written by beverly893 •
Hi, do you want any kind of solutions (with scripts) or only Excel solution?
Any solution will work. I am a wet lab research assistant with only minor knowledge of programming languages so if you could just try to give as much as a detailed overview as you can be bothered of how your script works and what data would go where that would be appreciated.
EDIT: I totally missed the excel answer you found. I will attempt this, but it seems to based on a windows os from screenshots. Still I should be able to reproduce it. I will edit with any results.
EDIT: Excel link did not work.