Problems with a for loop/ if statement in a pandas DataFrame

Question

my code is as follows:

having given DataFrame

df.head(3)

           1. open  2. high  3. low 4. close  5. volume
date                    
2021-02-18  241.80  243.93  240.86  243.79  16925563.0
2021-02-17  241.32  244.31  240.94  244.20  21451617.0
2021-02-16  245.03  246.13  242.92  243.70  26728487.0

I added a new column comparing "4.close" with previous day "4.close". That worked.

for i in df["4. close"]:
    df["changeAbsolute"] = df["4. close"] - df["4. close"].shift(-1)

df.head(3)

1. open     2. high     3. low  4. close    5. volume   changeAbsolute
date                        
2021-02-18  241.8000    243.93  240.86  243.79  16925563.0  -0.41
2021-02-17  241.3200    244.31  240.94  244.20  21451617.0  0.50
2021-02-16  245.0300    246.13  242.92  243.70  26728487.0  -1.29

Now comes my problem:

When adding another column that shows "+" if df["changeAbsolute"] is positiv, "-" if its negative, and "unch" if 0

for i in df["changeAbsolute"]:
    if i < 0:
        df["Change"] = "-"
    elif i > 0:
        df["Change"] = "+"
    else:
        df["Change"] = "unch"

df.head(3)

            1. open     2. high 3. low  4. close 5. volume  changeAbsolute      Change
date                            
2021-02-18  241.8000    243.93  240.86  243.79  16925563.0     -0.41             unch
2021-02-17  241.3200    244.31  240.94  244.20  21451617.0      0.50             unch
2021-02-16  245.0300    246.13  242.92  243.70  26728487.0     -1.29             unch

I was expecting:

            1. open     2. high 3. low  4. close 5. volume  changeAbsolute      Change
date                            
2021-02-18  241.8000    243.93  240.86  243.79  16925563.0     -0.41             -
2021-02-17  241.3200    244.31  240.94  244.20  21451617.0      0.50             +
2021-02-16  245.0300    246.13  242.92  243.70  26728487.0     -1.29             -

I changed the order in the if/elif/else statement. It didnt help. I got back only the else. Can anyone help?

Ch3steR · Accepted Answer · 2021-02-19 15:36:36Z

1

You can use np.sign then use Series.map here.

df['Change'] = np.sign(df['changeAbsolute']).map({1.0:'+', -1.0:'-', 0:'unch'})

or, using np.select

condList = [df['changeAbsolute']>0, df['changeAbsolute']<0]
choiceList = ['+', '-']
df['change'] = np.select(condList, choiceList, 'unch')

And Change this

for i in df["4. close"]:
    df["changeAbsolute"] = df["4. close"] - df["4. close"].shift(-1)

to

df["changeAbsolute"] = df["4. close"] - df["4. close"].shift(-1)

To make it more concise, you can Series.diff which does the same

df['changeAbsolute'] = df['4. close'].diff(-1)

Why your code gives wrong outputs is take a look at this example

df["Change"] = "-"

This assigns '-' to every value in the column.

If you want to use anyway then try this:

df.iloc[idx, 'change'] = '-' # idx is the index of the row

edited Feb 19, 2021 at 15:36

answered Feb 19, 2021 at 15:19

Ch3steR

20.8k4 gold badges34 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Hank Gordon Over a year ago

Thanks Chester, I will go through it to understand. I appreciate your answer.

Hank Gordon Over a year ago

Chester I tried np.sign It worked but I get a RuntimeWarning: invalid value encountered in sign result = getattr(ufunc, method)(*inputs, **kwargs) I think its a Nan I have in df["changeAbsolute"] (causing by the .shift).

Serge Ballesta · Accepted Answer · 2021-02-19 15:24:18Z

1

In Pandas you can process whole columns at a time, which is called vectorization.

To compute df['changeAbsolute'], one single action is required:

df["changeAbsolute"] = df["4. close"] - df["4. close"].shift(-1)

Then for the last column you could use np.where:

df['Change'] = np.where(df.changeAbsolute > 0, '+', np.where(
    df.changeAbsolute < 0, '-', 'unch'))

answered Feb 19, 2021 at 15:24

Serge Ballesta

150k13 gold badges137 silver badges267 bronze badges

3 Comments

Ch3steR Over a year ago

Cool but instead of two np.where, using np.selectseems better imo.

Hank Gordon Over a year ago

Serge, thanks for the np.where. And thanks for the additional advice on the df["changeAbsolute"]!

Hank Gordon Over a year ago

Chester, I will try this too.

Collectives™ on Stack Overflow

Problems with a for loop/ if statement in a pandas DataFrame

having given DataFrame

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

having given DataFrame

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related