How to add values from two different columns of pandas dataframe

Question

How to add first rows from two different columns of a dataframe together, such that if column A's first row is NaN, replace with value from balance's first row, otherwise, add column A's first row and balance's first row. And so likewise column B. The idea is using balnce first row to transform first row of other columns' A and B. I attempted using df.iloc[0] to get the first row but I'm unable to set the value or add it:

data = {'id': [1, 2, 3, 4, 5, 6],
        'A': [None, None, 20, 10, 39, 30],
        'B': [13, 98, 23, 45, 64, 10],
        'balance': [23, 41, 12, 22, 32, 0]}

df = pd.DataFrame(data)
df = df.set_index('id')
print df

    A   B    balance
id                 
1  NaN  13   23
2  NaN  98   41
3   20  23   12
4   10  45   22
5   39  64   32
6   30  10   0

for i in df.columns:
    if i not in ['balance']:
        if df[i].iloc[0] == None:
            df[i].iloc[0] = df['balance'].iloc[0]
        else:
            df[i].iloc[0] = df[i].iloc[0] + df['balance'].iloc[0]

        print df[i]


id
1   NaN
2   NaN
3    20
4    10
5    39
6    30
Name: A, dtype: float64


id
1    36
2    98
3    23
4    45
5    64
6    10
Name: B, dtype: int64
#A[0] should be 23, and B[0] should be 13 + 23 = 36

desired output:

id  A   B     balance               
1   23  36       23
2  NaN  98       41
3   20  23       12
4   10  45       22
5   39  64       32
6   30  10        0

Your prose explanation doesn't match your sample code. You are setting A to balance when A is empty. — chrisaycock
– chrisaycock, Commented Jul 6, 2016 at 17:38
@jezrael my bad - I made corrections to the post. The idea is to use balance to transform the first rows of columns A and B. If the columns are NaN, replace both A and B with balance. If otherwise: balance + A, balance + B`. This is only for the first rows. — DougKruger
– DougKruger, Commented Jul 6, 2016 at 19:29

jezrael · Accepted Answer · 2016-07-06 20:11:30Z

3

IIUC you need combine_first or fillna if need replace NaN by values of other column:

print (df.A.combine_first(df.B))
id
1    13.0
2    98.0
3    20.0
4    10.0
5    39.0
6    30.0
Name: A, dtype: float64

Or:

print (df.A.fillna(df.B))
id
1    13.0
2    98.0
3    20.0
4    10.0
5    39.0
6    30.0
Name: A, dtype: float64


print (df.A.combine_first(df.B) + df.B)
id
1     26.0
2    196.0
3     43.0
4     55.0
5    103.0
6     40.0
dtype: float64

If need sum two columns with replacing NaN to 0 use add with parameter fill_value:

print (df.A.add(df.B, fill_value=0))
id
1     13.0
2     98.0
3     43.0
4     55.0
5    103.0
6     40.0
dtype: float64

EDIT:

You need:

df.ix[1,'A'] = df.ix[1,'balance']
print (df)
       A   B  balance
id                   
1   23.0  13       23
2    NaN  98       41
3   20.0  23       12
4   10.0  45       22
5   39.0  64       32
6   30.0  10        0

EDIT1:

df.ix[1,'A'] = df.ix[1,'balance']
df.ix[1,'B'] = df.ix[1,'B'] + df.ix[1,'balance']

print (df)
       A   B  balance
id                   
1   23.0  36       23
2    NaN  98       41
3   20.0  23       12
4   10.0  45       22
5   39.0  64       32
6   30.0  10        0

edited Jul 6, 2016 at 20:11

answered Jul 6, 2016 at 17:39

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

7 Comments

chrisaycock Over a year ago

OP states that "A[0] should be 23".

jezrael Over a year ago

My solution is only guessing, if output is bad or all is bad, I remove it.

Grr Over a year ago

OP's wording of question is poor. Assuming the bottom comment in their code "A[0] should be 23, and B[0] should be 13 + 23 = 36" correctly describes what they want, I think using df.A.fillna(0) would provide the desired answer. Question needs to be better articulated.

jezrael Over a year ago

@Grr - thank you for comment - I am not sure what OP exactly want too.

DougKruger Over a year ago

@jezrael, only teh first row gets transformed. I included the desired output

|

Collectives™ on Stack Overflow

How to add values from two different columns of pandas dataframe

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related