How to find max Amount per month (for the year)

I'm just starting with Pandas, and Python. I have a CSV dump for the yearly transactions from my bank. Every tax season, I'm required to prepare a report of max values reached during each month (and the specific date), and the max value overall:

Sample data:

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

                        'Transaction': ['CREDIT', 'DEBIT', 'CREDIT'],

                         'Amount': [100.20, -50.00, 200.00]})

I can't figure out how to use pd.to_datetime on an inline DataFrame.

Tried df['Date'].apply(pd.to_datetime) but got an error

ValueError: ('Unknown string format:', 'CREDIT', 'occurred at index
# Transaction')

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

df = pd.DataFrame(data, columns=['Date', 'Transaction', 'Amount'])

df.set_index(['Date'], drop=True, inplace=True)



grouped = df.groupby(pd.Grouper(freq="M"))  # DataFrameGroupBy (grouped by Month)



for g, v in grouped:

   print(g, v.max())

Output:

2018-01-31 00:00:00 Transaction     DEBIT

Amount         100.02

dtype: object

2018-02-28 00:00:00 Transaction    CREDIT

Amount            200

dtype: object

What I would like to see is (some form of):

2018-01-01 00:00:00 Transaction     DEBIT

Amount         100.02

2018-02-01 00:00:00 Transaction    CREDIT

Amount            200

Thanks for any help.

edited Jan 1 at 4:39

AI_Learning

3,53621033

asked Jan 1 at 4:05

farhany

4081718

add a comment |

Sample data:

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

                        'Transaction': ['CREDIT', 'DEBIT', 'CREDIT'],

                         'Amount': [100.20, -50.00, 200.00]})

I can't figure out how to use pd.to_datetime on an inline DataFrame.

Tried df['Date'].apply(pd.to_datetime) but got an error

ValueError: ('Unknown string format:', 'CREDIT', 'occurred at index
# Transaction')

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

df = pd.DataFrame(data, columns=['Date', 'Transaction', 'Amount'])

df.set_index(['Date'], drop=True, inplace=True)



grouped = df.groupby(pd.Grouper(freq="M"))  # DataFrameGroupBy (grouped by Month)



for g, v in grouped:

   print(g, v.max())

Output:

2018-01-31 00:00:00 Transaction     DEBIT

Amount         100.02

dtype: object

2018-02-28 00:00:00 Transaction    CREDIT

Amount            200

dtype: object

What I would like to see is (some form of):

2018-01-01 00:00:00 Transaction     DEBIT

Amount         100.02

2018-02-01 00:00:00 Transaction    CREDIT

Amount            200

Thanks for any help.

edited Jan 1 at 4:39

AI_Learning

3,53621033

asked Jan 1 at 4:05

farhany

4081718

add a comment |

Sample data:

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

                        'Transaction': ['CREDIT', 'DEBIT', 'CREDIT'],

                         'Amount': [100.20, -50.00, 200.00]})

I can't figure out how to use pd.to_datetime on an inline DataFrame.

Tried df['Date'].apply(pd.to_datetime) but got an error

ValueError: ('Unknown string format:', 'CREDIT', 'occurred at index
# Transaction')

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

df = pd.DataFrame(data, columns=['Date', 'Transaction', 'Amount'])

df.set_index(['Date'], drop=True, inplace=True)



grouped = df.groupby(pd.Grouper(freq="M"))  # DataFrameGroupBy (grouped by Month)



for g, v in grouped:

   print(g, v.max())

Output:

2018-01-31 00:00:00 Transaction     DEBIT

Amount         100.02

dtype: object

2018-02-28 00:00:00 Transaction    CREDIT

Amount            200

dtype: object

What I would like to see is (some form of):

2018-01-01 00:00:00 Transaction     DEBIT

Amount         100.02

2018-02-01 00:00:00 Transaction    CREDIT

Amount            200

Thanks for any help.

edited Jan 1 at 4:39

AI_Learning

3,53621033

asked Jan 1 at 4:05

farhany

4081718

Sample data:

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

                        'Transaction': ['CREDIT', 'DEBIT', 'CREDIT'],

                         'Amount': [100.20, -50.00, 200.00]})

I can't figure out how to use pd.to_datetime on an inline DataFrame.

Tried df['Date'].apply(pd.to_datetime) but got an error

ValueError: ('Unknown string format:', 'CREDIT', 'occurred at index
# Transaction')

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

df = pd.DataFrame(data, columns=['Date', 'Transaction', 'Amount'])

df.set_index(['Date'], drop=True, inplace=True)



grouped = df.groupby(pd.Grouper(freq="M"))  # DataFrameGroupBy (grouped by Month)



for g, v in grouped:

   print(g, v.max())

Output:

2018-01-31 00:00:00 Transaction     DEBIT

Amount         100.02

dtype: object

2018-02-28 00:00:00 Transaction    CREDIT

Amount            200

dtype: object

What I would like to see is (some form of):

2018-01-01 00:00:00 Transaction     DEBIT

Amount         100.02

2018-02-01 00:00:00 Transaction    CREDIT

Amount            200

Thanks for any help.

python pandas

edited Jan 1 at 4:39

AI_Learning

3,53621033

asked Jan 1 at 4:05

farhany

4081718

edited Jan 1 at 4:39

AI_Learning

3,53621033

asked Jan 1 at 4:05

farhany

4081718

edited Jan 1 at 4:39

AI_Learning

3,53621033

edited Jan 1 at 4:39

AI_Learning

3,53621033

edited Jan 1 at 4:39

AI_Learning

3,53621033

asked Jan 1 at 4:05

farhany

4081718

asked Jan 1 at 4:05

farhany

4081718

asked Jan 1 at 4:05

farhany

4081718

add a comment |

1 Answer
1

active

oldest

votes

You have the convert the date format and then apply groupBy.
Try this!

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

             'Transaction': ['CREDIT', 'DEBIT', 'CREDIT'],

             'Amount': [100.20, -50.00, 200.00]})

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

print(df.groupby(df['Date'].dt.strftime('%B')).max())



#output:

         Amount       Date Transaction

Date                                  

January   100.2 2018-01-05       DEBIT

May       200.0 2018-05-01      CREDIT

edited Jan 1 at 4:33

answered Jan 1 at 4:18

AI_Learning

3,53621033

Almost, there! How would I add the date, not just the month? E.g. January-2, May-1, etc?

– farhany
Jan 1 at 4:26

just add %d in strftime. please find my updated solution

– AI_Learning
Jan 1 at 4:28

That gives me both entries, but I only wanted the max per month, so this seems to work: print(df.groupby(df['Date'].dt.strftime('%B'))['Date', 'Amount', 'Transaction'].max()) ` Date Amount Transaction` Date February 2018-02-01 200.0 CREDIT January 2018-01-05 100.2 DEBIT

– farhany
Jan 1 at 4:29

you want max date for every month is it?

– AI_Learning
Jan 1 at 4:31

1

Thanks a lot @AI_Learning. I've been at this for over 4 hours. I get it now... Why Python is used in Data Sciences. You, sir. Rock. And have the greatest New Year!

– farhany
Jan 1 at 4:36

|
show 2 more comments

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%2f53992939%2fhow-to-find-max-amount-per-month-for-the-year%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

You have the convert the date format and then apply groupBy.
Try this!

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

             'Transaction': ['CREDIT', 'DEBIT', 'CREDIT'],

             'Amount': [100.20, -50.00, 200.00]})

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

print(df.groupby(df['Date'].dt.strftime('%B')).max())



#output:

         Amount       Date Transaction

Date                                  

January   100.2 2018-01-05       DEBIT

May       200.0 2018-05-01      CREDIT

edited Jan 1 at 4:33

answered Jan 1 at 4:18

AI_Learning

3,53621033

Almost, there! How would I add the date, not just the month? E.g. January-2, May-1, etc?

– farhany
Jan 1 at 4:26

just add %d in strftime. please find my updated solution

– AI_Learning
Jan 1 at 4:28

That gives me both entries, but I only wanted the max per month, so this seems to work: print(df.groupby(df['Date'].dt.strftime('%B'))['Date', 'Amount', 'Transaction'].max()) ` Date Amount Transaction` Date February 2018-02-01 200.0 CREDIT January 2018-01-05 100.2 DEBIT

– farhany
Jan 1 at 4:29

you want max date for every month is it?

– AI_Learning
Jan 1 at 4:31

1

Thanks a lot @AI_Learning. I've been at this for over 4 hours. I get it now... Why Python is used in Data Sciences. You, sir. Rock. And have the greatest New Year!

– farhany
Jan 1 at 4:36

|
show 2 more comments

You have the convert the date format and then apply groupBy.
Try this!

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

             'Transaction': ['CREDIT', 'DEBIT', 'CREDIT'],

             'Amount': [100.20, -50.00, 200.00]})

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

print(df.groupby(df['Date'].dt.strftime('%B')).max())



#output:

         Amount       Date Transaction

Date                                  

January   100.2 2018-01-05       DEBIT

May       200.0 2018-05-01      CREDIT

edited Jan 1 at 4:33

answered Jan 1 at 4:18

AI_Learning

3,53621033

Almost, there! How would I add the date, not just the month? E.g. January-2, May-1, etc?

– farhany
Jan 1 at 4:26

just add %d in strftime. please find my updated solution

– AI_Learning
Jan 1 at 4:28

That gives me both entries, but I only wanted the max per month, so this seems to work: print(df.groupby(df['Date'].dt.strftime('%B'))['Date', 'Amount', 'Transaction'].max()) ` Date Amount Transaction` Date February 2018-02-01 200.0 CREDIT January 2018-01-05 100.2 DEBIT

– farhany
Jan 1 at 4:29

you want max date for every month is it?

– AI_Learning
Jan 1 at 4:31

1

Thanks a lot @AI_Learning. I've been at this for over 4 hours. I get it now... Why Python is used in Data Sciences. You, sir. Rock. And have the greatest New Year!

– farhany
Jan 1 at 4:36

|
show 2 more comments

You have the convert the date format and then apply groupBy.
Try this!

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

             'Transaction': ['CREDIT', 'DEBIT', 'CREDIT'],

             'Amount': [100.20, -50.00, 200.00]})

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

print(df.groupby(df['Date'].dt.strftime('%B')).max())



#output:

         Amount       Date Transaction

Date                                  

January   100.2 2018-01-05       DEBIT

May       200.0 2018-05-01      CREDIT

edited Jan 1 at 4:33

answered Jan 1 at 4:18

AI_Learning

3,53621033

You have the convert the date format and then apply groupBy.
Try this!

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

             'Transaction': ['CREDIT', 'DEBIT', 'CREDIT'],

             'Amount': [100.20, -50.00, 200.00]})

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

print(df.groupby(df['Date'].dt.strftime('%B')).max())



#output:

         Amount       Date Transaction

Date                                  

January   100.2 2018-01-05       DEBIT

May       200.0 2018-05-01      CREDIT

edited Jan 1 at 4:33

answered Jan 1 at 4:18

AI_Learning

3,53621033

edited Jan 1 at 4:33

answered Jan 1 at 4:18

AI_Learning

3,53621033

answered Jan 1 at 4:18

AI_Learning

3,53621033

answered Jan 1 at 4:18

AI_Learning

3,53621033

Almost, there! How would I add the date, not just the month? E.g. January-2, May-1, etc?

– farhany
Jan 1 at 4:26

just add %d in strftime. please find my updated solution

– AI_Learning
Jan 1 at 4:28

That gives me both entries, but I only wanted the max per month, so this seems to work: print(df.groupby(df['Date'].dt.strftime('%B'))['Date', 'Amount', 'Transaction'].max()) ` Date Amount Transaction` Date February 2018-02-01 200.0 CREDIT January 2018-01-05 100.2 DEBIT

– farhany
Jan 1 at 4:29

you want max date for every month is it?

– AI_Learning
Jan 1 at 4:31

1

Thanks a lot @AI_Learning. I've been at this for over 4 hours. I get it now... Why Python is used in Data Sciences. You, sir. Rock. And have the greatest New Year!

– farhany
Jan 1 at 4:36

|
show 2 more comments

Almost, there! How would I add the date, not just the month? E.g. January-2, May-1, etc?

– farhany
Jan 1 at 4:26

just add %d in strftime. please find my updated solution

– AI_Learning
Jan 1 at 4:28

That gives me both entries, but I only wanted the max per month, so this seems to work: print(df.groupby(df['Date'].dt.strftime('%B'))['Date', 'Amount', 'Transaction'].max()) ` Date Amount Transaction` Date February 2018-02-01 200.0 CREDIT January 2018-01-05 100.2 DEBIT

– farhany
Jan 1 at 4:29

you want max date for every month is it?

– AI_Learning
Jan 1 at 4:31

1

Thanks a lot @AI_Learning. I've been at this for over 4 hours. I get it now... Why Python is used in Data Sciences. You, sir. Rock. And have the greatest New Year!

– farhany
Jan 1 at 4:36

Almost, there! How would I add the date, not just the month? E.g. January-2, May-1, etc?

– farhany
Jan 1 at 4:26

just add %d in strftime. please find my updated solution

– AI_Learning
Jan 1 at 4:28

That gives me both entries, but I only wanted the max per month, so this seems to work: print(df.groupby(df['Date'].dt.strftime('%B'))['Date', 'Amount', 'Transaction'].max()) ` Date Amount Transaction` Date February 2018-02-01 200.0 CREDIT January 2018-01-05 100.2 DEBIT

– farhany
Jan 1 at 4:29

you want max date for every month is it?

– AI_Learning
Jan 1 at 4:31

Thanks a lot @AI_Learning. I've been at this for over 4 hours. I get it now... Why Python is used in Data Sciences. You, sir. Rock. And have the greatest New Year!

– farhany
Jan 1 at 4:36

|
show 2 more comments

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

搜尋此網誌

Bdtjtk