Replace '-' character in pandas dataframe only in cells lacking digits

Question

I'm trying to convert the '-' string characters into np.nan whilst leaving the strings denoting negative floats/ints so that I can convert this into floats once removing the '-' characters which denote absent data.

I've tried using .applymap() for this as I want to apply this to the whole dataframe, but it doesn't work.

Here is the line of code:

dataframe.applymap(lambda x: None if (x[-1] == '-'))

Here is a sample of the dataframe:

Metric              2020    2019    2018     2017   
Revenue Growth %    344.17  -14.88  107.11   -
Shares Change %     0.23    0       -        -
Gross Margin %      87.7    84      89.3     84.9
Operating Margin %  -17.1   -167.2  -42.2    -99.5

Corralien · Accepted Answer · 2021-07-27 21:31:56Z

1

Use replace and regex parameter:

>>> df.replace(r'^-$', np.NaN, regex=True)
                 Metric    2020    2019    2018   2017
Revenue   Growth      %  344.17  -14.88  107.11    NaN
Shares    Change      %    0.23    0.00     NaN    NaN
Gross     Margin      %   87.70   84.00    89.3   84.9
Operating Margin      %  -17.10 -167.20   -42.2  -99.5

If you want to convert to float:

>>> df.filter(regex='\d+').replace(r'^-$', np.NaN, regex=True).astype(float)

edited Jul 27, 2021 at 21:31

answered Jul 27, 2021 at 21:24

Corralien

121k8 gold badges44 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Henry Ecker · Accepted Answer · 2021-07-27 22:07:04Z

We can use DataFrame.mask to replace cells which are equal to '-':

df = df.mask(df.eq('-'))

df:

               Metric    2020    2019    2018  2017
0    Revenue Growth %  344.17  -14.88  107.11   NaN
1     Shares Change %    0.23    0.00     NaN   NaN
2      Gross Margin %   87.70   84.00   89.30  84.9
3  Operating Margin %  -17.10 -167.20  -42.20 -99.5

We can select columns with loc and convert to float with astype (in this example is all columns from 2020 forward):

df.loc[:, '2020':] = df.loc[:, '2020':].astype('float')

Or is 2020 is a number not a string:

df.loc[:, 2020:] = df.loc[:, 2020:].astype('float')

df.info():

 #   Column  Non-Null Count  Dtype  
---  ------  --------------  -----  
 0   Metric  4 non-null      object 
 1   2020    4 non-null      float64
 2   2019    4 non-null      float64
 3   2018    3 non-null      float64
 4   2017    2 non-null      float64

DataFrame:

df = pd.DataFrame({
    'Metric': ['Revenue Growth %', 'Shares Change %', 'Gross Margin %',
               'Operating Margin %'],
    '2020': [344.17, 0.23, 87.7, -17.1],
    '2019': [-14.88, 0.0, 84.0, -167.2],
    '2018': ['107.11', '-', '89.3', '-42.2'],
    '2017': ['-', '-', '84.9', '-99.5']
})

Collectives™ on Stack Overflow

Replace '-' character in pandas dataframe only in cells lacking digits

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related