How to perform a conditional calculation

user_13961615 0
Viewed 139

Trying to deal with an issue how to implement dictionary to existing DataFrame's column by certain indexes, i.e.:

Primary DataFrame:

             country    year      Corruption Index
0            X          2010           6,5
1            X          2015          78,0

So I have dictionary (separately devide 78 by 10):

x = {1: 7,8}

I want to change value of index 1, column 'Corruption Index'. I now I can use for loop, but are there any faster variants ? Or maybe I can directly divide values in Existing DataFrame which values are higher than 10 by ten? (the problem is that official statistics after 2012 turns from 1-10 to 1-100)

pandas dictionary python
2 Answers
  • The fastest solution is the answer from Mykola Zotko
  • For a vectorized solution, use pandas.DataFrame.where, which will replace values where the condition is False.
    • .where will be much faster than using .apply, as df['Corruption Index'].apply(lambda x: x/10 if x > 10 else x)
  • numpy.where is slightly different, in that it works on the True condition, and is faster than pandas.DataFrame.where, but requires importing numpy.
  • Since this issue seems to be related to official statistics after 2012 turns from 1-10 to 1-100, m = df.year <= 2012 should be used as the condition.
import pandas as pd
import numpy as np

# test dataframe
df = pd.DataFrame({'country': ['X', 'X'], 'year': [2010, 2015], 'Corruption_Index': [6.5, 78.0]})

# display(df)
  country  year  Corruption_Index
0       X  2010               6.5
1       X  2015              78.0

# create a Boolean mask for the condition
m = df.year <= 2012

# use pandas.DataFrame.where to calculate on the False condition
df['Corruption_Index'].where(m, df['Corruption_Index'] / 10, inplace=True)

# alternatively, use np.where to calculate on the True condition
df['Corruption_Index'] = np.where(df.year > 2012, df['Corruption_Index'] / 10, df['Corruption_Index'])

# display(df)
  country  year  Corruption_Index
0       X  2010               6.5
1       X  2015               7.8

%%timeit Comparison

# test data with 1M rows
np.random.seed(365)
df = pd.DataFrame({'v': [np.random.randint(20) for _ in range(1000000)]})

%%timeit
df.loc[df['v'] > 10, 'v'] /= 10
[out]:
2.66 ms ± 61.4 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

%%timeit
np.where(df['v'] > 10, df['v'] / 10, df['v'])
[out]:
8.11 ms ± 403 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

%%timeit
df['v'].where(df['v'] <= 10, df['v'] / 10)
[out]:
17.9 ms ± 615 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

%%timeit
df['v'].apply(lambda x: x/10 if x > 10 else x)
[out]:
319 ms ± 29.3 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)
user_77588040

You can use:

df.loc[df['Corruption_Index'] > 10, 'Corruption_Index'] /= 10
user_89736200
Related