Removing values from DataFrame columns based on list of values

Question

I have a slightly specific problem.

A pandas DataFrame with let's say 6 columns. Each column has a unique set of values, which need to be looked for and removed / updated with a new value.

The lookup list would look like this:

lookup_values_per_column = [[-99999], [9999], [99, 98],[9],[99],[996, 997, 998, 999]]

Now, what I want to do is: Look at column 1 of the dataframe and check if -99999 is present, if yes, remove / update each instance with a fixed value (lets say NA's) Then we move to the next column and then check for all 9999 and also update them with NA's. If we don't find a match, we just leave the column as it is.

I couldn't find a solution and I guess it's not so hard anyways.

Henry Ecker · Accepted Answer · 2021-11-15 20:58:41Z

We can use DataFrame.replace with a dictionary built from the list and columns:

df = df.replace(
    to_replace=dict(zip(df.columns, lookup_values_per_column)),
    value=np.NAN
)

Sample output:

     A    B    C    D    E    F
0  4.0  1.0  NaN  2.0  3.0  NaN
1  NaN  3.0  2.0  NaN  4.0  NaN
2  1.0  4.0  NaN  1.0  1.0  NaN
3  2.0  2.0  3.0  3.0  2.0  1.0
4  3.0  NaN  1.0  4.0  NaN  NaN

Setup Used:

from random import sample, seed
from string import ascii_uppercase

import numpy as np
import pandas as pd

lookup_values_per_column = [
    [-99999], [9999], [99, 98], [9], [99], [996, 997, 998, 999]
]
df_len = max(map(len, lookup_values_per_column)) + 1
seed(10)
df = pd.DataFrame({
    k: sample(v + list(range(1, df_len + 1 - len(v))), df_len)
    for k, v in
    zip(ascii_uppercase, lookup_values_per_column)
})

df:

       A     B   C  D   E    F
0      4     1  99  2   3  997
1 -99999     3   2  9   4  998
2      1     4  98  1   1  999
3      2     2   3  3   2    1
4      3  9999   1  4  99  996

Collectives™ on Stack Overflow

Removing values from DataFrame columns based on list of values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related