Coalesce values only from columns where column matches with data dates

Question

I have a data frame similar to one below.

        Date  20180601T32  20180604T33 20180605T32 20180610T33
  2018-06-04       0.1       0.5          4.5      nan
  2018-06-05       1.5       0.2         nan        0
  2018-06-07       1.1       1.6         nan       nan
  2018-06-10       0.4       1.1           0       0.3

The values in columns '20180601', '20180604', '20180605' and '20180607' needs to be coalesced into a new column. I am using the method bfill as below but it selects first value in the row.

coalsece_columns = ['20180601', '20180604', '20180605', '20180610] 
df['obs'] = df[coalesce_columns].bfill(axis=1).iloc[:,0]

But instead of taking value from first column, value should match 'Date' and respective column names. The expected output should be:

    Date  20180601T32  20180604T33 20180605T32 20180610T33  Obs
  2018-06-04       0.1       0.5          4.5         nan       0.5
  2018-06-05       1.5       0.2          1.7         0        1.7
  2018-06-07       1.1       1.6          nan         nan       nan
  2018-06-10       0.4       1.1           0          0.3       0.3

Any suggestions?

It is not only simple lookup, so reopened. – jezrael Sep 07 '22 at 05:04 — jezrael, Sep 07 '22 at 05:04

score 3 · Answer 1 · answered Sep 06 '22 at 13:04

Use lookup with convert Datecolumn to same format like columns names:

df['Date'] = pd.to_datetime(df['Date'])

idx, cols = pd.factorize(df['Date'].dt.strftime('%Y%m%d'))

df['obs'] = df.reindex(cols, axis=1).to_numpy()[np.arange(len(df)), idx]

print (df)
        Date  20180601  20180604  20180605  20180610  obs
0 2018-06-04       0.1       0.5       4.5       NaN  0.5
1 2018-06-05       1.5       0.2       1.7       0.0  1.7
2 2018-06-07       1.1       1.6       NaN       NaN  NaN
3 2018-06-10       0.4       1.1       0.0       0.3  0.3

If possible columnsnames are integers:

df['Date'] = pd.to_datetime(df['Date'])

idx, cols = pd.factorize(df['Date'].dt.strftime('%Y%m%d'))

df['obs'] = df.rename(columns=str).reindex(cols, axis=1).to_numpy()[np.arange(len(df)), idx]

print (df)
        Date  20180601  20180604  20180605  20180610  obs
0 2018-06-04       0.1       0.5       4.5       NaN  0.5
1 2018-06-05       1.5       0.2       1.7       0.0  1.7
2 2018-06-07       1.1       1.6       NaN       NaN  NaN
3 2018-06-10       0.4       1.1       0.0       0.3  0.3

Coalesce values only from columns where column matches with data dates

1 Answers1