title: Merge rows with same date to filll blanks/Nans using pandas
body: <p>I have a large dataset (100K rows) of chemical parameters and the date they were measured for numerous locations (IDs). Some of these records have been split between rows with the same date. I would like to recombine them.  </p>
  
  <pre class="lang-py prettyprint-override"><code>import pandas as pd
  import numpy as np
  
  df1=pd.DataFrame(np.array([["Site 1","1/12/2029",3,0.001,np.nan],["Site 1","1/12/2029",np.nan,np.nan,3],["Site 1","2/12/2029",7,np.nan,3],
                             ["Site 1","2/12/2029",np.nan,0.001,np.nan],["Site 2","1/12/2029",3,0.001,3],["Site 1","3/12/2029",5,0.005,3],
                             ["Site 2","2/12/2029",np.nan,np.nan,3],["Site 2","2/12/2029",np.nan,0.001,np.nan]]),
                   columns=["ID","Date", 'Na', 'SO4','Mg'])
  df1['Date'] = pd.to_datetime(df1['Date'],format= '%d/%m/%Y',dayfirst=True)
  df1
  
  Out[8]:
         ID       Date   Na    SO4   Mg
  0  Site 1 2029-12-01    3  0.001  nan
  1  Site 1 2029-12-01  nan    nan    3
  2  Site 1 2029-12-02    7    nan    3
  3  Site 1 2029-12-02  nan  0.001  nan
  4  Site 2 2029-12-01    3  0.001    3
  5  Site 1 2029-12-03    5  0.005    3
  6  Site 2 2029-12-02  nan    nan    3
  7  Site 2 2029-12-02  nan  0.001  nan
  
  </code></pre>
  
  <p>I have deliberately jumbled the orders a bit and there may still be some columns that are empty in my data.</p>
  
  <p>Result I would like is:</p>
  
  <pre class="lang-py prettyprint-override"><code>Out[2]:
         ID       Date   Na    SO4 Mg
  0  Site 1 2029-12-01    3  0.001  3
  1  Site 1 2029-12-02    7  0.001  3
  2  Site 2 2029-12-01    3  0.001  3
  3  Site 1 2029-12-03    5  0.005  3
  4  Site 2 2029-12-02  nan  0.001  3
  </code></pre>
  
  <p>Which is the values for the chemical parameters (Na, SO4, and Mg) for the same date and ID condensed, removing the nan values (nans will remain where there is no entry for a date and ID in any rows with that date and ID. </p>
  
  <p>I tried this:</p>
  
  <pre class="lang-py prettyprint-override"><code>df_new=df1.groupby(['ID','Date']).apply(pd.DataFrame.sort_values, 'Date').fillna(method='ffill')
  df_new
  
  Out[7]:
                           ID       Date   Na    SO4   Mg
  ID     Date
  Site 1 2029-12-01 1  Site 1 2029-12-01  nan    nan    3
         2029-12-02 0  Site 1 2029-12-02    3  0.001  nan
                    2  Site 1 2029-12-02    7    nan    3
                    3  Site 1 2029-12-02  nan  0.001  nan
         2029-12-03 5  Site 1 2029-12-03    5  0.005    3
  Site 2 2029-12-01 4  Site 2 2029-12-01    3  0.001    3
         2029-12-02 6  Site 2 2029-12-02  nan    nan    3
                    7  Site 2 2029-12-02  nan  0.001  nan
  
  </code></pre>
  
  <p>But this doesn't seem to do what I had hoped, and also introduces a weird index column. </p>