Spreadsheet: 130619d1322493050-collapsing-a-data-set-example
Sheet: Sheet1

Example of my Exising Data Set
id | year | company_name | Subsidiary | countryisocode | totalassets | Corruption | country
1 | 2002 | WAL-MART STORES, INC. | 1 | CL | 1060194 | 4 | CHILE
1 | 2003 | WAL-MART STORES, INC. | 1 | CL | 1409239 | 2.70833 | CHILE
1 | 2004 | WAL-MART STORES, INC. | 1 | CL | 1858343 | 2.5 | CHILE
1 | 2005 | WAL-MART STORES, INC. | 1 | CL | 2147491 | 4.5 | CHILE
1 | 2006 | WAL-MART STORES, INC. | 1 | CL | 2255497 | 4.5 | CHILE
1 | 2007 | WAL-MART STORES, INC. | 1 | CL | 2887234 | 4.5 | CHILE
1 | 2008 | WAL-MART STORES, INC. | 1 | CL | 2690158 | 4.5 | CHILE
1 | 2009 | WAL-MART STORES, INC. | 1 | CL | 3177126 | 4.5 | CHILE
1 | 2010 | WAL-MART STORES, INC. | 1 | CL | 3711823 | 4.5 | CHILE
2 | 2002 | WAL-MART STORES, INC. | 2 | CL | 482359 | 4 | CHILE
2 | 2003 | WAL-MART STORES, INC. | 2 | CL | 7784646 | 2.70833 | CHILE
2 | 2004 | WAL-MART STORES, INC. | 2 | CL | 1133860 | 2.5 | CHILE
2 | 2005 | WAL-MART STORES, INC. | 2 | CL | 1216421 | 4.5 | CHILE
2 | 2006 | WAL-MART STORES, INC. | 2 | CL | 1341478 | 4.5 | CHILE
2 | 2007 | WAL-MART STORES, INC. | 2 | CL | 1525963 | 4.5 | CHILE
2 | 2008 | WAL-MART STORES, INC. | 2 | CL | 1522828 | 4.5 | CHILE
2 | 2009 | WAL-MART STORES, INC. | 2 | CL | 1630046 | 4.5 | CHILE
2 | 2010 | WAL-MART STORES, INC. | 2 | CL | 1870340 | 4.5 | CHILE
3 | 2002 | ROYAL DUTCH SHELL PLC | 1 | PH |  | 1.5 | PHILIPPINES
3 | 2003 | ROYAL DUTCH SHELL PLC | 1 | PH | 667194 | 1.91667 | PHILIPPINES
3 | 2004 | ROYAL DUTCH SHELL PLC | 1 | PH |  | 2 | PHILIPPINES
3 | 2005 | ROYAL DUTCH SHELL PLC | 1 | PH | 997313 | 2 | PHILIPPINES
3 | 2006 | ROYAL DUTCH SHELL PLC | 1 | PH | 1134337 | 2 | PHILIPPINES
3 | 2007 | ROYAL DUTCH SHELL PLC | 1 | PH | 1446174 | 2 | PHILIPPINES
3 | 2008 | ROYAL DUTCH SHELL PLC | 1 | PH | 1180215 | 2 | PHILIPPINES
3 | 2009 | ROYAL DUTCH SHELL PLC | 1 | PH | 1232415 | 2 | PHILIPPINES
3 | 2010 | ROYAL DUTCH SHELL PLC | 1 | PH |  | 2 | PHILIPPINES
4 | 2002 | ROYAL DUTCH SHELL PLC | 2 | PH |  | 1.5 | PHILIPPINES
4 | 2003 | ROYAL DUTCH SHELL PLC | 2 | PH | 667194 | 1.91667 | PHILIPPINES
4 | 2004 | ROYAL DUTCH SHELL PLC | 2 | PH |  | 2 | PHILIPPINES
4 | 2005 | ROYAL DUTCH SHELL PLC | 2 | PH | 997313 | 2 | PHILIPPINES
4 | 2006 | ROYAL DUTCH SHELL PLC | 2 | PH | 1134337 | 2 | PHILIPPINES
4 | 2007 | ROYAL DUTCH SHELL PLC | 2 | PH | 1446174 | 2 | PHILIPPINES
4 | 2008 | ROYAL DUTCH SHELL PLC | 2 | PH | 1180215 | 2 | PHILIPPINES
4 | 2009 | ROYAL DUTCH SHELL PLC | 2 | PH | 1232415 | 2 | PHILIPPINES
4 | 2010 | ROYAL DUTCH SHELL PLC | 2 | PH |  | 2 | PHILIPPINES
5 | 2002 | ROYAL DUTCH SHELL PLC | 3 | PK |  | 1.5 | PAKISTAN
5 | 2003 | ROYAL DUTCH SHELL PLC | 3 | PK | 83015 | 1.5 | PAKISTAN
5 | 2004 | ROYAL DUTCH SHELL PLC | 3 | PK | 106477 | 1.5 | PAKISTAN
5 | 2005 | ROYAL DUTCH SHELL PLC | 3 | PK | 128217 | 1.5 | PAKISTAN
5 | 2006 | ROYAL DUTCH SHELL PLC | 3 | PK | 199197 | 1.5 | PAKISTAN
5 | 2007 | ROYAL DUTCH SHELL PLC | 3 | PK | 242663 | 1.95833 | PAKISTAN
5 | 2008 | ROYAL DUTCH SHELL PLC | 3 | PK | 348152 | 2 | PAKISTAN
5 | 2009 | ROYAL DUTCH SHELL PLC | 3 | PK | 400135 | 2 | PAKISTAN
5 | 2010 | ROYAL DUTCH SHELL PLC | 3 | PK | 361087 | 2 | PAKISTAN
What I want my new Dataset to look like:
I'm basically trying to constuct a dataset that shows each company_name's exposure, in terms of total assets, to a country. So in a sense I'm collapsing Wal-Marts to Chile subsidiaries into 1 and I'm collapsing Shell's subsidiaries in the Philippines into 1. Notice that for Shell there is 1 subsidiary in Pakistan so the observations are just repeated.
id | year | company_name |  | countryisocode | totalassets | Corruption | country
1 | 2002 | WAL-MART STORES, INC. |  | CL |  | 4 | CHILE
1 | 2003 | WAL-MART STORES, INC. |  | CL |  | 2.70833 | CHILE
1 | 2004 | WAL-MART STORES, INC. |  | CL |  | 2.5 | CHILE
1 | 2005 | WAL-MART STORES, INC. |  | CL |  | 4.5 | CHILE
1 | 2006 | WAL-MART STORES, INC. |  | CL |  | 4.5 | CHILE
1 | 2007 | WAL-MART STORES, INC. |  | CL |  | 4.5 | CHILE
1 | 2008 | WAL-MART STORES, INC. |  | CL |  | 4.5 | CHILE
1 | 2009 | WAL-MART STORES, INC. |  | CL |  | 4.5 | CHILE
1 | 2010 | WAL-MART STORES, INC. |  | CL |  | 4.5 | CHILE
2 | 2002 | ROYAL DUTCH SHELL PLC |  | PH |  | 1.5 | PHILIPPINES
2 | 2003 | ROYAL DUTCH SHELL PLC |  | PH |  | 1.91667 | PHILIPPINES
2 | 2004 | ROYAL DUTCH SHELL PLC |  | PH |  | 2 | PHILIPPINES
2 | 2005 | ROYAL DUTCH SHELL PLC |  | PH |  | 2 | PHILIPPINES
2 | 2006 | ROYAL DUTCH SHELL PLC |  | PH |  | 2 | PHILIPPINES
2 | 2007 | ROYAL DUTCH SHELL PLC |  | PH |  | 2 | PHILIPPINES
2 | 2008 | ROYAL DUTCH SHELL PLC |  | PH |  | 2 | PHILIPPINES
2 | 2009 | ROYAL DUTCH SHELL PLC |  | PH |  | 2 | PHILIPPINES
2 | 2010 | ROYAL DUTCH SHELL PLC |  | PH |  | 2 | PHILIPPINES
3 | 2002 | ROYAL DUTCH SHELL PLC |  | PK |  | 1.5 | PAKISTAN
3 | 2003 | ROYAL DUTCH SHELL PLC |  | PK | 83015 | 1.5 | PAKISTAN
3 | 2004 | ROYAL DUTCH SHELL PLC |  | PK | 106477 | 1.5 | PAKISTAN
3 | 2005 | ROYAL DUTCH SHELL PLC |  | PK | 128217 | 1.5 | PAKISTAN
3 | 2006 | ROYAL DUTCH SHELL PLC |  | PK | 199197 | 1.5 | PAKISTAN
3 | 2007 | ROYAL DUTCH SHELL PLC |  | PK | 242663 | 1.95833 | PAKISTAN
3 | 2008 | ROYAL DUTCH SHELL PLC |  | PK | 348152 | 2 | PAKISTAN
3 | 2009 | ROYAL DUTCH SHELL PLC |  | PK | 400135 | 2 | PAKISTAN
3 | 2010 | ROYAL DUTCH SHELL PLC |  | PK | 361087 | 2 | PAKISTAN