Create a new columns to show the month of last spend in Pandas-CodePudding

I am working on a spend data where I want to see the last month when the spend was made in current and previous year. If there is no spend in these years, then, I assume the last month of spend as Dec-2020.

My data looks like this

As shown in the data the months are already there in the form of columns. I want to create a new column last_txn_month which gives last month when the spend was made. So the output should look like this:

CodePudding user response：

Let's say your DataFrame looks like:

df = pd.DataFrame([[1, np.nan, np.nan, 3, np.nan], [10, 11, 12, 13, 14],
                   [101, 102, np.nan, np.nan, np.nan], 
                   [110, np.nan, np.nan, 111, np.nan]], 
                  columns=[*'abcde'])

Then you could use notna to create boolean DataFrame; then apply a lambda function that filters the last column name of a non-NaN value for each row:

df['last'] = df.notna().apply(lambda x: df.columns[x][-1], axis=1)

Output:

     a      b     c      d     e last
0    1    NaN   NaN    3.0   NaN    d
1   10   11.0  12.0   13.0  14.0    e
2  101  102.0   NaN    NaN   NaN    b
3  110    NaN   NaN  111.0   NaN    d