2
$\begingroup$

I have a pandas dataframe that reads in a PAF file from minimap2. What I would like to do is take the first 5 columns of the data from to create a BED file.

I used this to extract the first 5 columns:

import pandas as pd
import numpy as np
df=pd.read_csv(tag1_paf, delimiter = "\t")
#print(df[4])

specific_columns=df.iloc[:,[0,1,2,3,4]]
print(specific_columns)

Which outputs this:

read1  47215  20591  21087 +
read2  14111  3478   3973  +
read3  30861  21367  21855 +
read4  4647   767    1257  -
read5  11706  9569   9990  +

But now I want to do math on the third and fourth columns depending on the strand its on. For example, if column 5 = "+" add 1000 to the third column.

I can't figure it out on my own - I've tried looking it up and using the series add/sub methods from pandas (https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Series.add.html) but I'm still confused.

If anyone can help that would be very appreciated!

$\endgroup$
0

1 Answer 1

4
$\begingroup$

The vectorised solution (fast) is,

import pandas as pd
filepath = '/pathtodir/test/bioinfo.csv'

df = pd.read_csv(filepath, delimiter=" ", header=None)

df.loc[df[4] == "+", 'new2'] = df[2] + 1000
df.loc[df[4] == "-", 'new2'] = df[2]
df = df.drop([2], axis=1)
df = df[[0,1,'new2',3,4]]
print (df)
- 0 1 new2 3 4
0 read1 47215 21591.0 21087 +
1 read2 14111 4478.0 3973 +
2 read3 30861 22367.0 21855 +
3 read4 4647 767.0 1257 -
4 read5 11706 10569.0 9990 +

An alternative method to re-order the columns then the answer is Automate the def function in pandas for correlation

Original data

0 1 2 3 4
0 read1 47215 20591 21087 +
1 read2 14111 3478 3973 +
2 read3 30861 21367 21855 +
3 read4 4647 767 1257 -
4 read5 11706 9569 9990 +

Final solution

df = pd.read_csv(filepath, delimiter=" ", header=None)
df.loc[df[4] == "+", 'new2'] = df[2] + 1000
df.loc[df[4] == "-", 'new2'] = df[2] - 1000
df.loc[df['new2'] < 0, 'new2'] = 1
df = df.drop([2], axis=1)
df = df[[0,1,'new2',3,4]]
print (df)
- 0 1 new2 3 4
0 read1 47215 21591.0 21087 +
1 read2 14111 4478.0 3973 +
2 read3 30861 22367.0 21855 +
3 read4 4647 1.0 1257 -
4 read5 11706 10569.0 9990 +

... worked too

$\endgroup$
3
  • $\begingroup$ Thank you so much! That's exactly what I was looking for. Quick follow up question - I was able to add in parameters where if df[4] = "-" then subtract 1000 from df[2] but if there is an instance where the value becomes negative I would just like to make it equal 1. Is there a quick way to add that into this as well? I don't wanna mess around with if statements if it's going to yell at me for formatting $\endgroup$
    – rimo
    Feb 24, 2023 at 21:36
  • $\begingroup$ @rimo modified above below the ----. Any value that is negative will be changed to 1 $\endgroup$
    – M__
    Feb 24, 2023 at 22:45
  • $\begingroup$ The -1000 is now included. $\endgroup$
    – M__
    Feb 24, 2023 at 22:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.