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

user_8076768 0
Viewed 133
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.

pandas python
2 Answers

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
user_150706970

Assuming the strings in column_1 follow the same pattern, you can prepare a dictionary for the time period terms.

Preparing the dataframe

import pandas as pd

t="""had 2 months of ownership
had 1 week of ownership
had 2 years of ownership"""
df = pd.DataFrame(t.split('\n'), columns=['c1'])
df

Output

                          c1
0  had 2 months of ownership
1    had 1 week of ownership
2   had 2 years of ownership

To make it easier removing the s from the strings.

d = { 'day': 1, 'week': 7, 'month': 30, 'year': 365}
df['c1_in_days'] = df.c1.str.split().apply(lambda x: int(x[1])*d[x[2].replace('s','')])
df

Output

                          c1  c1_in_days
0  had 2 months of ownership          60
1    had 1 week of ownership           7
2   had 2 years of ownership         730
user_142777220
Related