How to map a new key value pair in panda Dataframe

Question

I need to generate an excel sheet based on the records of MySQL. But before writing them into excel I want to add new key-value in a data frame by checking the condition of amount > 10000. Unfortunately, the status is always showing Ok status in the data frame. Even though the condition is working fine it is not getting reflected in the data frame. Some ideas or hint is really appreciated.

import mysql.connector
import pandas as pd
import numpy as np
import sys
conn = mysql.connector.connect(host='localhost',
                         database='db_name',
                         user='sammy',
                         password='password')
def apply_color(x):
    if x < 10000:
        color = 'red'
    else:
        color = 'white'

    return 'background-color: %s' %color                        
if conn.is_connected():
    writer = pd.ExcelWriter('demo.xlsx', engine='xlsxwriter')
    writer.save()
    SQL_Query=pd.read_sql_query('''select * from demo_table''', conn)
    df = pd.DataFrame(SQL_Query, columns=['id','desp','amount'])
    df['status'] = np.where(df['amount'].astype(float) >= 10000, 'OK', 'NOTOKS')
    df.style.applymap(apply_color, subset=['status'])
    writer = pd.ExcelWriter('demo.xlsx', engine='xlsxwriter')
    df.to_excel(writer, sheet_name='Sheet1', index=False)
    writer.save()

Sajan · Accepted Answer · 2020-03-25T11:56:23.020

0

This edit to the code might make it work -

import numpy as np
df = pd.DataFrame(SQL_Query, columns=['id','desp','amount'])
df['status'] = np.where(df['amount'].astype(float) >= 1000, 'OK', 'NOTOK')

For Color coding of cell ( Reference : Format the color of a cell in a pandas dataframe according to multiple conditions )-

def apply_color(x):
    if x < 10000:
        color = 'red'
    else:
        color = 'white'

    return 'background-color: %s' %color

df.style.applymap(apply_color, subset=['status'])

I was able to able to get the above color coding working in a jupyter notebook. Not sure, if this is what you were looking out for.

edited Mar 25 '20 at 11:56

answered Mar 24 '20 at 19:28

Sajan

1,247
1
5
13

Along with this, I have one more need to give red background color for the cell status when the amount is less than 10000 – Mar 25 '20 at 04:37
Tried with above hints. But still style is not added For refernce you can check my edited code in the question section – Mar 25 '20 at 15:09
The code which I mentioned above will work only in a jupyter notebook and probably not when writing to excel. That is why style is not being added. You may want to check this post, maybe - https://pbpython.com/improve-pandas-excel-output.html – Sajan Mar 25 '20 at 16:02

How to map a new key value pair in panda Dataframe

1 Answers1