How to round and apply min and max to all values in Pandas Dataframe

user_4412568 0
Viewed 1095

I'm struggling on how to clean-up a dataframe. What I would like to do is truncate all items (i.e. floor()), and for any items below or over a min/max, replace with the min or max as applicable. E.g. for this dataframe:

enter image description here

If my min and max are 1 and 5 respectively, 1.2 would truncate to 1, 9.6 would map to 5, -1.2 would map to 1, and 3.5 would truncate to 3:

enter image description here

Other than brute-force iteration using iterrows(), I haven't been able to get this to work. Lots of stuff on finding the min and max, but not on applying a min and max.

May I please ask if anyone has some suggestions? Thank you.

data-wrangling pandas python
2 Answers

You can use applymap, such as:

from numpy import floor

MAX, MIN = 5, 1

df = df.applymap(lambda val: MAX if val > MAX else int(floor(val)) if val > MIN else MIN)
user_142988800

You can use df.clip and cast to int

df = pd.DataFrame({
    'A':[1.2, 3.5],
    'B':[9.6, -1.2]
})
df.clip(1,5).astype('int')

Out:

   A  B
0  1  5
1  3  1

If you want float values you can floor the dataframe with np.floor, which conveniently returns a pd.dataframe and then clip.

import numpy as np

np.floor(df)

Out:

     A    B
0  1.0  9.0
1  3.0 -2.0

np.floor(df).clip(1,5)

Out:

     A    B
0  1.0  5.0
1  3.0  1.0

Micro-Benchmark

With python 3.6.9, pandas 1.1.5 on a google colab instance

Results:

benchmark results

Code used for the benchmark

import pandas as pd
import numpy as np
import perfplot

def make_data(n=100):
    return pd.DataFrame(
        np.random.uniform(-1.2, 9.6, (n,10))
    )

def clip_castint(df):
    return df.clip(1,5).astype('int')

def clip_npfloor(df):
    return np.floor(df.clip(1,5))

from numpy import floor
def applymap(df):
    MAX, MIN = 5, 1
    return df.applymap(lambda val: MAX if val > MAX else int(floor(val)) if val > MIN else MIN)

perfplot.show(
    setup=make_data,
    kernels=[clip_castint, clip_npfloor, applymap],
    n_range=[2**k for k in range(10,22)],
    xlabel="df(rows, 10)"
)
user_142777220
Related