Identifying Column Values and then Assigning New Values to Each

Question

I have a pandas DataFrame with cities and a separate list with multipliers for each city. I want to update the TaxAmount in the first df with the corresponding multiplier for each city, from the list.

My current code functions and runs fine but it sets the multiplier to being the same for all cities instead of updating to a new multiplier. So basically all the city's tax rates are the same when they should be different. Any suggestions on how to get this to work?

import pandas as pd
df = pd.DataFrame({
    'City': ['BELLEAIR BEACH', 'BELLEAIR BEACH', 'CLEARWATER', 'CLEARWATER'],
    'TaxAnnualAmount': [5672, 4781, 2193.34, 2199.14]
})

flag = True
flag = (df['City'] == 'Belleair Bluffs') 
if (flag.any() == True):
    df.loc['TaxAnnualAmount'] = ((df['CurrentPrice'] / 1000) * 19.9818)
    
flag = True
flag = (df['City'] == 'Belleair') 
if (flag.any() == True):
    df.loc['TaxAnnualAmount'] = ((df['CurrentPrice'] / 1000) * 21.1318)
    
flag = True
flag = (df['City'] == 'Belleair Shore') 
if (flag.any() == True):
    df.loc['TaxAnnualAmount'] = ((df['CurrentPrice'] / 1000) * 14.4641)

RichieV · Accepted Answer · 2020-11-30T15:51:40.003

As per your comment, whenever you need to update all rows (or most of them) with a different factor, you can create a second dataframe with those values and merge it with your original.

# sample data
df = pd.DataFrame({
    'City': ['BELLEAIR BEACH', 'BELLEAIR BEACH', 'CLEARWATER', 'Belleair'],
    'TaxAnnualAmount': [5672, 4781, 2193.34, 500]
})

mults = pd.DataFrame([
    ['Belleair Bluffs', 19.9818],
    ['Belleair', 21.1318],
    ['Belleair Shore', 14.4641]
        ], columns=['City', 'factor'])

df = df.merge(mults, on='City', how='left')
df['NewTaxAmount'] = df['TaxAnnualAmount'].div(1000).mul(df['factor'])
print(df)

Output

             City  TaxAnnualAmount   factor  NewTaxAmount
0  BELLEAIR BEACH          5672.00      NaN           NaN
1  BELLEAIR BEACH          4781.00      NaN           NaN
2      CLEARWATER          2193.34      NaN           NaN
3        Belleair           500.00  21.1318       10.5659

Notice two things:

The how='left' parameter tells pandas to include all rows from the main dataframe and fill nan on the rows that don't have a match.
You must be careful whenever overwriting columns on a dataframe, make sure you don't have lines like this inside a loop (as you would have with your previous method).

For more on merging you can look at the documentation and this excellent answer by cs95.

Identifying Column Values and then Assigning New Values to Each

1 Answers1