Pandas apply multiple function with list

user_13441090 0
Viewed 67

I have a df with a 'File_name' column which contains strings of a file name, which I would like to parse:

data = [['f1h3_13oct2021_gt1.csv',  2], ['p8-gfr-20dec2021-81.csv', 0.5]]
df= pd.DataFrame(data, columns = ['File_name', 'Result'])
df.head()

Now I would like to create a new column where I parse the file name with '_' and '-' delimiters and then search in resulting list for the string that I could transform in datetime object. The name convention is not always the same (different order, so I cannot rely on string characters location) and the code should include a "try" conversion to datetime, as often the piece of string which should be the date is either in the wrong format or missing. I came up with the following, but it does not really look pythonic to me

# Solution #1
for i, value in df['File_name'].iteritems():
    chunks = value.split('-') + value.split('_')
    for chunk in chunks:
         try:
            df.loc[i,'Date_Sol#1'] = dt.datetime.strptime(chunk, '%d%b%Y')
         except:
            pass
df.head()

Alternative, I was trying to use the apply method with the two functions I really cannot think a way to solve the two functions chained and the try - pass statement, but I really did not manage to get it working

# Solution #2
import re
splitme = lambda x: re.split('_|-', x)
calcdate = lambda x : dt.datetime.strptime(x, '%d%b%Y') 
df['t1'] = df['File_name'].apply(splitme)
df['Date_Sol#2'] =df['t1'].apply(lambda x: calcdate(x) for x in df['t1'] if isinstance(calcdate(x),dt.datetime) else Pass)

df.head()

I thought a list comprehension might help? Any help how Solution #2 might look like? Thanks in advance

pandas apply lambda python
1 Answers

Assuming you want to extract and convert the possible chunks as date, you could split the string on delimiters, explode to multiple rows and attempt to convert to date with pandas.to_datetime:

df.join(pd
 .to_datetime(df['File_name']
              .str.split(r'[_-]')
              .explode(), errors='coerce')
 .dropna().rename('Date')
)

output:

                 File_name  Result       Date
0   f1h3_13oct2021_gt1.csv     2.0 2021-10-13
1  p8-gfr-20dec2021-81.csv     0.5 2021-12-20

NB. if you have potentially many dates per string, you need to add a further step to select the one you want. Please give more details if this is the case.

python version for old pandas
import re
s = pd.Series([next(iter(pd.to_datetime(re.split(r'[._-]', s), errors='coerce')
                           .dropna()), float('nan'))
               for s in df['File_name']], index=df.index, name='date')

df.join(s)
user_163434640
Related