2

I wrote this code that computes time since a sign change (from positive to negative or vice versa) in data frame columns.

df = pd.DataFrame({'x': [1, -4, 5, 1, -2, -4, 1, 3, 2, -4, -5, -5, -6, -1]})

for column in df.columns:
    days_since_sign_change = [0]
    for k in range(1, len(df[column])):
        last_different_sign_index = np.where(np.sign(df[column][:k]) != np.sign(df[column][k]))[0][-1]
        days_since_sign_change.append(abs(last_different_sign_index- k))
    df[column+ '_days_since_sign_change'] = days_since_sign_change        
    df[column+ '_days_since_sign_change'][df[column] < 0] = df[column+ '_days_since_sign_change'] *-1 
# this final stage allows the "days_since_sign_change" column to also indicate if the sign changed 
# from - to positive or from positive to negative.

In [302]:df
Out[302]: 
    x  x_days_since_sign_change
0   1                         0
1  -4                        -1
2   5                         1
3   1                         2
4  -2                        -1
5  -4                        -2
6   1                         1
7   3                         2
8   2                         3
9  -4                        -1
10 -5                        -2
11 -5                        -3
12 -6                        -4
13 -1                        -5

Issue: with large datasets (150,000 * 50,000), the python code is extremely slow. How can I speed this up?

jpp
  • 159,742
  • 34
  • 281
  • 339
Saeed
  • 1,848
  • 1
  • 18
  • 26
  • Can you include an example of the large datasets giving performance trouble? – user2699 Mar 22 '18 at 00:43
  • 1
    The large dataset is similar to sample data given but in hundred K or million of rows. His question is how to re write the codes to run faster. – jose_bacoy Mar 22 '18 at 00:55

2 Answers2

4

You can using cumcount 

s=df.groupby(df.x.gt(0).astype(int).diff().ne(0).cumsum()).cumcount().add(1)*df.x.gt(0).replace({True:1,False:-1})
s.iloc[0]=0
s
Out[645]: 
0     0
1    -1
2     1
3     2
4    -1
5    -2
6     1
7     2
8     3
9    -1
10   -2
11   -3
12   -4
13   -5
dtype: int64
BENY
  • 317,841
  • 20
  • 164
  • 234
3

You can surely do this without a loop. Create a sign column with -1 if value in x is less than 0 and 1 otherwise. Then group that sign column by difference in the value in the current row vs the previous one and get cumulative sum.

df['x_days_since_sign_change'] = (df['x'] > 0).astype(int).replace(0, -1)

df.iloc[0,1] = 0
df.groupby((df['x_days_since_sign_change'] != df['x_days_since_sign_change'].shift()).cumsum()).cumsum()


    x   x_days_since_sign_change
0   1   0
1   -4  -1
2   5   1
3   6   2
4   -2  -1
5   -6  -2
6   1   1
7   4   2
8   6   3
9   -4  -1
10  -9  -2
11  -14 -3
12  -20 -4
13  -21 -5
Vaishali
  • 37,545
  • 5
  • 58
  • 86