How to calculate/ extract co-ordinates of TSS and TTS from gtf file in excel ?

Question

[isha.lily20]

Hello Researchers

I want to know the range of co-ordinates for TSS and TTS in plus and minus strand from gtf file in excel ?

means from 5pUTR to TSS and TSS to CDS in gtf file, How can i fetch extract co-ordinates For TSS?

same way, from TTS to 3pUTR, How to fetch co-ordinates for TTS ?

Thank you

[i.sudbery]

I'm not entirely sure what you mean by "from 5pUTR to TSS" and "from TTS" to 3p UTR?

The TSS is the first base of the 5pUTR and the TTS is the last base of the 3p UTR (corrected for strand).

Answer 1

[i.sudbery]

This is really a job for which Excel is going to struggle.

It might be possible, depending on your abilities with excel and what your GTF looks like.

If you GTF contains transcript lines, then that will have things substantially easier.

It will be easiest to do this seperately for the + and - strands. Start by filtering the third column so that you are only showing lines where the third column contains "transcript" and the strand column contains +. Copy the content of the first (chromosome) and fourth (start) columns into a new sheet. Now refilter so you are only showing the transcript lines for the -ve strand. Copy the content of the first (chromosome) and fifth (end) columns into your new sheet.

If you don't have "transcript" lines, the only way to do it would be with some sort of complex pivot table, grouping things on transcript_id, (which is beyond my excel skills). To be honest, if you are not already an expert at pivot tables, it would probably be easier to learn R or python than to do that.