Pandas df, detecting dates

Question

I have a pandas df as follows:

Name    Cust1   Cust2   Cust3      Cust4

ABC      Y       N       Y       2022-01-01
DEF      N       N       N          N

I am looking to detect if a date is in a row for Cust1, Cust2, Cust3 and Cust4 and if so create a column to populate that date. So that output would look like

Name    Date

ABC    2022-01-01
DEF      na

Any ideas on how I can do this? I am trying to do df.iloc[:,1:].apply(np.where<xxx> but not sure of how to approach this from here on.

Thanks!

Good catch, in that case I want to take the latest date (as in most recent date). Thanks! — FlyingPickle
– FlyingPickle, Commented Mar 16, 2022 at 6:32
Not really...performance is secondary here..not many rows of data. Thanks! — FlyingPickle
– FlyingPickle, Commented Mar 16, 2022 at 16:27

Corralien · Accepted Answer · 2022-03-16 07:03:34Z

2

You can flatten your dataframe then keep the most recent date per Name:

to_date = lambda x: pd.to_datetime(x['Date'], errors='coerce')
out = df.melt('Name', value_name='Date').assign(Date=to_date) \
        .groupby('Name', as_index=False)['Date'].max()
print(out)

# Output
  Name       Date
0  ABC 2022-01-01
1  DEF        NaT

answered Mar 16, 2022 at 7:03

Corralien

121k8 gold badges44 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jezrael · Accepted Answer · 2022-03-16 06:36:18Z

1

Try convert values of all columns to datetimes by to_datetime with errors='coerce' for missing values if not datetimelike values and then get maximal dates per rows:

f = lambda x: pd.to_datetime(x, errors='coerce')
df = df.set_index('Name').apply(f).max(axis=1).reset_index(name='Date')
print (df)
  Name       Date
0  ABC 2022-01-01
1  DEF        NaT

Alternative solution:

f = lambda x: pd.to_datetime(x, errors='coerce')
df = df[['Name']].join(df.iloc[:,1:].apply(f).max(axis=1).rename('Date'))
print (df)
  Name       Date
0  ABC 2022-01-01
1  DEF        NaT

edited Mar 16, 2022 at 6:36

answered Mar 16, 2022 at 6:31

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Collectives™ on Stack Overflow

Pandas df, detecting dates

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related