How do I sum values by an ID and date, keeping the values ordered by date?-CodePudding

I have the following dataframe in Python:

product_num    date       qty
001            1/1/2022     4
001            1/1/2022     2
002            1/2/2022     1
002            1/2/2022     3
002            1/3/2022     5
001            1/3/2022     5
003            1/1/2022     2
004            1/4/2022     3
004            1/4/2022     4

I'm trying to organize the data as follows (summing quantity by product ID and date):

product_num    date       qty
001            1/1/2022     6
001            1/3/2022     5
003            1/1/2022     2
002            1/2/2022     4
002            1/3/2022     5
004            1/4/2022     7

I tried using a group by and sum function but the part that I haven't been been successful in is keeping the product number in each row, maintaining the column headers, as well as keeping the product numbers and dates in order.

CodePudding user response：

use groupby and sum

df.groupby(['product_num','date']).sum().sort_values('date').reset_index()

    product_num     date    qty
0   1             1/1/2022   6
1   1             1/3/2022   5
2   2             1/2/2022   4
3   2             1/3/2022   5
4   3             1/1/2022   2
5   4             1/4/2022   7

data used for the question

product_num date qty
001 1/1/2022 4
001 1/1/2022 2
002 1/2/2022 1
002 1/2/2022 3
002 1/3/2022 5
001 1/3/2022 5
003 1/1/2022 2
004 1/4/2022 3
004 1/4/2022 4