0

I have three columns. 

Client_Number|Date|Transactions
1|  2018-01-13| 11.22|
1|  2018-07-23| 900|
2|  2018-01-12| 990|
7|  2018-07-13|   458|
2|  2018-01-21| 525|
5|  2018-02-24|     773|
5|  2018-02-14| 276|
7|  2018-07-17| 619.75|
3|  2018-08-25| 465.1|
3|  2018-08-28| 8000|

I need to group the clients number column by month and then sum the total transaction made by the client per month.

I have made this :

data.groupby(['Client_Number','Calendar'])['Transactions'].sum()

Client_Number Calendar

1|                    2018-07-23|             900.00|
2|                    2018-01-12|             990.00|
                     2018-01-13|              11.22|
                     2018-01-21|             525.00|
3|                    2018-08-25|             465.10|
                     2018-08-28|            8000.00|
5|                    2018-02-14|             276.00|
                     2018-02-24|             773.00|
7|                    2018-07-13|             458.00|
                     2018-07-17|             619.75|

But what I need is to group the same months in one by client number and then sum all the transactions of those months. I have tried different group by, but I still haven't made it work.

anky
  • 74,114
  • 11
  • 41
  • 70
DS01
  • 3
  • 2
  • Thank you a lot @anky_91 . I was looking about how to do what you did with my data. I need to learn how to do it. – DS01 Aug 10 '19 at 13:32
  • no problem, this is a good read : https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples and this: https://stackoverflow.com/editing-help – anky Aug 10 '19 at 13:33
  • 1
    bdw are you looking for `df.groupby(['Client_Number',df.Date.dt.month])['Transactions'].sum()` ? – anky Aug 10 '19 at 13:35
  • @anky_91, yes, your code help me too. Now I want to add a column with the number of transations made by the client per month too. – DS01 Aug 10 '19 at 19:22

1 Answers1

1

First make sure 'Date' is a timestamp

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

Then add month to the dataframe

df['Month']=df['Date'].dt.month

And use groupby()

df_grouped=df.groupby(['Client_Number','Month'])['Transactions'].sum().reset_index()
Nev1111
  • 1,039
  • 9
  • 13
  • Thanks @Nev1111, your solution helped me a lot. I have a question: What if I want to add a column with the number of trasations made by the client per month . How could I do it? – DS01 Aug 10 '19 at 19:18
  • I tried this: 1 --- df['count_Transactions']=df['Calendar'].count() 2.df_continue=df.groupby(['Client_Number','Months_Transactions']).agg({"count_Transactions":"count", "Transactions":"sum"}) – DS01 Aug 10 '19 at 20:18
  • You would do the same as with the sum, instead of sum use count: df_grouped=df.groupby(['Client_Number','Month'])['Transactions'].count().reset_index() – Nev1111 Aug 11 '19 at 18:21
  • Please upvote this answer if you found it helpful. Thanks! – Nev1111 Aug 11 '19 at 18:23