CODEWITHSUNDEEP
pythonpython-3.xpandas
Rahul rajan
Rahul rajan

Reputation: 1266

How to do Groupby Count based on Date in Pandas

Have a pandas dataframe as below. I need to calculate the total count for Orgin and Destination combination for specific date and write the total count to a new column called total count

Input Data

Date       ORG  DEST    LoadMeter 
6/6/2018    FR  EST       4
6/6/2018    FR  EST       5
6/6/2018    BE  CZ        7
6/6/2018    BE  CZ        8
6/6/2018    BE  CZ        2
7/6/2018    BE  CZ        6
7/6/2018    BE  CZ        2
7/6/2018    FR  EST       4
8/6/2018    FR  EST       6
8/6/2018    LUX EST       2
8/6/2018    LUX EST       4
8/6/2018    FR  EST       6

Expected Output

Date    ORG     DEST    LoadMeter   Total Meter 
6/6/2018    FR  EST          4        9
6/6/2018    FR  EST          5        9
6/6/2018    BE  CZ           7       17
6/6/2018    BE  CZ           8       17
6/6/2018    BE  CZ           2       17
7/6/2018    BE  CZ           6        8
7/6/2018    BE  CZ           2        8
7/6/2018    FR  EST          4        4
8/6/2018    FR  EST          6       12
8/6/2018    LUX EST          2        6
8/6/2018    LUX EST          4        6
8/6/2018    FR  EST          6       12

How can this be done.

Upvotes: 1

Views: 66

Answers (2)

piRSquared
piRSquared

Reputation: 294258

factorize and numpy.add.at

cols = ['Date', 'ORG', 'DEST']
i, u = pd.factorize([*zip(*map(df.get, cols))])

a = np.zeros(len(u), dtype=np.int64)
np.add.at(a, i, df.LoadMeter)
df.assign(**{'Total Meter': a[i]})

        Date  ORG DEST  LoadMeter  Total Meter
0   6/6/2018   FR  EST          4            9
1   6/6/2018   FR  EST          5            9
2   6/6/2018   BE   CZ          7           17
3   6/6/2018   BE   CZ          8           17
4   6/6/2018   BE   CZ          2           17
5   7/6/2018   BE   CZ          6            8
6   7/6/2018   BE   CZ          2            8
7   7/6/2018   FR  EST          4            4
8   8/6/2018   FR  EST          6           12
9   8/6/2018  LUX  EST          2            6
10  8/6/2018  LUX  EST          4            6
11  8/6/2018   FR  EST          6           12

Upvotes: 0

BENY
BENY

Reputation: 323226

Using transform sum

df.groupby(['Date','ORG','DEST']).LoadMeter.transform('sum')
Out[262]: 
0      9
1      9
2     17
3     17
4     17
5      8
6      8
7      4
8     12
9      6
10     6
11    12
Name: LoadMeter, dtype: int64

df['Total']=df.groupby(['Date','ORG','DEST']).LoadMeter.transform('sum')

Upvotes: 3

Related Questions