# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

# + slideshow={"slide_type": "slide"}
b_parameters = {
    "B_1": "D76.V1-level1",
    "B_2": "D76.V8",
    "B_3": "*None*", 
    "B_4": "*None*", 
    "B_5": "*None*"
}
m_parameters = {
    "M_1": "D76.M1",
    "M_2": "D76.M2",
    "M_3": "D76.M3",
}
f_parameters = {
    "F_D76.V1": ["*All*"],
    "F_D76.V10": ["*All*"],
    "F_D76.V2": ["K00-K92"],
    "F_D76.V27": ["*All*"],
    "F_D76.V9": ["*All*"]
}
i_parameters = {
    "I_D76.V1": "*All* (All Dates)",
    "I_D76.V10": "*All* (The United States)",
    "I_D76.V2": "K00-K92 (Diseases of the digestive system)",
    "I_D76.V27": "*All* (The United States)",
    "I_D76.V9": "*All* (The United States)"
}
v_parameters = {
    "V_D76.V1": "",
    "V_D76.V10": "",
    "V_D76.V11": "*All*",
    "V_D76.V12": "*All*",
    "V_D76.V17": "*All*",
    "V_D76.V19": "*All*",
    "V_D76.V2": "",
    "V_D76.V20": "*All*",
    "V_D76.V21": "*All*",
    "V_D76.V22": "*All*",
    "V_D76.V23": "*All*",
    "V_D76.V24": "*All*",
    "V_D76.V25": "*All*",
    "V_D76.V27": "",
    "V_D76.V4": "*All*",
    "V_D76.V5": ["15-24", "25-34", "35-44"],
    "V_D76.V51": "*All*",
    "V_D76.V52": "*All*",
    "V_D76.V6": "00",
    "V_D76.V7": "*All*",
    "V_D76.V8": "*All*",
    "V_D76.V9": ""
}
o_parameters = {
    "O_V10_fmode": "freg",
    "O_V1_fmode": "freg",
    "O_V27_fmode": "freg",
    "O_V2_fmode": "freg",
    "O_V9_fmode": "freg",
    "O_aar": "aar_none",
    "O_aar_pop": "0000",
    "O_age": "D76.V5",
    "O_javascript": "on",
    "O_location": "D76.V9",
    "O_precision": "9",
    "O_rate_per": "100000",
    "O_show_totals": "false",
    "O_timeout": "300",
    "O_title": "Digestive Disease Deaths, by Year and Race",
    "O_ucd": "D76.V2",
    "O_urban": "D76.V19"
}
vm_parameters = {
    "VM_D76.M6_D76.V10": "",
    "VM_D76.M6_D76.V17": "*All*",
    "VM_D76.M6_D76.V1_S": "*All*",
    "VM_D76.M6_D76.V7": "*All*",
    "VM_D76.M6_D76.V8": "*All*"
}
misc_parameters = {
    "action-Send": "Send",
    "finder-stage-D76.V1": "codeset",
    "finder-stage-D76.V1": "codeset",
    "finder-stage-D76.V2": "codeset",
    "finder-stage-D76.V27": "codeset",
    "finder-stage-D76.V9": "codeset",
    "stage": "request"
}


# + slideshow={"slide_type": "slide"}
def createParameterList(parameterList):
    parameterString = ""
    
    for key in parameterList:
        parameterString += "<parameter>\n"
        parameterString += "<name>" + key + "</name>\n"
        
        if isinstance(parameterList[key], list):
            for value in parameterList[key]:
                parameterString += "<value>" + value + "</value>\n"
        else:
            parameterString += "<value>" + parameterList[key] + "</value>\n"
        
        parameterString += "</parameter>\n"
        
    return parameterString


# + slideshow={"slide_type": "slide"}
xml_request = "<request-parameters>\n"
xml_request += createParameterList(b_parameters)
xml_request += createParameterList(m_parameters)
xml_request += createParameterList(f_parameters)
xml_request += createParameterList(i_parameters)
xml_request += createParameterList(o_parameters)
xml_request += createParameterList(vm_parameters)
xml_request += createParameterList(v_parameters)
xml_request += createParameterList(misc_parameters)
xml_request += "</request-parameters>"

# + slideshow={"slide_type": "slide"}
import requests

url = "https://wonder.cdc.gov/controller/datarequest/D76"
response = requests.post(url, data={"request_xml": xml_request, "accept_datause_restrictions": "true"})

if response.status_code == 200:
    data = response.text
else:
    print("something went wrong")

# + slideshow={"slide_type": "slide"}
import bs4 as bs
import pandas as pd


# + code_folding=[] slideshow={"slide_type": "slide"}
def xml2df(xml_data):
    root = bs.BeautifulSoup(xml_data,"lxml")
    all_records = []
    row_number = 0
    rows = root.find_all("r")
    
    for row in rows:
        if row_number >= len(all_records):
            all_records.append([])
              
        for cell in row.find_all("c"):
            if 'v' in cell.attrs:
                try:
                    all_records[row_number].append(float(cell.attrs["v"].replace(',','')))
                except ValueError:
                    all_records[row_number].append(cell.attrs["v"])
            else:
                if 'r' not in cell.attrs:
                    all_records[row_number].append(cell.attrs["l"])
                else:
                
                    for row_index in range(int(cell.attrs["r"])):
                        if (row_number + row_index) >= len(all_records):
                            all_records.append([])
                            all_records[row_number + row_index].append(cell.attrs["l"])
                        else:
                            all_records[row_number + row_index].append(cell.attrs["l"])
                                           
        row_number += 1
    return all_records


# + slideshow={"slide_type": "slide"}
data_frame = xml2df(data)

pd.DataFrame(data=data_frame, columns=["Year", "Race", "Deaths", "Population", "Crude Rate Per 100,000"])

StarcoderdataJupyterScriptsDedupFiltered