python pandas split datafram column into two new column and drop the original column

user_14440486 0
Viewed 277

I have the following dataframe:

import pandas as pd
import numpy as np

df = pd.DataFrame({'Name': ['Steve Smith', 'Joe Nadal',
                            'Roger Federer'],
                  'birthdat/company': ['1995-01-26Sharp, Reed and Crane',
                                      '1955-08-14Price and Sons',
                                      '2000-06-28Pruitt, Bush and Mcguir']})

df[['data_time','full_company_name']] = df['birthdat/company'].str.split('[0-9]{4}-[0-9]{2}-[0-9]{2}', expand=True)
df

with my code I get the following:

____|____Name______|__birthdat/company_______________|_birthdate_|____company___________
0   |Steve Smith   |1995-01-26Sharp, Reed and Crane  |           |Sharp, Reed and Crane
1   |Joe Nadal     |1955-08-14Price and Sons         |           |Price and Sons
2   |Roger Federer |2000-06-28Pruitt, Bush and Mcguir|           |Pruitt, Bush and Mcguir

what I want is - get this regex ('[0-9]{4}-[0-9]{2}-[0-9]{2}') and the rest should go to the column "full_company_name" and :

____|____Name______|_birthdate_|____company_name_______
0   |Steve Smith   |1995-01-26 |Sharp, Reed and Crane
1   |Joe Nadal     |1955-08-14 |Price and Sons
2   |Roger Federer |2000-06-28 |Pruitt, Bush and Mcguir

Updated Question: How could I handle missing values for birthdate or company name, example: birthdate/company = "NaApple" or birthdate/company = "2003-01-15Na" the missing values are not only limited to Na

pandas dataframe python-3.x python regex
2 Answers

You may use

df[['data_time','full_company_name']] = df['birthdat/company'].str.extract(r'^([0-9]{4}-[0-9]{2}-[0-9]{2})(.*)', expand=False)
>>> df
            Name  Age  ...   data_time        full_company_name
0    Steve Smith   32  ...  1995-01-26    Sharp, Reed and Crane
1      Joe Nadal   34  ...  1955-08-14           Price and Sons
2  Roger Federer   36  ...  2000-06-28  Pruitt, Bush and Mcguir

[3 rows x 5 columns]

The Series.str.extract is used here because you need to get two parts without losing the date.

The regex is

  • ^ - start of string
  • ([0-9]{4}-[0-9]{2}-[0-9]{2}) - your date pattern captured into Group 1
  • (.*) - the rest of the string captured into Group 2.

See the regex demo.

user_38329700

split splits the string by the separator while ignoring them. I think you want extract with two capture groups:

df[['data_time','full_company_name']] = \
   df['birthdat/company'].str.extract('^([0-9]{4}-[0-9]{2}-[0-9]{2})(.*)')

Output:

    Name           birthdat/company                   data_time    full_company_name
--  -------------  ---------------------------------  -----------  -----------------------
 0  Steve Smith    1995-01-26Sharp, Reed and Crane    1995-01-26   Sharp, Reed and Crane
 1  Joe Nadal      1955-08-14Price and Sons           1955-08-14   Price and Sons
 2  Roger Federer  2000-06-28Pruitt, Bush and Mcguir  2000-06-28   Pruitt, Bush and Mcguir
user_42384080
Related