2

How can I merge column data of the same value and sum its specific data (in this case based of the DATE column)

For Example: df includes:

78      79      80      DATE
8.99    7.99    6.99    201107
3.5     2.5     1.5     201107
5.48    4.48    3.48    201108
4.04    3.04    2.04    201108
5.03    4.03    3.03    201108

What I would like is:

78      79      80      DATE
12.49   10.49   8.49    201107
14.55   11.55   8.55    201108

What is the simplest way of achieving this?

Techno04335
  • 1,365
  • 6
  • 22
  • 43

1 Answers1

4

You can groupby on 'DATE' column and then call sum:

In [202]:
df.groupby('DATE', as_index=False).sum()

Out[202]:
     DATE     78     79    80
0  201107  12.49  10.49  8.49
1  201108  14.55  11.55  8.55
EdChum
  • 376,765
  • 198
  • 813
  • 562
  • I have tried `df.groupby('DATE', as_index=False).sum()` but I resulted in the same df originally with none of the columns merged. – Techno04335 Oct 09 '15 at 11:39
  • That means there is something odd with your 'DATE' col, you'll need to post raw input data and code to reproduce why it doesn't work as it clearly works fine for me – EdChum Oct 09 '15 at 11:58
  • I found the issue I forgot to set `df.groupby('DATE , as_index=False).sum()` to `df` via `df=df.groupby('DATE', as_index=False).sum()`. – Techno04335 Oct 09 '15 at 12:08
  • Yes that catches a lot of people out that they need to assign back the result, glad I could help – EdChum Oct 09 '15 at 12:11