CODEWITHSUNDEEP
pythonpandas
daniel blanco
daniel blanco

Reputation: 90

Pandas detecting consecutive negative numbers in a row, and returning values

I have a Dataframe that looks like this, and I need to extract the Quarter where two consecutive negative change values occur. Eg. 2000q4. It has to be the Quarter of the first negative Change, not 2001q1

I also need the last quarter of the first two positive quarters after to negative quarters eg 2002q1

         Quarter    GDP  Change
Quarter                         
2000q1   2000q1  12359.1     NaN
2000q2   2000q2  12592.5   233.4
2000q3   2000q3  12607.7    15.2
2000q4   2000q4  12679.3   -71.6
2001q1   2001q1  12643.3   -36.0
2001q2   2001q2  12710.3    67.0
2001q3   2001q3  12670.1   -40.2
2001q4   2001q4  12705.3    35.2
2002q1   2002q1  12822.3   117.0
2002q2   2002q2  12893.0    70.7
2002q3   2002q3  12955.8    62.8
2002q4   2002q4  12964.0     8.2

Ive tried using apply, and where. Apply did not work because I could not use shift. 

df['bool'] = df['change'].where(df['change'] < 0).where(df['change'].shift() < 0)

The results I am expecting are 2000q4 and 2002q1

Upvotes: 1

Views: 1611

Answers (1)

ansev
ansev

Reputation: 30920

you need to verify the conditions correctly:

cond1=(df['Change'].shift(1)>0) & (df['Change']<0) & (df['Change'].shift(-1)<0)
cond2=(df['Change'].shift(2)<0) & (df['Change'].shift(1)>0) & (df['Change']>0)
cond = cond1 | cond2
df1=df[cond1] #to show the rows that verify the condition1
df2=df[cond2] #to show the rows that verify the condition2

Now if you want to save in a df['bool'] the values ​​that verify the coding use:

df['bool']=df.loc[cond,'Change']

Also you can use where:

df['bool'] = df['Change'].where(cond)

Output df1,df2:

df1

         Quarter.1      GDP  Change
Quarter                           
2000q4     2000q4  12679.3   -71.6

 df2

        Quarter.1      GDP  Change  
Quarter                                               
2002q1     2002q1  12822.3   117.0

Output df:

        Quarter.1      GDP  Change   bool
Quarter                                  
2000q1     2000q1  12359.1     NaN    NaN
2000q2     2000q2  12592.5   233.4    NaN
2000q3     2000q3  12607.7    15.2    NaN
2000q4     2000q4  12679.3   -71.6  -71.6
2001q1     2001q1  12643.3   -36.0    NaN
2001q2     2001q2  12710.3    67.0    NaN
2001q3     2001q3  12670.1   -40.2    NaN
2001q4     2001q4  12705.3    35.2    NaN
2002q1     2002q1  12822.3   117.0  117.0
2002q2     2002q2  12893.0    70.7    NaN
2002q3     2002q3  12955.8    62.8    NaN
2002q4     2002q4  12964.0     8.2    NaN

df[cond] 

        Quarter.1      GDP  Change   bool
Quarter                                  
2000q4     2000q4  12679.3   -71.6  -71.6
2002q1     2002q1  12822.3   117.0  117.0

Upvotes: 2

Related Questions