CODEWITHSUNDEEP
python-3.xpandas
Jason Baker
Jason Baker

Reputation: 3706

I'm trying to find the last column with data in each row and return the column name to the data frame

I'm trying to get the column name the value from ffill is from.

I've searched google and stack overflow and haven't found a way to accomplish this.

This is the ffill code: df["LAST_PUNCH"] = df.ffill(axis=1).iloc[:, -1]

This is my dataframe:

SHIFT     IN     OUT    IN_1    
DA6-0730  07:30  12:35  13:05      
DB0-ACOM  08:18  12:30             
DC4-0730  07:30  12:39  13:09     
DC4-0730  07:30  12:34  13:04

This is my dataframe after using ffill:

SHIFT     IN     OUT    IN_1   LAST_PUNCH
DA6-0730  07:30  12:35  13:05  13:05
DB0-ACOM  08:18  12:30         12:30
DC4-0730  07:30  12:39  13:09  13:09
DC4-0730  07:30  12:34  13:04  13:04

I would like to get the column name where the ffill value came from and append to the end of the ffill value:

SHIFT     IN     OUT    IN_1   LAST_PUNCH
DA6-0730  07:30  12:35  13:05  13:05_IN_1
DB0-ACOM  08:18  12:30         12:30_OUT
DC4-0730  07:30  12:39  13:09  13:09_IN_1
DC4-0730  07:30  12:34  13:04  13:04_IN_1

Upvotes: 1

Views: 55

Answers (1)

BENY
BENY

Reputation: 323236

Ummm this is a little bit tricky 

(df+'_'+pd.DataFrame(dict(zip(df.columns.values,df.columns.values)),index=df.index)).\
    reindex(columns=df.columns).ffill(axis=1).iloc[:,-1]
Out[360]: 
0    13:05_IN_1
1     12:30_OUT
2    13:09_IN_1
3    13:04_IN_1
Name: IN_1, dtype: object

Or using idxmax with reversed order of columns 

df.ffill(axis=1).iloc[:, -1]+'_'+df[df.columns[::-1]].notnull().idxmax(1)
Out[375]: 
0    13:05_IN_1
1     12:30_OUT
2    13:09_IN_1
3    13:04_IN_1
dtype: object

Upvotes: 1

Related Questions