Use filename as an indicator to process datetime columns using pandas

user_10829044 0
Viewed 57

I am reading 15 csv files into pandas dataframe. The columns that I want in final dataframe are spread across multiple csv files

Filename pattern for file 1 to file 8 - Med* (ex: Med1, Med2, Medtest, Medkill)

Sample data from file 1 to file 8 looks like below

df = pd.DataFrame({'person_id': [101,101,101,101,202,202,202],
                   'start_date':['5/7/2013 09:27:00 AM','09/08/2013 11:21:00 AM','06/06/2014 08:00:00 AM','06/06/2014 05:00:00 AM','12/11/2011 10:00:00 AM','13/10/2012 12:00:00 AM','13/12/2012 11:45:00 AM'],'type':['O','I','O','O','I','O','I']})

Filename pattern for file 9 to file 12 - Adm* (ex: Adm1, Adm2, Admaasd, Admmed)

Sample data from file 9 to file 12 looks like below

df = pd.DataFrame({'person_id': [101,101,101,101,202,202,202],
                        'start_date':['5/7/2013 09:27:00 AM','09/08/2013 11:21:00 AM','06/06/2014 08:00:00 AM','06/06/2014 05:00:00 AM','12/11/2011 10:00:00 AM','13/10/2012 12:00:00 AM','13/12/2012 11:45:00 AM'],
                        'end_date':['5/12/2013 09:27:00 AM',np.nan,'06/11/2014 08:00:00 AM',np.nan,'12/16/2011 10:00:00','10/18/2012 00:00:00',np.nan],
                        'type':['O','I','O','O','I','O','I']})

Filename pattern for file 13 to file 15 - cas* (ex: castest, caste22, caskill)

Sample data from file 13 to file 15 looks like below

df = pd.DataFrame({'person_id': [101,101,101],
                   'start_date':['5/2/2013 09:27:00 AM','09/03/2013 11:21:00 AM','06/01/2014 08:00:00 AM'],'dur':['3w','3m','2d']})

In my final dataframe, I expect the column names to be as shown below

person_ID, start_date, end_date

As you can see in file 1 to 8 there is no end_date column. Because end_date = start_date. So we have to copy the start_date value to end_date column

Similarly in file 13 to 15, we don't have end_date column as well. But we can derive end_date by adding the duration column values to start_date.

I tried the below

pat_dir = ['Med*.csv','C:\\test\\Adm*.csv', 'C:\\test12\\test13\\cas*.csv']
files_grabbed = [] 
cols = ['person_id','start_date','end_date']
dfs = [pd.read_csv(f, sep=",",low_memory=False).reindex(columns=cols) 
       for f in files_grabbed]

Though the above code creates end_date column in the final dataframe with NA (for files which didn't have this column in their raw files).

The problem is how can I recognize that the files with patterns require different way of processing end_dates

Med* - copy start_date values as end_date

cas* - add duration to start_date and store them in end_date column

Through my code, I created a final dataframe but lost on how to apply end_date logic to those files?

Is it possible to add those logic before creating final dataframe or during read operation?

pandas series dataframe numpy python
1 Answers

Use if statemenet for test by filenames:

import os

#custom function for add values by duration
def func(x):

    if pd.isna(x['dur']):
        return x['start_date']

    elif x[1] == 'w':
        return x['start_date'] + pd.offsets.DateOffset(weeks=x[0])
    elif x[1] == 'm':
        return x['start_date'] + pd.offsets.DateOffset(months=x[0])
    elif x[1] == 'd':
        return x['start_date'] + pd.offsets.DateOffset(days=x[0])
    else:
        return x['start_date']

dfs = []
for f in files_grabbed:
    df = pd.read_csv(f, sep=",",low_memory=False)
    name = os.path.basename(f)
    if name.startswith('Med'):
        dfs.append(df.assign(end_date = df['start_date']))

    elif name.startswith('cas'):

        df['start_date'] = pd.to_datetime(df['start_date'])
        df[[0,1]] = df['dur'].str.extract('(\d+)(\D+)')
        df[0] = df[0].fillna(0).astype(int)
     
        df['end_date'] = df.apply(func, axis=1)
        dfs.append(df)
    else:
        dfs.append(df)
user_29010020
Related