CODEWITHSUNDEEP
pandas
esc
esc

Reputation: 25

Pandas sum over multiple columns after group by

if I have a data set where the columns are something like:

Day Column2 Column3 Column4......Column100

Is there a better way to do something like the below?

grouped_df = df.groupby('Day').agg({
'Column2': lambda x : sum(x),
'Column3': lambda x : sum(x),
'Column4': lambda x : sum(x),
..........
'Column100': lambda x : sum(x)})

What I have works but wondering if there is a more elegant solution.

Thank You

Upvotes: 1

Views: 1947

Answers (2)

ye jiawei
ye jiawei

Reputation: 902

You can try df.groupby('Day').sum() just like what MaxU said.

Upvotes: 1

MaxU - stand with Ukraine
MaxU - stand with Ukraine

Reputation: 210882

you can do it this way:

In [17]: df
Out[17]:
    a  b  c  d  e        Day
0   7  5  4  9  4 2016-01-01
1   2  1  5  4  5 2014-01-01
2   2  8  8  6  9 2014-01-01
3   1  4  4  3  7 2015-01-01
4   5  6  7  9  5 2016-01-01
5   3  6  0  8  7 2015-01-01
6   7  4  4  5  5 2014-01-01
7   1  1  0  1  6 2015-01-01
8   7  8  9  8  3 2015-01-01
9   8  5  5  2  8 2015-01-01
10  6  1  3  0  3 2014-01-01
11  1  8  2  7  2 2016-01-01
12  2  5  2  5  1 2016-01-01
13  1  2  3  2  2 2016-01-01
14  7  4  9  5  2 2016-01-01
15  4  0  8  9  5 2015-01-01
16  8  5  8  9  7 2015-01-01
17  6  7  9  5  4 2016-01-01
18  7  4  2  3  2 2016-01-01
19  2  7  8  6  8 2015-01-01

In [18]: cols = df.columns

In [19]: cols[1:]
Out[19]: Index(['b', 'c', 'd', 'e', 'Day'], dtype='object')


In [20]: df.ix[:, cols[1:]].groupby('Day').sum()
Out[20]:
             b   c   d   e
Day
2014-01-01  14  20  15  22
2015-01-01  36  42  46  51
2016-01-01  41  38  45  22

setup sample DF:

rows = 20
df = pd.DataFrame(np.random.randint(0, 10, size=(rows, 5)), columns=list('abcde'))
dates = [pd.to_datetime(d) for d in ['2016-01-01','2015-01-01','2014-01-01']]
df['Day'] = np.random.choice(dates, len(df))

Upvotes: 0

Related Questions