How do I add/calculate a 'Balance' row?

I have a transaction download from my bank, e.g.

Date, Amount

Unfortunately, the CSV download does not contain a starting balance, so I've added the initial value at the top of the DataFrame. So now the data looks like:

Date, Amount, Balance

2018-01-01, 0, 10

2018-01-01, 10, 20

2018-01-02, 20, 40

2018-01-02, -10, 30

2018-01-03, 20, 50

2018-01-31, 0, 50

Balance is computed, by adding previous balance amount to current amount.

This is what I could muster, and it smells bad:

df = pd.read_csv("~/Downloads/Chequing.CSV", parse_dates=[0], na_values="n/a")



df['Date'] = pd.to_datetime(df['Date'])

df['Balance'] = 0



df1 = pd.DataFrame(data={'Date': ['2018-01-01'], 'Transaction': 

['CREDIT'], 'Name': ['Open'], 'Memo': ['Open'], 'Amount': [0], "Balance": [10.00]})

df1['Date'] = pd.to_datetime(df1['Date'])



df2 = pd.concat([df1, df], sort=False, ignore_index=True)



for i in range(1, len(df2)):

    prev_balance = df2['Balance'].iloc[i-1]

    amount = df2['Amount'].iloc[i]

    new_balance = round(amount + prev_balance, 2)

    df2['Balance'].iloc[i] = new_balance

    # Above generates a warning: 

    # SettingWithCopyWarning: 

    # A value is trying to be set on a copy of a slice from a DataFrame



# While writing this, I was able to get it working by replacing the for loop above with:

df2['Balance'] = round((df2["Amount"] + df2["Balance"]).cumsum(), 2)



pd.set_option('display.max_columns', None)



print(df2.groupby(df['Date'].dt.strftime('%m %B'))['Date', 'Amount', 'Transaction', 'Name', 'Balance'].max())

My question now becomes, is round-ing necessary? Can this be optimized or written in a better way?

Thank you!

asked Jan 1 at 9:25

farhany

4081718

2

Your cumsum() approach is suitable for this question. Since your amount and balance data have only non-decimal values, then no need for rounding

– meW
Jan 1 at 9:32

add a comment |

I have a transaction download from my bank, e.g.

Date, Amount

Unfortunately, the CSV download does not contain a starting balance, so I've added the initial value at the top of the DataFrame. So now the data looks like:

Date, Amount, Balance

2018-01-01, 0, 10

2018-01-01, 10, 20

2018-01-02, 20, 40

2018-01-02, -10, 30

2018-01-03, 20, 50

2018-01-31, 0, 50

Balance is computed, by adding previous balance amount to current amount.

This is what I could muster, and it smells bad:

df = pd.read_csv("~/Downloads/Chequing.CSV", parse_dates=[0], na_values="n/a")



df['Date'] = pd.to_datetime(df['Date'])

df['Balance'] = 0



df1 = pd.DataFrame(data={'Date': ['2018-01-01'], 'Transaction': 

['CREDIT'], 'Name': ['Open'], 'Memo': ['Open'], 'Amount': [0], "Balance": [10.00]})

df1['Date'] = pd.to_datetime(df1['Date'])



df2 = pd.concat([df1, df], sort=False, ignore_index=True)



for i in range(1, len(df2)):

    prev_balance = df2['Balance'].iloc[i-1]

    amount = df2['Amount'].iloc[i]

    new_balance = round(amount + prev_balance, 2)

    df2['Balance'].iloc[i] = new_balance

    # Above generates a warning: 

    # SettingWithCopyWarning: 

    # A value is trying to be set on a copy of a slice from a DataFrame



# While writing this, I was able to get it working by replacing the for loop above with:

df2['Balance'] = round((df2["Amount"] + df2["Balance"]).cumsum(), 2)



pd.set_option('display.max_columns', None)



print(df2.groupby(df['Date'].dt.strftime('%m %B'))['Date', 'Amount', 'Transaction', 'Name', 'Balance'].max())

My question now becomes, is round-ing necessary? Can this be optimized or written in a better way?

Thank you!

asked Jan 1 at 9:25

farhany

4081718

2

Your cumsum() approach is suitable for this question. Since your amount and balance data have only non-decimal values, then no need for rounding

– meW
Jan 1 at 9:32

add a comment |

I have a transaction download from my bank, e.g.

Date, Amount

Unfortunately, the CSV download does not contain a starting balance, so I've added the initial value at the top of the DataFrame. So now the data looks like:

Date, Amount, Balance

2018-01-01, 0, 10

2018-01-01, 10, 20

2018-01-02, 20, 40

2018-01-02, -10, 30

2018-01-03, 20, 50

2018-01-31, 0, 50

Balance is computed, by adding previous balance amount to current amount.

This is what I could muster, and it smells bad:

df = pd.read_csv("~/Downloads/Chequing.CSV", parse_dates=[0], na_values="n/a")



df['Date'] = pd.to_datetime(df['Date'])

df['Balance'] = 0



df1 = pd.DataFrame(data={'Date': ['2018-01-01'], 'Transaction': 

['CREDIT'], 'Name': ['Open'], 'Memo': ['Open'], 'Amount': [0], "Balance": [10.00]})

df1['Date'] = pd.to_datetime(df1['Date'])



df2 = pd.concat([df1, df], sort=False, ignore_index=True)



for i in range(1, len(df2)):

    prev_balance = df2['Balance'].iloc[i-1]

    amount = df2['Amount'].iloc[i]

    new_balance = round(amount + prev_balance, 2)

    df2['Balance'].iloc[i] = new_balance

    # Above generates a warning: 

    # SettingWithCopyWarning: 

    # A value is trying to be set on a copy of a slice from a DataFrame



# While writing this, I was able to get it working by replacing the for loop above with:

df2['Balance'] = round((df2["Amount"] + df2["Balance"]).cumsum(), 2)



pd.set_option('display.max_columns', None)



print(df2.groupby(df['Date'].dt.strftime('%m %B'))['Date', 'Amount', 'Transaction', 'Name', 'Balance'].max())

My question now becomes, is round-ing necessary? Can this be optimized or written in a better way?

Thank you!

asked Jan 1 at 9:25

farhany

4081718

I have a transaction download from my bank, e.g.

Date, Amount

Unfortunately, the CSV download does not contain a starting balance, so I've added the initial value at the top of the DataFrame. So now the data looks like:

Date, Amount, Balance

2018-01-01, 0, 10

2018-01-01, 10, 20

2018-01-02, 20, 40

2018-01-02, -10, 30

2018-01-03, 20, 50

2018-01-31, 0, 50

Balance is computed, by adding previous balance amount to current amount.

This is what I could muster, and it smells bad:

df = pd.read_csv("~/Downloads/Chequing.CSV", parse_dates=[0], na_values="n/a")



df['Date'] = pd.to_datetime(df['Date'])

df['Balance'] = 0



df1 = pd.DataFrame(data={'Date': ['2018-01-01'], 'Transaction': 

['CREDIT'], 'Name': ['Open'], 'Memo': ['Open'], 'Amount': [0], "Balance": [10.00]})

df1['Date'] = pd.to_datetime(df1['Date'])



df2 = pd.concat([df1, df], sort=False, ignore_index=True)



for i in range(1, len(df2)):

    prev_balance = df2['Balance'].iloc[i-1]

    amount = df2['Amount'].iloc[i]

    new_balance = round(amount + prev_balance, 2)

    df2['Balance'].iloc[i] = new_balance

    # Above generates a warning: 

    # SettingWithCopyWarning: 

    # A value is trying to be set on a copy of a slice from a DataFrame



# While writing this, I was able to get it working by replacing the for loop above with:

df2['Balance'] = round((df2["Amount"] + df2["Balance"]).cumsum(), 2)



pd.set_option('display.max_columns', None)



print(df2.groupby(df['Date'].dt.strftime('%m %B'))['Date', 'Amount', 'Transaction', 'Name', 'Balance'].max())

My question now becomes, is round-ing necessary? Can this be optimized or written in a better way?

Thank you!

python pandas

asked Jan 1 at 9:25

farhany

4081718

asked Jan 1 at 9:25

farhany

4081718

asked Jan 1 at 9:25

farhany

4081718

asked Jan 1 at 9:25

farhany

4081718

asked Jan 1 at 9:25

farhany

4081718

2

Your cumsum() approach is suitable for this question. Since your amount and balance data have only non-decimal values, then no need for rounding

– meW
Jan 1 at 9:32

add a comment |

2

Your cumsum() approach is suitable for this question. Since your amount and balance data have only non-decimal values, then no need for rounding

– meW
Jan 1 at 9:32

Your cumsum() approach is suitable for this question. Since your amount and balance data have only non-decimal values, then no need for rounding

– meW
Jan 1 at 9:32

add a comment |

1 Answer
1

active

oldest

votes

Thanks to @meW I would not have thought of cumsum()

Here's what I could do

%%time

df.Balance = np.concatenate((df.Balance[:1], (df.Balance.shift().fillna(0)+df.Amount).cumsum()[1:]))



#Wall time: 2 ms

Comparing to for loop method

%%time

for i in range(1,len(df.Balance)):

    df.Balance[i] = df.Balance[i-1]+df.Amount[i]



# Wall time: 173 ms

Month-wise maximum balance

df



          Date   Amount    Balance

0    2018-01-01       0         10

1    2018-01-01      10         20

2    2018-01-02      20         40

3    2018-02-02     -10         30

4    2018-03-03      20         50

5    2018-03-31      10         60





df.groupby(df.Date.dt.month).apply(lambda x: x[x.Balance == x.Balance.max()]).reset_index(drop=True)



          Date  Amount   Balance

0   2018-01-02      20        40

1   2018-02-02     -10        30

2   2018-03-31      10        60

I hope this helped. Comments are welcome ;)

edited Jan 8 at 17:52

answered Jan 1 at 14:20

Ananay Mital

635512

That's great! I used the one liner I came up with in the original question while writing the question. Though I think your way seems a bit more clear. The problem I am having now is how to group this so that the date (entire date, not just the month) when the balance amount reaches max value in a month is also displayed. I asked a similar question earlier, but at that time I had simple Amounts (no Balance calculated), link. Specific groupby is: df2.groupby(df2['Date'].dt.strftime('%m %B'))['Date', 'Name', 'Balance'].max(), but it doesn't display the date correctly?

– farhany
Jan 2 at 6:50

1

I was not sure what exactly you wanted but was this it i.stack.imgur.com/eS6kO.png

– Ananay Mital
Jan 2 at 8:11

I think that's it! Thanks so much for you help. If you want to add it to the answer, I can mark it as accepted.

– farhany
Jan 8 at 17:27

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53994331%2fhow-do-i-add-calculate-a-balance-row%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

Thanks to @meW I would not have thought of cumsum()

Here's what I could do

%%time

df.Balance = np.concatenate((df.Balance[:1], (df.Balance.shift().fillna(0)+df.Amount).cumsum()[1:]))



#Wall time: 2 ms

Comparing to for loop method

%%time

for i in range(1,len(df.Balance)):

    df.Balance[i] = df.Balance[i-1]+df.Amount[i]



# Wall time: 173 ms

Month-wise maximum balance

df



          Date   Amount    Balance

0    2018-01-01       0         10

1    2018-01-01      10         20

2    2018-01-02      20         40

3    2018-02-02     -10         30

4    2018-03-03      20         50

5    2018-03-31      10         60





df.groupby(df.Date.dt.month).apply(lambda x: x[x.Balance == x.Balance.max()]).reset_index(drop=True)



          Date  Amount   Balance

0   2018-01-02      20        40

1   2018-02-02     -10        30

2   2018-03-31      10        60

I hope this helped. Comments are welcome ;)

edited Jan 8 at 17:52

answered Jan 1 at 14:20

Ananay Mital

635512

That's great! I used the one liner I came up with in the original question while writing the question. Though I think your way seems a bit more clear. The problem I am having now is how to group this so that the date (entire date, not just the month) when the balance amount reaches max value in a month is also displayed. I asked a similar question earlier, but at that time I had simple Amounts (no Balance calculated), link. Specific groupby is: df2.groupby(df2['Date'].dt.strftime('%m %B'))['Date', 'Name', 'Balance'].max(), but it doesn't display the date correctly?

– farhany
Jan 2 at 6:50

1

I was not sure what exactly you wanted but was this it i.stack.imgur.com/eS6kO.png

– Ananay Mital
Jan 2 at 8:11

I think that's it! Thanks so much for you help. If you want to add it to the answer, I can mark it as accepted.

– farhany
Jan 8 at 17:27

add a comment |

Thanks to @meW I would not have thought of cumsum()

Here's what I could do

%%time

df.Balance = np.concatenate((df.Balance[:1], (df.Balance.shift().fillna(0)+df.Amount).cumsum()[1:]))



#Wall time: 2 ms

Comparing to for loop method

%%time

for i in range(1,len(df.Balance)):

    df.Balance[i] = df.Balance[i-1]+df.Amount[i]



# Wall time: 173 ms

Month-wise maximum balance

df



          Date   Amount    Balance

0    2018-01-01       0         10

1    2018-01-01      10         20

2    2018-01-02      20         40

3    2018-02-02     -10         30

4    2018-03-03      20         50

5    2018-03-31      10         60





df.groupby(df.Date.dt.month).apply(lambda x: x[x.Balance == x.Balance.max()]).reset_index(drop=True)



          Date  Amount   Balance

0   2018-01-02      20        40

1   2018-02-02     -10        30

2   2018-03-31      10        60

I hope this helped. Comments are welcome ;)

edited Jan 8 at 17:52

answered Jan 1 at 14:20

Ananay Mital

635512

That's great! I used the one liner I came up with in the original question while writing the question. Though I think your way seems a bit more clear. The problem I am having now is how to group this so that the date (entire date, not just the month) when the balance amount reaches max value in a month is also displayed. I asked a similar question earlier, but at that time I had simple Amounts (no Balance calculated), link. Specific groupby is: df2.groupby(df2['Date'].dt.strftime('%m %B'))['Date', 'Name', 'Balance'].max(), but it doesn't display the date correctly?

– farhany
Jan 2 at 6:50

1

I was not sure what exactly you wanted but was this it i.stack.imgur.com/eS6kO.png

– Ananay Mital
Jan 2 at 8:11

I think that's it! Thanks so much for you help. If you want to add it to the answer, I can mark it as accepted.

– farhany
Jan 8 at 17:27

add a comment |

Thanks to @meW I would not have thought of cumsum()

Here's what I could do

%%time

df.Balance = np.concatenate((df.Balance[:1], (df.Balance.shift().fillna(0)+df.Amount).cumsum()[1:]))



#Wall time: 2 ms

Comparing to for loop method

%%time

for i in range(1,len(df.Balance)):

    df.Balance[i] = df.Balance[i-1]+df.Amount[i]



# Wall time: 173 ms

Month-wise maximum balance

df



          Date   Amount    Balance

0    2018-01-01       0         10

1    2018-01-01      10         20

2    2018-01-02      20         40

3    2018-02-02     -10         30

4    2018-03-03      20         50

5    2018-03-31      10         60





df.groupby(df.Date.dt.month).apply(lambda x: x[x.Balance == x.Balance.max()]).reset_index(drop=True)



          Date  Amount   Balance

0   2018-01-02      20        40

1   2018-02-02     -10        30

2   2018-03-31      10        60

I hope this helped. Comments are welcome ;)

edited Jan 8 at 17:52

answered Jan 1 at 14:20

Ananay Mital

635512

Thanks to @meW I would not have thought of cumsum()

Here's what I could do

%%time

df.Balance = np.concatenate((df.Balance[:1], (df.Balance.shift().fillna(0)+df.Amount).cumsum()[1:]))



#Wall time: 2 ms

Comparing to for loop method

%%time

for i in range(1,len(df.Balance)):

    df.Balance[i] = df.Balance[i-1]+df.Amount[i]



# Wall time: 173 ms

Month-wise maximum balance

df



          Date   Amount    Balance

0    2018-01-01       0         10

1    2018-01-01      10         20

2    2018-01-02      20         40

3    2018-02-02     -10         30

4    2018-03-03      20         50

5    2018-03-31      10         60





df.groupby(df.Date.dt.month).apply(lambda x: x[x.Balance == x.Balance.max()]).reset_index(drop=True)



          Date  Amount   Balance

0   2018-01-02      20        40

1   2018-02-02     -10        30

2   2018-03-31      10        60

I hope this helped. Comments are welcome ;)

edited Jan 8 at 17:52

answered Jan 1 at 14:20

Ananay Mital

635512

edited Jan 8 at 17:52

answered Jan 1 at 14:20

Ananay Mital

635512

answered Jan 1 at 14:20

Ananay Mital

635512

answered Jan 1 at 14:20

Ananay Mital

635512

That's great! I used the one liner I came up with in the original question while writing the question. Though I think your way seems a bit more clear. The problem I am having now is how to group this so that the date (entire date, not just the month) when the balance amount reaches max value in a month is also displayed. I asked a similar question earlier, but at that time I had simple Amounts (no Balance calculated), link. Specific groupby is: df2.groupby(df2['Date'].dt.strftime('%m %B'))['Date', 'Name', 'Balance'].max(), but it doesn't display the date correctly?

– farhany
Jan 2 at 6:50

1

I was not sure what exactly you wanted but was this it i.stack.imgur.com/eS6kO.png

– Ananay Mital
Jan 2 at 8:11

I think that's it! Thanks so much for you help. If you want to add it to the answer, I can mark it as accepted.

– farhany
Jan 8 at 17:27

add a comment |

That's great! I used the one liner I came up with in the original question while writing the question. Though I think your way seems a bit more clear. The problem I am having now is how to group this so that the date (entire date, not just the month) when the balance amount reaches max value in a month is also displayed. I asked a similar question earlier, but at that time I had simple Amounts (no Balance calculated), link. Specific groupby is: df2.groupby(df2['Date'].dt.strftime('%m %B'))['Date', 'Name', 'Balance'].max(), but it doesn't display the date correctly?

– farhany
Jan 2 at 6:50

1

I was not sure what exactly you wanted but was this it i.stack.imgur.com/eS6kO.png

– Ananay Mital
Jan 2 at 8:11

I think that's it! Thanks so much for you help. If you want to add it to the answer, I can mark it as accepted.

– farhany
Jan 8 at 17:27

That's great! I used the one liner I came up with in the original question while writing the question. Though I think your way seems a bit more clear. The problem I am having now is how to group this so that the date (entire date, not just the month) when the balance amount reaches max value in a month is also displayed. I asked a similar question earlier, but at that time I had simple Amounts (no Balance calculated), link. Specific groupby is: df2.groupby(df2['Date'].dt.strftime('%m %B'))['Date', 'Name', 'Balance'].max(), but it doesn't display the date correctly?

– farhany
Jan 2 at 6:50

I was not sure what exactly you wanted but was this it i.stack.imgur.com/eS6kO.png

– Ananay Mital
Jan 2 at 8:11

I think that's it! Thanks so much for you help. If you want to add it to the answer, I can mark it as accepted.

– farhany
Jan 8 at 17:27

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

Search This Blog

Ufyukyu