title: Superhuman Level - Pandas DataFrame Reshaping because of Duplicates
body: <p><strong>Do you like puzzles that only superhumans can solve? This is the final test to prove such an ability.</strong></p>
  
  <p>A single company might get different levels of funding (seed, a) from multiple banks possibly at different times. </p>
  
  <p>Let's look at the data then the story to get a better picture.</p>
  
  <pre><code>import pandas as pd
  data = {'id':[1,2,2,3,4],'company':['alpha','beta','beta','alpha','alpha'],'bank':['z', 'x', 'y', 'z', 'j'], 
      'rd': ['seed', 'seed', 'seed', 'a', 'a'], 'funding': [100, 200, 200, 300, 50],
     'date': ['2006-12-01', '2004-09-01', '2004-09-01', '2007-05-01', '2007-09-01']}
  df = pd.DataFrame(data, columns = ['id','company', 'round', 'bank', 'funding', 'date'])
  df
  </code></pre>
  
  <h1>Yields:</h1>
  
  <pre><code>   id  company        rd   bank    funding        date
  0   1    alpha      seed      z        100  2006-12-01
  1   2     beta      seed      x        200  2004-09-01
  2   2     beta      seed      y        200  2004-09-01
  3   3    alpha         a      z        300  2007-05-01
  4   4    alpha         a      j         50  2007-09-01
  </code></pre>
  
  <h1>Desired Output:</h1>
  
  <pre><code>   company     bank_seed   funding_seed      date_seed    bank_a  funding_a      date_a 
  0    alpha             z            100     2006-12-01     [z,j]        350  2007-09-01
  1     beta         [x,y]            200     2004-09-01      None       None        None
  </code></pre>
  
  <p>As you can see, I am not a superhuman but shall try to explain my thought process. </p>
  
  <h3><em>Let's look at company alpha</em></h3>
  
  <p>Company alpha first got seed money for $100 from bank z in late 2006. A few months later, their investors were very happy with their progress so bank z gave them money ($300 more!). However, Company alpha needed a little more cash but had to go to some random Swiss bank j to stay alive. Bank j reluctantly gave $50 more. Yay! They now have $350 from their updated 'a' round ending in September 2007. </p>
  
  <p>Company beta is pretty new. They got funding totaling $200 from two different banks. But wait... there's nothing in here about their round 'a'. That's okay we'll put None for now and check back with them later. </p>
  
  <p><strong>The issue is that Company alpha sucks and got money from the Swiss...</strong>
  This is my non-working code that had worked on a subset of my data - it won't work here.</p>
  
  <pre><code>import itertools
  
  unique_company = df.company.unique()
  df_indexed = df.set_index(['company', 'rd'])
  index = pd.MultiIndex.from_tuples(list(itertools.product(unique_company, list(df.rd.unique()))))
  reindexed = df_indexed.reindex(index, fill_value=0)
  
  reindexed = reindexed.unstack().applymap(lambda cell: 0 if '1970-01-01' in str(cell) else cell)
  
  working_df = pd.DataFrame(reindexed.iloc[:, 
  reindexed.columns.get_level_values(0).isin(['company', 'funding'])].to_records())
  </code></pre>
  
  <p>If you know how to solve part of this problem, go ahead and put it below. Thank you in advance for taking the time to look at this! :)</p>
  
  <p>Lastly, if you want to see how my code <em>does</em> work. Then, do this but you lose so much valuable info...</p>
  
  <pre><code> df = df.drop_duplicates(subset='id')
   df = df.drop_duplicates(subset='rd')
  </code></pre>