CODEWITHSUNDEEP
pythonpandas
MartinHN
MartinHN

Reputation: 19772

Easy way to split column into two in Pandas

I have a DataFrame in Pandas that looks like this:

transactions_df = pd.DataFrame({'Date': ['2019-08-01', '2019-09-01', '2019-10-01'], 'Amount': [150, -25, 200]})
transactions_df.head()

Date        Amount
2019-08-01  150
2019-09-01  -25
2019-10-01  200

I want to split the amount column into Invoiced and Reimbursed, if the value is positive or negative, respectively.

I am currently doing it like this:

def split_trans_amount(row):
    invoiced = row['Amount'] if row['Amount'] > 0 else 0
    reimbursed = row['Amount'] if row['Amount'] < 0 else 0

    return pd.Series([invoiced, reimbursed], index=['Invoiced', 'Reimbursed'])

transactions_in_out_df = transactions_df.apply(lambda x: split_trans_amount(x), axis=1)
transactions_split_df = pd.concat([transactions_df, transactions_in_out_df], axis=1)
transactions_split_df.head()

It gives me what I need:

Date        Amount      Invoiced    Reimbursed
2019-08-01  150         150         0
2019-09-01  -25         0           -25
2019-10-01  200         200         0

But, isn't there an easier way to do this in Pandas?

Upvotes: 2

Views: 48

Answers (2)

Derek Eden
Derek Eden

Reputation: 4618

transactions_df['Invoiced'] = transactions_df['Amount'].multiply(transactions_df['Amount']>0)
transactions_df['Reimbursed'] = transactions_df['Amount'].multiply(transactions_df['Amount']<0)

also works

         Date  Amount  Invoiced  Reimbursed
0  2019-08-01     150       150           0
1  2019-09-01     -25         0         -25
2  2019-10-01     200       200           0

Upvotes: 1

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476567

You can use np.clip(..) here:

transactions_df['Invoiced'] = transactions_df['Amount'].values.clip(min=0)
transactions_df['Reimbursed'] = transactions_df['Amount'].values.clip(max=0)

This gives us:

>>> transactions_df
         Date  Amount  Invoiced  Reimbursed
0  2019-08-01     150       150           0
1  2019-09-01     -25         0         -25
2  2019-10-01     200       200           0

Upvotes: 1

Related Questions