CODEWITHSUNDEEP
pythonpandasnumpy
CLSolutions
CLSolutions

Reputation: 1

Why does trying to divide these Dataframe columns cause a TypeError?

I have this dataset, which comes from an excel report:

when I use the following code:

import pandas as pd

df = pd.read_excel(r'C:\Users\Charles\Documents\CLS\CLS Python\Dashboard.xlsx')

profit = df['Gross Margin']
sales = df['Total Billing Rate']
df['Net_%'] = profit / sales

it results in:

TypeError: unsupported operand type(s) for /: 'str' and 'int'

I have tried googling and using int and float but cannot get the code to even begin running. What am I doing wrong?

Upvotes: -1

Views: 235

Answers (2)

mahmoud lotfi
mahmoud lotfi

Reputation: 21

The best way is to create a custom function to handle the error during the conversion process:

def try_convert_int(x):   
    try:
        x = int(x)
    except:
        x = 1                
    return x

Applying the function to df:

import pandas as pd

df = pd.read_excel(r'C:\Users\Charles\Documents\CLS\CLS Python\Dashboard.xlsx')

# try your code now
profit = df.apply(lambda x: try_convert_int(x['Gross Margin']), axis=1)
sales  = df.apply(lambda x: try_convert_int(x['Total Billing Rate']), axis=1)
df['Net_%'] = profit / sales

Upvotes: 0

Ach113
Ach113

Reputation: 1825

The error means that when you are trying to perform / operation, left hand side of it is str, while right hand side is int, which is not allowed.

The problem is, df['Gross Margin'] seems to include some - entries, which are str, so whole column gets read as str. Probably what you should do is replace - with 0 or some other value like this:

df['Gross Margin'] = df['Gross Margin'].replace('-', 0)

Upvotes: 2

Related Questions