# Math on Pandas Columns

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
#print(df[4])

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

Which outputs this:

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!

The vectorised solution (fast) is,

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

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.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

• 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
– rimo
Feb 24, 2023 at 21:36
• @rimo modified above below the ----. Any value that is negative will be changed to 1
– M__
Feb 24, 2023 at 22:45
• The -1000 is now included.
– M__
Feb 24, 2023 at 22:51