Separate string column into multiple columns in Pandas

Question

There is this one column that has two variables stored namely date and company name. My goal is to seperate these two variables into two columns.

date_time/full_company_name

2020-05-19Lopez-Wallace
2020-05-12Smith-Simon
2020-10-02Jenkins Inc
2020-07-06Moore-Weiss

My approach so far was:

df['date_time'] = [i[:10] for i in df['date_time/full_company_name']]
df['full_company_name'] = [i[10:] for i in df['date_time/full_company_name']]
df.drop('date_time/full_company_name', axis=1, inplace=True)

The code above has worked well, however there are a number of botched data entries in the data set such as:

0Lopez, Barton and Jones
NaNBrown, Singleton and Harrell
84635Ball-Thomas

I have thought about some possible solutions such as using a loop with a bunch of if statements to handle the exceptions or perhaps inserting some kind of delimiter into the string and then using string.split('_'). But these workarounds are fairly cumbersome.

I can't help but wonder if there is a more generic function or method available out there.

Edgar Ramírez Mondragón · Accepted Answer · 2020-11-06 21:59:57Z

In general iterating over the contents of a pandas series or dataframe is bad for performance and instead vectorized approaches are recommended when possible:

Iterating through pandas objects is generally slow. In many cases, iterating manually over the rows is not needed and can be avoided...

See the docs.

That said, for the "non-botched" entries you can do this:

df['date_time'] = df['date_time/full_company_name'].str.slice(0, 10)
df['full_company_name'] = df['date_time/full_company_name'].str.slice(10, None)
df.drop('date_time/full_company_name', axis=1, inplace=True)

For botched entries, if there is no consistent pattern, it's gonna be hard to come up with a programmatic approach, but something that works for your examples and could be extended with other known entry errors is using extract with a regular expression:

# The first capture group is either a Y-M-D date, the NaN string or a sequence of digits
# The second one is any string
pattern = r'(\d{4}-\d{2}-\d{2}|NaN|0|\d+)(.+)'

df['date_time/full_company_name'].str.extract(pattern)

Output:

            0                             1
0  2020-05-19                 Lopez-Wallace
1  2020-05-12                   Smith-Simon
2  2020-10-02                   Jenkins Inc
3  2020-07-06                   Moore-Weiss
4           0       Lopez, Barton and Jones
5         NaN  Brown, Singleton and Harrell
6       84635                   Ball-Thomas

Collectives™ on Stack Overflow

Separate string column into multiple columns in Pandas

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related