CODEWITHSUNDEEP
pythonpandasdataframe
Arvinth Kumar
Arvinth Kumar

Reputation: 1014

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

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!

Upvotes: 0

Views: 184

Answers (2)

BENY
BENY

Reputation: 323226

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

Upvotes: 2

skrubber
skrubber

Reputation: 1095

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

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

enter image description here

Upvotes: 3

Related Questions