Split and type cast columns values using Pandas

Question

How do i add an extra column in a dataframe, so it could split and convert to integer types but np.nan for string types

Col1   
1|2|3
"string"

so

Col1      ExtraCol
1|2|3     [1,2,3]
"string"  nan

I tried long contorted way but failed

df['extracol'] = df["col1"].str.strip().str.split("|").str[0].apply(lambda x: x.astype(np.float) if x.isnumeric() else np.nan).astype("Int32")

I would suggest to write a specific function inside the lambda with some try-except with your specific case — Let's try
– Let's try, Commented Dec 28, 2022 at 16:26

PaulS · Accepted Answer · 2022-12-28 16:38:31Z

1

Another possible solution:

import re

df['ExtraCol'] = df['Col1'].apply(lambda x: [int(y) for y in re.split(
    r'\|', x)] if x.replace('|', '').isnumeric() else np.nan)

Output:

     Col1   ExtraCol
0   1|2|3  [1, 2, 3]
1  string        NaN

answered Dec 28, 2022 at 16:38

PaulS

27.1k3 gold badges19 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

SomeDude · Accepted Answer · 2022-12-28 16:25:10Z

1

You can use regex and Series.str.match to find the rows whose value can be split into integer lists

df['ExtraCol'] = df.loc[df['Col1'].str.match(r'\|?\d+\|?'), 'Col1'].str.split('|')

answered Dec 28, 2022 at 16:25

SomeDude

14.3k5 gold badges26 silver badges49 bronze badges