We asked local Arizona Non-profit Organizations (NPOs) to tell us their most significant problems. The challenge is to match each NPO with their biggest problem, but also distribute NPOs over each problem statement.
We hope you enjoy the following solution and we welcome any suggestions for next time!
For more info on Opportunity Hack, please see opportunity-hack.org
If you don't want to read through our code, here's what we came up with.
import pandas as pd
import numpy as np
from plotly import __version__
from plotly.offline import download_plotlyjs, init_notebook_mode, plot, iplot
import collections
import numpy as np
import cufflinks as cf
cf.set_config_file(theme='ggplot')
Let's look at the raw data
# This file was directly exported from Google Forms, but names and email addresses were removed.
file = "../data/Arizona Opportunity Hack 2018 - Non-profit Survey (Responses) - Form Responses.csv"
df = pd.read_csv(file)
df
Timestamp | Name of your non-profit or government agency | Which of these problems does your company face? [Recruiting volunteers] | Which of these problems does your company face? [Communication to volunteers] | Which of these problems does your company face? [Registration, sign-in, and sign-up forms] | Which of these problems does your company face? [Keeping track of a person’s progress] | Which of these problems does your company face? [Data analysis] | Which of these problems does your company face? [Sending customized emails] | Which of these problems does your company face? [Inventory management system] | Which of these problems does your company face? [Website or social media marketing] | Which of these problems does your company face? [Location finder] | Which of these problems does your company face? [Storing data on people] | Which of these problems does your company face? [Storing data on animals] | Which of these problems does your company face? [Government compliance documentation] | Other problems not listed above | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 3/9/18 8:14 | CO+HOOTS Foundation | 1 - Not a problem for us | 2 - Need help with this | 2 - Need help with this | 1 - Not a problem for us | 1 - Not a problem for us | 2 - Need help with this | 1 - Not a problem for us | 2 - Need help with this | 3 - A big problem for us | 2 - Need help with this | 1 - Not a problem for us | 1 - Not a problem for us | Keeping track of and acknowledging donations |
1 | 3/9/18 9:39 | Be A Leader Foundation | 1 - Not a problem for us | 2 - Need help with this | 1 - Not a problem for us | 2 - Need help with this | 1 - Not a problem for us | 1 - Not a problem for us | 1 - Not a problem for us | 3 - A big problem for us | 1 - Not a problem for us | 3 - A big problem for us | 1 - Not a problem for us | 1 - Not a problem for us | NaN |
2 | 3/9/18 20:21 | Never Again Foundation | 3 - A big problem for us | 3 - A big problem for us | 3 - A big problem for us | 3 - A big problem for us | 3 - A big problem for us | 3 - A big problem for us | 3 - A big problem for us | 3 - A big problem for us | 2 - Need help with this | 3 - A big problem for us | 1 - Not a problem for us | 3 - A big problem for us | NaN |
3 | 3/12/18 20:22 | Unlimited Potential | 2 - Need help with this | 3 - A big problem for us | 3 - A big problem for us | 2 - Need help with this | 3 - A big problem for us | 3 - A big problem for us | 1 - Not a problem for us | 1 - Not a problem for us | 1 - Not a problem for us | 2 - Need help with this | 1 - Not a problem for us | 2 - Need help with this | NaN |
4 | 3/13/18 11:01 | Tempe Community Action Agency | 1 - Not a problem for us | 2 - Need help with this | 3 - A big problem for us | 3 - A big problem for us | 3 - A big problem for us | 1 - Not a problem for us | 1 - Not a problem for us | 3 - A big problem for us | 1 - Not a problem for us | 1 - Not a problem for us | 1 - Not a problem for us | 1 - Not a problem for us | Over 19,000 individuals served by TCAA's five ... |
5 | 3/18/18 14:48 | Swift youth foundation | 1 - Not a problem for us | 2 - Need help with this | 2 - Need help with this | 2 - Need help with this | 3 - A big problem for us | 1 - Not a problem for us | 3 - A big problem for us | 2 - Need help with this | 1 - Not a problem for us | 2 - Need help with this | 1 - Not a problem for us | 1 - Not a problem for us | In depth volunteer scheduling |
6 | 3/20/18 10:42 | Newtown CDC | 1 - Not a problem for us | 1 - Not a problem for us | 1 - Not a problem for us | 2 - Need help with this | 2 - Need help with this | 1 - Not a problem for us | 1 - Not a problem for us | 3 - A big problem for us | 1 - Not a problem for us | 2 - Need help with this | 1 - Not a problem for us | 2 - Need help with this | NaN |
7 | 3/21/18 22:42 | Children's Cancer Network | 1 - Not a problem for us | 2 - Need help with this | 3 - A big problem for us | 2 - Need help with this | 2 - Need help with this | 3 - A big problem for us | 1 - Not a problem for us | 2 - Need help with this | 1 - Not a problem for us | 1 - Not a problem for us | 1 - Not a problem for us | 1 - Not a problem for us | Registration platform with customized fundrais... |
8 | 3/26/18 21:13 | Tranquility Trail Animal Sanctuary | 1 - Not a problem for us | 2 - Need help with this | 3 - A big problem for us | 3 - A big problem for us | 2 - Need help with this | 2 - Need help with this | 1 - Not a problem for us | 1 - Not a problem for us | 1 - Not a problem for us | 3 - A big problem for us | 1 - Not a problem for us | 1 - Not a problem for us | Volunteer Data Management |
9 | 3/27/18 8:11 | Matthew's Crossing Food Bank | 2 - Need help with this | 2 - Need help with this | 2 - Need help with this | 1 - Not a problem for us | 3 - A big problem for us | 2 - Need help with this | 3 - A big problem for us | 2 - Need help with this | 1 - Not a problem for us | 2 - Need help with this | 1 - Not a problem for us | 2 - Need help with this | NaN |
10 | 3/27/18 11:39 | RESCUE | 2 - Need help with this | 1 - Not a problem for us | 1 - Not a problem for us | 1 - Not a problem for us | 1 - Not a problem for us | 3 - A big problem for us | 1 - Not a problem for us | 2 - Need help with this | 1 - Not a problem for us | 1 - Not a problem for us | 1 - Not a problem for us | 1 - Not a problem for us | having general, ongoing IT person for a small ... |
11 | 3/27/18 14:29 | Neurologic Music Therapy Services of Arizona | 1 - Not a problem for us | 1 - Not a problem for us | 1 - Not a problem for us | 3 - A big problem for us | 2 - Need help with this | 2 - Need help with this | 1 - Not a problem for us | 3 - A big problem for us | 1 - Not a problem for us | 2 - Need help with this | 1 - Not a problem for us | 3 - A big problem for us | NaN |
12 | 3/27/18 12:47 | Sharing Down Syndrome Arizona | 2 - Need help with this | 2 - Need help with this | 3 - A big problem for us | 2 - Need help with this | 2 - Need help with this | 1 - Not a problem for us | 2 - Need help with this | 1 - Not a problem for us | 2 - Need help with this | 2 - Need help with this | 1 - Not a problem for us | 1 - Not a problem for us | NaN |
13 | 3/27/18 22:26 | Impact One Breast Cancer Foundation | 1 - Not a problem for us | 1 - Not a problem for us | 2 - Need help with this | 2 - Need help with this | 3 - A big problem for us | 3 - A big problem for us | 2 - Need help with this | 3 - A big problem for us | 2 - Need help with this | 3 - A big problem for us | 1 - Not a problem for us | 1 - Not a problem for us | NaN |
14 | 3/28/18 17:46 | RealTimeSTEAM | 1 - Not a problem for us | 1 - Not a problem for us | 1 - Not a problem for us | 2 - Need help with this | 3 - A big problem for us | 3 - A big problem for us | 1 - Not a problem for us | 2 - Need help with this | 1 - Not a problem for us | 2 - Need help with this | 1 - Not a problem for us | 2 - Need help with this | NaN |
15 | 5/6/18 11:43 | Swift youth foundation | 1 - Not a problem for us | 2 - Need help with this | 1 - Not a problem for us | 2 - Need help with this | 3 - A big problem for us | 1 - Not a problem for us | 3 - A big problem for us | 2 - Need help with this | 1 - Not a problem for us | 2 - Need help with this | 1 - Not a problem for us | 1 - Not a problem for us | Intricate daily Volunteer scheduling |
16 | 6/28/18 22:40 | Arizona Council on Economic Education | 1 - Not a problem for us | 1 - Not a problem for us | 1 - Not a problem for us | 1 - Not a problem for us | 1 - Not a problem for us | 1 - Not a problem for us | 1 - Not a problem for us | 1 - Not a problem for us | 1 - Not a problem for us | 1 - Not a problem for us | 1 - Not a problem for us | 1 - Not a problem for us | We used to have two issues: registration/sign ... |
17 | 6/29/18 4:46 | Arizonans for Children | 2 - Need help with this | 1 - Not a problem for us | 2 - Need help with this | 1 - Not a problem for us | 1 - Not a problem for us | 2 - Need help with this | 2 - Need help with this | 1 - Not a problem for us | 1 - Not a problem for us | 1 - Not a problem for us | 1 - Not a problem for us | 1 - Not a problem for us | NaN |
18 | 7/1/18 9:15 | Cece’s Hope Center | 2 - Need help with this | 2 - Need help with this | 2 - Need help with this | 2 - Need help with this | 2 - Need help with this | 2 - Need help with this | 2 - Need help with this | 2 - Need help with this | 2 - Need help with this | 2 - Need help with this | 3 - A big problem for us | 2 - Need help with this | Tracking board member and volunteer connections |
19 | 7/3/18 17:23 | Neurologic Music Therapy Services of Arizona | 2 - Need help with this | 2 - Need help with this | 2 - Need help with this | 3 - A big problem for us | 3 - A big problem for us | 1 - Not a problem for us | 1 - Not a problem for us | 3 - A big problem for us | 1 - Not a problem for us | 3 - A big problem for us | 1 - Not a problem for us | 3 - A big problem for us | electronic documentation system |
20 | 7/5/18 17:11 | Tranquility Trail Animal Sanctuary | 1 - Not a problem for us | 2 - Need help with this | 3 - A big problem for us | 3 - A big problem for us | 3 - A big problem for us | 2 - Need help with this | 1 - Not a problem for us | 1 - Not a problem for us | 1 - Not a problem for us | 3 - A big problem for us | 1 - Not a problem for us | 1 - Not a problem for us | NaN |
21 | 7/9/18 15:58 | Lost Our Home Pet Rescue | 1 - Not a problem for us | 2 - Need help with this | 1 - Not a problem for us | 1 - Not a problem for us | 3 - A big problem for us | 2 - Need help with this | 2 - Need help with this | 2 - Need help with this | 1 - Not a problem for us | 2 - Need help with this | 2 - Need help with this | 1 - Not a problem for us | Looking for a way to quickly disseminate infor... |
22 | 7/11/18 10:39 | Matthew's Crossing Food Bank | 1 - Not a problem for us | 1 - Not a problem for us | 1 - Not a problem for us | 1 - Not a problem for us | 2 - Need help with this | 1 - Not a problem for us | 2 - Need help with this | 1 - Not a problem for us | 1 - Not a problem for us | 1 - Not a problem for us | 1 - Not a problem for us | 2 - Need help with this | our biggest problem is data systems that don't... |
23 | 8/2/18 15:19 | Dog Rescue & Community Project Accelerator | 3 - A big problem for us | 3 - A big problem for us | 1 - Not a problem for us | 3 - A big problem for us | 2 - Need help with this | 3 - A big problem for us | 3 - A big problem for us | 3 - A big problem for us | 1 - Not a problem for us | 3 - A big problem for us | 3 - A big problem for us | 1 - Not a problem for us | We need help solving a very big problem that w... |
24 | 8/2/18 16:48 | Pan de Vida Foundation | 2 - Need help with this | 3 - A big problem for us | 2 - Need help with this | 3 - A big problem for us | 3 - A big problem for us | 3 - A big problem for us | 3 - A big problem for us | 3 - A big problem for us | 2 - Need help with this | 3 - A big problem for us | 1 - Not a problem for us | 3 - A big problem for us | on line grant writing help. |
25 | 8/2/18 18:53 | LETI Foundation | 3 - A big problem for us | 3 - A big problem for us | 3 - A big problem for us | 3 - A big problem for us | 3 - A big problem for us | 3 - A big problem for us | 3 - A big problem for us | 3 - A big problem for us | 1 - Not a problem for us | 3 - A big problem for us | 1 - Not a problem for us | 1 - Not a problem for us | I would like a way for families to register th... |
26 | 8/3/18 6:45 | Animals (and Humans) in Disaster, Inc (state... | 2 - Need help with this | 2 - Need help with this | 2 - Need help with this | 3 - A big problem for us | 3 - A big problem for us | 1 - Not a problem for us | 3 - A big problem for us | 2 - Need help with this | 2 - Need help with this | 2 - Need help with this | 2 - Need help with this | 2 - Need help with this | a huge problem is a place to keep track of a... |
27 | 8/3/18 10:24 | Paz de Cristo Community Center | 1 - Not a problem for us | 2 - Need help with this | 1 - Not a problem for us | 1 - Not a problem for us | 2 - Need help with this | 2 - Need help with this | 3 - A big problem for us | 3 - A big problem for us | 1 - Not a problem for us | 1 - Not a problem for us | 1 - Not a problem for us | 1 - Not a problem for us | NaN |
28 | 8/27/18 11:57 | House of Refuge | 1 - Not a problem for us | 1 - Not a problem for us | 1 - Not a problem for us | 2 - Need help with this | 1 - Not a problem for us | 1 - Not a problem for us | 1 - Not a problem for us | 1 - Not a problem for us | 1 - Not a problem for us | 1 - Not a problem for us | 1 - Not a problem for us | 1 - Not a problem for us | NaN |
29 | 8/30/18 13:27 | ICM Food & Clothing Bank | 2 - Need help with this | 3 - A big problem for us | 3 - A big problem for us | 3 - A big problem for us | 3 - A big problem for us | 2 - Need help with this | 2 - Need help with this | 2 - Need help with this | 1 - Not a problem for us | 3 - A big problem for us | 1 - Not a problem for us | 2 - Need help with this | NaN |
Can you spot where we did our email campaigns?
df_time = df
df_time["timestamp_obj"] = pd.to_datetime(df_time['Timestamp'])
df_time["week"] = df_time["timestamp_obj"].dt.strftime('Week %U (%b)') # Group by week
import plotly.graph_objs as go
init_notebook_mode(connected=True)
iplot([go.Scatter(x=df_time["timestamp_obj"])] )
There were a couple of NPOs that submitted twice
df.groupby(['Name of your non-profit or government agency'])["Name of your non-profit or government agency"].count().reset_index(name='count').sort_values(['count'], ascending=False).head(6)
Name of your non-profit or government agency | count | |
---|---|---|
13 | Matthew's Crossing Food Bank | 2 |
24 | Tranquility Trail Animal Sanctuary | 2 |
22 | Swift youth foundation | 2 |
14 | Neurologic Music Therapy Services of Arizona | 2 |
1 | Arizona Council on Economic Education | 1 |
23 | Tempe Community Action Agency | 1 |
If we had a duplicate submission from an NPO, keep the last one
df.drop_duplicates(["Name of your non-profit or government agency"], keep="last", inplace=True)
df.groupby(['Name of your non-profit or government agency'])["Name of your non-profit or government agency"].count().reset_index(name='count').sort_values(['count'], ascending=False)
Name of your non-profit or government agency | count | |
---|---|---|
0 | Animals (and Humans) in Disaster, Inc (state... | 1 |
1 | Arizona Council on Economic Education | 1 |
24 | Tranquility Trail Animal Sanctuary | 1 |
23 | Tempe Community Action Agency | 1 |
22 | Swift youth foundation | 1 |
21 | Sharing Down Syndrome Arizona | 1 |
20 | RealTimeSTEAM | 1 |
19 | RESCUE | 1 |
18 | Paz de Cristo Community Center | 1 |
17 | Pan de Vida Foundation | 1 |
16 | Newtown CDC | 1 |
15 | Never Again Foundation | 1 |
14 | Neurologic Music Therapy Services of Arizona | 1 |
13 | Matthew's Crossing Food Bank | 1 |
12 | Lost Our Home Pet Rescue | 1 |
11 | LETI Foundation | 1 |
10 | Impact One Breast Cancer Foundation | 1 |
9 | ICM Food & Clothing Bank | 1 |
8 | House of Refuge | 1 |
7 | Dog Rescue & Community Project Accelerator | 1 |
6 | Children's Cancer Network | 1 |
5 | Cece’s Hope Center | 1 |
4 | CO+HOOTS Foundation | 1 |
3 | Be A Leader Foundation | 1 |
2 | Arizonans for Children | 1 |
25 | Unlimited Potential | 1 |
print("Total number of submissions:", len(df))
Total number of submissions: 26
We also want to consider the free-text "other" problems to see if we can find a home for them. Most of these will give us ideas for future Opportunity Hacks.
pd.options.display.max_colwidth = 15500
from pandas import DataFrame
from IPython.display import HTML
HTML(DataFrame(df[["Name of your non-profit or government agency","Other problems not listed above"]].dropna()).to_html())
Name of your non-profit or government agency | Other problems not listed above | |
---|---|---|
0 | CO+HOOTS Foundation | Keeping track of and acknowledging donations |
4 | Tempe Community Action Agency | Over 19,000 individuals served by TCAA's five departments are tracked separately using a variety of software programs (primarily funder-prescribed/administered systems). TCAA lacks the ability to consolidate this data into a single reporting system to identify and track all individuals served (unduplicated). |
7 | Children's Cancer Network | Registration platform with customized fundraising platform for participants (2300 participants) |
10 | RESCUE | having general, ongoing IT person for a small non-profit is difficult to find. |
15 | Swift youth foundation | Intricate daily Volunteer scheduling |
16 | Arizona Council on Economic Education | We used to have two issues: registration/sign in and data analysis but the last hackathon helped us solve them! |
18 | Cece’s Hope Center | Tracking board member and volunteer connections |
19 | Neurologic Music Therapy Services of Arizona | electronic documentation system |
21 | Lost Our Home Pet Rescue | Looking for a way to quickly disseminate information to rescue partners. Perhaps, through an internet forum with text message notifications based on user preference. |
22 | Matthew's Crossing Food Bank | our biggest problem is data systems that don't talk to each other |
23 | Dog Rescue & Community Project Accelerator | We need help solving a very big problem that will help all small nonprofits get the donated items they need. I have interviewed over 300 nonprofit organizations and this is one of the top 4 needs for all nonprofits, especially smaller nonprofits. We need a system that curates all of the nonprofits' needs and alerts them when a donation is available from a business or individual donor. All small nonprofits are dying on the vine because they cannot get the items they need. Meanwhile, businesses and individuals are throwing perfectly good food & items into the landfill. This destroys our beautiful desert landscape and pollutes our groundwater. We all need a Donate Item Alert System Project. Here is the problem we are trying to solve:\n\n1. We need to create an alert system that lets us know when a business or individual has a donation to pick up. It needs to be quick & easy.\n\n2. We have to alert transportation volunteers that a donation needs to be picked up. A system like Uber would be great so that we know which volunteer took the request and assure that the donation was picked up.\n\n3. We need to track which primary sorting location the item went to.\n\n4. We need to track which nonprofit or school needs the item and where they are located.\n\n5. We need to create efficient systems for tracking which storage locations the donations are stored in.\n\n6. We need to track how many of each items are at each storage space.\n\n7. We need an efficient way of tracking what items need to be moved to different parts of the valley and we need a fuel efficient way of deciding the route for drop off of items to the final nonprofit destination.\n\n8. We need to track who got the donated items for tax write offs and thank yous to donors. |
24 | Pan de Vida Foundation | on line grant writing help. |
25 | LETI Foundation | I would like a way for families to register themselves with a picture of them and their children, be able to also upload DNA /Diagnosis test results |
26 | Animals (and Humans) in Disaster, Inc (state-wide) | a huge problem is a place to keep track of a system for donated items (in-kind) |
import re
df = df.rename(columns=lambda x: re.sub('Which of these problems does your company face\?','',x))
df = df.rename(columns=lambda x: re.sub('[\[\]]','',x))
df = df.rename(columns=lambda x: re.sub('^\s','',x))
df_results = df[["Recruiting volunteers","Communication to volunteers","Registration, sign-in, and sign-up forms","Keeping track of a person’s progress","Data analysis","Sending customized emails","Inventory management system","Website or social media marketing","Location finder","Storing data on people","Storing data on animals","Government compliance documentation"]]
df_results.head(2)
Recruiting volunteers | Communication to volunteers | Registration, sign-in, and sign-up forms | Keeping track of a person’s progress | Data analysis | Sending customized emails | Inventory management system | Website or social media marketing | Location finder | Storing data on people | Storing data on animals | Government compliance documentation | |
---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 1 - Not a problem for us | 2 - Need help with this | 2 - Need help with this | 1 - Not a problem for us | 1 - Not a problem for us | 2 - Need help with this | 1 - Not a problem for us | 2 - Need help with this | 3 - A big problem for us | 2 - Need help with this | 1 - Not a problem for us | 1 - Not a problem for us |
1 | 1 - Not a problem for us | 2 - Need help with this | 1 - Not a problem for us | 2 - Need help with this | 1 - Not a problem for us | 1 - Not a problem for us | 1 - Not a problem for us | 3 - A big problem for us | 1 - Not a problem for us | 3 - A big problem for us | 1 - Not a problem for us | 1 - Not a problem for us |
df_counts = df_results.apply(pd.value_counts)
df_counts
Recruiting volunteers | Communication to volunteers | Registration, sign-in, and sign-up forms | Keeping track of a person’s progress | Data analysis | Sending customized emails | Inventory management system | Website or social media marketing | Location finder | Storing data on people | Storing data on animals | Government compliance documentation | |
---|---|---|---|---|---|---|---|---|---|---|---|---|
1 - Not a problem for us | 14 | 8 | 11 | 7 | 6 | 10 | 12 | 7 | 19 | 8 | 22 | 16 |
2 - Need help with this | 9 | 12 | 7 | 10 | 7 | 7 | 7 | 9 | 6 | 9 | 2 | 7 |
3 - A big problem for us | 3 | 6 | 8 | 9 | 13 | 9 | 7 | 10 | 1 | 9 | 2 | 3 |
df_counts.iplot(kind="bar")
Sort the data by severity
df_counts.transpose().sort_values(by=["1 - Not a problem for us", "2 - Need help with this", "3 - A big problem for us"]).iplot(kind="bar")
df_score = df_counts.transpose()
df_score["score"] = df_score["1 - Not a problem for us"]*.1 + df_score["2 - Need help with this"]*0.3 + df_score["3 - A big problem for us"]*.6
df_score = df_score.sort_values(by="score", ascending=False)
df_score["cumulative_sum"] = df_score.score.cumsum()
df_score['cumulative_perc'] = round(100*df_score.cumulative_sum/df_score.score.sum(),1)
df_score = df_score.reset_index()
df_score
index | 1 - Not a problem for us | 2 - Need help with this | 3 - A big problem for us | score | cumulative_sum | cumulative_perc | |
---|---|---|---|---|---|---|---|
0 | Data analysis | 6 | 7 | 13 | 10.5 | 10.5 | 11.7 |
1 | Website or social media marketing | 7 | 9 | 10 | 9.4 | 19.9 | 22.2 |
2 | Keeping track of a person’s progress | 7 | 10 | 9 | 9.1 | 29.0 | 32.4 |
3 | Storing data on people | 8 | 9 | 9 | 8.9 | 37.9 | 42.3 |
4 | Sending customized emails | 10 | 7 | 9 | 8.5 | 46.4 | 51.8 |
5 | Registration, sign-in, and sign-up forms | 11 | 7 | 8 | 8.0 | 54.4 | 60.7 |
6 | Communication to volunteers | 8 | 12 | 6 | 8.0 | 62.4 | 69.6 |
7 | Inventory management system | 12 | 7 | 7 | 7.5 | 69.9 | 78.0 |
8 | Recruiting volunteers | 14 | 9 | 3 | 5.9 | 75.8 | 84.6 |
9 | Government compliance documentation | 16 | 7 | 3 | 5.5 | 81.3 | 90.7 |
10 | Location finder | 19 | 6 | 1 | 4.3 | 85.6 | 95.5 |
11 | Storing data on animals | 22 | 2 | 2 | 4.0 | 89.6 | 100.0 |
df_score.sort_values(by="score", ascending=False)["score"].iplot(kind="bar")
target_areas = df_score[df_score["score"] >= 7.0]["index"]
# Remove some text from the original column names to make it easier to read
df = df.rename(columns=lambda x: re.sub('Which of these problems does your company face\?','',x))
df = df.rename(columns=lambda x: re.sub('[\[\]]','',x))
df = df.rename(columns=lambda x: re.sub('^\s','',x))
target_areas
0 Data analysis 1 Website or social media marketing 2 Keeping track of a person’s progress 3 Storing data on people 4 Sending customized emails 5 Registration, sign-in, and sign-up forms 6 Communication to volunteers 7 Inventory management system Name: index, dtype: object
# Grab the list of categories that made the cut
target_categories = target_areas.tolist()
# Add the columns for the other info we want to grab
target_categories.append("Name of your non-profit or government agency")
# Define our list by passing in the columns we want from the original data
target_npos = df[target_categories]
target_npos.head(2)
Data analysis | Website or social media marketing | Keeping track of a person’s progress | Storing data on people | Sending customized emails | Registration, sign-in, and sign-up forms | Communication to volunteers | Inventory management system | Name of your non-profit or government agency | |
---|---|---|---|---|---|---|---|---|---|
0 | 1 - Not a problem for us | 2 - Need help with this | 1 - Not a problem for us | 2 - Need help with this | 2 - Need help with this | 2 - Need help with this | 2 - Need help with this | 1 - Not a problem for us | CO+HOOTS Foundation |
1 | 1 - Not a problem for us | 3 - A big problem for us | 2 - Need help with this | 3 - A big problem for us | 1 - Not a problem for us | 1 - Not a problem for us | 2 - Need help with this | 1 - Not a problem for us | Be A Leader Foundation |
b = []
for index, row in target_npos.iterrows():
# Check all of the winning problem statements (e.g. Data analysis)
for c in target_areas:
# Ignore things that are not problems for an NPO
if row[c] != "1 - Not a problem for us":
score = .3 if row[c] == "2 - Need help with this" else .6
d = {
"Problem Statement" : c,
"NPO" : row["Name of your non-profit or government agency"],
"Score" : score
}
s = pd.Series(d)
b.append(s)
basket = pd.DataFrame(b)
basket.index = np.arange(1, len(basket) + 1) # Make the table below start at 1 instead of 0
# Give priority to the people who said things were big problems
basket = basket.sort_values(by="Score", ascending=False)
basket.head(7)
NPO | Problem Statement | Score | |
---|---|---|---|
119 | Animals (and Humans) in Disaster, Inc (state... | Data analysis | 0.6 |
76 | Neurologic Music Therapy Services of Arizona | Data analysis | 0.6 |
113 | LETI Foundation | Keeping track of a person’s progress | 0.6 |
114 | LETI Foundation | Storing data on people | 0.6 |
54 | RealTimeSTEAM | Data analysis | 0.6 |
115 | LETI Foundation | Sending customized emails | 0.6 |
116 | LETI Foundation | Registration, sign-in, and sign-up forms | 0.6 |
# We're going to maintain a counter of how many times an NPO gets selected
df_counter = pd.DataFrame(columns=["NPO", "count"])
# Get the unique set of NPOs
df_counter["NPO"] = basket['NPO'].unique()
# Initialize all to 0
df_counter["count"] = 0
# Use this to keep track of how the problems are mapped to NPOs
selection = {}
def all_npos_accounted_for():
min_count = min(df_counter["count"])
# If everyone is accounted for 1 time, then all NPOs are accounted for
if min_count == 1:
return True
else:
return False
def npo_maxed_out(npo, max_times = 1):
# Look through all problem statements and ensure that the NPO is not in more than 2
items = [v for key, v in selection.items()]
counter = 0
for it in items:
for i in it:
# Increase counter if we get a hit
if npo == i:
counter = counter + 1
if counter == max_times:
#print("Not adding since NPO: " + npo + " already at max capacity (" + str(max_times) + ")")
return True
return False
def max_npos_per_problem(problem_statement, max_npos_per_problem = 3):
return len(selection[problem_statement]) < max_npos_per_problem
def add_to_selection(problem_statement, npo):
# Handle the first time we add an NPO
if problem_statement not in selection:
selection[problem_statement] = []
if max_npos_per_problem(problem_statement) and not npo_maxed_out(npo):
selection[problem_statement].append(npo)
df_counter.loc[df_counter["NPO"] == npo, "count"] = df_counter[df_counter["NPO"] == npo]["count"] + 1
# Start with the most import problem statement first
done = False
counter = 0
while not done:
done = all_npos_accounted_for()
counter = counter + 1
done = True if counter == 100 else done #Super-hacky, but if we don't have all NPOs by now, break
# Get the NPOs with this problem sorted by impact
for index, score_row in df_score.iterrows():
problem = score_row["index"]
# Get all NPOs for this problem
for idx, row in basket[basket["Problem Statement"] == problem].iterrows():
add_to_selection(row["Problem Statement"], row["NPO"])
# Make the output readable
f_selection = pd.DataFrame.from_dict(selection, orient='index').transpose()
f_selection.index = np.arange(1, len(f_selection) + 1) # Print out starting at index of 1 instead of 0
# Add scores to each column
for c in f_selection.columns:
if len(df_score[df_score["index"].str.contains(c)]) > 0:
score = str(round(df_score[df_score["index"] == c]["score"].values[0],1)).zfill(5)
f_selection = f_selection.rename(index=str, columns={c: score + " " + c})
# Sort by scores
f_selection = f_selection.reindex(sorted(f_selection.columns, reverse=True), axis=1)
# Remove leading zeros that are needed for sorting
f_selection = f_selection.rename(columns=lambda x: re.sub(r'\b0+(.*)', r'\1', x))
# Manually add NPOs that didn't get placed
d = {
"9.4 Website or social media marketing":"Newtown CDC",
"9.1 Keeping track of a person’s progress":"House of Refuge"
}
f_selection = f_selection.append(pd.Series(d), ignore_index=True ).replace(np.nan, "None", regex=True)
f_selection
10.5 Data analysis | 9.4 Website or social media marketing | 9.1 Keeping track of a person’s progress | 8.9 Storing data on people | 8.5 Sending customized emails | 8.0 Registration, sign-in, and sign-up forms | 8.0 Communication to volunteers | 7.5 Inventory management system | |
---|---|---|---|---|---|---|---|---|
0 | Animals (and Humans) in Disaster, Inc (state... | Impact One Breast Cancer Foundation | Dog Rescue & Community Project Accelerator | Be A Leader Foundation | RESCUE | Sharing Down Syndrome Arizona | Paz de Cristo Community Center | Matthew's Crossing Food Bank |
1 | Neurologic Music Therapy Services of Arizona | LETI Foundation | Never Again Foundation | Tranquility Trail Animal Sanctuary | Unlimited Potential | Tempe Community Action Agency | Cece’s Hope Center | Arizonans for Children |
2 | RealTimeSTEAM | Pan de Vida Foundation | ICM Food & Clothing Bank | Lost Our Home Pet Rescue | Children's Cancer Network | CO+HOOTS Foundation | Swift youth foundation | None |
3 | None | Newtown CDC | House of Refuge | None | None | None | None | None |
Arizona Council on Economic Education (ACEE) didn't have any problems, so this data looks right
print("The table above includes",len(collections.Counter(f_selection.values.flatten())),"non-profits")
print("\nWe are missing these non-profits from the table:")
print("\n".join(df[~df["Name of your non-profit or government agency"].isin(f_selection.values.flatten())]["Name of your non-profit or government agency"].values))
The table above includes 26 non-profits We are missing these non-profits from the table: Arizona Council on Economic Education
# How many times does each NPO show up? - Should only be once
collections.Counter(f_selection.values.flatten()).most_common()
[('None', 7), ('ICM Food & Clothing Bank', 1), ('Swift youth foundation ', 1), ('Tempe Community Action Agency', 1), ('LETI Foundation', 1), ('Lost Our Home Pet Rescue', 1), ("Children's Cancer Network", 1), ('CO+HOOTS Foundation', 1), ('RESCUE', 1), ('RealTimeSTEAM', 1), ('Sharing Down Syndrome Arizona', 1), ('Pan de Vida Foundation', 1), ('Newtown CDC', 1), ('Dog Rescue & Community Project Accelerator', 1), ('Never Again Foundation', 1), ('Be A Leader Foundation', 1), ('House of Refuge', 1), ('Paz de Cristo Community Center', 1), ('Cece’s Hope Center', 1), ("Matthew's Crossing Food Bank", 1), ('Tranquility Trail Animal Sanctuary ', 1), ('Arizonans for Children', 1), ('Neurologic Music Therapy Services of Arizona', 1), ('Unlimited Potential ', 1), ('Impact One Breast Cancer Foundation', 1), ('Animals (and Humans) in Disaster, Inc (state-wide)', 1)]
Make sure that the NPOs actually ranked their matched problem as problem
for r in f_selection:
print(r)
# Remove all numbers so we can use this as the index
problem = re.sub('[\d\.]+','',r).strip()
npos = f_selection[r]
s = pd.Series()
for n in npos:
if n != None and n != "None":
survey_submission = df[df["Name of your non-profit or government agency"] == n][problem].values[0]
print("\t%s: %s" % (n,survey_submission))
10.5 Data analysis Animals (and Humans) in Disaster, Inc (state-wide): 3 - A big problem for us Neurologic Music Therapy Services of Arizona: 3 - A big problem for us RealTimeSTEAM: 3 - A big problem for us 9.4 Website or social media marketing Impact One Breast Cancer Foundation: 3 - A big problem for us LETI Foundation: 3 - A big problem for us Pan de Vida Foundation: 3 - A big problem for us Newtown CDC: 3 - A big problem for us 9.1 Keeping track of a person’s progress Dog Rescue & Community Project Accelerator: 3 - A big problem for us Never Again Foundation: 3 - A big problem for us ICM Food & Clothing Bank: 3 - A big problem for us House of Refuge: 2 - Need help with this 8.9 Storing data on people Be A Leader Foundation: 3 - A big problem for us Tranquility Trail Animal Sanctuary : 3 - A big problem for us Lost Our Home Pet Rescue: 2 - Need help with this 8.5 Sending customized emails RESCUE: 3 - A big problem for us Unlimited Potential : 3 - A big problem for us Children's Cancer Network: 3 - A big problem for us 8.0 Registration, sign-in, and sign-up forms Sharing Down Syndrome Arizona: 3 - A big problem for us Tempe Community Action Agency: 3 - A big problem for us CO+HOOTS Foundation: 2 - Need help with this 8.0 Communication to volunteers Paz de Cristo Community Center: 2 - Need help with this Cece’s Hope Center: 2 - Need help with this Swift youth foundation : 2 - Need help with this 7.5 Inventory management system Matthew's Crossing Food Bank: 2 - Need help with this Arizonans for Children: 2 - Need help with this