Do you like puzzles that only superhumans can solve? This is the final test to prove such an ability.

A single company might get different levels of funding (seed, a) from multiple banks possibly at different times.

Let's look at the data then the story to get a better picture.

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

Yields:

   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

Desired Output:

   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

As you can see, I am not a superhuman but shall try to explain my thought process.

Let's look at company alpha

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.

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.

The issue is that Company alpha sucks and got money from the Swiss... This is my non-working code that had worked on a subset of my data - it won't work here.

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())

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! :)

Lastly, if you want to see how my code does work. Then, do this but you lose so much valuable info...

 df = df.drop_duplicates(subset='id')
 df = df.drop_duplicates(subset='rd')