Concatenate/merge several rows within ONE pandas dataframe, based on column value

Question

I have a pandas dataframe in which several rows share a particular column value. For those, I want to concatenate/merge these into one single row (sometimes the column value will only appear once in the table, maximum 5 times, so in the end the rows would have variable lengths). I also want the data to be sorted by date (ascending).

The data looks something like this:

df1 =  pd.DataFrame({'Funded_Venture': ['Helia', 'Loop', 'Asana', 'Helia', 'Helia', 'Loop'], 
'Funding Type': ['Seed', 'Seed', 'Series A', 'Series B', 'Series C', 'Series A'],
'Money Raised': [500000,200000,1000000,250000,300000,200000], 
'Announced_Date': ['01/10/2006', '03/03/2007', '04/08/2008', '10/12/2008', '20/06/2009', '30/09/2009'], 
'Investor.1': ['Public VC', 'Private VC', 'Public VC', 'Public VC', 'Private VC', 'Company'], 
'Investor.2': ['Public VC', '', 'Public VC', 'Public VC', '', 'Angel Investor'], 
'Investor.3': ['Incubation Funding', 'Public VC', 'Public VC', '', 'Angel Investor', 'Public VC']})

(The actual dataset has about 750 columns and 15 rows)

I am stuck here:

df1['Announced_Date'] = pd.to_datetime(df1['Announced_Date'])
df1 = df1.sort_values(by=['Announced_Date', 'Funded_Venture'],
                      ascending=[True, True])
df1['number of rounds'] = iv_chain.groupby('Funded_Venture').cumcount().add(1)
df1 = df1.merge(df1, on=['Funded_Venture'], how='inner')
df1.to_csv('df1.csv', index=False, sep=";")

The outcome for now is that it creates several rows with different combinations.

I would like to have one row where all Helia Funding can be found starting from the first date, one row where all the Loop Funding can be found etc.

The result I am looking for would look something like this:

Funded_Venture;Funding Type_x;Money Raised_x;Announced_Date_x;Investor.1_x;Investor.2_x;Investor.3_x;number of rounds_x;Funding Type_y;Money Raised_y;Announced_Date_y;Investor.1_y;Investor.2_y;Investor.3_y;number of rounds_y;Funding Type_z;Money Raised_z;Announced_Date_z;Investor.1_z;Investor.2_z;Investor.3_z;number of rounds_z
Helia;Seed;500000;2006-01-10;Public VC;Public VC;Incubation Funding;1;Series B;250000;2008-10-12;Public VC;Public VC;;2;Series C;300000;2009-06-20;Private VC;;Angel Investor;3
Loop;Seed;200000;2007-03-03;Private VC;;Public VC;1;Series A;200000;2009-09-30;Company;Angel Investor;Public VC;2
Asana;Series A;1000000;2008-04-08;Public VC;Public VC;Public VC;1

Any help is highly appreciated!

user_17106024 · Answer

It depends on your wanted result if you want them grouped by sum:

df1.groupby('Funded_Venture').sum()

it give you this result:

user_3357175 · Answer

I would suggest using unstack. Since you want to group results by the round number, that information needs to be added to the DataFrame.

I added the Round column manually, which should be automated for larger DataFrames:

df1 =  pd.DataFrame({
'Funded_Venture': ['Helia', 'Loop', 'Asana', 'Helia', 'Helia', 'Loop'],
'Round': [0, 0, 0, 1, 2, 1],
'Funding Type': ['Seed', 'Seed', 'Series A', 'Series B', 'Series C', 'Series A'],
'Money Raised': [500000,200000,1000000,250000,300000,200000], 
'Announced_Date': ['01/10/2006', '03/03/2007', '04/08/2008', '10/12/2008', '20/06/2009', '30/09/2009'], 
'Investor.1': ['Public VC', 'Private VC', 'Public VC', 'Public VC', 'Private VC', 'Company'], 
'Investor.2': ['Public VC', '', 'Public VC', 'Public VC', '', 'Angel Investor'], 
'Investor.3': ['Incubation Funding', 'Public VC', 'Public VC', '', 'Angel Investor', 'Public VC']
})

Running

df1.set_index(['Funded_Venture', 'Round']).unstack()

seems to yield some useful results.

Hope this helps!

Concatenate/merge several rows within ONE pandas dataframe, based on column value

2 Answers