<p>The vectorised solution (fast) is,</p>
<pre><code>import pandas as pd
filepath = '/pathtodir/test/bioinfo.csv'

df = pd.read_csv(filepath, delimiter=&quot; &quot;, header=None)

df.loc[df[4] == &quot;+&quot;, 'new2'] = df[2] + 1000
df.loc[df[4] == &quot;-&quot;, 'new2'] = df[2]
df = df.drop([2], axis=1)
df = df[[0,1,'new2',3,4]]
print (df)
</code></pre>
<div class="s-table-container">
<table class="s-table">
<thead>
<tr>
<th>-</th>
<th>0</th>
<th>1</th>
<th>new2</th>
<th>3</th>
<th>4</th>
</tr>
</thead>
<tbody>
<tr>
<td>0</td>
<td>read1</td>
<td>47215</td>
<td>21591.0</td>
<td>21087</td>
<td>+</td>
</tr>
<tr>
<td>1</td>
<td>read2</td>
<td>14111</td>
<td>4478.0</td>
<td>3973</td>
<td>+</td>
</tr>
<tr>
<td>2</td>
<td>read3</td>
<td>30861</td>
<td>22367.0</td>
<td>21855</td>
<td>+</td>
</tr>
<tr>
<td>3</td>
<td>read4</td>
<td>4647</td>
<td>767.0</td>
<td>1257</td>
<td>-</td>
</tr>
<tr>
<td>4</td>
<td>read5</td>
<td>11706</td>
<td>10569.0</td>
<td>9990</td>
<td>+</td>
</tr>
</tbody>
</table>
</div>
<p>An alternative method to re-order the columns then the answer is <a href="https://bioinformatics.stackexchange.com/questions/20605/automate-the-def-function-in-pandas-for-correlation">Automate the def function in pandas for correlation</a></p>
<p>Original data</p>
<div class="s-table-container">
<table class="s-table">
<thead>
<tr>
<th></th>
<th>0</th>
<th>1</th>
<th>2</th>
<th>3</th>
<th>4</th>
</tr>
</thead>
<tbody>
<tr>
<td>0</td>
<td>read1</td>
<td>47215</td>
<td>20591</td>
<td>21087</td>
<td>+</td>
</tr>
<tr>
<td>1</td>
<td>read2</td>
<td>14111</td>
<td>3478</td>
<td>3973</td>
<td>+</td>
</tr>
<tr>
<td>2</td>
<td>read3</td>
<td>30861</td>
<td>21367</td>
<td>21855</td>
<td>+</td>
</tr>
<tr>
<td>3</td>
<td>read4</td>
<td>4647</td>
<td>767</td>
<td>1257</td>
<td>-</td>
</tr>
<tr>
<td>4</td>
<td>read5</td>
<td>11706</td>
<td>9569</td>
<td>9990</td>
<td>+</td>
</tr>
</tbody>
</table>
</div><hr />
<p>Final solution</p>
<pre><code>df = pd.read_csv(filepath, delimiter=&quot; &quot;, header=None)
df.loc[df[4] == &quot;+&quot;, 'new2'] = df[2] + 1000
df.loc[df[4] == &quot;-&quot;, 'new2'] = df[2] - 1000
df.loc[df['new2'] &lt; 0, 'new2'] = 1
df = df.drop([2], axis=1)
df = df[[0,1,'new2',3,4]]
print (df)
</code></pre>
<div class="s-table-container">
<table class="s-table">
<thead>
<tr>
<th>-</th>
<th>0</th>
<th>1</th>
<th>new2</th>
<th>3</th>
<th>4</th>
</tr>
</thead>
<tbody>
<tr>
<td>0</td>
<td>read1</td>
<td>47215</td>
<td>21591.0</td>
<td>21087</td>
<td>+</td>
</tr>
<tr>
<td>1</td>
<td>read2</td>
<td>14111</td>
<td>4478.0</td>
<td>3973</td>
<td>+</td>
</tr>
<tr>
<td>2</td>
<td>read3</td>
<td>30861</td>
<td>22367.0</td>
<td>21855</td>
<td>+</td>
</tr>
<tr>
<td>3</td>
<td>read4</td>
<td>4647</td>
<td>1.0</td>
<td>1257</td>
<td>-</td>
</tr>
<tr>
<td>4</td>
<td>read5</td>
<td>11706</td>
<td>10569.0</td>
<td>9990</td>
<td>+</td>
</tr>
</tbody>
</table>
</div>
<p>... worked too</p>

CC BY-SA 4.0
2023-02-24T21:36:55.860Z
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
CC BY-SA 4.0
2023-02-24T22:45:45.167Z
@rimo modified above below the `----`. Any value that is negative will be changed to 1
M__
CC BY-SA 4.0
2023-02-24T22:51:00.137Z
The `-1000` is now included.
M__
CC BY-SA 4.0
2023-02-24T20:15:03.090Z
2023-02-24T23:39:21.790Z
2023-02-24T23:39:21.790Z
M__
<p>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.</p>
<p>I used this to extract the first 5 columns:</p>
<pre class="lang-py prettyprint-override"><code>import pandas as pd
import numpy as np
df=pd.read_csv(tag1_paf, delimiter = &quot;\t&quot;)
#print(df[4])

specific_columns=df.iloc[:,[0,1,2,3,4]]
print(specific_columns)
</code></pre>
<p>Which outputs this:</p>
<pre><code>read1  47215  20591  21087 +
read2  14111  3478   3973  +
read3  30861  21367  21855 +
read4  4647   767    1257  -
read5  11706  9569   9990  +
</code></pre>
<p>But now I want to do math on the third and fourth columns depending on the strand its on. For example, if column 5 = &quot;+&quot; add 1000 to the third column.</p>
<p>I can't figure it out on my own - I've tried looking it up and using the series add/sub methods from pandas (<a href="https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Series.add.html" rel="nofollow noreferrer">https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Series.add.html</a>) but I'm still confused.</p>
<p>If anyone can help that would be very appreciated!</p>

<python><phylogenetics><phylogeny><sequence-analysis><pandas>
Math on Pandas Columns
# Math on Pandas Columns

(Asked by: username_0 on 2023-02-24)

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!

**Tags:** python, phylogenetics, phylogeny, sequence-analysis, pandas

---

## 1 answers

### Answer by username_1 on 2023-02-24. Score: 4

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

> **Comments:**
> **username_0**: 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  
> **username_1**: @rimo modified above below the `----`. Any value that is negative will be changed to 1  
> **username_1**: The `-1000` is now included.  

---

