How to create new column and insert row values while iterating through pandas data frame

Question

I am trying to create a function that iterates through a pandas dataframe row by row. I want to create a new column based on row values of other columns. My original dataframe could look like this:

Now I want to create a new column filled with the row values of Column A - Column B at each index position, so that the result looks like this:

 df:

       A   B   A-B
    0  1   2   -1
    1  3   4   -1
    2  2   2    0

the solution I have works, but only when I do NOT use it in a function:

for index, row in df.iterrows():
        print index
        df['A-B']=df['A']-df['B']

This gives me the desired output, but when I try to use it as a function, I get an error.

def test(x):
    for index, row in df.iterrows():
        print index
        df['A-B']=df['A']-df['B']
    return df
df.apply(test)

ValueError: cannot copy sequence with size 4 to array axis with dimension 3

What am I doing wrong here and how can I get it to work?

do you need only substract column from each other or it's just a simple example? — Anton Protopopov, Dec 07 '15 at 08:31
I actually really need just a new column containing the results of column A minus column B — sequence_hard, Dec 07 '15 at 08:36

score 14 · Accepted Answer · edited Jun 20 '20 at 09:12

14

It's because apply method works for column by default, change axis to 1 if you'd like through rows:

axis : {0 or ‘index’, 1 or ‘columns’}, default 0

0 or ‘index’: apply function to each column

1 or ‘columns’: apply function to each row

df.apply(test, axis=1)

EDIT

I thought that you need to do something complex manupulation with each row. If you need just substract columns from each other:

df['A-B'] = df.A - df.B

edited Jun 20 '20 at 09:12

Community

1
1

answered Dec 07 '15 at 08:14

Anton Protopopov

30,354
12
88
93

That makes sense, thank you. I still get the same error, though it seems to work anyway... – sequence_hard Dec 07 '15 at 08:26
for me it works both ways with `axis=1` an `axis=0` what `pandas` version are you using? – Anton Protopopov Dec 07 '15 at 08:30
I am using version 0.17.0 – sequence_hard Dec 07 '15 at 08:35
1

@AntonProtopopov could this approach be used for constants as well? for example `my_df['Division by constant c'] = my_df.column_A / 4` ? – 3kstc Mar 07 '18 at 23:18

score 3 · Answer 2 · edited May 23 '17 at 12:31

3

Like indicated by Anton you should execute the apply function with axis=1 parameter. However it is not necessary to then loop through the rows as you did in the function test, since the apply documentation mentions:

Objects passed to functions are Series objects

So you could simplify the function to:

def test(x):
    x['A-B']=x['A']-x['B']
    return x

and then run:

df.apply(test,axis=1)

Note that in fact you named the parameter of test x, while not using x in the function test at all.

Finally I should comment that you can do column wise operations with pandas (i.e. without for loop) doing simply this:

df['A-B']=df['A']-df['B']

Also see:

edited May 23 '17 at 12:31

Community

1
1

answered Dec 07 '15 at 08:27

agold

6,140
9
38
54

Thank you, I did not know that the creation of ne new column was possible without the for-loop! – sequence_hard Dec 07 '15 at 08:39
Actually this is a new question. But, already asked before: http://stackoverflow.com/questions/18472634/how-to-compute-a-new-column-based-on-the-values-of-other-columns-in-pandas-pyt – agold Dec 07 '15 at 09:45
`df['A-B']=df['A']-df['B']` does not work if you want to pass anything from the right through an external function. For example `df['A-B'] = my_function(df['A'])` will not work. Instead you have to use this syntax: `df['A-B'] = df.apply(lambda x: my_function(x.A), axis=1)`, as described in https://stackoverflow.com/a/52854800/6793507 – SomJura May 03 '21 at 13:03

How to create new column and insert row values while iterating through pandas data frame

2 Answers2