EDIT: Using advanced search in Excel (under data tab) I have been able to create a list of unique company names, and am now able to SUMIF based on the cell containing the companies name!

Disclaimer: Any python solutions would be greatly appreciated as well, pandas specifically!

I have 60,000 rows of data, containing information about grants awarded to companies.

[![enter image description here](https://i.stack.imgur.com/gDnd5.png)](https://i.stack.imgur.com/gDnd5.png)

I am planning on creating a python dictionary to store each unique company name, with their total grant $ given (agreemen\_2), and location coordinates. Then, I want to display this using Dash (Plotly) on a live MapBox map of Canada.

First thing first, how do I calculate and store the total value that was awarded to each company?

I have seen SUMIF in other solutions, but am unsure how to output this to a new column, if that makes sense.

One potential solution I thought was to create a new column of unique company names, and next to it SUMIF all the appropriate cells in col D.

**PYTHON STUFF SO FAR**

So with the below code, I take a much messier looking spreadsheet, drop duplicates, sort based on company name, and create a new pandas database with the relevant data columns:

corp\_df is the cleaned up new dataframe that I want to work with.

and recipien\_4 is the companies unique ID number, as you can see it repeats with each grant awarded. Folia Biotech in the screenshot shows a duplicate grant, as proven with a column i did not include in the screenshot. There are quite a few duplicates, as seen in the screenshot.

```
import pandas as pd

in_file = '2019-20 Grants and Contributions.csv'

# create dataframe 
df = pd.read_csv(in_file)

# sort in order of agreemen_1
df.sort_values("recipien_2", inplace = True)

# remove duplicates
df.drop_duplicates(subset='agreemen_1', keep='first', inplace=True)

corp_dict = { }

# creates empty dict with only 1 copy of all corporation names, all values of 0
for name in corp_df_2['recipien_2']:
    if name not in corp_dict:
        corp_dict[name] = 0

# full name, id, grant $, longitude, latitude
corp_df = df[['recipien_2', 'recipien_4', 'agreemen_2','longitude','latitude']]

```

any tips or tricks would be greatly appreciated, .ittertuples() didn't seem like a good solution as I am unsure how to filter and compare data, or if datatypes are preserved. But feel free to prove me wrong haha.

I thought perhaps there was a better way to tackle this problem, straight in Excel vs. iterating through rows of a pandas dataframe. This is a pretty open question so thank you for any help or direction you think is best!