Make two new columns and fill them with appropriate data-CodePudding

I have a dataset that looks as follows

id   Message

0    abc
1    def
2    <AGVPOS> 000, x_coor, y_coor, 000, 000...
3    abc
.    .

I want to add two columns named X and Y and fill them with the values 'x_coor' and 'y_coor' for columns that have the string 'AGVPOS' in them, and leave the cells empty for columns that don't have this string

Something like this

id   Message.                                     X       Y

0    abc                                          Nan     Nan
1    def                                          Nan     Nan
2    <AGVPOS> 000, x_coor, y_coor, 000, 000...    x_coor  y_coor
3    abc                                          Nan     Nan
.    .

I have tried this

df[['x','y']] = df['Message'].apply(lambda x: x.split(', ')[1:3] if ('AGVPOS' in x) else ['np.NaN','np.NaN'])

But it didn't work, I keep getting the following error.

ValueError: Must have equal len keys and value when setting with an iterable

I would like to know if there is more efficient way as well

Thank you.

CodePudding user response：

I have solved it.

I forgot to transform the lists to series, should've done this instead

df[['x','y']] = df['Message'].apply(lambda x: pd.Series(x.split(', ')[1:3]) if 'AGVPOS' in x else pd.Series([np.NaN,np.NaN]))

CodePudding user response：

You can do

df[['x','y']] = df.Message.str.split(',',expand=True).iloc[:,1:3]
         1        2
0     None     None
1     None     None
2   x_coor   y_coor
3     None     None