Answers.Body: <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>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-02-24T21:36:55.860Z
Answers.Text: 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
Answers.UserDisplayName: rimo
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-02-24T22:45:45.167Z
Answers.Text: @rimo modified above below the `----`. Any value that is negative will be changed to 1
Answers.UserDisplayName: M__
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-02-24T22:51:00.137Z
Answers.Text: The `-1000` is now included.
Answers.UserDisplayName: M__
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-02-24T20:15:03.090Z
Answers.LastActivityDate: 2023-02-24T23:39:21.790Z
Answers.LastEditDate: 2023-02-24T23:39:21.790Z
Answers.UserDisplayName: M__
Body: <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>
  
Tags: <python><phylogenetics><phylogeny><sequence-analysis><pandas>
Title: Math on Pandas Columns
ThreadText: # 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:
  
  ```py
  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](https://bioinformatics.stackexchange.com/questions/20605/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**: @username_0 modified above below the `----`. Any value that is negative will be changed to 1  
  > **username_1**: The `-1000` is now included.  
  
  ---
  
  