CODEWITHSUNDEEP
pythonpandas
user3709260
user3709260

Reputation: 431

How to multiply only the numeric values in a column

  1. I read an Excel file using pd.read_excel()
  2. One of the columns is called 'Energy Supply' containing strings and numbers
  3. I use the following code:

for x in energy['Energy Supply']: print(type(x))

and the sample of results is:

<class 'str'>
<class 'int'>
<class 'float'>

I need to multiply on the numeric values in this column by a million.

I tried the following code but it didn't work saying:

unorderable types: str() > int()

for x in energy['Energy Supply']:
    if type(x) != 'str':
        while x>0:
            x = x*1000000

I tried:

energy['Energy Supply'].select_dtypes(include=['int64']) * 1000000

but it said:

AttributeError: 'Series' object has no attribute 'select_dtypes'

I tried:

energy['Energy Supply'] = [x * 1000000 for (type(x)=='int' 
                                            & x in energy['Energy Supply'])]

but got the syntax error.

Neither worked the following line:

energy.multiply(1000000, axis='Energy Supply', level=None, fill_value=None)

The following line doesn't give me errors but it doesn't do anything:

energy[energy.select_dtypes(include=['number']).columns] *= 1000000

I really appreciate if someone you could please help me how to do this.

Upvotes: 2

Views: 1458

Answers (3)

piRSquared
piRSquared

Reputation: 294338

  • pd.to_numeric with parameter errors='coerce'
  • dropna + update

Using @jezrael's dataframe

energy = pd.DataFrame({'Energy Supply':[10, 1.0,'a']})

energy.update(pd.to_numeric(energy['Energy Supply'], 'coerce').mul(1e6).dropna())
energy

  Energy Supply
0         1e+07
1         1e+06
2             a

Upvotes: 2

jezrael
jezrael

Reputation: 862771

You can use mask with mask created by to_numeric and notnull:

energy = pd.DataFrame({'Energy Supply':[10, 1.0,'a']})
print(energy)
  Energy Supply
0            10
1             1
2             a

mask = pd.to_numeric(energy['Energy Supply'], errors='coerce').notnull()
print (mask)
0     True
1     True
2    False
Name: Energy Supply, dtype: bool

Another solution is check types:

mask = energy['Energy Supply'].apply(lambda x: type(x) in [int, float])
print (mask)
0     True
1     True
2    False
Name: Energy Supply, dtype: bool

energy['Energy Supply'] = energy['Energy Supply'].mask(mask, 
                                                       energy['Energy Supply'] * 1000000)
print (energy)
  Energy Supply
0      10000000
1         1e+06
2             a

Instead mask is possible use loc for select only numeric values:

print (energy.loc[mask, 'Energy Supply'])
0    10
1     1
Name: Energy Supply, dtype: object

energy.loc[mask, 'Energy Supply'] = energy.loc[mask, 'Energy Supply'] * 1000000
print (energy)
  Energy Supply
0      10000000
1         1e+06
2             a

Upvotes: 2

DYZ
DYZ

Reputation: 57033

Another solution:

def multiply_if_number(x): # This could be a `lambda`
    return x * 1e6 if type(x) in [int,float] else x

energy['Energy Supply'] = energy['Energy Supply'].apply(multiply_if_number)

Upvotes: 1

Related Questions