Trim, Aggregate and Plot From Pandas DataFrame

Question

My problem is similar to the question asked How to aggregate and plot data from pandas dataframe?

Except that my data is less than perfect.

I have a DataFrame with the data read in from a CSV file with the following format:

total_value;payout_date  
25;2021-02-24-09-AM
10;2021-02-24-09-PM
16;2021-02-24-10-AM
2;2021-02-24-10-PM
12;2021-02-24-11-AM
6;2021-02-24-11-PM
27;2021-02-24-12-PM
3;2021-02-25-01-AM
20;2021-02-25-01-PM
16;2021-02-26-10-AM
1;2021-02-26-10-PM
17;2021-02-26-11-AM
2;2021-02-26-11-PM
4;2021-02-26-12-AM
17;2021-02-26-12-PM
4;2021-02-27-01-AM
5;2021-02-27-01-PM
6;2021-02-27-02-AM

The datetime format is yyyy-mm-dd-h12-am. How can I trim the date to just yyyy-mm-dd before I can aggregate the totals. For example,

4;2021-02-27-01-AM
5;2021-02-27-01-PM
6;2021-02-27-02-AM

becomes

4;2021-02-27
5;2021-02-27
6;2021-02-27

and the expected result is

15;2021-02-27

Thanks in advance for your help!

Mustafa Aydın · Accepted Answer · 2021-06-10 08:28:21Z

1

You can take the last 6 characters off, convert it to date-time and then groupby & sum:

df.payout_date = df.payout_date.str[:-6]

df.payout_date = pd.to_datetime(df.payout_date)

result = df.groupby(df.payout_date).sum()

to get

>>> result

             total_value
payout_date
2021-02-24            98
2021-02-25            23
2021-02-26            57
2021-02-27            15

with result.plot giving

answered Jun 10, 2021 at 8:28

Mustafa Aydın

18.4k4 gold badges21 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

absolutelynewbie Over a year ago

Thanks Mustafa. Great job! That is exactly what I am looking for.

absolutelynewbie Over a year ago

I am trying to vote up +1 but I am getting this message "Don't forget you can mark this as the accepted answer by clicking its check mark." How can I mark the answer as the accepted answer?

Mustafa Aydın Over a year ago

@absolutelynewbie glad to be of help! There is a check mark under the vote buttons, you can click that; for the visual, please see meta.stackexchange.com/a/5235/791774. Thanks!

Collectives™ on Stack Overflow

Trim, Aggregate and Plot From Pandas DataFrame

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related