Select two words of time period from a string in Pandas column and convert to number of days

Question

df.column_1:
had 2 months of ownership
had 1 week of ownership
had 2 years of ownership

I want to convert ownership time to date in df.column_1. The expected output is:

df.column_1:
60
7
730

Below is what I have so far:

df['column_1'] = df['column_1'].str.split(r'\D').str.get(1)

But this only gives the second string (e.g. 2, 1, 2). I was planning to get second and third strings (e.g. 2 months) and convert them to date.

Answer 1

You can use str.extract() to extract the number and period text (day/week/month/year). Then, replace the period text with a multiplication sign * followed by the corresponding number of days to make a formula (e.g. 2 *30 for 2 months). Then, use pd.eval to evaluate the values of the formula:

df['result'] = (df['column_1'].str.extract(r'(\d+\s*\w+)')[0]
                              .replace({r'days?': '*1', 
                                        r'weeks?': '*7', 
                                        r'fortnights?': '*14', 
                                        r'months?': '*30',  
                                        r'years?': '*365'}, regex=True)
                              .apply(pd.eval)
               )

Result:

print(df)

                    column_1  result
0  had 2 months of ownership      60
1    had 1 week of ownership       7
2   had 2 years of ownership     730