Answers.Body: <p>You can use <a href="https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.groupby.html" rel="nofollow noreferrer">groupby()</a> as following :</p>
  <p><code>dd[&quot;new_synergy&quot;] = dd[[&quot;ANCHOR_NAME&quot;, &quot;LIBRARY_NAME&quot;, &quot;Synergy&quot;]].groupby(by=[&quot;ANCHOR_NAME&quot;, &quot;LIBRARY_NAME&quot;]).any()</code></p>
  <p>Explenation : you want to group your dataframe by both <code>ANCHOR_NAME</code> and <code>LIBRARY_NAME</code>, and return True if any of the <code>synergy</code>value is true, which is what <code>any()</code> does.</p>
  <p>Note : you can check for all methods available for <code>GroupBy</code> objects <a href="https://pandas.pydata.org/docs/reference/groupby.html" rel="nofollow noreferrer">here</a>.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-05-27T06:28:19.190Z
Answers.LastActivityDate: 2022-05-27T06:28:19.190Z
Answers.UserDisplayName: TUI lover
Answers.Body: <p>To elaborate on <a href="https://bioinformatics.stackexchange.com/a/19130/292">this answer</a>, here is a full working example where I assume that <code>&quot;BARCODE&quot;</code>, <code>&quot;ANCHOR_NAME&quot;</code> and <code>&quot;LIBRARY_NAME&quot;</code> are used as <code>MultiIndex</code>:</p>
  <p>Creating the test data:</p>
  <pre class="lang-python prettyprint-override"><code>from io import StringIO
  import pandas as pd
  data_str = &quot;&quot;&quot;BARCODE\tANCHOR_NAME\tLIBRARY_NAME\tANCHOR_CONC\tSynergy\tHSA\tBliss
  14482\t5-Fluorouracil\tCisplatin\t2.5\tFALSE\tFALSE\tFALSE
  14482\t5-Fluorouracil\tCisplatin\t10\tFALSE\tFALSE\tFALSE
  14482\t5-Fluorouracil\tDocetaxel\t2.5\tFALSE\tTRUE\tTRUE
  14482\t5-Fluorouracil\tDocetaxel\t10\tFALSE\tFALSE\tFALSE
  14482\tAZD7762\tCisplatin\t0.0625\tTRUE\tTRUE\tTRUE
  14482\tAZD7762\tCisplatin\t0.25\tFALSE\tTRUE\tTRUE
  14482\tAZD7762\tDocetaxel\t0.0625\tFALSE\tFALSE\tFALSE
  14482\tAZD7762\tDocetaxel\t0.25\tFALSE\tTRUE\tFALSE
  &quot;&quot;&quot;
  # StringIO is to simulate an import from a file
  data = pd.read_table(StringIO(data_str), sep=&quot;\t&quot;, index_col=[0, 1, 2])
  # reset_index is here just for displaying purposes
  # (MultiIndex does not seem well supported in (pandas?) markdown export)
  print(data.reset_index().to_markdown(index=False))
  </code></pre>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: right;">BARCODE</th>
  <th style="text-align: left;">ANCHOR_NAME</th>
  <th style="text-align: left;">LIBRARY_NAME</th>
  <th style="text-align: right;">ANCHOR_CONC</th>
  <th style="text-align: left;">Synergy</th>
  <th style="text-align: left;">HSA</th>
  <th style="text-align: left;">Bliss</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: right;">14482</td>
  <td style="text-align: left;">5-Fluorouracil</td>
  <td style="text-align: left;">Cisplatin</td>
  <td style="text-align: right;">2.5</td>
  <td style="text-align: left;">False</td>
  <td style="text-align: left;">False</td>
  <td style="text-align: left;">False</td>
  </tr>
  <tr>
  <td style="text-align: right;">14482</td>
  <td style="text-align: left;">5-Fluorouracil</td>
  <td style="text-align: left;">Cisplatin</td>
  <td style="text-align: right;">10</td>
  <td style="text-align: left;">False</td>
  <td style="text-align: left;">False</td>
  <td style="text-align: left;">False</td>
  </tr>
  <tr>
  <td style="text-align: right;">14482</td>
  <td style="text-align: left;">5-Fluorouracil</td>
  <td style="text-align: left;">Docetaxel</td>
  <td style="text-align: right;">2.5</td>
  <td style="text-align: left;">False</td>
  <td style="text-align: left;">True</td>
  <td style="text-align: left;">True</td>
  </tr>
  <tr>
  <td style="text-align: right;">14482</td>
  <td style="text-align: left;">5-Fluorouracil</td>
  <td style="text-align: left;">Docetaxel</td>
  <td style="text-align: right;">10</td>
  <td style="text-align: left;">False</td>
  <td style="text-align: left;">False</td>
  <td style="text-align: left;">False</td>
  </tr>
  <tr>
  <td style="text-align: right;">14482</td>
  <td style="text-align: left;">AZD7762</td>
  <td style="text-align: left;">Cisplatin</td>
  <td style="text-align: right;">0.0625</td>
  <td style="text-align: left;">True</td>
  <td style="text-align: left;">True</td>
  <td style="text-align: left;">True</td>
  </tr>
  <tr>
  <td style="text-align: right;">14482</td>
  <td style="text-align: left;">AZD7762</td>
  <td style="text-align: left;">Cisplatin</td>
  <td style="text-align: right;">0.25</td>
  <td style="text-align: left;">False</td>
  <td style="text-align: left;">True</td>
  <td style="text-align: left;">True</td>
  </tr>
  <tr>
  <td style="text-align: right;">14482</td>
  <td style="text-align: left;">AZD7762</td>
  <td style="text-align: left;">Docetaxel</td>
  <td style="text-align: right;">0.0625</td>
  <td style="text-align: left;">False</td>
  <td style="text-align: left;">False</td>
  <td style="text-align: left;">False</td>
  </tr>
  <tr>
  <td style="text-align: right;">14482</td>
  <td style="text-align: left;">AZD7762</td>
  <td style="text-align: left;">Docetaxel</td>
  <td style="text-align: right;">0.25</td>
  <td style="text-align: left;">False</td>
  <td style="text-align: left;">True</td>
  <td style="text-align: left;">False</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Creating a <code>DataFrame</code> with the new column:</p>
  <pre class="lang-python prettyprint-override"><code>new_synergy = data.reset_index().groupby(
      [&quot;BARCODE&quot;, &quot;ANCHOR_NAME&quot;, &quot;LIBRARY_NAME&quot;])[[&quot;Synergy&quot;]].any()
  # Rename the resulting &quot;Synergy&quot; column
  new_synergy.columns = [&quot;new_synergy&quot;]
  print(new_synergy.reset_index().to_markdown(index=False))
  </code></pre>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: right;">BARCODE</th>
  <th style="text-align: left;">ANCHOR_NAME</th>
  <th style="text-align: left;">LIBRARY_NAME</th>
  <th style="text-align: left;">new_synergy</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: right;">14482</td>
  <td style="text-align: left;">5-Fluorouracil</td>
  <td style="text-align: left;">Cisplatin</td>
  <td style="text-align: left;">False</td>
  </tr>
  <tr>
  <td style="text-align: right;">14482</td>
  <td style="text-align: left;">5-Fluorouracil</td>
  <td style="text-align: left;">Docetaxel</td>
  <td style="text-align: left;">False</td>
  </tr>
  <tr>
  <td style="text-align: right;">14482</td>
  <td style="text-align: left;">AZD7762</td>
  <td style="text-align: left;">Cisplatin</td>
  <td style="text-align: left;">True</td>
  </tr>
  <tr>
  <td style="text-align: right;">14482</td>
  <td style="text-align: left;">AZD7762</td>
  <td style="text-align: left;">Docetaxel</td>
  <td style="text-align: left;">False</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Merging the two <code>DataFrame</code>s based on their indices:</p>
  <pre class="lang-python prettyprint-override"><code>new_data = data.merge(new_synergy, left_index=True, right_index=True)
  print(new_data.reset_index().to_markdown(index=False))
  </code></pre>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: right;">BARCODE</th>
  <th style="text-align: left;">ANCHOR_NAME</th>
  <th style="text-align: left;">LIBRARY_NAME</th>
  <th style="text-align: right;">ANCHOR_CONC</th>
  <th style="text-align: left;">Synergy</th>
  <th style="text-align: left;">HSA</th>
  <th style="text-align: left;">Bliss</th>
  <th style="text-align: left;">new_synergy</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: right;">14482</td>
  <td style="text-align: left;">5-Fluorouracil</td>
  <td style="text-align: left;">Cisplatin</td>
  <td style="text-align: right;">2.5</td>
  <td style="text-align: left;">False</td>
  <td style="text-align: left;">False</td>
  <td style="text-align: left;">False</td>
  <td style="text-align: left;">False</td>
  </tr>
  <tr>
  <td style="text-align: right;">14482</td>
  <td style="text-align: left;">5-Fluorouracil</td>
  <td style="text-align: left;">Cisplatin</td>
  <td style="text-align: right;">10</td>
  <td style="text-align: left;">False</td>
  <td style="text-align: left;">False</td>
  <td style="text-align: left;">False</td>
  <td style="text-align: left;">False</td>
  </tr>
  <tr>
  <td style="text-align: right;">14482</td>
  <td style="text-align: left;">5-Fluorouracil</td>
  <td style="text-align: left;">Docetaxel</td>
  <td style="text-align: right;">2.5</td>
  <td style="text-align: left;">False</td>
  <td style="text-align: left;">True</td>
  <td style="text-align: left;">True</td>
  <td style="text-align: left;">False</td>
  </tr>
  <tr>
  <td style="text-align: right;">14482</td>
  <td style="text-align: left;">5-Fluorouracil</td>
  <td style="text-align: left;">Docetaxel</td>
  <td style="text-align: right;">10</td>
  <td style="text-align: left;">False</td>
  <td style="text-align: left;">False</td>
  <td style="text-align: left;">False</td>
  <td style="text-align: left;">False</td>
  </tr>
  <tr>
  <td style="text-align: right;">14482</td>
  <td style="text-align: left;">AZD7762</td>
  <td style="text-align: left;">Cisplatin</td>
  <td style="text-align: right;">0.0625</td>
  <td style="text-align: left;">True</td>
  <td style="text-align: left;">True</td>
  <td style="text-align: left;">True</td>
  <td style="text-align: left;">True</td>
  </tr>
  <tr>
  <td style="text-align: right;">14482</td>
  <td style="text-align: left;">AZD7762</td>
  <td style="text-align: left;">Cisplatin</td>
  <td style="text-align: right;">0.25</td>
  <td style="text-align: left;">False</td>
  <td style="text-align: left;">True</td>
  <td style="text-align: left;">True</td>
  <td style="text-align: left;">True</td>
  </tr>
  <tr>
  <td style="text-align: right;">14482</td>
  <td style="text-align: left;">AZD7762</td>
  <td style="text-align: left;">Docetaxel</td>
  <td style="text-align: right;">0.0625</td>
  <td style="text-align: left;">False</td>
  <td style="text-align: left;">False</td>
  <td style="text-align: left;">False</td>
  <td style="text-align: left;">False</td>
  </tr>
  <tr>
  <td style="text-align: right;">14482</td>
  <td style="text-align: left;">AZD7762</td>
  <td style="text-align: left;">Docetaxel</td>
  <td style="text-align: right;">0.25</td>
  <td style="text-align: left;">False</td>
  <td style="text-align: left;">True</td>
  <td style="text-align: left;">False</td>
  <td style="text-align: left;">False</td>
  </tr>
  </tbody>
  </table>
  </div>
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-05-30T10:12:15.197Z
Answers.LastActivityDate: 2022-05-30T10:12:15.197Z
Answers.UserDisplayName: bli
Body: <p>I have a  data frame <code>df</code>. The below table only represent top 8 rows of the data frame. There are multiple combination of <code>LIBRARY_NAME</code> and <code>ANCHOR_NAME</code></p>
  <pre><code>BARCODE ANCHOR_NAME     LIBRARY_NAME    ANCHOR_CONC Synergy HSA     Bliss
  14482   5-Fluorouracil  Cisplatin       2.5         FALSE   FALSE   FALSE
                                          10          FALSE   FALSE   FALSE
                          Docetaxel       2.5         FALSE   TRUE    TRUE
                                          10          FALSE   FALSE   FALSE
          AZD7762         Cisplatin       0.0625      TRUE    TRUE    TRUE
                                          0.25        FALSE   TRUE    TRUE
                          Docetaxel       0.0625      FALSE   FALSE   FALSE
                                          0.25        FALSE   TRUE    FALSE
  </code></pre>
  <p>I want to create a new column 'new_synergy 'with pandas which is based on column <code>ANCHOR_CONC</code> and <code>Synergy</code>. There are two ANCHOR_CONC for each anchor_name and library_name combination. If for any concentration of ANCHOR_CONC the synergy is <code>TRUE</code>, for other concentration also synergy becomes <code>TRUE</code></p>
  <p>Eg: <code>new_synergy</code>: Condition for TRUE (for AZD7762 and Cisplatin), IF at 0.0625 <code>Synergy</code> is TRUE, it will be TRUE for 0.25 also.</p>
  <p><code>Expected data_frame</code></p>
  <p><code>new_df</code></p>
  <pre><code>BARCODE ANCHOR_NAME     LIBRARY_NAME    ANCHOR_CONC Synergy HSA     Bliss  new_synergy
  14482   5-Fluorouracil  Cisplatin       2.5         FALSE   FALSE   FALSE    FALSE
                                          10          FALSE   FALSE   FALSE    FALSE
                          Docetaxel       2.5         FALSE   TRUE    TRUE     FALSE  
                                          10          FALSE   FALSE   FALSE    FALSE
          AZD7762         Cisplatin       0.0625      TRUE    TRUE    TRUE     TRUE
                                          0.25        FALSE   TRUE    TRUE     TRUE
                          Docetaxel       0.0625      FALSE   FALSE   FALSE    FALSE
                                          0.25        FALSE   TRUE    FALSE    FALSE
  </code></pre>
  <p>I tried the following code</p>
  <pre><code>import pandas as pd
  import numpy as np
  dd = data[[&quot;BARCODE&quot;, &quot;LIBRARY_NAME&quot;, &quot;ANCHOR_NAME&quot;, &quot;ANCHOR_CONC&quot;, &quot;Synergy&quot;, &quot;Bliss&quot;, &quot;HSA&quot;]]
  df = pd.pivot_table(data=dd,index=[&quot;BARCODE&quot;, &quot;ANCHOR_NAME&quot;, &quot;LIBRARY_NAME&quot;, &quot;ANCHOR_CONC&quot;])
  ## setting conditions as pivot table step converted true to 1 and false to 0. 
  conditions = [(dd['Synergy'] == 1.0), (dd['Synergy'] == 0.0)]
  values = ['True', 'False']
  dd['new_synergy'] = np.select(conditions, values)
  ## However it is giving me the same results in both new_synergy and synergy column. I have no idea how to fit `ANCHOR_CONC` condition in this step.
  
  </code></pre>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2022-05-26T18:45:44.723Z
Comments.Text: Could you supply your code please?
Comments.UserDisplayName: M__
Tags: <python><pandas>
Title: To create a new column with Pandas on the basis of two types of column values
ThreadText: # To create a new column with Pandas on the basis of two types of column values
  
  (Asked by: username_0 on 2022-05-26)
  
  I have a data frame `df`. The below table only represent top 8 rows of the data frame. There are multiple combination of `LIBRARY_NAME` and `ANCHOR_NAME`
  
  ```
  BARCODE ANCHOR_NAME     LIBRARY_NAME    ANCHOR_CONC Synergy HSA     Bliss
  14482   5-Fluorouracil  Cisplatin       2.5         FALSE   FALSE   FALSE
                                          10          FALSE   FALSE   FALSE
                          Docetaxel       2.5         FALSE   TRUE    TRUE
                                          10          FALSE   FALSE   FALSE
          AZD7762         Cisplatin       0.0625      TRUE    TRUE    TRUE
                                          0.25        FALSE   TRUE    TRUE
                          Docetaxel       0.0625      FALSE   FALSE   FALSE
                                          0.25        FALSE   TRUE    FALSE
  
  ```
  
  I want to create a new column 'new\_synergy 'with pandas which is based on column `ANCHOR_CONC` and `Synergy`. There are two ANCHOR\_CONC for each anchor\_name and library\_name combination. If for any concentration of ANCHOR\_CONC the synergy is `TRUE`, for other concentration also synergy becomes `TRUE`
  
  Eg: `new_synergy`: Condition for TRUE (for AZD7762 and Cisplatin), IF at 0.0625 `Synergy` is TRUE, it will be TRUE for 0.25 also.
  
  `Expected data_frame`
  
  `new_df`
  
  ```
  BARCODE ANCHOR_NAME     LIBRARY_NAME    ANCHOR_CONC Synergy HSA     Bliss  new_synergy
  14482   5-Fluorouracil  Cisplatin       2.5         FALSE   FALSE   FALSE    FALSE
                                          10          FALSE   FALSE   FALSE    FALSE
                          Docetaxel       2.5         FALSE   TRUE    TRUE     FALSE  
                                          10          FALSE   FALSE   FALSE    FALSE
          AZD7762         Cisplatin       0.0625      TRUE    TRUE    TRUE     TRUE
                                          0.25        FALSE   TRUE    TRUE     TRUE
                          Docetaxel       0.0625      FALSE   FALSE   FALSE    FALSE
                                          0.25        FALSE   TRUE    FALSE    FALSE
  
  ```
  
  I tried the following code
  
  ```
  import pandas as pd
  import numpy as np
  dd = data[["BARCODE", "LIBRARY_NAME", "ANCHOR_NAME", "ANCHOR_CONC", "Synergy", "Bliss", "HSA"]]
  df = pd.pivot_table(data=dd,index=["BARCODE", "ANCHOR_NAME", "LIBRARY_NAME", "ANCHOR_CONC"])
  ## setting conditions as pivot table step converted true to 1 and false to 0. 
  conditions = [(dd['Synergy'] == 1.0), (dd['Synergy'] == 0.0)]
  values = ['True', 'False']
  dd['new_synergy'] = np.select(conditions, values)
  ## However it is giving me the same results in both new_synergy and synergy column. I have no idea how to fit `ANCHOR_CONC` condition in this step.
  
  ```
  
  **Tags:** python, pandas
  
  > **Comments:**
  > **username_1**: Could you supply your code please?  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_2 on 2022-05-27. Score: 3 | Accepted answer
  
  You can use [groupby()](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.groupby.html) as following :
  
  `dd["new_synergy"] = dd[["ANCHOR_NAME", "LIBRARY_NAME", "Synergy"]].groupby(by=["ANCHOR_NAME", "LIBRARY_NAME"]).any()`
  
  Explenation : you want to group your dataframe by both `ANCHOR_NAME` and `LIBRARY_NAME`, and return True if any of the `synergy`value is true, which is what `any()` does.
  
  Note : you can check for all methods available for `GroupBy` objects [here](https://pandas.pydata.org/docs/reference/groupby.html).
  
  
  ---
  
  ### Answer by username_3 on 2022-05-30. Score: 3
  
  To elaborate on [this answer](https://bioinformatics.stackexchange.com/a/19130/292), here is a full working example where I assume that `"BARCODE"`, `"ANCHOR_NAME"` and `"LIBRARY_NAME"` are used as `MultiIndex`:
  
  Creating the test data:
  
  ```python
  from io import StringIO
  import pandas as pd
  data_str = """BARCODE\tANCHOR_NAME\tLIBRARY_NAME\tANCHOR_CONC\tSynergy\tHSA\tBliss
  14482\t5-Fluorouracil\tCisplatin\t2.5\tFALSE\tFALSE\tFALSE
  14482\t5-Fluorouracil\tCisplatin\t10\tFALSE\tFALSE\tFALSE
  14482\t5-Fluorouracil\tDocetaxel\t2.5\tFALSE\tTRUE\tTRUE
  14482\t5-Fluorouracil\tDocetaxel\t10\tFALSE\tFALSE\tFALSE
  14482\tAZD7762\tCisplatin\t0.0625\tTRUE\tTRUE\tTRUE
  14482\tAZD7762\tCisplatin\t0.25\tFALSE\tTRUE\tTRUE
  14482\tAZD7762\tDocetaxel\t0.0625\tFALSE\tFALSE\tFALSE
  14482\tAZD7762\tDocetaxel\t0.25\tFALSE\tTRUE\tFALSE
  """
  # StringIO is to simulate an import from a file
  data = pd.read_table(StringIO(data_str), sep="\t", index_col=[0, 1, 2])
  # reset_index is here just for displaying purposes
  # (MultiIndex does not seem well supported in (pandas?) markdown export)
  print(data.reset_index().to_markdown(index=False))
  
  ```
  
  | BARCODE | ANCHOR\_NAME | LIBRARY\_NAME | ANCHOR\_CONC | Synergy | HSA | Bliss |
  | --- | --- | --- | --- | --- | --- | --- |
  | 14482 | 5-Fluorouracil | Cisplatin | 2.5 | False | False | False |
  | 14482 | 5-Fluorouracil | Cisplatin | 10 | False | False | False |
  | 14482 | 5-Fluorouracil | Docetaxel | 2.5 | False | True | True |
  | 14482 | 5-Fluorouracil | Docetaxel | 10 | False | False | False |
  | 14482 | AZD7762 | Cisplatin | 0.0625 | True | True | True |
  | 14482 | AZD7762 | Cisplatin | 0.25 | False | True | True |
  | 14482 | AZD7762 | Docetaxel | 0.0625 | False | False | False |
  | 14482 | AZD7762 | Docetaxel | 0.25 | False | True | False |
  
  Creating a `DataFrame` with the new column:
  
  ```python
  new_synergy = data.reset_index().groupby(
      ["BARCODE", "ANCHOR_NAME", "LIBRARY_NAME"])[["Synergy"]].any()
  # Rename the resulting "Synergy" column
  new_synergy.columns = ["new_synergy"]
  print(new_synergy.reset_index().to_markdown(index=False))
  
  ```
  
  | BARCODE | ANCHOR\_NAME | LIBRARY\_NAME | new\_synergy |
  | --- | --- | --- | --- |
  | 14482 | 5-Fluorouracil | Cisplatin | False |
  | 14482 | 5-Fluorouracil | Docetaxel | False |
  | 14482 | AZD7762 | Cisplatin | True |
  | 14482 | AZD7762 | Docetaxel | False |
  
  Merging the two `DataFrame`s based on their indices:
  
  ```python
  new_data = data.merge(new_synergy, left_index=True, right_index=True)
  print(new_data.reset_index().to_markdown(index=False))
  
  ```
  
  | BARCODE | ANCHOR\_NAME | LIBRARY\_NAME | ANCHOR\_CONC | Synergy | HSA | Bliss | new\_synergy |
  | --- | --- | --- | --- | --- | --- | --- | --- |
  | 14482 | 5-Fluorouracil | Cisplatin | 2.5 | False | False | False | False |
  | 14482 | 5-Fluorouracil | Cisplatin | 10 | False | False | False | False |
  | 14482 | 5-Fluorouracil | Docetaxel | 2.5 | False | True | True | False |
  | 14482 | 5-Fluorouracil | Docetaxel | 10 | False | False | False | False |
  | 14482 | AZD7762 | Cisplatin | 0.0625 | True | True | True | True |
  | 14482 | AZD7762 | Cisplatin | 0.25 | False | True | True | True |
  | 14482 | AZD7762 | Docetaxel | 0.0625 | False | False | False | False |
  | 14482 | AZD7762 | Docetaxel | 0.25 | False | True | False | False |
  
  
  ---
  
  