How to find Intersecting and unique strings in specific groups in a pd.dataframe?

user_15276460 0
Viewed 70

I have 98 samples/dataframes of which I have reshaped into one dataframe via concatenation.

The dataframe has been subjected to extensive sorting and filtering for identifying locus' for comparison across its group.

Example.

data = {"Overall_Sample":["Sample1", "Sample1", "Sample1", "Sample1", "Sample1", "Sample1", "Sample1", "Sample1", "Sample2", "Sample2", "Sample2", "Sample2", "Sample2", "Sample2", "Sample2"], "Sub_Sample":["Sample1_a", "Sample1_a", "Sample1_a", "Sample1_a", "Sample1_b", "Sample1_b", "Sample1_b", "Sample1_b", "Sample2_a", "Sample2_a", "Sample2_a", "Sample2_b", "Sample2_b", "Sample2_b", "Sample2_b"], "Panel":["a", "a", "a", "a", "b", "b", "b", "b", "a", "a", "a", "b", "b", "b", "b"], "Identified_Locus":["Locus1", "Locus2", "Locus3", "Locus4", "Locus2", "Locus3", "Locus4", "Locus5", "Locus3", "Locus5", "Locus6", "Locus1", "Locus3", "Locus5", "Locus6"]}

df.DataFrame(data)

I wish to find intersecting Locus per sampleName between panel a and b.

Desired outcome.

Sample1 
Intersecting: Locus2, Locus3, Locus4
Panel a unique: Locus1
Panel b unique: Locus5

Sample2
Intersecting: Locus3, Locus5, Locus6
Panel a unique: None
Panel b unique: Locus1

with 3 different lists, 
intersection = (3,3)
Panel a unique = (1, 0)
Panel b unique = (1, 1)

The goal is to make a percentage barplot in the end.

I have succeded manually to split the dataframe and retrieve the desired output. But I would like to avoid to split the dataframe - is it possible to keep it at the main dataframe and still retrieve the desired output?

The code I used to manually identify intersecting and unique ones.

grouped = df.groupby(df.Sub_Sample)
sample1_a = grouped.get_group("Sample1_a")
sample1_b = grouped.get_group("Sample1_b")

sample1a_locus = sample1_a["Locus"].tolist()
sample1b_locus = sample1_b["Locus"].tolist()

sample1a_set = set(sample1a_locus)
sample1b_set = set(sample1a_locus)

Overlap_locus = sample1a_set.intersection(sample1b_set)
Panel_a_unique = sample1a_set.symmetric_difference(sample1b_set)
Panel_b_unique = sample1b_set.symmetric_difference(sample1a_set)

Overlapping_genes.append(len(Overlap_locus))
Panela_unique.append(len(Panel_a_unique))
Panelb_unique.append(len(Panel_b_unique))
pandas-groupby pandas dataframe group-by python
2 Answers

I think your code is not bad. Here's a slightly more concise approach that scales also to larger data sets.

You can get the unique values for Identified_Locus per sub sample with

locus_per_sub_sample = df.groupby(['Overall_Sample', 'Panel'])['Identified_Locus'].apply(lambda grp: set(grp))

You can then perform set operations on the different samples rather easily. E.g., to get the number of shared elements between the sub-samples for Sample1 you can do:

len(set.intersection(*locus_per_sub_sample.loc[pd.IndexSlice['Sample1', :]]))

Similarly, for the unique elements of the sub-samples Sample1_a and Sample1_b:

symmetric_difference1 = set.symmetric_difference(*locus_per_sub_sample.loc[pd.IndexSlice['Sample1', :]])

To get the symmetric difference for each sub sample

locus_per_sub_sample.loc[pd.IndexSlice['Sample1', :]].apply(
     lambda r: r.intersection(symmetric_difference1))

which gives you

Overall_Sample  Panel
Sample1         a        {Locus1}
                b        {Locus5}
Name: Identified_Locus, dtype: object
user_104665560

I went with creating a for loop to append a list, respectively. The list will be needed for creating a percentage bar chart for easy visualization in a presentation.

names = df.Overall_Sample.unique()
Overlapping_locus = []
Sample1_unique = [] 
Sample2_unique = []
for i in names:
    Overlapping_Locus.append(len(set.intersection(*locus_per_sample.loc[pd.IndexSlice[i]])))
    Sample1_unique.append(len(locus_per_sample.loc[pd.IndexSlice[[i], :]].apply(lambda u: u.intersection(set.symmetric_difference(*locus_per_sample.loc[pd.IndexSlice[[i], :]])))[0]))
    Sample2_unique.append(len(locus_per_sample.loc[pd.IndexSlice[[i], :]].apply(lambda u: u.intersection(set.symmetric_difference(*locus_per_sample.loc[pd.IndexSlice[[i], :]])))[1]))
user_152764600
Related