Analyzing 2 columns grouping by a different column

user_11374646 0
Viewed 55

I am trying to look at 2 different 'If' conditions in the below data frame but both have to be analyzed grouping by one column.

For every 'Text_1', if the 'Relation' is 'COVER' for more than one 'Text_2' and the 'Type' is different for the Text_2 which is 'COVER', then output should be 'Y', otherwise it should be no.

df = pd.DataFrame({
    'ID': [1, 1, 1, 1, 1, 1, 2, 2, 2],
    'Text_1': ['ABCDEF', 'ABCDEF', 'ABCDEF', 'GHIJKL', 'GHIJKL', 'GHIJKL', 'MNOPQR', 'MNOPQR', 'MNOPQR'],
    'Text_2': ['ABC', 'BCD', 'GHI', 'JKL', 'XYZ', 'RST', 'MNO', 'PQR', 'XYZ'],
    'Relation': ['COVER', 'COVER', 'UNRELATED', 'COVER', 'UNRELATED', 'UNRELATED', 'COVER','COVER', 'UNRELATED'],
    'Type': ['NAME', 'PLACE', 'PLACE', 'NAME', 'PLACE', 'THING', 'PLACE', 'PLACE', 'PLACE']})

   ID  Text_1 Text_2   Relation   Type
0   1  ABCDEF    ABC      COVER   NAME
1   1  ABCDEF    BCD      COVER  PLACE
2   1  ABCDEF    GHI  UNRELATED  PLACE
3   1  GHIJKL    JKL      COVER   NAME
4   1  GHIJKL    XYZ  UNRELATED  PLACE
5   1  GHIJKL    RST  UNRELATED  THING
6   2  MNOPQR    MNO      COVER  PLACE
7   2  MNOPQR    PQR      COVER  PLACE
8   2  MNOPQR    XYZ  UNRELATED  PLACE

This is how the output looks like:

df_output = pd.DataFrame({
    'ID': [1, 1, 1, 1, 1, 1, 2, 2, 2],
    'Text_1': ['ABCDEF', 'ABCDEF', 'ABCDEF', 'GHIJKL', 'GHIJKL', 'GHIJKL', 'MNOPQR', 'MNOPQR', 'MNOPQR'],
    'Text_2': ['ABC', 'BCD', 'GHI', 'JKL', 'XYZ', 'RST', 'MNO', 'PQR', 'XYZ'],
    'Relation': ['COVER', 'COVER', 'UNRELATED', 'COVER', 'UNRELATED', 'UNRELATED', 'COVER','COVER', 'UNRELATED'],
    'Type': ['NAME', 'PLACE', 'PLACE', 'NAME', 'PLACE', 'THING', 'PLACE', 'PLACE', 'PLACE'],
    'Output': ['Y', 'Y', 'N', 'N', 'N', 'N', 'N', 'N', 'N']})

   ID  Text_1 Text_2   Relation   Type Output
0   1  ABCDEF    ABC      COVER   NAME      Y
1   1  ABCDEF    BCD      COVER  PLACE      Y
2   1  ABCDEF    GHI  UNRELATED  PLACE      N
3   1  GHIJKL    JKL      COVER   NAME      N
4   1  GHIJKL    XYZ  UNRELATED  PLACE      N
5   1  GHIJKL    RST  UNRELATED  THING      N
6   2  MNOPQR    MNO      COVER  PLACE      N
7   2  MNOPQR    PQR      COVER  PLACE      N
8   2  MNOPQR    XYZ  UNRELATED  PLACE      N
pandas-groupby pandas python
1 Answers

First mask COVER, Use groupby transform to calculate count of Text_1 and Text_2. Use np.where(condition, if true, else) to impose your if conditions. Drop the temporary columns.

   m=df.Relation=='COVER'#mask 'COVER'
   df[['temp1','temp2']]=df[m].groupby('Text_1')[['Text_2','Type']].transform('nunique')#create temporary columns which would have count for Text_1 and Text_2. Please take note only the Relation COVER has count, the others are NaN
   df=df.assign(output=np.where((df.temp1==2)&(df.temp2==2), 'Y','N')).drop(columns=['temp1','temp2'])#Use np where together with boolean select to ensure only temp1>1 and tempt>1 counts are made Y



  ID  Text_1 Text_2   Relation   Type output
0   1  ABCDEF    ABC      COVER   NAME      Y
1   1  ABCDEF    BCD      COVER  PLACE      Y
2   1  ABCDEF    GHI  UNRELATED  PLACE      N
3   1  GHIJKL    JKL      COVER   NAME      N
4   1  GHIJKL    XYZ  UNRELATED  PLACE      N
5   1  GHIJKL    RST  UNRELATED  THING      N
6   2  MNOPQR    MNO      COVER  PLACE      N
7   2  MNOPQR    PQR      COVER  PLACE      N
8   2  MNOPQR    XYZ  UNRELATED  PLACE      N

How it works

Step 1: Mask

m=df.Relation=='COVER'
print(m)

0     True
1     True
2    False
3     True
4    False
5    False
6     True
7     True
8    False

Step:2 Create temporary columns with count of the Text_1 and Text_2

df[['temp1','temp2']]=df[m].groupby('Text_1')[['Text_2','Type']].transform('nunique')
print(df)



ID  Text_1 Text_2   Relation   Type  temp1  temp2
0   1  ABCDEF    ABC      COVER   NAME    2.0    2.0
1   1  ABCDEF    BCD      COVER  PLACE    2.0    2.0
2   1  ABCDEF    GHI  UNRELATED  PLACE    NaN    NaN
3   1  GHIJKL    JKL      COVER   NAME    1.0    1.0
4   1  GHIJKL    XYZ  UNRELATED  PLACE    NaN    NaN
5   1  GHIJKL    RST  UNRELATED  THING    NaN    NaN
6   2  MNOPQR    MNO      COVER  PLACE    2.0    1.0
7   2  MNOPQR    PQR      COVER  PLACE    2.0    1.0
8   2  MNOPQR    XYZ  UNRELATED  PLACE    NaN    NaN

Step:3 Assign condition, this is a combination of the result above with np.where

df=df.assign(output=np.where((df.temp1==2)&(df.temp2==2), 'Y','N')).drop(columns=['temp1','temp2'])
user_89869750
Related