CODEWITHSUNDEEP
python
Ben21
Ben21

Reputation: 93

Sum Two columns If Other Columns Are Equal

I'm trying to use pandas to add columns Amount and Amount2 only if Account is in Account2.

input

 Account      Amount    Account2     Amount2
0   0001957331   409.50  0404618555    26.31
1   0404618555  1535.40  0490812351     5.31
2   0490812351   338.12  0521656066   787.94
3   0696978386    13.11  0696978386   700.55

expected output:

0001957331   409.50
0404618555  1561.71
0490812351   343.43
0696978386   713.66

my code:

    df = pd.DataFrame(allc_acct_amt, columns=["Account", "Amount", "Account2", "Amount2"])
    print(df)
    dff = df.groupby(["Account","Account2"])[['Amount', 'Amount2']].sum().reset_index()
    print(dff)

Upvotes: 0

Views: 41

Answers (2)

Corralien
Corralien

Reputation: 120429

Use merge:

out = pd.merge(df[['Account', 'Amount']], df[['Account2', 'Amount2']], 
               left_on='Account', right_on='Account2', how='left')

out = out.assign(Amount=out[['Amount', 'Amount2']].sum(1)) \
         .drop(columns={'Account2', 'Amount2'})

Output:

>>> out
      Account   Amount
0  0001957331   409.50
1  0404618555  1561.71
2  0490812351   343.43
3  0696978386   713.66

Setup to be reproducible:

data = {'Account': ['0001957331', '0404618555', '0490812351', '0696978386'],
        'Amount': [409.5, 1535.4, 338.12, 13.11],
        'Account2': ['0404618555', '0490812351', '0521656066', '0696978386'],
        'Amount2': [26.31, 5.31, 787.94, 700.55]}
df = pd.DataFrame(data)

Upvotes: 1

SuperPineapple
SuperPineapple

Reputation: 31

mask = df["Account"] == df["Account2"]
df["total_amount"] = df["Amount"] + df["Amount2"][mask]

Upvotes: 1

Related Questions