0

I am learning python and at the moment I am playing with some sales data. The data is in csv format and is showing weekly sales. I have below columns with some sample data as below:

store#  dept#    dates      weeklysales
   1      1    01/01/2005      50000
   1      1    08/01/2005      120000
   1      1    15/01/2005      75000
   1      1    22/01/2005      25000
   1      1    29/01/2005      18000 
   1      2    01/01/2005      15000
   1      2    08/01/2005      12000
   1      2    15/01/2005      75000
   1      2    22/01/2005      35000
   1      2    29/01/2005      28000
   1      1    01/02/2005      50000
   1      1    08/02/2005      120000
   1      1    15/02/2005      75000
   1      1    22/03/2005      25000
   1      1    29/03/2005      18000

I want to add the weeklysales to monthly basis in each department and want to display the records.

I have tried to use groupby function in Pandas from below links:

how to convert monthly data to quarterly in pandas

Pandas group by and sum two columns

Pandas group-by and sum

But what is happening in the above that I get sum of all the columns and getting the following output by adding the store and dept numbers as well:

store#  dept#    dates      weeklysales
   4      3     01/2005        28800
   4      1     01/2005        165000
   4      3     02/2005        245000
   4      3     03/2005        43000

I do not want to add store and dept numbers but want to just add the weeklysales figure by each month and want the display like:

store#  dept#    dates      weeklysales
   1      1     01/2005        28800
   1      2     01/2005        165000
   1      1     02/2005        245000
   1      1     03/2005        43000

Will be grateful if I can get a solution for that.

Cheers,

Baig
  • 469
  • 2
  • 7
  • 19

1 Answers1

3

Is this what you are after?

Convert dates to month/year format and then group and sum sales.

(df.assign(dates=df.dates.dt.strftime('%m/%Y'))
    .groupby(['store#','dept#','dates'])
    .sum()
    .reset_index()
)
Out[243]: 
   store#  dept#    dates  weeklysales
0       1      1  01/2005       288000
1       1      1  02/2005       245000
2       1      1  03/2005        43000
3       1      2  01/2005       165000
Allen Qin
  • 19,507
  • 8
  • 51
  • 67