Is there anyway to ungroup data in a grouped-by pandas dataframe?

Question

I have a dataset that for simplicity I need to group by and aggregate based on one column so that I can remove some rows easily. Once I am done with the calculations, I need to reverse the group by actions so that I can see the dataframe easily in excel. If I do not inverse the action, I would export the whole list to excel which is not easy to analyse. Any help is gretaly appreciated.

Example:

Col1  Col2 Col3
123   11   Yes
123   22   Yes
256   33   Yes
256   33   No
337   00   No
337   44   No

After applying groupby and aggregate:

X=dataset.groupby('Col1').agg(lambda x:set(x)).reset_index()

I get

Col1   Col2      Col3
123   {11,22}   {Yes}
256   {33}      {Yes, No}
337   {00,44}   {No}

I then remove all the columns that contain Yes using drop

X=X.reset_index(drop=True)

what I need to get before exporting to excel is

Col1 Col2 Col3
337   00   No
337   44   No

Hope this is clear enough

Thaks in advance

Solution 1

I don't believe converting to a set is a good idea. Here's an alternative: First sort in descending order by Col3, then create a mapping of Col2 : Yes/No and filter based on that.

In [1191]: df = df.sort_values('Col3', ascending=True)

In [1192]: mapping = dict(df[['Col2', 'Col3']].values)

In [1193]: df[df.Col2.replace(mapping) == 'No'] # or df.Col2.map(mapping)
Out[1193]: 
   Col1  Col2 Col3
4   337     0   No
5   337    44   No

Solution 2

I am agree with COLDSPEED. You do not need convert to set

df['Temp']=df.Col3.eq('Yes')
DF=df.groupby('Col1')['Temp'].sum()
df[df.Col1==DF.index[DF==0].values[0]].drop('Temp',axis=1)


Out[113]: 
   Col1  Col2 Col3
4   337     0   No
5   337    44   No

Comments

[DYZ]

X.reset_index(drop=True) does not change anything in your dataframe. It is unclear why you have it and what you expected it to do. Please include the expected result.

[Omido]

Sorry my bad I meant this: YY=set('Yes') X=X.drop(X[X.Col3==YY].index)

[BENY]

that is wisely replace

[Omido]

Thanks for this. I guess I need to rewrite my code then. I am dealing with too many columns and need to modify your method to apply it to my actual data. What I provided was just a super simplified version of what I wanted to do.