title: pandas groupby index: nan conflicts or take only value
body: <p>I'm working with some data via <code>pandas</code> which can have some inconsistencies I need to handle. The data is a time series of values:</p>
  <pre><code>                     A     B      C
  YYYY MM DD hh mm                                
  2017 8  20 23 0      1   2.0    NaN
                10     2   4.0    NaN
                20     3   6.0    NaN
                30     4   8.0    NaN
  ...                ...   ...    ...
  2019 6  4  10 10   100   100    NaN
                20   200   102    NaN
                30   300   104    NaN
                40   400   106    NaN
                50   500   108      0
  </code></pre>
  <p>The inconsistency the data can have is multiple entries for the same <code>[&quot;YYYY&quot;, &quot;MM&quot;, &quot;DD&quot;, &quot;hh&quot;, &quot;mm&quot;]</code> index. In most cases, the values at that time are are identical so I can use <code>df.drop_duplicates(keep=&quot;first&quot;)</code> to drop all rows with the same index and column values.</p>
  <p>However, there are index collisions where the values are not identical or a non-nan value is only present in one of the rows. The behaviour I'm after is:</p>
  <p>For duplicate indices, on a per-column bases:</p>
  <ol>
  <li>If only one non-nan value: use that.</li>
  <li>If all nan values: use nan.</li>
  <li>If all the same (non-nan) value: use that.</li>
  <li>If non-equal (non-nan) value: use nan.</li>
  </ol>
  <p>For example (is a simplified DataFrame):</p>
  <pre><code>     A      B    C
  0    1    2.0  NaN
  1    2    NaN  NaN
  1  100  500.0  NaN
  2    3    6.0  NaN
  2  200    6.0  NaN
  3  300    8.0  NaN
  3  300    NaN  5.0
  3  300    NaN  NaN
  4  400  106.0  NaN
  </code></pre>
  <p>Should result in:</p>
  <pre><code>       A      B    C
  0    1.0    2.0  NaN
  1    NaN  500.0  NaN
  2    NaN    6.0  NaN
  3  300.0    8.0  5.0
  4  400.0  106.0  NaN
  </code></pre>
  <p>I tried to solve this in a couple of methods, but they were both horrendously slow on the dataset size.</p>
  <p>current slow solutions (you may need to scroll the code snippet window):</p>
  <pre class="lang-py prettyprint-override"><code>import numpy as np
  import pandas as pd
  
  df = pd.DataFrame(
      [
          dict(A=1, B=2.0, C=None),
          dict(A=2, B=None, C=None),
          dict(A=100, B=500, C=None),
          dict(A=3, B=6.0, C=None),
          dict(A=200, B=6.0, C=None),
          dict(A=300, B=8.0, C=None),
          dict(A=300, B=None, C=5.0),
          dict(A=300, B=None, C=None),
          dict(A=400, B=106, C=None),
      ],
      index=[0, 1, 1, 2, 2, 3, 3, 3, 4],
  )
  
  
  # SLOW SOLUTION 1
  def canonical(colum_values):
      candidates = colum_values.dropna().unique()
      if len(candidates) == 1:
          return candidates[0]
      else:
          return np.nan
  
  
  solution_1 = df.groupby(df.index).aggregate(canonical)
  
  
  # SLOW &amp; UGLY SOLUTION 2
  def solve_2(df):
      df = df.copy()
  
      for dupe in df.index[df.index.duplicated(keep=&quot;first&quot;)]:
          for column in df.columns:
              values = df[df.index == dupe][column]
  
              if len(values.dropna().unique()) == 1:
                  df.loc[df.index == dupe, column] = values.dropna().iloc[0]
              else:
                  df.loc[df.index == dupe, column] = np.nan
      
      # duplicate rows should all now share the same value, so just keep one.
      df.drop_duplicates(keep=&quot;first&quot;, inplace=True)
  
      return df
  
  
  solution_2 = solve_2(df)
  </code></pre>
  <p>Looking for any improvements on these to get better performance on large datasets.</p>
  <p>Thanks.</p>