2
$\begingroup$

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.

$\endgroup$
1
  • 1
    $\begingroup$ Could you supply your code please? $\endgroup$
    – M__
    May 26 at 18:45

2 Answers 2

3
$\begingroup$

You can use groupby() 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 synergyvalue is true, which is what any() does.

Note : you can check for all methods available for GroupBy objects here.

$\endgroup$
2
$\begingroup$

To elaborate on this answer, here is a full working example where I assume that "BARCODE", "ANCHOR_NAME" and "LIBRARY_NAME" are used as MultiIndex:

Creating the test data:

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:

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 DataFrames based on their indices:

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
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.