Given a dataframe:
import pandas as pd
df = pd.DataFrame(data= {'Col1': ['No', 'Yes', 'No', 'Maybe'], 'Col2': ['Yes', 'No', 'No', 'No'], 'Result': ''})
I want to populate Result with a list that may need to be appended based upon a column value. In this case, the parameters would be:
If the value is 'Yes' keep the current value of Result, if the value is 'Maybe' append 'Attention needed (insert column name)', if the value is 'No' append 'Failure (insert column name)'
Desired result:
Not very pretty, but you could create a dict, then use stack, map and groupby with join aggregation:
d = {'No': 'Failure', 'Maybe': 'Attention needed'}
s = df[['Col1', 'Col2']].stack().map(d).dropna()
df['Result'] = (s + ' ' + s.index.get_level_values(1)).groupby(level=0).agg(', '.join)
[out]
Col1 Col2 Result
0 No Yes Failure Col1
1 Yes No Failure Col2
2 No No Failure Col1, Failure Col2
3 Maybe No Attention needed Col1, Failure Col2
Try this one liner code using lambda function:
df['Result'] = df[['Col1','Col2']].apply(lambda x: 'Failure Col1' if (x[0]=='No' and x[1]=='Yes') else ('Failure Col2' if (x[1]=='No' and x[0]=='Yes') else ('Failure Col1, Failure Col2' if (x[0]=='No' and x[1]=='No') else("Attention needed Col1, Failure Col2" if (x[0]=='Maybe' and x[1]=='No') else None))), axis=1)
Output:
Col1 Col2 Result
0 No Yes Failure Col1
1 Yes No Failure Col2
2 No No Failure Col1, Failure Col2
3 Maybe No Attention needed Col1, Failure Col2
You may first construct the result column as a numpy array while traversing the data frame columns and checking the values then you can add the new result column and drop the old one.
Construct a dictionary to replace values in df and Using * and + to construct a series of appropriate message strings and finally join them and assign to df.Result
d = {'Yes': '', 'No': 'Failure ', 'Maybe': 'Attention needed '}
df1 = df[['Col1', 'Col2']]
df['Result'] = ((df1.replace(d)
+ df1.ne('Yes').values * df1.columns.values).agg(','.join, axis=1)
.str.strip(','))
Or
df['Result'] = ((df1.replace(d)
+ df1.ne('Yes').values * (df1.columns+',').values).sum(1)
.str.strip(','))
Out[267]:
Col1 Col2 Result
0 No Yes Failure Col1
1 Yes No Failure Col2
2 No No Failure Col1,Failure Col2
3 Maybe No Attention needed Col1,Failure Col2
Here the detail
df1.replace(d) + df1.ne('Yes').values * df1.columns.values
Out[268]:
Col1 Col2
0 Failure Col1
1 Failure Col2
2 Failure Col1 Failure Col2
3 Attention needed Col1 Failure Col2
((df1.replace(d) + df1.ne('Yes').values * df1.columns.values).agg(','.join, axis=1)
.str.strip(','))
Out[269]:
0 Failure Col1
1 Failure Col2
2 Failure Col1,Failure Col2
3 Attention needed Col1,Failure Col2
dtype: object
