29:null 2a:["$","title",null,{"children":"Custom Bar Charts for Water Service Line Reports | Eli Standard | Observable"}] 2f:Tb5a,htl.html`` 30:T491,// Filter ServiceLines_CSV based on the checkbox and then create ServiceLines_Objects // If the Private Side classification checkbox is checked, exclude all values with an empty Private Side Material // If the Public Side classification checkbox is checked, exclude all values with an empty Public Side Material // Makes an array of objects, totalling the number of rows per category for each categorical field ServiceLines_Objects = Array.from(d3.rollup( (filterPrivateSide ? ServiceLines_CSV.filter(d => (d["Private Side Material"] && d["Private Side Material"].trim() !== "") || (d["Classification - Private Side"] && d["Classification - Private Side"].trim() !== "")) : ServiceLines_CSV ).filter(d => filterPublicSide ? (d["Public Side Material"] && d["Public Side Material"].trim() !== "") || (d["Classification - Public Side"] && d["Classification - Public Side"].trim() !== "") : true ), v => v.length, d => d[fieldToClassify] )).map(function(arr) { return {material: arr[0] ?? 'Unknown (null)', count: arr[1]}; }) // Sort by count in descending order (displays bars from longest to shortest) .sort((a, b) => b.count - a.count);31:T563,// Creates an array with a list of categories from the data and assigns each one a custom color. // Any category can be assinged a color. If one is not assigned, it gets assigned automatically in another block below. typicalMaterialColorAssignments = [ { material: "Copper", color: '#ff745c'}, { material: "Other", color: '#57db83' }, { material: "Nonlead", color: '#57db83' }, { material: "Not Lead", color: '#57db83' }, { material: "Other (Nonlead)", color: '#57db83' }, { material: "Cast Iron", color: '#616161' }, { material: "HDPE", color: '#383838' }, { material: "Ductile Iron", color: '#2c2a26' }, { material: "PEX", color: '#578fea' }, { material: "PVC", color: '#e6e6e6' }, { material: "Plastic", color: '#578fea' }, { material: "Unknown", color: '#ff1f1f' }, { material: null, color: '#ff1f1f' }, { material: "Unknown (null)", color: '#ff1f1f' }, { material: "Lead", color: '#ff1f1f' }, { material: "Galvanized", color: '#707070' }, { material: "Iron", color: '#707070' }, { material: "Steel", color: '#707070' }, { material: "PVC", color: '#939598' }, { material: "Brass", color: '#ffbe4d' }, { material: "Private Side Classified", color: '#57db83' }, { material: "Observed", color: '#57db83' }, { material: "Yes", color: '#57db83' }, { material: "No", color: '#ff1f1f' }, { material: "Refusal", color: '#ff0000' } ];32:T64d,// Function that assigns a color to a category if it's in typicalMaterialColorAssignments. Otherwise it assigns a color from the MSK Colors array and loops through assigning colors to each next empty one. /*colorAssignmentsForThisFile = { const result = []; let currentColorIdx = 0; for (const material of materialsArray) { let color = colorDictionary[material]; if (!color) { // Assign a color from mskColors and update the index color = mskColors[currentColorIdx % mskColors.length]; currentColorIdx += 1; // Move to the next color for the next material } result.push({ material: material, color: color }); } return result; }*/ colorAssignmentsForThisFile = { const result = []; let currentColorIdx = 0; const assignedColors = {}; // To keep track of which material has been assigned which color for (const material of materialsArray) { let color = colorDictionary[material]; // Check if the material already has a color, or if we need to assign a new one from mskColors if (!color && !assignedColors[material]) { // Assign a color from mskColors and update the index color = mskColors[currentColorIdx % mskColors.length]; assignedColors[material] = color; // Store the assigned color for future reference currentColorIdx += 1; // Move to the next color for the next material } else if (!color) { // If color isn't in the dictionary but was previously assigned in assignedColors color = assignedColors[material]; } result.push({ material: material, color: color }); } return result; } 33:[{"id":"13eeeb01c9f548b6d6098f48fa4308846b7ae40f9180a5002b3a6aa4d5cfc305278f3ffe4f2401d444a2599a03a473bc19355338fd63f40b5baaf55145941d15","href":"https://static.observableusercontent.com/files/13eeeb01c9f548b6d6098f48fa4308846b7ae40f9180a5002b3a6aa4d5cfc305278f3ffe4f2401d444a2599a03a473bc19355338fd63f40b5baaf55145941d15","name":"2024 0909 Derby Center CSV for Bar Charts.csv","type":"text/csv","lastModified":1755278240805.382,"size":56725}] 2d:{"id":"81154958db30bdad","slug":null,"title":"Custom Bar Charts for Water Service Line Reports","thumbnail":"41a43111e7a63bf1d233ce94eca20ad796bbaa679f57a8f252911af9f67c8d6b","access_level":"public","create_time":"$D2025-08-15T17:17:20.805Z","update_time":"$D2025-08-15T17:17:20.805Z","view_time":null,"first_public_time":"$D2025-08-15T17:17:20.805Z","fork":{"id":"b1df3d4ee17cd8f7","slug":"custom-bar-charts-for-sl-reports","title":"Custom Bar Charts for Water Service Line Reports","owner":{"id":"4a23620d7c44927c","login":"msk-engineers","name":"MSK Engineers"}},"stars":0,"starred":false,"owner":{"type":"team","id":"1fc4efa9ca05c06e","login":"elistandard","name":"Eli Standard","avatar_url":"https://avatars-next.observableusercontent.com/4c1b8090cff6c12c6bf97e8cdb4931f3e0e6321dbef5c66c031ce98e49bf9c75","tier":"starter_2024"},"version":1987,"publish_version":null,"role":null,"authored":false,"authors":[{"type":"individual","id":"b21f049d995eb1d5","login":"elistandard","name":"Eli Standard","avatar_url":"https://avatars-next.observableusercontent.com/4c1b8090cff6c12c6bf97e8cdb4931f3e0e6321dbef5c66c031ce98e49bf9c75","tier":null}],"members":[{"type":"team","id":"1fc4efa9ca05c06e","login":"elistandard","name":"Eli Standard","avatar_url":"https://avatars-next.observableusercontent.com/4c1b8090cff6c12c6bf97e8cdb4931f3e0e6321dbef5c66c031ce98e49bf9c75","tier":"starter_2024","role":"editor"},{"type":"individual","id":"b21f049d995eb1d5","login":"elistandard","name":"Eli Standard","avatar_url":"https://avatars-next.observableusercontent.com/4c1b8090cff6c12c6bf97e8cdb4931f3e0e6321dbef5c66c031ce98e49bf9c75","tier":null,"role":"editor"}],"comments":[],"custom_thumbnail":true,"trashed":false,"subscribed":null,"forks":0,"collection":null,"type":"notebook","body":{"title":"Custom Bar Charts for Water Service Line Reports","cells":[{"id":0,"value":"# Custom Bar Charts for Water Service Line Reports","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined"},{"id":1240,"value":"Use this page to create custom bar charts for reporting on water service line data.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined"},{"id":1250,"value":"### Start by forking this document\nIn the upper right corner of this page across from the profile picture, click on the second button in that says \"Fork this notebook\". Click on 'Public' or 'Unlisted' and then 'Fork'. This will make a copy so you can create your own bar charts.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined"},{"id":1398,"value":"### Download your CSV from Airtable","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined"},{"id":1401,"value":"Before you download your CSV from Airtable. **Hide any fields that contain homeowner information.** This data is proprietary and shouldn't be included in the uploaded data.\n \nIn Inventory this includes:\n1. **Homeowner**\n2. **Contact Name(s)**\n3. **Contact Phone(s)**\n4. **Contact Email(s)**\n5. **Contact info**\n\nIn Activities this includes:\n1. **Primary Contact Name**\n3. **Primary Contact Phone**\n4. **Primary Contact Email**\n\nWhen these fields have been hidden, you can download your CSV by clicking on the Grid View Name on the top left of the menu tab and clicking 'Download CSV'. \nRename this CSV to include the name of the water system and today's date.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined"},{"id":1841,"value":"### Validate the file","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined"},{"id":1845,"value":"Select the CSV from your computer to validate it for upload.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined"},{"id":1794,"value":"viewof ProcessedCSV = {\n const input = html``;\n \n const resultDiv = html`

`;\n input.onchange = async event => {\n const file = event.target.files[0];\n let data = null;\n \n if (file) {\n // Read the CSV file\n const text = await file.text();\n data = d3.csvParse(text);\n\n // Get CSV headers\n const headers = data.columns;\n\n // Validate the CSV headers\n if (validateCSV(headers)) {\n resultDiv.innerHTML = \"This CSV contains forbidden fields. Do not upload.\";\n data = null; // Prevent processing of forbidden CSV\n } else {\n resultDiv.innerHTML = \"CSV is validated. Proceed to next step.\";\n }\n }\n\n // Update the ProcessedCSV variable with the valid data or null\n input.value = data;\n input.dispatchEvent(new CustomEvent(\"input\"));\n };\n\n return html`${input}${resultDiv}`;\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":1847,"value":"If your CSV contains forbidden fields, you still need to hide all fields with homeowner information and redownload it from Airtable.\n\nIf you CSV is validated, proceed to the next step. Choosing your file for validation did not upload it to this document, it only approved it for upload.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined"},{"id":1259,"value":"### Upload your CSV to this document","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined"},{"id":1262,"value":" **Click on the paper clip icon** at the top of the menu on the right side of this page. \nClick on the plus sign (+) across from 'File attachments'. Select the validated csv from your computer.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined"},{"id":1270,"value":"### Add your file name to the code block below","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined"},{"id":1272,"value":"**In the code block below, replace the text in the FileAttachment function by typing your filename between the quotation marks.** For example, instead of \"SouthBurlington-Inventory-0821-2024.csv\" type \"YourWaterSystem-Today'sDate.csv\"\n\nThis is the active file that data will be pulled from.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined"},{"id":5,"value":"// Read in your CSV by typing your file name into the FileAttachment function, between the quotation marks.\nServiceLines_CSV = FileAttachment(\"2024 0909 Derby Center CSV for Bar Charts.csv\").csv({typed: true});","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":1913,"value":"After the file name has been changed, **RUN THE CELL by clicking the blue triangle in the upper right corner of the cell** (you can also hit Shift+Enter on a PC). This will update the active data. If you get an error, check the file name for spelling errors and run the cell again. The file name needs to match the name of your uploaded file exactly. Often \".csv\" gets repeated twice by accident.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined"},{"id":460,"value":"data = ServiceLines_CSV","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":1318,"value":"### Select a field to classify in the bar chart, enter a custom title, and filter the public or private side data that will be included","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined"},{"id":1954,"value":"The checkboxes below filter out rows that did not result in private or public side classifications. Use these when you want to adjust the total number of service lines represented in the chart. These adjust percentage values. For example, maybe you want to only display Airtable activities that resulted in private side classifications, rather than all activities.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined"},{"id":1657,"value":"viewof filterPrivateSide = Inputs.toggle({\n label: \"Exclude rows that do not contain Private Side classification data\",\n value: false\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":1744,"value":"viewof filterPublicSide = Inputs.toggle({\n label: \"Exclude rows that do not contain Public Side classification data\",\n value: false\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":1956,"value":"The checkbox below simply hides the unknown (null) values from any chart. Use this when you want to hide the unknown values, but don't want to change the total number of service lines displayed by the chart. This does not adjust percentage values.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined"},{"id":1642,"value":"viewof excludeNullUnknowns = Inputs.toggle({\n label: \"Hide the bar for Unknown (nulll) values\",\n value: false\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":1958,"value":"The checkbox below hides the percentage values at the end of each bar. Use this when the above box is checked, which makes the displayed percentage values add up to less than 100.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined"},{"id":1631,"value":"viewof showPercentage = Inputs.toggle({\n label: \"Include percentages at the end of each bar\",\n value: true\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":1193,"value":"viewof customTitle = Inputs.text({label:\"Custom Title\", placeholder: \"Enter custom title (optional)\"})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":1462,"value":"viewof customBoxLabel = Inputs.text({label:\"Custom Box Label\", placeholder: \"Enter custom box label (optional)\", value: \"Total Service Lines\"})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":1465,"value":"viewof customSubtitle = Inputs.text({label:\"Custom Subtitle\", placeholder: \"Enter custom subtitle (optional)\"})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":1961,"value":"Use this dropdown menu to select the desired field.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined"},{"id":500,"value":"viewof fieldToClassify = Inputs.select(categoricalFields, {\n value: categoricalFields.includes(\"Activity Type\") ? \"Activity Type\" : \"Classification - Private Side\",\n label: \"Field to classify\"\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":28,"value":"$2f","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":1390,"value":"To use this bar chart in your report, simply take a screenshot with the Snipping Tool","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined"},{"id":1285,"value":"# Appendix","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined"},{"id":1287,"value":"Below is the code that runs this document.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined"},{"id":1327,"value":"### Data","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined"},{"id":1760,"value":"// List of forbidden fields\nforbiddenFields = [\"Contact Name(s)\", \"Contact Phone(s)\", \"Contact Email(s)\", \"Contact info\", \"Primary Contact Name\", \"Primary Contact Phone\", \"Primary Contact Email\"];","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":1763,"value":"// Function to validate the CSV file\nfunction validateCSV(headers) {\n return forbiddenFields.some(field => headers.includes(field));\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":1690,"value":"//Function to detect the data type of each field in the CSV\nfunction detectColumnTypes(data) {\n const columnTypes = {};\n\n data.columns.forEach(column => {\n for (let i = 0; i < data.length; i++) {\n const value = data[i][column];\n if (value !== null && value !== undefined && value !== \"\") {\n columnTypes[column] = typeof value === 'string' && !isNaN(Number(value))\n ? 'number'\n : typeof value;\n break;\n }\n }\n });\n\n return columnTypes;\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":1698,"value":"// Detect the data types of each field in the CSV\ndetectColumnTypes(ServiceLines_CSV)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":512,"value":"// Makes an array of the categorical fields in the uploaded CSV\n/*categoricalFields = Object.keys(ServiceLines_CSV[0]).filter(key => {\n return typeof ServiceLines_CSV[0][key] == 'string' || typeof ServiceLines_CSV[0][key] == 'boolean';\n});*/\n\ncategoricalFields = Object.keys(ServiceLines_CSV[0]).filter(key => {\n // Check if any row has the field as a string or boolean\n return ServiceLines_CSV.some(row => typeof row[key] == 'string' || typeof row[key] == 'boolean');\n});","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":1753,"value":"$30","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":15,"value":"// Make an array of the categories within each categorical field\nmaterialsArray = ServiceLines_Objects.map(obj => obj.material ?? 'Unknown');","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":20,"value":"// Calcluates the total number of service lines in an inventory by counting the number of rows in the CSV\n totalLines = {\n const sumArray = ServiceLines_Objects.map(obj => obj.count);\n const totalLines = sumArray.reduce((accumulator, currentValue) => accumulator + currentValue, 0);\n return totalLines;\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":1544,"value":"unfilteredData = ServiceLines_Objects","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":1520,"value":"// Creates a new dataset without Unknown values if checkbox is checked\nfilteredData = {\n let data;\n if (excludeNullUnknowns != true) {\n data = ServiceLines_Objects;\n } else {\n data = ServiceLines_Objects.filter(d => d.material !== null && d.material !== \"Unknown\" && d.material !== \"Unknown (null)\");\n }\n return data;\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":1542,"value":"activeData = excludeNullUnknowns ? filteredData : unfilteredData;","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":1590,"value":"// Makes an array of the counts in each category and their percentages so they can be toggled on and off with the checkbox\nbarLabels = activeData.map(d => {\n let label = `${d.count.toLocaleString()}`; // Always include the count\n if (showPercentage == true) {\n label += ` (${(d.count / totalLines * 100).toFixed(1)}%)`; // Append percentage if checkbox is checked\n }\n return label;\n});","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":1292,"value":"### Text","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined"},{"id":1228,"value":"customFontFamily = \"Calibri, Arial, sans-serif\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":1225,"value":"customFontSize = 28","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":1209,"value":"title = customTitle !== \"\" ? customTitle : fieldToClassify;","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":964,"value":"// Function to estimate the width of a text block\n// Used to space out the grey rectangle with 'Total Lines' from the Title\nfunction estimateTextWidth(text, fontSize, fontWeight, fontFamily = customFontFamily) {\n const canvas = document.createElement('canvas');\n const context = canvas.getContext('2d');\n context.font = `${fontWeight} ${fontSize}px ${fontFamily}`;\n const metrics = context.measureText(text);\n return metrics.width;\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":1095,"value":"// Sets the font size for 'Total Lines' 13 pixels smaller than the title's custom font size\ntotalLinesCustomFontSize = customFontSize - 13","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":1136,"value":"// // Sets the font size for the text on the axes 15 pixels smaller than the title's custom font size\naxesCustomFontSize = customFontSize - 15","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":1077,"value":"// Calculates the width of the grey rectangle with 'Total Lines'\ntotalLinesWidth = estimateTextWidth(totalLines, totalLinesCustomFontSize, 'bold') + estimateTextWidth(customBoxLabel, totalLinesCustomFontSize, 'bold') + 24","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":970,"value":"// Calculates the width of the title so the grey rectangle can be spaced appropriately\nestimatedTitleWidth = estimateTextWidth(title, customFontSize, 'bold');","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":1290,"value":"### Colors","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined"},{"id":1157,"value":"// Function to display an array of hex codes as colored squares\nfunction swatches(colors, name) {\n if (!Array.isArray(colors)) {\n colors = d3[`scheme${name}`];\n if (!colors) {\n throw new Error(`Invalid color scheme name: ${name}`);\n }\n }\n\n const n = colors.length;\n const dark = d3.lab(colors[0]).l < 50;\n const canvas = svg`