Math on Pandas Columns

Question

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!

Answer 1

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