Pandas Dataframe Color Styling based on NaN Values-CodePudding

I currently have a pandas dataframe that has four columns and is structured below accordingly.

| Date        | Transaction ID | Amount 1 | Amount 2 
| Nov-10-2021 | ABC123         | 100.20   | nan
| Dec-10-2021 | DEF456         | nan      | 30.21
| Dec-13-2021 | GHI789         | 341.88   | nan
| Dec-18-2021 | JKL101         | nan      | 122.3

As you can see, this is a dataframe of transactions. I want to color the transaction rows that are considered "income" as "yellow" and those that are "expenses" as "green". To do so, if amount 1 is nan, then the row is an income row, and otherwise, it'd be an expense. I tried the following after reading up the pandas color styling documentation and other Stack Overflow posts:

def yellow_or_green(dataframe):
    if (pd.isna(dataframe["Amount 1"])):
        color = "yellow"
    else:
        color = "green"
    return "color: %s" % color

df1.style.applymap(yellow_or_green)

Then, after displaying the dataframe, it remains unchanged. I'm not sure why it's not working, your help and guidance are appreciated.

CodePudding user response：

Use:

df = pd.DataFrame({'Date': ['Nov-10-2021', 'Dec-10-2021', 'Dec-13-2021', 'Dec-18-2021'], 
                   'Transaction ID': ['ABC123', 'DEF456', 'GHI789', 'JKL101'], 
                   'Amount 1': [100.2, np.nan, 341.88, np.nan],
                   'Amount 2': [ np.nan, 30.21,  np.nan, 122.3]})
    

print (df)

          Date Transaction ID  Amount 1  Amount 2
0  Nov-10-2021         ABC123    100.20       NaN
1  Dec-10-2021         DEF456       NaN     30.21
2  Dec-13-2021         GHI789    341.88       NaN
3  Dec-18-2021         JKL101       NaN    122.30

print (df.columns.tolist())
['Date', 'Transaction ID', 'Amount 1', 'Amount 2']

def yellow_or_green(x):
    c1 = 'background-color: yellow'
    c2 = 'background-color: green'
    # condition
    m = x["Amount 1"].isna()
    # DataFrame of styles
    df1 = pd.DataFrame(c1, index=x.index, columns=x.columns)

    # set columns by condition
    return df1.mask(m, c2)

#if not working convert to numeric
df["Amount 1"] = df["Amount 1"].astype(float)
#then try convert with NaNs for non parseable values
df["Amount 1"] = pd.to_numeric(df["Amount 1"], errors='coerce')

df.style.apply(yellow_or_green, axis=None).to_excel('styled.xlsx', engine='openpyxl')

If need colored only some columns:

def yellow_or_green(x):
    c1 = 'background-color: yellow'
    c2 = 'background-color: green'
    # condition
    m = x["Amount 1"].isna()
    # DataFrame of styles
    df1 = pd.DataFrame('', index=x.index, columns=x.columns)
    df1.loc[:, ['Date','Transaction ID']] = np.where(m.to_numpy()[:, None], c1, c2)
    # set columns by condition
    return df1

df.style.apply(yellow_or_green, axis=None).to_excel('styled.xlsx', engine='openpyxl')