Replacing values in specific columns in a Pandas Dataframe, when number of columns are unknown

user_9008856 0
Viewed 1817

I am brand new to Python and stacks exchange. I have been trying to replace invalid values ( x<-3 and x>12) with np.nan in specific columns.

I don't know how many columns I will have to deal with and thus will have to create a general code that takes this into account. I do however know, that the first two columns are ids and names respectively. I have searched google and stacks exchange for a solution but haven't been able to find a solution that solves my specific objective.

My question is; How would one replace values found in the third column and onwards?

My dataframe looks like this;

Data

I tried this line:

Data[Data > 12.0] = np.nan.

this replaced the first two columns with nan

1st attempt

I tried this line:

Data[(Data.iloc[(range(2,Columns))] >=12) & (Data.iloc[(range(2,Columns))]<=-3)] = np.nan

where,

Columns = len(Data.columns)

This is clearly wrong replacing all values in rows 2 to 6 (Columns = 7).

2nd attempt

Any thoughts would be greatly appreciated.

Python 3.6.1 64bits, Qt 5.6.2, PyQt5 5.6 on Darwin

pandas dataframe python-3.x
2 Answers

You're looking for the applymap() method.

import pandas as pd
import numpy as np

# get the columns after the second one
cols = Data.columns[2:]

# apply mask to those columns
new_df = Data[cols].applymap(lambda x: np.nan if x > 12 or x <= -3 else x)

Documentation: https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.applymap.html

This approach assumes your columns after the second contain float or int values.

user_39029260

You can set values to specific columns of a dataframe by using iloc and slicing the columns that you need. Then we can set the values using where

A short example using some random data

df = pd.DataFrame(np.random.randint(0,10,(4,10)))


   0  1  2  3  4  5  6  7  8  9
0  7  7  9  4  2  6  6  1  7  9
1  0  1  2  4  5  5  3  9  0  7
2  0  1  4  4  3  8  7  0  6  1
3  1  4  0  2  5  7  2  7  9  9

Now we set the region to update and the region we want to update using iloc, and we slice columns indexed as 2 to the last column

df.iloc[:,2:] = df.iloc[:,2:].where((df < 7) & (df > 2))

Which will set the values in the Data Frame to NaN.

   0  1    2    3    4    5    6   7    8   9
0  7  7  NaN  4.0  NaN  6.0  6.0 NaN  NaN NaN
1  0  1  NaN  4.0  5.0  5.0  3.0 NaN  NaN NaN
2  0  1  4.0  4.0  3.0  NaN  NaN NaN  6.0 NaN
3  1  4  NaN  NaN  5.0  NaN  NaN NaN  NaN NaN

For your data the code would be this

Data.iloc[:,2:] = Data.iloc[:,2:].where((Data <= 12) & (Data >= -3))

Operator clarification

The setup I show directly above would look like this

-3 <= Data <= 12, gives everything between those numbers

If we reverse this logic using the & operator it looks like this

-3 >= Data <= 12, a number cannot be both less than -3 and greater than 12 at the same time.

So we use the or operator instead |. Code looks like this now....

Data.iloc[:,2:] = Data.iloc[:,2:].where((Data >= 12) | (Data <= -3))

So the data is checked on a conditional basis

Data <= -3 or Data >= 12

user_55144760
Related