Best way to net values in successive rows of CSV

Question

Looking for advice on the best way to perform the following operation. Preferably in python, javascript, or excel. Data is in CSV (although I removed the commas below). I'm a noob; I should be able to do it, but I'm thinking there's an elegant way to do something like this since it is such a basic operation.

Direction    Name       Trial    Amount
        +    Matthew        1       500
        -    Matthew        1       250
        -    Matthew        1       250
        +    Matthew        2      1000
        -    Matthew        2       750
        +    Matthew        3       500
        +    Mark           1       500
        -    Mark           1       250
        +    Luke           1      1000
        +    Luke           2      1000
        +    Luke           3      1000
        -    Luke           2       500

Net each amount by name and trial (large number of trials in dataset).

Result would be:

Direction    Name       Trial    Amount
        +    Matthew        2       250
        +    Matthew        3       500
        +    Mark           1       250
        +    Luke           1      1000
        +    Luke           2       500
        +    Luke           3      1000

Thanks in advance!

You should show us what you've attempted to do to solve this problem. — Will Da Silva, May 30 '17 at 18:21

score 0 · Answer 1 · answered May 30 '17 at 18:23

Here's one way to do in Python.

import pandas as pd
import numpy as np

df['vals'] = df.apply(lambda x: x['Amount'] if x['Direction'] == '+' else x['Amount']*-1, axis=1)

pd.pivot_table(df, values='vals', columns=['Name','Trial'], aggfunc=np.sum)

Name     Trial
Luke     1        1000
         2         500
         3        1000
Mark     1         250
Matthew  1           0
         2         250
         3         500
dtype: int64

Best way to net values in successive rows of CSV

1 Answers1