Subscriber Extra Info_V1.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import psycopg2\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Connect to PostgeSQL\n",
    "\n",
    "try:\n",
    "    conn = psycopg2.connect(user = \"pbiusr1\",\n",
    "                                  password = \"RE_^V%Gj@EL6R!G\",\n",
    "                                  host = \"app.brightbytext.org\",\n",
    "                                  port = \"5432\",\n",
    "                                  database = \"bbtapi\")\n",
    "\n",
    "except (Exception, psycopg2.Error) as error :\n",
    "    print (\"Error while connecting to PostgreSQL\", error)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Subscriber Extra Information "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "sql = '''\n",
    "-- subsriber attribute\n",
    "select distinct s.id as subscriber,\n",
    "sv.title as survey_name,\n",
    "qt.title as question,\n",
    "--ot.title as option,\n",
    "a.value  as answer_value\n",
    "from public.sg_responses as r\n",
    "---- survey\n",
    "join public.surveys as sv\n",
    "on sv.id = r.survey_id\n",
    "and sv.sg_id = r.sg_survey_id \n",
    "--- question\n",
    "join public.sg_questions as q\n",
    "on q.sg_survey_id = sv.sg_id \n",
    "--- question title\n",
    "join public.sg_question_titles as qt\n",
    "on q.id =qt.question_id\n",
    "\n",
    "--- option\n",
    "--join public.sg_options as o\n",
    "--on o.question_id  = q.id \n",
    "\n",
    "---- option title\n",
    "--join public.sg_option_titles as ot \n",
    "--on o.id = ot.option_id \n",
    "\n",
    "---- subscriber\n",
    "join public.subscribers s\n",
    "on s.id = r.subscriber_id\n",
    "--- answer\n",
    "join public.sg_answers as a\n",
    "on r.id = a.response_id\n",
    "and a.question_id = q.id\n",
    "\n",
    "\n",
    "where qt.language_id = 1;\n",
    "'''\n",
    "extrainfo = pd.read_sql_query(sql, conn)\n",
    "conn = None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>subscriber</th>\n",
       "      <th>survey_name</th>\n",
       "      <th>question</th>\n",
       "      <th>answer_value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00029a87-e949-44e8-beaa-b9680f5e55c7</td>\n",
       "      <td>Program Message of Day Survey</td>\n",
       "      <td>When would you prefer to receive BBT program m...</td>\n",
       "      <td>Between 11am and 2pm</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0005eafe-c416-4226-af97-7d3a2bf7636f</td>\n",
       "      <td>Bright By Text Feedback</td>\n",
       "      <td>Are the messages you receive from BBT relevant...</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0005eafe-c416-4226-af97-7d3a2bf7636f</td>\n",
       "      <td>Bright By Text Feedback</td>\n",
       "      <td>Has BBT helped you feel more confident/informe...</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0005eafe-c416-4226-af97-7d3a2bf7636f</td>\n",
       "      <td>Bright By Text Feedback</td>\n",
       "      <td>Have you attended a local event as a result of...</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0005eafe-c416-4226-af97-7d3a2bf7636f</td>\n",
       "      <td>Bright By Text Feedback</td>\n",
       "      <td>Have you received a BBT message about a local ...</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0005eafe-c416-4226-af97-7d3a2bf7636f</td>\n",
       "      <td>Bright By Text Feedback</td>\n",
       "      <td>Have you recommended BBT to others?</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0005eafe-c416-4226-af97-7d3a2bf7636f</td>\n",
       "      <td>Bright By Text Feedback</td>\n",
       "      <td>How likely would you be to recommend BBT on a ...</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>0005eafe-c416-4226-af97-7d3a2bf7636f</td>\n",
       "      <td>Bright By Text Feedback</td>\n",
       "      <td>If BBT were able to help you track your child'...</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>0005eafe-c416-4226-af97-7d3a2bf7636f</td>\n",
       "      <td>Bright By Text Feedback</td>\n",
       "      <td>What do you like most about BBT?</td>\n",
       "      <td>The parent child interactive play</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>0005eafe-c416-4226-af97-7d3a2bf7636f</td>\n",
       "      <td>Getting to Know You</td>\n",
       "      <td>Affordable Housing</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>0005eafe-c416-4226-af97-7d3a2bf7636f</td>\n",
       "      <td>Getting to Know You</td>\n",
       "      <td>Healthcare</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>0005eafe-c416-4226-af97-7d3a2bf7636f</td>\n",
       "      <td>Getting to Know You</td>\n",
       "      <td>How did you hear about Bright by Text (BBT)?</td>\n",
       "      <td>PBS Programming</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>0005eafe-c416-4226-af97-7d3a2bf7636f</td>\n",
       "      <td>Getting to Know You</td>\n",
       "      <td>How many children do you have/care for under t...</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>0005eafe-c416-4226-af97-7d3a2bf7636f</td>\n",
       "      <td>Getting to Know You</td>\n",
       "      <td>Quality Childcare</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>0005eafe-c416-4226-af97-7d3a2bf7636f</td>\n",
       "      <td>Getting to Know You</td>\n",
       "      <td>Reliable Transportation</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>0005eafe-c416-4226-af97-7d3a2bf7636f</td>\n",
       "      <td>Getting to Know You</td>\n",
       "      <td>Supportive family and friends</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>0005eafe-c416-4226-af97-7d3a2bf7636f</td>\n",
       "      <td>Getting to Know You</td>\n",
       "      <td>What is your family's annual household income?</td>\n",
       "      <td>Under $50,000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>0005eafe-c416-4226-af97-7d3a2bf7636f</td>\n",
       "      <td>Getting to Know You</td>\n",
       "      <td>What is your relationship to the child(ren) yo...</td>\n",
       "      <td>Parent</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>0005eafe-c416-4226-af97-7d3a2bf7636f</td>\n",
       "      <td>Profile Builder</td>\n",
       "      <td>Are you interested in helping improve Bright b...</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>0005eafe-c416-4226-af97-7d3a2bf7636f</td>\n",
       "      <td>Profile Builder</td>\n",
       "      <td>Email</td>\n",
       "      <td>janszdee@gmail.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>0005eafe-c416-4226-af97-7d3a2bf7636f</td>\n",
       "      <td>Profile Builder</td>\n",
       "      <td>How likely are you to recommend Bright by Text...</td>\n",
       "      <td>Very likely</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>0005eafe-c416-4226-af97-7d3a2bf7636f</td>\n",
       "      <td>Profile Builder</td>\n",
       "      <td>Is your phone the primary device you use to ac...</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>0005eafe-c416-4226-af97-7d3a2bf7636f</td>\n",
       "      <td>Profile Builder</td>\n",
       "      <td>What is the primary language you speak at home?</td>\n",
       "      <td>English</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>0005eafe-c416-4226-af97-7d3a2bf7636f</td>\n",
       "      <td>Profile Builder</td>\n",
       "      <td>What is your relationship to the child for who...</td>\n",
       "      <td>Parent</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>0005eafe-c416-4226-af97-7d3a2bf7636f</td>\n",
       "      <td>Profile Builder</td>\n",
       "      <td>What type of health insurance does the child h...</td>\n",
       "      <td>Medicaid or CHIP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>0006ed40-7821-459d-a68a-8748ae91f136</td>\n",
       "      <td>Profile Builder</td>\n",
       "      <td>As a result of Bright by Text I know more abou...</td>\n",
       "      <td>Somewhat agree</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>0006ed40-7821-459d-a68a-8748ae91f136</td>\n",
       "      <td>Profile Builder</td>\n",
       "      <td>Considering your total experience with Bright ...</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>0006ed40-7821-459d-a68a-8748ae91f136</td>\n",
       "      <td>Profile Builder</td>\n",
       "      <td>In the past 30 days, have you done any of the ...</td>\n",
       "      <td>Play together</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>0006ed40-7821-459d-a68a-8748ae91f136</td>\n",
       "      <td>Profile Builder</td>\n",
       "      <td>In the past 30 days, have you done any of the ...</td>\n",
       "      <td>Work on numbers, shapes, or other math skills</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>0006ed40-7821-459d-a68a-8748ae91f136</td>\n",
       "      <td>Profile Builder</td>\n",
       "      <td>What is the primary language you speak at home?</td>\n",
       "      <td>English</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>0006ed40-7821-459d-a68a-8748ae91f136</td>\n",
       "      <td>Profile Builder</td>\n",
       "      <td>What is your relationship to the child for who...</td>\n",
       "      <td>Parent</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>0006ed40-7821-459d-a68a-8748ae91f136</td>\n",
       "      <td>Profile Builder</td>\n",
       "      <td>What type of health insurance does the child h...</td>\n",
       "      <td>Private (through parent/caregiver)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>00110b62-0019-408d-b8f9-e6fd586055aa</td>\n",
       "      <td>Profile Builder</td>\n",
       "      <td>As a result of Bright by Text I know more abou...</td>\n",
       "      <td>Strongly agree</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>00110b62-0019-408d-b8f9-e6fd586055aa</td>\n",
       "      <td>Profile Builder</td>\n",
       "      <td>Considering your total experience with Bright ...</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>00110b62-0019-408d-b8f9-e6fd586055aa</td>\n",
       "      <td>Profile Builder</td>\n",
       "      <td>In the past 30 days, have you done any of the ...</td>\n",
       "      <td>Attend a local event</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>00110b62-0019-408d-b8f9-e6fd586055aa</td>\n",
       "      <td>Profile Builder</td>\n",
       "      <td>In the past 30 days, have you done any of the ...</td>\n",
       "      <td>Explore science, such as observing nature or w...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>00110b62-0019-408d-b8f9-e6fd586055aa</td>\n",
       "      <td>Profile Builder</td>\n",
       "      <td>In the past 30 days, have you done any of the ...</td>\n",
       "      <td>Find fun ways to put learning into everyday ac...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>00110b62-0019-408d-b8f9-e6fd586055aa</td>\n",
       "      <td>Profile Builder</td>\n",
       "      <td>In the past 30 days, have you done any of the ...</td>\n",
       "      <td>Other</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>00110b62-0019-408d-b8f9-e6fd586055aa</td>\n",
       "      <td>Profile Builder</td>\n",
       "      <td>In the past 30 days, have you done any of the ...</td>\n",
       "      <td>Play together</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>00110b62-0019-408d-b8f9-e6fd586055aa</td>\n",
       "      <td>Profile Builder</td>\n",
       "      <td>In the past 30 days, have you done any of the ...</td>\n",
       "      <td>Read or tell stories</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>00110b62-0019-408d-b8f9-e6fd586055aa</td>\n",
       "      <td>Profile Builder</td>\n",
       "      <td>In the past 30 days, have you done any of the ...</td>\n",
       "      <td>Watch an educational video or play a digital g...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>00110b62-0019-408d-b8f9-e6fd586055aa</td>\n",
       "      <td>Profile Builder</td>\n",
       "      <td>In the past 30 days, have you done any of the ...</td>\n",
       "      <td>Work on numbers, shapes, or other math skills</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>00110b62-0019-408d-b8f9-e6fd586055aa</td>\n",
       "      <td>Profile Builder</td>\n",
       "      <td>In the past 30 days, have you done any of the ...</td>\n",
       "      <td>Work on reading or writing skills</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>00110b62-0019-408d-b8f9-e6fd586055aa</td>\n",
       "      <td>Profile Builder</td>\n",
       "      <td>What is the primary language you speak at home?</td>\n",
       "      <td>English</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>00110b62-0019-408d-b8f9-e6fd586055aa</td>\n",
       "      <td>Profile Builder</td>\n",
       "      <td>What is your relationship to the child for who...</td>\n",
       "      <td>Parent</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>00110b62-0019-408d-b8f9-e6fd586055aa</td>\n",
       "      <td>Profile Builder</td>\n",
       "      <td>What type of health insurance does the child h...</td>\n",
       "      <td>Medicaid or CHIP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>001489b1-5dd5-4f42-8ed2-3db1b420cc82</td>\n",
       "      <td>Bright by Text Feedback</td>\n",
       "      <td>Does your community provide supportive program...</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>001489b1-5dd5-4f42-8ed2-3db1b420cc82</td>\n",
       "      <td>Bright by Text Feedback</td>\n",
       "      <td>Do you feel overwhelmed by the responsibility ...</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>001489b1-5dd5-4f42-8ed2-3db1b420cc82</td>\n",
       "      <td>Bright by Text Feedback</td>\n",
       "      <td>Has Bright by Text helped you feel more confid...</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>001489b1-5dd5-4f42-8ed2-3db1b420cc82</td>\n",
       "      <td>Bright by Text Feedback</td>\n",
       "      <td>Has Bright by Text prompted you to do any of t...</td>\n",
       "      <td>Other</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              subscriber                    survey_name  \\\n",
       "0   00029a87-e949-44e8-beaa-b9680f5e55c7  Program Message of Day Survey   \n",
       "1   0005eafe-c416-4226-af97-7d3a2bf7636f        Bright By Text Feedback   \n",
       "2   0005eafe-c416-4226-af97-7d3a2bf7636f        Bright By Text Feedback   \n",
       "3   0005eafe-c416-4226-af97-7d3a2bf7636f        Bright By Text Feedback   \n",
       "4   0005eafe-c416-4226-af97-7d3a2bf7636f        Bright By Text Feedback   \n",
       "5   0005eafe-c416-4226-af97-7d3a2bf7636f        Bright By Text Feedback   \n",
       "6   0005eafe-c416-4226-af97-7d3a2bf7636f        Bright By Text Feedback   \n",
       "7   0005eafe-c416-4226-af97-7d3a2bf7636f        Bright By Text Feedback   \n",
       "8   0005eafe-c416-4226-af97-7d3a2bf7636f        Bright By Text Feedback   \n",
       "9   0005eafe-c416-4226-af97-7d3a2bf7636f            Getting to Know You   \n",
       "10  0005eafe-c416-4226-af97-7d3a2bf7636f            Getting to Know You   \n",
       "11  0005eafe-c416-4226-af97-7d3a2bf7636f            Getting to Know You   \n",
       "12  0005eafe-c416-4226-af97-7d3a2bf7636f            Getting to Know You   \n",
       "13  0005eafe-c416-4226-af97-7d3a2bf7636f            Getting to Know You   \n",
       "14  0005eafe-c416-4226-af97-7d3a2bf7636f            Getting to Know You   \n",
       "15  0005eafe-c416-4226-af97-7d3a2bf7636f            Getting to Know You   \n",
       "16  0005eafe-c416-4226-af97-7d3a2bf7636f            Getting to Know You   \n",
       "17  0005eafe-c416-4226-af97-7d3a2bf7636f            Getting to Know You   \n",
       "18  0005eafe-c416-4226-af97-7d3a2bf7636f                Profile Builder   \n",
       "19  0005eafe-c416-4226-af97-7d3a2bf7636f                Profile Builder   \n",
       "20  0005eafe-c416-4226-af97-7d3a2bf7636f                Profile Builder   \n",
       "21  0005eafe-c416-4226-af97-7d3a2bf7636f                Profile Builder   \n",
       "22  0005eafe-c416-4226-af97-7d3a2bf7636f                Profile Builder   \n",
       "23  0005eafe-c416-4226-af97-7d3a2bf7636f                Profile Builder   \n",
       "24  0005eafe-c416-4226-af97-7d3a2bf7636f                Profile Builder   \n",
       "25  0006ed40-7821-459d-a68a-8748ae91f136                Profile Builder   \n",
       "26  0006ed40-7821-459d-a68a-8748ae91f136                Profile Builder   \n",
       "27  0006ed40-7821-459d-a68a-8748ae91f136                Profile Builder   \n",
       "28  0006ed40-7821-459d-a68a-8748ae91f136                Profile Builder   \n",
       "29  0006ed40-7821-459d-a68a-8748ae91f136                Profile Builder   \n",
       "30  0006ed40-7821-459d-a68a-8748ae91f136                Profile Builder   \n",
       "31  0006ed40-7821-459d-a68a-8748ae91f136                Profile Builder   \n",
       "32  00110b62-0019-408d-b8f9-e6fd586055aa                Profile Builder   \n",
       "33  00110b62-0019-408d-b8f9-e6fd586055aa                Profile Builder   \n",
       "34  00110b62-0019-408d-b8f9-e6fd586055aa                Profile Builder   \n",
       "35  00110b62-0019-408d-b8f9-e6fd586055aa                Profile Builder   \n",
       "36  00110b62-0019-408d-b8f9-e6fd586055aa                Profile Builder   \n",
       "37  00110b62-0019-408d-b8f9-e6fd586055aa                Profile Builder   \n",
       "38  00110b62-0019-408d-b8f9-e6fd586055aa                Profile Builder   \n",
       "39  00110b62-0019-408d-b8f9-e6fd586055aa                Profile Builder   \n",
       "40  00110b62-0019-408d-b8f9-e6fd586055aa                Profile Builder   \n",
       "41  00110b62-0019-408d-b8f9-e6fd586055aa                Profile Builder   \n",
       "42  00110b62-0019-408d-b8f9-e6fd586055aa                Profile Builder   \n",
       "43  00110b62-0019-408d-b8f9-e6fd586055aa                Profile Builder   \n",
       "44  00110b62-0019-408d-b8f9-e6fd586055aa                Profile Builder   \n",
       "45  00110b62-0019-408d-b8f9-e6fd586055aa                Profile Builder   \n",
       "46  001489b1-5dd5-4f42-8ed2-3db1b420cc82        Bright by Text Feedback   \n",
       "47  001489b1-5dd5-4f42-8ed2-3db1b420cc82        Bright by Text Feedback   \n",
       "48  001489b1-5dd5-4f42-8ed2-3db1b420cc82        Bright by Text Feedback   \n",
       "49  001489b1-5dd5-4f42-8ed2-3db1b420cc82        Bright by Text Feedback   \n",
       "\n",
       "                                             question  \\\n",
       "0   When would you prefer to receive BBT program m...   \n",
       "1   Are the messages you receive from BBT relevant...   \n",
       "2   Has BBT helped you feel more confident/informe...   \n",
       "3   Have you attended a local event as a result of...   \n",
       "4   Have you received a BBT message about a local ...   \n",
       "5                 Have you recommended BBT to others?   \n",
       "6   How likely would you be to recommend BBT on a ...   \n",
       "7   If BBT were able to help you track your child'...   \n",
       "8                    What do you like most about BBT?   \n",
       "9                                  Affordable Housing   \n",
       "10                                         Healthcare   \n",
       "11       How did you hear about Bright by Text (BBT)?   \n",
       "12  How many children do you have/care for under t...   \n",
       "13                                  Quality Childcare   \n",
       "14                            Reliable Transportation   \n",
       "15                      Supportive family and friends   \n",
       "16     What is your family's annual household income?   \n",
       "17  What is your relationship to the child(ren) yo...   \n",
       "18  Are you interested in helping improve Bright b...   \n",
       "19                                              Email   \n",
       "20  How likely are you to recommend Bright by Text...   \n",
       "21  Is your phone the primary device you use to ac...   \n",
       "22    What is the primary language you speak at home?   \n",
       "23  What is your relationship to the child for who...   \n",
       "24  What type of health insurance does the child h...   \n",
       "25  As a result of Bright by Text I know more abou...   \n",
       "26  Considering your total experience with Bright ...   \n",
       "27  In the past 30 days, have you done any of the ...   \n",
       "28  In the past 30 days, have you done any of the ...   \n",
       "29    What is the primary language you speak at home?   \n",
       "30  What is your relationship to the child for who...   \n",
       "31  What type of health insurance does the child h...   \n",
       "32  As a result of Bright by Text I know more abou...   \n",
       "33  Considering your total experience with Bright ...   \n",
       "34  In the past 30 days, have you done any of the ...   \n",
       "35  In the past 30 days, have you done any of the ...   \n",
       "36  In the past 30 days, have you done any of the ...   \n",
       "37  In the past 30 days, have you done any of the ...   \n",
       "38  In the past 30 days, have you done any of the ...   \n",
       "39  In the past 30 days, have you done any of the ...   \n",
       "40  In the past 30 days, have you done any of the ...   \n",
       "41  In the past 30 days, have you done any of the ...   \n",
       "42  In the past 30 days, have you done any of the ...   \n",
       "43    What is the primary language you speak at home?   \n",
       "44  What is your relationship to the child for who...   \n",
       "45  What type of health insurance does the child h...   \n",
       "46  Does your community provide supportive program...   \n",
       "47  Do you feel overwhelmed by the responsibility ...   \n",
       "48  Has Bright by Text helped you feel more confid...   \n",
       "49  Has Bright by Text prompted you to do any of t...   \n",
       "\n",
       "                                         answer_value  \n",
       "0                                Between 11am and 2pm  \n",
       "1                                                 Yes  \n",
       "2                                                 Yes  \n",
       "3                                                  No  \n",
       "4                                                  No  \n",
       "5                                                 Yes  \n",
       "6                                                  10  \n",
       "7                                                 Yes  \n",
       "8                   The parent child interactive play  \n",
       "9                                                  No  \n",
       "10                                                Yes  \n",
       "11                                    PBS Programming  \n",
       "12                                                  4  \n",
       "13                                                Yes  \n",
       "14                                                Yes  \n",
       "15                                                Yes  \n",
       "16                                      Under $50,000  \n",
       "17                                             Parent  \n",
       "18                                                Yes  \n",
       "19                                 janszdee@gmail.com  \n",
       "20                                        Very likely  \n",
       "21                                                 No  \n",
       "22                                            English  \n",
       "23                                             Parent  \n",
       "24                                   Medicaid or CHIP  \n",
       "25                                     Somewhat agree  \n",
       "26                                                 10  \n",
       "27                                      Play together  \n",
       "28      Work on numbers, shapes, or other math skills  \n",
       "29                                            English  \n",
       "30                                             Parent  \n",
       "31                 Private (through parent/caregiver)  \n",
       "32                                     Strongly agree  \n",
       "33                                                 10  \n",
       "34                               Attend a local event  \n",
       "35  Explore science, such as observing nature or w...  \n",
       "36  Find fun ways to put learning into everyday ac...  \n",
       "37                                              Other  \n",
       "38                                      Play together  \n",
       "39                               Read or tell stories  \n",
       "40  Watch an educational video or play a digital g...  \n",
       "41      Work on numbers, shapes, or other math skills  \n",
       "42                  Work on reading or writing skills  \n",
       "43                                            English  \n",
       "44                                             Parent  \n",
       "45                                   Medicaid or CHIP  \n",
       "46                                                Yes  \n",
       "47                                                 No  \n",
       "48                                                Yes  \n",
       "49                                              Other  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "extrainfo.head(50)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "extrainfo.to_csv(\"Subscribers Extra Info.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Question Values "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([\"When would you prefer to receive BBT program messages?\\xa0 Program Messages are the messages sent 2 to 4 times a week based on your child's age.\\xa0 Currently the messages are set to go out at 3pm.\",\n",
       "       \"Are the messages you receive from BBT relevant/appropriate for your child's age?\",\n",
       "       \"Has BBT helped you feel more confident/informed about your child's development?\",\n",
       "       'Have you attended a local event as a result of receiving a BBT message?',\n",
       "       'Have you received a BBT message about a local event in your community?',\n",
       "       'Have you recommended BBT to others?',\n",
       "       'How likely would you be to recommend BBT on a scale of 0 (very unlikely) to 10 (very likely)',\n",
       "       \"If BBT were able to help you track your child's developmental progress would you use the feature?\",\n",
       "       'What do you like most about BBT?', 'Affordable Housing',\n",
       "       'Healthcare', 'How did you hear about Bright by Text (BBT)?',\n",
       "       'How many children do you have/care for under the age of 8?',\n",
       "       'Quality Childcare', 'Reliable Transportation',\n",
       "       'Supportive family and friends',\n",
       "       \"What is your family's annual household income?\",\n",
       "       'What is your relationship to the child(ren) you have signed up for with Bright by Text (BBT)?',\n",
       "       'Are you interested in helping improve Bright by Text by answering additional questions via text message or email?',\n",
       "       'Email',\n",
       "       'How likely are you to recommend Bright by Text to a friend who cares for a young child?',\n",
       "       'Is your phone the primary device you use to access the internet?',\n",
       "       'What is the primary language you speak at home?',\n",
       "       'What is your relationship to the child for whom you subscribed to Bright by Text?',\n",
       "       'What type of health insurance does the child have?',\n",
       "       'As a result of Bright by Text I know more about what my child should be learning and doing at this age.',\n",
       "       'Considering your total experience with Bright by Text how likely are you to recommend Bright by Text to a friend?',\n",
       "       'In the past 30 days, have you done any of the following as a result of receiving a Bright by Text message? (check all that apply)',\n",
       "       'Does your community provide supportive programs for raising children?',\n",
       "       'Do you feel overwhelmed by the responsibility of being a parent?',\n",
       "       \"Has Bright by Text helped you feel more confident/informed about your child's development?\",\n",
       "       'Has Bright by Text prompted you to do any of the following with your child? (select all that apply)',\n",
       "       'How likely would you be to opt in for additional message programs by topic (examples could include practicing mindfulness with your child, easy math games, parent encouragement, and others)?',\n",
       "       'How often do you click on the links provided in our text messages?',\n",
       "       '<p dir=\"ltr\">How often would you like to receive text messages from Bright by Text (total per week per child, understanding that some subscribers have signed up for more than 1 child):</p>',\n",
       "       'Please share any other suggestions you have that you think would make Bright by Text better.',\n",
       "       'Which message about reading do you prefer?',\n",
       "       'Which message about unwanted behaviors do you prefer?',\n",
       "       'Which topics are you most interested in receiving information about (assuming all content is relevant to your child’s age)? Check all that apply.',\n",
       "       'Caring for my child(ren) sometimes takes more time and energy than I have to give.',\n",
       "       'Having child(ren) gives me a more certain and optimistic view for the future.',\n",
       "       'Having child(ren) has been a financial burden.',\n",
       "       'Having child(ren) has meant having too few choices and too little control over my life.',\n",
       "       'Having child(ren) leaves little time and flexibility in my life.',\n",
       "       'I am happy in my role as a parent', 'I am satisfied as a parent',\n",
       "       'I enjoy spending time with my child(ren).',\n",
       "       'I feel close to my child(ren).',\n",
       "       'I feel overwhelmed by the responsibility of being a parent.',\n",
       "       'If I had it to do over again, I might decide not to have child(ren).',\n",
       "       'I find my child(ren) enjoyable',\n",
       "       'I sometimes worry whether I am doing enough for my child(ren).',\n",
       "       'It is difficult to balance different responsibilities because of my child(ren).',\n",
       "       'My child(ren) is an important source of affection for me.',\n",
       "       'The behaviour of my child(ren) is often embarrassing or stressful to me.',\n",
       "       'The major source of stress in my life is my child(ren).',\n",
       "       \"There is little or nothing I wouldn't do for my child(ren) if it was necessary.\",\n",
       "       'Bright By Text has increased my\\xa0<u>confidence</u>\\xa0as a parent or caregiver',\n",
       "       'Bright By Text has increased my\\xa0<u>knowledge</u>\\xa0of what I should do as a parent or caregiver',\n",
       "       \"Bright By Text Messages are not advanced enough for my child's development level\",\n",
       "       'I receive too many texts from Bright By Text',\n",
       "       'Apple, orange or other fruit juice',\n",
       "       'Bottled water (not flavored)', 'Chocolate or other flavored milk',\n",
       "       'Do you agree or disagree with this statement:\\xa0<br /><br />\\n“Even though I do my best to take care of my child’s teeth, I feel like others in the community are constantly working against me, offering my child sugary beverages like juice, rather than making water readily available.”<br />\\n\\xa0',\n",
       "       'How important do you consider baby teeth compared to adult teeth?',\n",
       "       'How likely are you to reduce the amount of juice you give to your child to help protect their baby teeth from cavities, based on what you have learned?',\n",
       "       'How often does your child drink juice, either at home, at daycare, school or other places they go?',\n",
       "       'If a child gets a cavity in a baby tooth, can that cavity spread to other teeth in the child’s mouth?<br />\\n\\xa0',\n",
       "       'Kool Aid', 'Regular white milk', 'Soda or pop',\n",
       "       'Sports drinks like Gatorade', 'Tap water from the sink/faucet',\n",
       "       'Thinking about your community, to what extent do you feel there is an effort to replace the sugary drinks, like juice, that are being served to children, with water?',\n",
       "       'Browse Internet', 'Browse Parenting Websites', 'Computer',\n",
       "       'Does your mobile/smart phone have an <strong>unlimited data/internet</strong> plan?',\n",
       "       'Does your mobile/Smart phone have an unlimited <strong>text message</strong> plan?',\n",
       "       'Does your mobile/smart phone have internet access?',\n",
       "       'Do you actively use push notifications/alerts from apps on your mobile/smart phone?',\n",
       "       'Do you have internet/WiFi access at home?',\n",
       "       'Have you ever downloaded a parenting app?', 'Listen to music',\n",
       "       'Mobile/Cell phone(not smartphone)', 'Play games', 'Smart phone',\n",
       "       'Social Media (e.g. Facebook, Snapchat, Twitter)',\n",
       "       'Subscribe to text programs other than BBT', 'Tablet/iPad',\n",
       "       'Take photos/videos', 'Text/SMS', 'Use Parenting Apps',\n",
       "       'Watch Videos (e.g. YouTube)',\n",
       "       'Which of the following best describes your employment situation?',\n",
       "       'Child 1 (Youngest)\\t',\n",
       "       'Do any of the following apply to you or another parent or guardian who cares for your child/children? (Please select all that apply):',\n",
       "       'Have you used any of the following to care for your child/children? (Check all that apply.)',\n",
       "       'How many children under the age of six are living at your home?',\n",
       "       \"We're interested in providing supports and services to all children. We'd like to know if any of the following describe your child/children.\\xa0 (Please select all that apply).\",\n",
       "       'What is your race / ethnicity?',\n",
       "       'What is your relationship to these children?',\n",
       "       'Which of the following are the\\xa0<strong>THREE\\xa0</strong>most important reasons to select care for your child/children? (select up to 3)',\n",
       "       'Which of the following ranges most approximately describes your household income for the year 2019?',\n",
       "       'Which of the following services have you accessed or used for your child/children? (check all that apply)',\n",
       "       'Which of the\\xa0<strong>THREE\\xa0</strong>sources do you most trust to provide information when selecting care for your child/children? (Select up to 3)',\n",
       "       'Mobile/Cell phone  (not smartphone)', 'Child 2',\n",
       "       'What does high quality child care look like to you?',\n",
       "       'How did you hear about Bright By Text?',\n",
       "       'Is there any feedback you can share with us to help us improve Bright by Text?',\n",
       "       'Why did out opt-out of Bright by Text?', 'Child 3',\n",
       "       'Are you interested in helping improve Bright by Text by answering additional questions via text message?',\n",
       "       'Are you interested in doing this?',\n",
       "       'At what age do you think a child’s brain develops most rapidly?',\n",
       "       'Education', 'Estimated household income',\n",
       "       'Has Bright by Text prompted you to do any of the following with you child?',\n",
       "       'Have you used online forums (e.g. chat groups, Facebook groups) to connect with other parents or get information on parenting topics?\\xa0',\n",
       "       'How did you hear about Bright by Text?\\xa0', 'How old are you?',\n",
       "       'Is there information that you would like to receive that you are not getting from Bright by Text?',\n",
       "       'Race', 'What are your biggest challenges caring for your child?',\n",
       "       'What information from Bright by Text do you find most useful?',\n",
       "       'When caring for your child, what do you do well?',\n",
       "       'Which of these topics are you most interested in?',\n",
       "       'Would you like Bright by Text to expand our content to include tip, activities, and resources for children 5 - 8 years old?',\n",
       "       'Child 6', 'Child 4', 'Child 5'], dtype=object)"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "extrainfo.question.unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Valuable Questions:\n",
    "'Have you recommended BBT to others?'\n",
    "'How likely would you be to recommend BBT on a scale of 0 (very unlikely) to 10 (very likely)',\n",
    "'Affordable Housing'\n",
    "'What is your relationship to the child(ren) you have signed up for with Bright by Text (BBT)?',\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Surveys"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Program Message of Day Survey', 'Bright By Text Feedback',\n",
       "       'Getting to Know You', 'Profile Builder',\n",
       "       'Bright by Text Feedback', 'BBT user experience survey 2020',\n",
       "       'Caregiver Feedback', '6 Month User Survey',\n",
       "       'Delta Dental Survey 2019', 'Tech Use', 'PDG',\n",
       "       'Getting to Know You  -testing', 'Tech Use - testing',\n",
       "       'STOP survey', 'Profile Builder DEMO',\n",
       "       'Getting to Know You  -testing uat',\n",
       "       'Bright by Text Caregiver Feedback - Messages',\n",
       "       'Delta Dental Survey 2018 - test only'], dtype=object)"
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "extrainfo.survey_name.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [],
   "source": [
    "survey_question = extrainfo[['survey_name','question']].drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [],
   "source": [
    "survey_question.head()\n",
    "survey_question.to_csv('questions.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Create Subscriber Dataframe "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "s = pd.DataFrame({'subscriber':extrainfo.subscriber.unique()})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Collect Information "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "def collect(df,question,attribute):\n",
    "    info = df[df[\"question\"]== question]\n",
    "    output = info[['subscriber','answer_value']].drop_duplicates().rename(columns = {'answer_value':attribute}).reset_index(drop=True)\n",
    "    return pd.merge(s,output,on='subscriber',how='outer')\n",
    "        "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Income"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Under $50,000', 'Over $75,000', 'Do not know',\n",
       "       '$50,000 to $75,000'], dtype=object)"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Income Category\n",
    "extrainfo[extrainfo[\"question\"]==\"What is your family's annual household income?\"].answer_value.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "s = collect(extrainfo,\"What is your family's annual household income?\",'income')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>subscriber</th>\n",
       "      <th>income</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00029a87-e949-44e8-beaa-b9680f5e55c7</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0005eafe-c416-4226-af97-7d3a2bf7636f</td>\n",
       "      <td>Under $50,000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0006ed40-7821-459d-a68a-8748ae91f136</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>00110b62-0019-408d-b8f9-e6fd586055aa</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>001489b1-5dd5-4f42-8ed2-3db1b420cc82</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             subscriber         income\n",
       "0  00029a87-e949-44e8-beaa-b9680f5e55c7            NaN\n",
       "1  0005eafe-c416-4226-af97-7d3a2bf7636f  Under $50,000\n",
       "2  0006ed40-7821-459d-a68a-8748ae91f136            NaN\n",
       "3  00110b62-0019-408d-b8f9-e6fd586055aa            NaN\n",
       "4  001489b1-5dd5-4f42-8ed2-3db1b420cc82            NaN"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Employment "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Full-time homemaker', 'Employed full-time outside of the home',\n",
       "       'Employed part-time outside of the home',\n",
       "       'Not employed and looking for work', 'Student'], dtype=object)"
      ]
     },
     "execution_count": 94,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "extrainfo[extrainfo[\"question\"]==\"Which of the following best describes your employment situation?\"].answer_value.unique()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [],
   "source": [
    "s = collect(extrainfo,\"Which of the following best describes your employment situation?\",'employment')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>subscriber</th>\n",
       "      <th>income</th>\n",
       "      <th>healthcare</th>\n",
       "      <th>quality childcare</th>\n",
       "      <th>education</th>\n",
       "      <th>affordable housing</th>\n",
       "      <th>children health insurance</th>\n",
       "      <th># of children younger than 8</th>\n",
       "      <th>employment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00029a87-e949-44e8-beaa-b9680f5e55c7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0005eafe-c416-4226-af97-7d3a2bf7636f</td>\n",
       "      <td>Under $50,000</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "      <td>Medicaid or CHIP</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0006ed40-7821-459d-a68a-8748ae91f136</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Private (through parent/caregiver)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>00110b62-0019-408d-b8f9-e6fd586055aa</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Medicaid or CHIP</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>001489b1-5dd5-4f42-8ed2-3db1b420cc82</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             subscriber         income healthcare  \\\n",
       "0  00029a87-e949-44e8-beaa-b9680f5e55c7            NaN        NaN   \n",
       "1  0005eafe-c416-4226-af97-7d3a2bf7636f  Under $50,000        Yes   \n",
       "2  0006ed40-7821-459d-a68a-8748ae91f136            NaN        NaN   \n",
       "3  00110b62-0019-408d-b8f9-e6fd586055aa            NaN        NaN   \n",
       "4  001489b1-5dd5-4f42-8ed2-3db1b420cc82            NaN        NaN   \n",
       "\n",
       "  quality childcare education affordable housing  \\\n",
       "0               NaN       NaN                NaN   \n",
       "1               Yes       NaN                 No   \n",
       "2               NaN       NaN                NaN   \n",
       "3               NaN       NaN                NaN   \n",
       "4               NaN       NaN                NaN   \n",
       "\n",
       "            children health insurance # of children younger than 8 employment  \n",
       "0                                 NaN                          NaN        NaN  \n",
       "1                    Medicaid or CHIP                            4        NaN  \n",
       "2  Private (through parent/caregiver)                          NaN        NaN  \n",
       "3                    Medicaid or CHIP                          NaN        NaN  \n",
       "4                                 NaN                          NaN        NaN  "
      ]
     },
     "execution_count": 99,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Healthcare "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Yes', 'No', 'N/A'], dtype=object)"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Healthcare\n",
    "extrainfo[extrainfo[\"question\"]==\"Healthcare\"].answer_value.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "s = collect(extrainfo,\"Healthcare\",'healthcare')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>subscriber</th>\n",
       "      <th>income</th>\n",
       "      <th>healthcare</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00029a87-e949-44e8-beaa-b9680f5e55c7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0005eafe-c416-4226-af97-7d3a2bf7636f</td>\n",
       "      <td>Under $50,000</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0006ed40-7821-459d-a68a-8748ae91f136</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>00110b62-0019-408d-b8f9-e6fd586055aa</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>001489b1-5dd5-4f42-8ed2-3db1b420cc82</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             subscriber         income healthcare\n",
       "0  00029a87-e949-44e8-beaa-b9680f5e55c7            NaN        NaN\n",
       "1  0005eafe-c416-4226-af97-7d3a2bf7636f  Under $50,000        Yes\n",
       "2  0006ed40-7821-459d-a68a-8748ae91f136            NaN        NaN\n",
       "3  00110b62-0019-408d-b8f9-e6fd586055aa            NaN        NaN\n",
       "4  001489b1-5dd5-4f42-8ed2-3db1b420cc82            NaN        NaN"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Childcare "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Yes', 'N/A', 'No'], dtype=object)"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Childcare\n",
    "extrainfo[extrainfo[\"question\"]==\"Quality Childcare\"].answer_value.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [],
   "source": [
    "s = collect(extrainfo,\"Quality Childcare\",'quality childcare')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>subscriber</th>\n",
       "      <th>income</th>\n",
       "      <th>healthcare</th>\n",
       "      <th>quality childcare</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00029a87-e949-44e8-beaa-b9680f5e55c7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0005eafe-c416-4226-af97-7d3a2bf7636f</td>\n",
       "      <td>Under $50,000</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0006ed40-7821-459d-a68a-8748ae91f136</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>00110b62-0019-408d-b8f9-e6fd586055aa</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>001489b1-5dd5-4f42-8ed2-3db1b420cc82</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             subscriber         income healthcare  \\\n",
       "0  00029a87-e949-44e8-beaa-b9680f5e55c7            NaN        NaN   \n",
       "1  0005eafe-c416-4226-af97-7d3a2bf7636f  Under $50,000        Yes   \n",
       "2  0006ed40-7821-459d-a68a-8748ae91f136            NaN        NaN   \n",
       "3  00110b62-0019-408d-b8f9-e6fd586055aa            NaN        NaN   \n",
       "4  001489b1-5dd5-4f42-8ed2-3db1b420cc82            NaN        NaN   \n",
       "\n",
       "  quality childcare  \n",
       "0               NaN  \n",
       "1               Yes  \n",
       "2               NaN  \n",
       "3               NaN  \n",
       "4               NaN  "
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Education "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Post-graduate education', 'College degree', 'Some college'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Education Category\n",
    "extrainfo[extrainfo[\"question\"]==\"Education\"].answer_value.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [],
   "source": [
    "s = collect(extrainfo,\"Education\",'education')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>subscriber</th>\n",
       "      <th>income</th>\n",
       "      <th>healthcare</th>\n",
       "      <th>quality childcare</th>\n",
       "      <th>education</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00029a87-e949-44e8-beaa-b9680f5e55c7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0005eafe-c416-4226-af97-7d3a2bf7636f</td>\n",
       "      <td>Under $50,000</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0006ed40-7821-459d-a68a-8748ae91f136</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>00110b62-0019-408d-b8f9-e6fd586055aa</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>001489b1-5dd5-4f42-8ed2-3db1b420cc82</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             subscriber         income healthcare  \\\n",
       "0  00029a87-e949-44e8-beaa-b9680f5e55c7            NaN        NaN   \n",
       "1  0005eafe-c416-4226-af97-7d3a2bf7636f  Under $50,000        Yes   \n",
       "2  0006ed40-7821-459d-a68a-8748ae91f136            NaN        NaN   \n",
       "3  00110b62-0019-408d-b8f9-e6fd586055aa            NaN        NaN   \n",
       "4  001489b1-5dd5-4f42-8ed2-3db1b420cc82            NaN        NaN   \n",
       "\n",
       "  quality childcare education  \n",
       "0               NaN       NaN  \n",
       "1               Yes       NaN  \n",
       "2               NaN       NaN  \n",
       "3               NaN       NaN  \n",
       "4               NaN       NaN  "
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Housing "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['No', 'Yes', 'N/A'], dtype=object)"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "extrainfo[extrainfo[\"question\"]==\"Affordable Housing\"].answer_value.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [],
   "source": [
    "s = collect(extrainfo,\"Affordable Housing\",'affordable housing')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>subscriber</th>\n",
       "      <th>income</th>\n",
       "      <th>healthcare</th>\n",
       "      <th>quality childcare</th>\n",
       "      <th>education</th>\n",
       "      <th>affordable housing</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00029a87-e949-44e8-beaa-b9680f5e55c7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0005eafe-c416-4226-af97-7d3a2bf7636f</td>\n",
       "      <td>Under $50,000</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0006ed40-7821-459d-a68a-8748ae91f136</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>00110b62-0019-408d-b8f9-e6fd586055aa</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>001489b1-5dd5-4f42-8ed2-3db1b420cc82</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             subscriber         income healthcare  \\\n",
       "0  00029a87-e949-44e8-beaa-b9680f5e55c7            NaN        NaN   \n",
       "1  0005eafe-c416-4226-af97-7d3a2bf7636f  Under $50,000        Yes   \n",
       "2  0006ed40-7821-459d-a68a-8748ae91f136            NaN        NaN   \n",
       "3  00110b62-0019-408d-b8f9-e6fd586055aa            NaN        NaN   \n",
       "4  001489b1-5dd5-4f42-8ed2-3db1b420cc82            NaN        NaN   \n",
       "\n",
       "  quality childcare education affordable housing  \n",
       "0               NaN       NaN                NaN  \n",
       "1               Yes       NaN                 No  \n",
       "2               NaN       NaN                NaN  \n",
       "3               NaN       NaN                NaN  \n",
       "4               NaN       NaN                NaN  "
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Children Health Insurance "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Medicaid or CHIP', 'Private (through parent/caregiver)',\n",
       "       \"Don't Know\", 'None'], dtype=object)"
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Children Health Insurance\n",
    "extrainfo[extrainfo[\"question\"]==\"What type of health insurance does the child have?\"].answer_value.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [],
   "source": [
    "s = collect(extrainfo,\"What type of health insurance does the child have?\",'children health insurance')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>subscriber</th>\n",
       "      <th>income</th>\n",
       "      <th>healthcare</th>\n",
       "      <th>quality childcare</th>\n",
       "      <th>education</th>\n",
       "      <th>affordable housing</th>\n",
       "      <th>children health insurance</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00029a87-e949-44e8-beaa-b9680f5e55c7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0005eafe-c416-4226-af97-7d3a2bf7636f</td>\n",
       "      <td>Under $50,000</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "      <td>Medicaid or CHIP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0006ed40-7821-459d-a68a-8748ae91f136</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Private (through parent/caregiver)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>00110b62-0019-408d-b8f9-e6fd586055aa</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Medicaid or CHIP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>001489b1-5dd5-4f42-8ed2-3db1b420cc82</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             subscriber         income healthcare  \\\n",
       "0  00029a87-e949-44e8-beaa-b9680f5e55c7            NaN        NaN   \n",
       "1  0005eafe-c416-4226-af97-7d3a2bf7636f  Under $50,000        Yes   \n",
       "2  0006ed40-7821-459d-a68a-8748ae91f136            NaN        NaN   \n",
       "3  00110b62-0019-408d-b8f9-e6fd586055aa            NaN        NaN   \n",
       "4  001489b1-5dd5-4f42-8ed2-3db1b420cc82            NaN        NaN   \n",
       "\n",
       "  quality childcare education affordable housing  \\\n",
       "0               NaN       NaN                NaN   \n",
       "1               Yes       NaN                 No   \n",
       "2               NaN       NaN                NaN   \n",
       "3               NaN       NaN                NaN   \n",
       "4               NaN       NaN                NaN   \n",
       "\n",
       "            children health insurance  \n",
       "0                                 NaN  \n",
       "1                    Medicaid or CHIP  \n",
       "2  Private (through parent/caregiver)  \n",
       "3                    Medicaid or CHIP  \n",
       "4                                 NaN  "
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Young Children Number"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['4', '1', '2', '5', '5+', '3'], dtype=object)"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "extrainfo[extrainfo[\"question\"]==\"How many children do you have/care for under the age of 8?\"].answer_value.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [],
   "source": [
    "s = collect(extrainfo,\"How many children do you have/care for under the age of 8?\",'# of children younger than 8')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>subscriber</th>\n",
       "      <th>income</th>\n",
       "      <th>healthcare</th>\n",
       "      <th>quality childcare</th>\n",
       "      <th>education</th>\n",
       "      <th>affordable housing</th>\n",
       "      <th>children health insurance</th>\n",
       "      <th># of children younger than 8</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00029a87-e949-44e8-beaa-b9680f5e55c7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0005eafe-c416-4226-af97-7d3a2bf7636f</td>\n",
       "      <td>Under $50,000</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "      <td>Medicaid or CHIP</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0006ed40-7821-459d-a68a-8748ae91f136</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Private (through parent/caregiver)</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>00110b62-0019-408d-b8f9-e6fd586055aa</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Medicaid or CHIP</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>001489b1-5dd5-4f42-8ed2-3db1b420cc82</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             subscriber         income healthcare  \\\n",
       "0  00029a87-e949-44e8-beaa-b9680f5e55c7            NaN        NaN   \n",
       "1  0005eafe-c416-4226-af97-7d3a2bf7636f  Under $50,000        Yes   \n",
       "2  0006ed40-7821-459d-a68a-8748ae91f136            NaN        NaN   \n",
       "3  00110b62-0019-408d-b8f9-e6fd586055aa            NaN        NaN   \n",
       "4  001489b1-5dd5-4f42-8ed2-3db1b420cc82            NaN        NaN   \n",
       "\n",
       "  quality childcare education affordable housing  \\\n",
       "0               NaN       NaN                NaN   \n",
       "1               Yes       NaN                 No   \n",
       "2               NaN       NaN                NaN   \n",
       "3               NaN       NaN                NaN   \n",
       "4               NaN       NaN                NaN   \n",
       "\n",
       "            children health insurance # of children younger than 8  \n",
       "0                                 NaN                          NaN  \n",
       "1                    Medicaid or CHIP                            4  \n",
       "2  Private (through parent/caregiver)                          NaN  \n",
       "3                    Medicaid or CHIP                          NaN  \n",
       "4                                 NaN                          NaN  "
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Topics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {},
   "outputs": [],
   "source": [
    "def append_multiple_choices(df,question):\n",
    "    info = df[df[\"question\"]== question]\n",
    "    output = info.groupby('subscriber')['answer_value'].apply(';'.join).reset_index()\n",
    "    #output = info[['subscriber','answer_value']].drop_duplicates().rename(columns = {'answer_value':attribute}).reset_index(drop=True)\n",
    "    return output"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Brain development (building language, vocabulary, science/math ideas, etc)',\n",
       "       'Games/activities to do with children',\n",
       "       'Milestones/what to expect by age', 'Nutrition',\n",
       "       'Challenges (sleep, potty training, tantrums, sibling rivalry, etc)',\n",
       "       'Health', 'Parenting tips/advice',\n",
       "       'Relevant news alerts (outbreaks, recalls, etc)', 'Safety',\n",
       "       'Social-emotional development (kindness, sharing, getting along, etc)',\n",
       "       'Tips for finding resources/support', 'Local activities',\n",
       "       'Things to do while out to keep learning fun for both kids',\n",
       "       'Autism resources', 'Events in my area',\n",
       "       'Anything and everything!', 'Other (Please describe)',\n",
       "       'My 2 1/2 year old is autistic. Some content geared towards that would be great. ',\n",
       "       \"Anything y'all send us is always informative and useful. \",\n",
       "       'Community events, celebrations, cultural knowledge for kids',\n",
       "       'Activities,  events.', 'Teaching toddlers how to read ',\n",
       "       'Managing developmental and social delays of special needs children',\n",
       "       \"Games and activities that can be played inside by a caregiver that's not as mobile.   Also I would like activities for a three year old toddler.\",\n",
       "       'More ways to keep electronics to a minimum and not fall into the trap of using electronics for babysitters.',\n",
       "       'Would love more indoor and outdoor activity ideas',\n",
       "       'I would like to unsubscribe ',\n",
       "       'types of books for each age - board, flap, puppet, rhyme, colors, etc',\n",
       "       'Free activities in the area ',\n",
       "       'y understanding that I am a grandmother, as a caregiver 3 to 4 days a week for a 14 month old now, and a 4 1/2-year-old, how to carefully express to your children about things of their children.',\n",
       "       'Personality traits and how to encourage positive growth for the type of child',\n",
       "       'Public school system', 'All of the above', 'None',\n",
       "       'local pbs events', 'All of them are importante ',\n",
       "       'Kid friendly events in near future, by area',\n",
       "       'story times and other activities in my area that are good for kids',\n",
       "       'Separation anxiety ',\n",
       "       'Activities going on in community to do with baby',\n",
       "       \"I appreciate a variety of content. I love recieving information on current milestones, news alerts, and health/safety information however I wouldn't trade our tips and ideas for games and dealing with challenges that we come across. It's all so great.\",\n",
       "       'I love getting alerts about local events and free days at museums',\n",
       "       'All of these are good ', 'No time',\n",
       "       'School resources & availability near me. Programs and classes near me',\n",
       "       'Spiritual development ', 'Community events',\n",
       "       'Free/low cost Community Events',\n",
       "       'Pretty much anything at all related to kids and bringing them to the best we can: info on new studies, book recommendations (for parents or child), quick videos for parents, anything',\n",
       "       'Please do not text anymore. Thank you.',\n",
       "       'How to reduce screen time.  Warnings against screen time',\n",
       "       'None anymore',\n",
       "       'Potty training tips, and fun alphabet and counting games',\n",
       "       'Quick healthy ness or snacks ', 'Siblings rivalry ',\n",
       "       'Maybe event notifications for anything in my city', 'Everything ',\n",
       "       'Iocal events', 'Creative play',\n",
       "       'All of what you send is great love it', 'Events',\n",
       "       'Mental health for my kids and myself ',\n",
       "       'Some helpful homemade food but easy to make that they would actually like to eat',\n",
       "       'Grandparent tips/advice', 'Stop please',\n",
       "       'Topics on prematurely and development.', 'Do not text me',\n",
       "       'Types of education and availability of schools in my area. Such as Charter schools ',\n",
       "       \"I'd like to see stuff for special needs kids\",\n",
       "       'Resources for community oriented or entertainment ops',\n",
       "       'autism related articles', 'Activities happening in the city',\n",
       "       \"Local event's \", 'More local events ', 'All of the above ', 'All',\n",
       "       'Development/issues with split families (divorce or both parents not living together ',\n",
       "       'Educational tips ', 'Events happening in local areas', 'None ',\n",
       "       'All available ', 'Local events', 'I would like to UNSUBSCRIBE.',\n",
       "       'Mental health, how to support parents & twins, etcetera.',\n",
       "       'Local activities for my child',\n",
       "       'I would like more local events for las cruces NM',\n",
       "       'No longer relavent', 'Child care options ',\n",
       "       \"I do not live in Madison, so I'm not as fond of notifications about specific shows or events in Madison. I'd much prefer hearing about a little known kid-friendly park, etc. \",\n",
       "       \"I don't have kids. Stop texting me \",\n",
       "       'Events for kids in the city',\n",
       "       'Local events and free activities for kids ',\n",
       "       'Places to go with kid', 'Local events for kids ', 'nearby events',\n",
       "       'I have aa', 'Food allergies ',\n",
       "       'Natural remedies for common diseases',\n",
       "       'I know behavior was a choice already, but something on difficult behaviors and tantrums would be much appreciated!',\n",
       "       'Local events in my community ', 'Crying',\n",
       "       \"More for mom tips.... Alot of mom's feel alone.\",\n",
       "       'Printables appropriate for she of our child (toddler/preschool)',\n",
       "       \"I don't want to receive these messages AT ALL. \",\n",
       "       'Ways to teach how to handle emotions in healthy ways',\n",
       "       'Babyproofing ', 'all above ', 'Family events to attend',\n",
       "       'All of the above!', 'Ways to talk to teenagers',\n",
       "       'Information for 4year olds too',\n",
       "       'I need to do Grandson. 3 Dec 1st', 'Food', 'All of it',\n",
       "       'My own child is an adult. I am a caregiver for a toddler making my perspective different.  I find the information encouraging and helpful. ',\n",
       "       'Single mother resources', 'I love it all!',\n",
       "       'Local kid friendly activities for the week or month',\n",
       "       'Family trips, vacations or activities to do as a family. ',\n",
       "       'Tips for parents to take care of themselves in order to be better and patient parents',\n",
       "       'LocLocal programs and opportunities to connect with others',\n",
       "       'Grandparent advice',\n",
       "       \"Suggestions on Autistic children because I can't do most of these things with my child.\",\n",
       "       'Pets and babies',\n",
       "       ' Sleep patterns/vivid dreams. Technology (is it harmful and limits)',\n",
       "       'Social Skill Development ', 'Dealing w autism', 'All of then. ',\n",
       "       'Bilingualism ', 'Eventos para niños en mi ciudad',\n",
       "       'Outings and events', 'I was hoping for local event info!',\n",
       "       'Discounts on kids stuff ', 'Community events ',\n",
       "       'Craft-academics, seasonal ', 'Mental health',\n",
       "       'How and where to find community events and activities for kids',\n",
       "       'Daycare Tips and How To Save on Clothing/Things they grow out of',\n",
       "       'Events!'], dtype=object)"
      ]
     },
     "execution_count": 112,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "extrainfo[extrainfo['question'] == 'Which topics are you most interested in receiving information about (assuming all content is relevant to your child’s age)? Check all that apply.'].answer_value.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "subscriber                   00e2df28-01e9-4f49-b2df-8b4e7576d486\n",
       "answer_value    Challenges (sleep, potty training, tantrums, s...\n",
       "Name: 13, dtype: object"
      ]
     },
     "execution_count": 110,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "append_multiple_choices(extrainfo,'Which topics are you most interested in receiving information about (assuming all content is relevant to your child’s age)? Check all that apply.').iloc[13]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Children Relationship"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "metadata": {},
   "outputs": [],
   "source": [
    "def assign_pos_score(x):\n",
    "    if x == 'Strongly Disagree':\n",
    "        score = 1\n",
    "    elif x == 'Disagree':\n",
    "        score = 2\n",
    "    elif x == 'Undecided':\n",
    "        score = 3\n",
    "    elif x == 'Agree':\n",
    "        score = 4\n",
    "    elif x == 'Strongly Agree':\n",
    "        score = 5\n",
    "    return score"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 153,
   "metadata": {},
   "outputs": [],
   "source": [
    "def assign_neg_score(x):\n",
    "    if x == 'Strongly Disagree':\n",
    "        score = 5\n",
    "    elif x == 'Disagree':\n",
    "        score = 4\n",
    "    elif x == 'Undecided':\n",
    "        score = 3\n",
    "    elif x == 'Agree':\n",
    "        score = 2\n",
    "    elif x == 'Strongly Agree':\n",
    "        score = 1\n",
    "    return score"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 154,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "179       1\n",
       "368       2\n",
       "622       1\n",
       "687       2\n",
       "1352      2\n",
       "1758      2\n",
       "1920      2\n",
       "2401      3\n",
       "2718      4\n",
       "3053      2\n",
       "3316      3\n",
       "3425      2\n",
       "3482      1\n",
       "3590      1\n",
       "4034      2\n",
       "4115      1\n",
       "5069      1\n",
       "5244      1\n",
       "5510      2\n",
       "6006      2\n",
       "6131      1\n",
       "6207      3\n",
       "7020      1\n",
       "7176      1\n",
       "7382      1\n",
       "7438      2\n",
       "7561      2\n",
       "7629      1\n",
       "7922      3\n",
       "8288      3\n",
       "         ..\n",
       "232254    1\n",
       "232420    2\n",
       "233142    1\n",
       "233586    2\n",
       "233657    2\n",
       "234329    1\n",
       "234480    3\n",
       "234901    2\n",
       "235876    2\n",
       "236035    1\n",
       "236984    1\n",
       "237388    1\n",
       "237664    3\n",
       "238574    4\n",
       "239185    2\n",
       "239436    1\n",
       "239967    1\n",
       "240221    1\n",
       "240355    1\n",
       "240437    3\n",
       "240635    2\n",
       "240742    1\n",
       "240803    1\n",
       "241075    3\n",
       "241139    1\n",
       "241857    1\n",
       "242237    1\n",
       "242417    1\n",
       "242917    3\n",
       "243373    1\n",
       "Name: answer_value, Length: 663, dtype: int64"
      ]
     },
     "execution_count": 154,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "extrainfo[extrainfo[\"question\"]== \"Having child(ren) gives me a more certain and optimistic view for the future.\"]['answer_value'].apply(lambda x:assign_neg_score(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 290,
   "metadata": {},
   "outputs": [],
   "source": [
    "def children_rel(df, pos_q_list, neg_q_list):\n",
    "    def assign_score(x):\n",
    "        if x.question in pos_q_list:\n",
    "            return assign_pos_score(x.answer_value)\n",
    "        elif x.question in neg_q_list:\n",
    "            return assign_neg_score(x.answer_value)\n",
    "        else:\n",
    "            return pd.np.nan\n",
    "        \n",
    "    df[\"score\"] = df.apply(lambda x: assign_score(x), axis=1)\n",
    "\n",
    "    return df\n",
    "                            \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 281,
   "metadata": {},
   "outputs": [],
   "source": [
    "pos_q_list = [\"Having child(ren) gives me a more certain and optimistic view for the future.\"\n",
    "             ,\"I am happy in my role as a parent\"\n",
    "             ,\"I am satisfied as a parent\"\n",
    "             ,\"I enjoy spending time with my child(ren).\"\n",
    "             ,\"I feel close to my child(ren).\"\n",
    "             ,\"I find my child(ren) enjoyable\"\n",
    "             ,\"My child(ren) is an important source of affection for me.\"\n",
    "             ,\"There is little or nothing I wouldn't do for my child(ren) if it was necessary.\"\n",
    "             \n",
    "             ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 292,
   "metadata": {},
   "outputs": [],
   "source": [
    "neg_q_list = ['Having child(ren) has been a financial burden.'\n",
    "             ,\"Caring for my child(ren) sometimes takes more time and energy than I have to give.\"\n",
    "             ,\"Having child(ren) has meant having too few choices and too little control over my life.\"\n",
    "             ,\"Having child(ren) leaves little time and flexibility in my life.\"\n",
    "             ,\"I feel overwhelmed by the responsibility of being a parent.\"\n",
    "             ,\"If I had it to do over again, I might decide not to have child(ren).\"\n",
    "             ,\"I sometimes worry whether I am doing enough for my child(ren).\"\n",
    "             ,\"It is difficult to balance different responsibilities because of my child(ren).\"\n",
    "             ,\"The behaviour of my child(ren) is often embarrassing or stressful to me.\"\n",
    "             ,\"The major source of stress in my life is my child(ren).\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 295,
   "metadata": {},
   "outputs": [],
   "source": [
    "extrainfo_copy = children_rel(extrainfo, pos_q_list, neg_q_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 297,
   "metadata": {},
   "outputs": [],
   "source": [
    "extrainfo_copy = extrainfo_copy.dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 298,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>subscriber</th>\n",
       "      <th>survey_name</th>\n",
       "      <th>question</th>\n",
       "      <th>answer_value</th>\n",
       "      <th>score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>178</th>\n",
       "      <td>003f3633-765e-4eb8-ba02-4f6c4bcf6d60</td>\n",
       "      <td>Caregiver Feedback</td>\n",
       "      <td>Caring for my child(ren) sometimes takes more ...</td>\n",
       "      <td>Strongly Disagree</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179</th>\n",
       "      <td>003f3633-765e-4eb8-ba02-4f6c4bcf6d60</td>\n",
       "      <td>Caregiver Feedback</td>\n",
       "      <td>Having child(ren) gives me a more certain and ...</td>\n",
       "      <td>Strongly Agree</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>180</th>\n",
       "      <td>003f3633-765e-4eb8-ba02-4f6c4bcf6d60</td>\n",
       "      <td>Caregiver Feedback</td>\n",
       "      <td>Having child(ren) has been a financial burden.</td>\n",
       "      <td>Strongly Disagree</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>181</th>\n",
       "      <td>003f3633-765e-4eb8-ba02-4f6c4bcf6d60</td>\n",
       "      <td>Caregiver Feedback</td>\n",
       "      <td>Having child(ren) has meant having too few cho...</td>\n",
       "      <td>Strongly Disagree</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>182</th>\n",
       "      <td>003f3633-765e-4eb8-ba02-4f6c4bcf6d60</td>\n",
       "      <td>Caregiver Feedback</td>\n",
       "      <td>Having child(ren) leaves little time and flexi...</td>\n",
       "      <td>Strongly Disagree</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               subscriber         survey_name  \\\n",
       "178  003f3633-765e-4eb8-ba02-4f6c4bcf6d60  Caregiver Feedback   \n",
       "179  003f3633-765e-4eb8-ba02-4f6c4bcf6d60  Caregiver Feedback   \n",
       "180  003f3633-765e-4eb8-ba02-4f6c4bcf6d60  Caregiver Feedback   \n",
       "181  003f3633-765e-4eb8-ba02-4f6c4bcf6d60  Caregiver Feedback   \n",
       "182  003f3633-765e-4eb8-ba02-4f6c4bcf6d60  Caregiver Feedback   \n",
       "\n",
       "                                              question       answer_value  \\\n",
       "178  Caring for my child(ren) sometimes takes more ...  Strongly Disagree   \n",
       "179  Having child(ren) gives me a more certain and ...     Strongly Agree   \n",
       "180     Having child(ren) has been a financial burden.  Strongly Disagree   \n",
       "181  Having child(ren) has meant having too few cho...  Strongly Disagree   \n",
       "182  Having child(ren) leaves little time and flexi...  Strongly Disagree   \n",
       "\n",
       "     score  \n",
       "178    5.0  \n",
       "179    5.0  \n",
       "180    5.0  \n",
       "181    5.0  \n",
       "182    5.0  "
      ]
     },
     "execution_count": 298,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "extrainfo_copy.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 302,
   "metadata": {},
   "outputs": [],
   "source": [
    "child_rel_score = pd.DataFrame(extrainfo_copy.groupby(\"subscriber\")['score'].sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 303,
   "metadata": {},
   "outputs": [],
   "source": [
    "s = pd.merge(s,child_rel_score,on='subscriber',how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 308,
   "metadata": {},
   "outputs": [],
   "source": [
    "s = s.rename(columns = {'score':\"children relationship score\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 309,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>subscriber</th>\n",
       "      <th>income</th>\n",
       "      <th>healthcare</th>\n",
       "      <th>quality childcare</th>\n",
       "      <th>education</th>\n",
       "      <th>affordable housing</th>\n",
       "      <th>children health insurance</th>\n",
       "      <th># of children younger than 8</th>\n",
       "      <th>employment</th>\n",
       "      <th>children relationship score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00029a87-e949-44e8-beaa-b9680f5e55c7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0005eafe-c416-4226-af97-7d3a2bf7636f</td>\n",
       "      <td>Under $50,000</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "      <td>Medicaid or CHIP</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0006ed40-7821-459d-a68a-8748ae91f136</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Private (through parent/caregiver)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>00110b62-0019-408d-b8f9-e6fd586055aa</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Medicaid or CHIP</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>001489b1-5dd5-4f42-8ed2-3db1b420cc82</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             subscriber         income healthcare  \\\n",
       "0  00029a87-e949-44e8-beaa-b9680f5e55c7            NaN        NaN   \n",
       "1  0005eafe-c416-4226-af97-7d3a2bf7636f  Under $50,000        Yes   \n",
       "2  0006ed40-7821-459d-a68a-8748ae91f136            NaN        NaN   \n",
       "3  00110b62-0019-408d-b8f9-e6fd586055aa            NaN        NaN   \n",
       "4  001489b1-5dd5-4f42-8ed2-3db1b420cc82            NaN        NaN   \n",
       "\n",
       "  quality childcare education affordable housing  \\\n",
       "0               NaN       NaN                NaN   \n",
       "1               Yes       NaN                 No   \n",
       "2               NaN       NaN                NaN   \n",
       "3               NaN       NaN                NaN   \n",
       "4               NaN       NaN                NaN   \n",
       "\n",
       "            children health insurance # of children younger than 8 employment  \\\n",
       "0                                 NaN                          NaN        NaN   \n",
       "1                    Medicaid or CHIP                            4        NaN   \n",
       "2  Private (through parent/caregiver)                          NaN        NaN   \n",
       "3                    Medicaid or CHIP                          NaN        NaN   \n",
       "4                                 NaN                          NaN        NaN   \n",
       "\n",
       "   children relationship score  \n",
       "0                          NaN  \n",
       "1                          NaN  \n",
       "2                          NaN  \n",
       "3                          NaN  \n",
       "4                          NaN  "
      ]
     },
     "execution_count": 309,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 310,
   "metadata": {},
   "outputs": [],
   "source": [
    "s.to_csv(\"subscriber.csv\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}