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

user_14065969 0
Viewed 50

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?

pandas dataframe python
2 Answers

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
user_124164530

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'))
user_35452730
Related