Pandas: Break a column based on a condition?

Question

I am trying to smooth out the lines and save the information from the lines that I want.

What I have:

id  var1  var2 var3
1      Y     N    Y
1      N          Y
2      Y          N
2      N     Y    N
2      Y     N    Y

What I would like:

id  var1  var2 var3
1      Y     N    Y
2      Y     Y    Y

Essentially, it will check if there is Y / N and always give priority to Y. There are also more columns than var1, var2, var3; so I would like to do something more general so that I can refer to other columns.

Answer 1

You can use replace+ groupby+ GroupBy.max+ replace+ reset_index:

df1 = df.replace({'Y':1,'N':0, np.nan:-1})
        .groupby('id')
        .max()
        .replace({1:'Y', 0:'N',-1:np.nan})
        .reset_index()
print (df1)

   id var1 var2 var3
0   1    Y    N    Y
1   2    Y    Y    Y

EDIT:

df = pd.DataFrame({
'id': [1, 1, 2, 2, 3, 3], 
'var2': ['N', 'N', 'N', 'Y', 'N', np.nan], 
'var1': ['Y', 'Y', 'Y', 'N', 'Y', np.nan], 
'var3': [np.nan, np.nan, np.nan, 'N', np.nan, 'Y']
})

print (df)
   id var1 var2 var3
0   1    Y    N  NaN
1   1    Y    N  NaN
2   2    Y    N  NaN
3   2    N    Y    N
4   3    Y    N  NaN
5   3  NaN  NaN    Y

Dynamically, you can create another one dict:

#check all unique values without column id
print (df.set_index('id').stack(dropna=False).unique())
['Y' 'N' nan]

#create dict for first replace
d = {'Y':1,'N':0, np.nan:-1}
#swap keys, values in dict for another replace
d1 = {v: k for k, v in d.items()}

df1 = df.replace(d).groupby('id').max().replace(d1).reset_index()
print (df1)
   id var1 var2 var3
0   1    Y    N  NaN
1   2    Y    Y    N
2   3    Y    N    Y

EDIT1:

The solution, if only Y, Nand NaNin the columns var1is varN:

varNAN = 'A'
print (df.fillna(varNAN).groupby('id').max().replace({varNAN:np.nan}).reset_index())
   id var1 var2 var3
0   1    Y    N  NaN
1   2    Y    Y    N
2   3    Y    N    Y

Answer 2

, groupby sum , OR, , " Y":

df1 = df.replace({'Y':True,'N':False})

df_out = (df1.groupby('id').sum(skipna=False)
         .astype(bool)
         .replace({True:'Y',False:'N'})
         .reset_index())

print(df_out)

:

   id var1 var2 var3
0   1    Y    N    Y
1   2    Y    Y    Y

Answer 3

If your data frame has only "Y", "N" and "NAN", this is an easier way to do this.

Customization

df = pd.DataFrame({'id': {0: 1, 1: 1, 2: 2, 3: 2, 4: 2},
 'var1': {0: 'Y', 1: 'N', 2: 'Y', 3: 'N', 4: 'Y'},
 'var2': {0: 'N', 1: np.nan, 2: np.nan, 3: 'Y', 4: 'N'},
 'var3': {0: 'Y', 1: 'Y', 2: 'N', 3: 'N', 4: 'Y'}})

Out[45]: 
   id var1 var2 var3
0   1    Y    N    Y
1   1    N  NaN    Y
2   2    Y  NaN    N
3   2    N    Y    N
4   2    Y    N    Y

Decision

#Fill na with 'A' and get the max (Y>N>A) from each column.
df.fillna('A').groupby('id').max().reset_index()
Out[46]: 
   var1 var2 var3
id               
1     Y    N    Y
2     Y    Y    Y