Need help in removing duplicate dates and merging values of two rows in Pandas Dataframe (python)

Question

I am following data in pandas dataframe. Some date vales are repeating(2010-07-31,2010-10-31). How to remove repeated dates and merge the values between two rows. Take A % B values from 1st row and C & D values from 2nd row.

        Date                A       B           C       D
     1  2010-06-30 0:00:00  47.1    29.34       0.036   100.8   
     2  2010-07-31 0:00:00  47.1    29.34               
     3  2010-07-31 0:00:00                      -4.644  100.2   
     4  2010-08-31 0:00:00  47.1    29.34       -1.481  100.4   
     5  2010-09-30 0:00:00  29.3    14.15        3.865  101.6   
     6  2010-10-31 0:00:00  29.3    14.15               
     7  2010-10-31 0:00:00                       0.517  102.6

Expected Output:

        Date                A       B           C       D
     1  2010-06-30 0:00:00  47.1    29.34       0.036   100.8   
     2  2010-07-31 0:00:00  47.1    29.34       -4.644  100.2           
     4  2010-08-31 0:00:00  47.1    29.34       -1.481  100.4   
     5  2010-09-30 0:00:00  29.3    14.15        3.865  101.6   
     6  2010-10-31 0:00:00  29.3    14.15        0.517  102.6

Thanks!

skrubber · Accepted Answer · 2017-10-16 17:39:35Z

3

Use pandas groupby and aggregate it by sum (use Dated as column name, instead of the reserved Date):

df.groupby(['Dated']).sum()

answered Oct 16, 2017 at 17:39

skrubber

1,1051 gold badge9 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

cs95 · Accepted Answer · 2017-10-16 18:33:04Z

2

Magic of stack and unstack

df.set_index('Date').replace({'':np.nan}).stack().unstack()
Out[515]: 
                      A      B      C      D
Date                                        
2010-06-300:00:00  47.1  29.34  0.036  100.8
2010-07-310:00:00  47.1  29.34 -4.644  100.2
2010-08-310:00:00  47.1  29.34 -1.481  100.4
2010-09-300:00:00  29.3  14.15  3.865  101.6
2010-10-310:00:00  29.3  14.15  0.517  102.6

edited Oct 16, 2017 at 18:33

cs95

406k106 gold badges744 silver badges797 bronze badges

answered Oct 16, 2017 at 17:52

BENY

324k22 gold badges176 silver badges250 bronze badges

Collectives™ on Stack Overflow

Need help in removing duplicate dates and merging values of two rows in Pandas Dataframe (python)

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related