2a:null 2b:["$","title",null,{"children":"Exploring PPP loans approved for New Mexico companies | Christopher L. Keller | Observable"}] 30:T6e0,dashboardDisplay = html`

Topline PPP Figures for New Mexico

Number of loans approved ${parseInt(totalApprovedLoans).toLocaleString()} Total approved loans ${totalApprovedLoanAmount.toLocaleString('en-US', { style: 'currency', currency: 'USD' })}

Loans with jobs figure ${parseInt( jobsCheck.hasJobs.length ).toLocaleString()} Total approved loans ${withJobsApprovedLoanAmount.toLocaleString('en-US', { style: 'currency', currency: 'USD' })}

Loans without jobs figure ${parseInt( jobsCheck.lackJobs.length ).toLocaleString()} Total approved loans ${withoutJobsApprovedLoanAmount.toLocaleString( 'en-US', { style: 'currency', currency: 'USD' } )}

`31:T8c5,md` # About the data The data release on Tuesday includes company names and specific amounts for some 5.2 million Paycheck Protection Program loans that the Small Business Administration approved from April 2020 through the end of August 2020. For loans approved for New Mexico, we considered data and loan approval details for ${parseInt( totalApprovedLoans ).toLocaleString()} loans that total ${totalApprovedLoanAmount.toLocaleString( 'en-US', { style: 'currency', currency: 'USD' } )} The SBA released the data after a lawsuit filed in May by several news organizations, including this newspaper's parent, American City Business Journals. Previous PPP data contained some detailed information, but only for approved loans above $150,000. But even this information lacked specifics. Instead of specific loan approval amounts, the SBA used dollar ranges. For smaller loans, the data contained redacted business names and contained only industry sector designations. That's not to say the data released on Tuesday is pristine and comprehensive. It is not. Among the areas that stick out: - The data contain only active loans, and doesn't include loans that were cancelled of given back. - In terms of lending to New Mexico businesses, the data contains fewer records than previous SBA reports. At this time, it is unclear if this is related to the SBA including only active loans. - While the data contains some information about the number of jobs a business reported, about ${jobsCheck.lackJobsPct.toLocaleString( 'en-US', { style: 'percent', minimumFractionDigits: 2 } )} of the approved loans show zero jobs or contained no value for jobs reported by a business. - The data overall contains spelling errors for business names, cities and lenders, and in some cases transposed information. The lender data lacks location information, which means a loan approved by First American Bank could be the one in Artesia, New Mexico, or the one in Melrose Park, Illinois. Even with its faults, the PPP data released Tuesday provides the best picture of the federal program's effort to keep workers paid and businesses functioning amid the wide-spread and deeply-felt economic impacts of the Covid-19 pandemic. `32:T6ce,lenderCountFacetByDate = vl({ data: { values: facetedLenderCount }, mark: 'line', point: true, title: { text: 'No. of PPP loans approved by date by N.M.\'s top lenders', orient: 'top', anchor: 'start', font: chartFonts.headlineFont, fontSize: chartFonts.headlineSize, color: chartFonts.headlineColor, subtitle: ['\n', 'Source: Small Business Administration', '\n'], subtitleFont: chartFonts.subtitleFont, subtitleFontSize: chartFonts.subtitleFontSize, subtitlePadding: chartFonts.subtitlePadding, subtitleColor: chartFonts.subtitleColor, subtitleFontWeight: chartFonts.FontWeight }, encoding: { // tooltip: { // field: 'rate', // type: 'quantitative' // }, facet: { field: 'lender', type: 'nominal', sort: { field: 'dateapproved', aggregate: 'count', order: 'descending' } }, x: { field: 'dateapproved', type: 'temporal', title: 'Date', axis: { tickCount: 4 } }, y: { field: 'dateapproved', aggregate: 'count', type: 'quantitative', title: 'No. of loans approved', scale: { zero: false } } }, resolve: { scale: { // x: "independent", // y: "independent" }, axis: { x: "independent", y: "independent" } }, width: 210, height: 150, config: { axis: { labelFont: chartFonts.labelFont, labelFontSize: chartFonts.labelFontSize, labelFontWeight: chartFonts.labelFontWeight, titleFont: chartFonts.titleFont, titleFontSize: chartFonts.titleFontSize, titleFontWeight: chartFonts.titleFontWeight }, facet: { columns: 3 } } })33:T6dd,lenderTotalFacetByDate = vl({ data: { values: facetedLenderCount }, mark: 'line', point: true, title: { text: 'Amount of PPP loans approved by date by N.M.\'s top lenders', orient: 'top', anchor: 'start', font: chartFonts.headlineFont, fontSize: chartFonts.headlineSize, color: chartFonts.headlineColor, subtitle: ['\n', 'Source: Small Business Administration', '\n'], subtitleFont: chartFonts.subtitleFont, subtitleFontSize: chartFonts.subtitleFontSize, subtitlePadding: chartFonts.subtitlePadding, subtitleColor: chartFonts.subtitleColor, subtitleFontWeight: chartFonts.FontWeight }, encoding: { // tooltip: { // field: 'rate', // type: 'quantitative' // }, facet: { field: 'lender', type: 'nominal', sort: { field: 'approved_loan_amount', aggregate: 'sum', order: 'descending' } }, x: { field: 'dateapproved', type: 'temporal', title: 'Date', axis: { tickCount: 4 } }, y: { field: 'approved_loan_amount', aggregate: 'sum', type: 'quantitative', title: 'No. of loans approved', scale: { zero: false } } }, resolve: { scale: { // x: "independent", // y: "independent" }, axis: { x: "independent", y: "independent" } }, width: 150, height: 150, config: { axis: { labelFont: chartFonts.labelFont, labelFontSize: chartFonts.labelFontSize, labelFontWeight: chartFonts.labelFontWeight, titleFont: chartFonts.titleFont, titleFontSize: chartFonts.titleFontSize, titleFontWeight: chartFonts.titleFontWeight }, facet: { columns: 3 } } })34:T7ea,plotJobsByAmount = vl({ data: { values: pppDataWorking }, transform: [ { filter: "datum.jobs_reported > 0" } ], title: { text: ['Jobs reported compared to approved loan amounts'], orient: 'top', anchor: 'start', font: chartFonts.headlineFont, fontSize: chartFonts.headlineSize, color: chartFonts.headlineColor, subtitle: ['\n', 'Source: Small Business Administration', '\n'], subtitleFont: chartFonts.subtitleFont, subtitleFontSize: chartFonts.subtitleFontSize, subtitlePadding: chartFonts.subtitlePadding, subtitleColor: chartFonts.subtitleColor, subtitleFontWeight: chartFonts.FontWeight }, layer: [ { mark: 'circle', encoding: { tooltip: [ { field: 'business_name' }, { field: 'approved_loan_amount' }, { field: 'jobs_reported' } ], x: { title: 'Approved Loan Amount', field: 'approved_loan_amount', type: 'quantitative' }, y: { title: 'Jobs reported', field: 'jobs_reported', type: 'quantitative' } } } // { // mark: { // type: 'line', // color: 'firebrick' // }, // transform: [ // { // regression: 'loan_amount', // on: 'jobs_reported' // } // ], // encoding: { // x: { // field: 'loan_amount', // type: 'quantitative' // }, // y: { // field: 'jobs_reported', // type: 'quantitative' // } // } // } ], width: 600, height: 450, config: { axis: { labelFont: chartFonts.labelFont, labelFontSize: chartFonts.labelFontSize, labelFontWeight: chartFonts.labelFontWeight, titleFont: chartFonts.titleFont, titleFontSize: chartFonts.titleFontSize, titleFontWeight: chartFonts.titleFontWeight } } })35:T48c,html`

Six industry sectors accounted for ${highestSectors.count.toLocaleString( 'en-US', { style: 'percent', minimumFractionDigits: 2 } )} of the ${totalApprovedLoans.toLocaleString()} loans approved for New Mexico companies under the PPP, according to the latest SBA data released Tuesday.

They top industry sectors are:

${key} businesses accounted for ${value.percentOfCount.toLocaleString( 'en-US', { style: 'percent', minimumFractionDigits: 2 } )} of the approved loans and ${value.percentOfTotal.toLocaleString( 'en-US', { style: 'percent', minimumFractionDigits: 2 } )} of the total value of the loans approved.

In all, these six industries accounted for ${highestSectors.totalAmount.toLocaleString( 'en-US', { style: 'currency', currency: 'USD' } )} — or ${highestSectors.total.toLocaleString('en-US', { style: 'percent', minimumFractionDigits: 2 })} — of the approved loans to New Mexico companies.

`36:T6b9,lendingByIndustrySector = vl({ data: { values: pppDataWorking }, title: { text: 'Count of PPP loans approved by industry group', orient: 'top', anchor: 'start', font: chartFonts.headlineFont, fontSize: chartFonts.headlineSize, color: chartFonts.headlineColor, subtitle: ['\n', 'Source: Small Business Administration', '\n'], subtitleFont: chartFonts.subtitleFont, subtitleFontSize: chartFonts.subtitleFontSize, subtitlePadding: chartFonts.subtitlePadding, subtitleColor: chartFonts.subtitleColor, subtitleFontWeight: chartFonts.FontWeight }, encoding: { // tooltip: { // aggregate: 'count', // field: 'naics_group' // }, x: { title: 'Number of loans approved', field: 'naics_group', // type: 'nominal', aggregate: 'count', scale: { domain: [0, 3000] } }, y: { title: 'Industry sector', field: 'naics_group', // type: 'nominal', sort: { op: 'count', field: 'naics_group', order: 'descending' } } }, layer: [ { mark: 'bar' }, { mark: { type: 'text', align: 'left', baseline: 'middle', stroke: 'black', fontSize: 14, dx: 5 }, encoding: { text: { field: 'naics_group', aggregate: 'count' } } } ], width: 600, height: 450, config: { axis: { labelFont: chartFonts.labelFont, labelFontSize: chartFonts.labelFontSize, labelFontWeight: chartFonts.labelFontWeight, titleFont: chartFonts.titleFont, titleFontSize: chartFonts.titleFontSize, titleFontWeight: chartFonts.titleFontWeight } } })37:T6ca,loanTotalByIndustrySector = vl({ data: { values: pppDataWorking }, title: { text: 'Sum of PPP loans approved by industry group', orient: 'top', anchor: 'start', font: chartFonts.headlineFont, fontSize: chartFonts.headlineSize, color: chartFonts.headlineColor, subtitle: ['\n', 'Source: Small Business Administration', '\n'], subtitleFont: chartFonts.subtitleFont, subtitleFontSize: chartFonts.subtitleFontSize, subtitlePadding: chartFonts.subtitlePadding, subtitleColor: chartFonts.subtitleColor, subtitleFontWeight: chartFonts.FontWeight }, encoding: { tooltip: { aggregate: 'sum', field: 'approved_loan_amount' }, x: { title: 'Amount of loans approved', field: 'approved_loan_amount', type: 'quantitative', aggregate: 'sum' // scale: { domain: [0, 3000] } }, y: { title: 'Industry sector', field: 'naics_group', type: 'nominal', sort: { op: 'sum', field: 'approved_loan_amount', order: 'descending' } } }, layer: [ { mark: 'bar' }, { mark: { type: 'text', align: 'left', baseline: 'middle', stroke: 'black', fontSize: 14, dx: 5 }, encoding: { text: { field: 'approved_loan_amount', aggregate: 'sum' } } } ], width: 600, height: 450, config: { axis: { labelFont: chartFonts.labelFont, labelFontSize: chartFonts.labelFontSize, labelFontWeight: chartFonts.labelFontWeight, titleFont: chartFonts.titleFont, titleFontSize: chartFonts.titleFontSize, titleFontWeight: chartFonts.titleFontWeight } } })38:T822,plotIndustryLoanCountTotal = vl({ data: { values: sortedSectorApprovedCount }, // transform: [ // { // filter: "datum.jobs_reported > 0" // } // ], title: { text: ['Total approved loan amounts by sector compared to number of loans'], orient: 'top', anchor: 'start', font: chartFonts.headlineFont, fontSize: chartFonts.headlineSize, color: chartFonts.headlineColor, subtitle: [ 'Does not include approved loans in which zero jobs were reported', '\n', 'Source: Small Business Administration', '\n' ], subtitleFont: chartFonts.subtitleFont, subtitleFontSize: chartFonts.subtitleFontSize, subtitlePadding: chartFonts.subtitlePadding, subtitleColor: chartFonts.subtitleColor, subtitleFontWeight: chartFonts.FontWeight }, layer: [ { mark: 'circle', encoding: { tooltip: [ { field: 'key' }, { field: 'value.total' }, { field: 'value.count' } ], x: { title: 'No. of N.M. Loans', field: 'value.count', type: 'quantitative' }, y: { title: 'Amount of N.M. Loans', field: 'value.total', type: 'quantitative' } } }, { mark: { type: 'line', color: 'firebrick' }, transform: [ { regression: 'value.total', on: 'value.count' } ], encoding: { x: { field: 'value.count', type: 'quantitative' }, y: { field: 'value.total', type: 'quantitative' } } } ], width: 600, height: 450, config: { axis: { labelFont: chartFonts.labelFont, labelFontSize: chartFonts.labelFontSize, labelFontWeight: chartFonts.labelFontWeight, titleFont: chartFonts.titleFont, titleFontSize: chartFonts.titleFontSize, titleFontWeight: chartFonts.titleFontWeight } } })39:T839,plotIndustryJobsTotal = vl({ data: { values: sortedSectorApprovedCount }, // transform: [ // { // filter: "datum.jobs_reported > 0" // } // ], title: { text: ['Total approved loan amounts by sector compared to jobs reported'], orient: 'top', anchor: 'start', font: chartFonts.headlineFont, fontSize: chartFonts.headlineSize, color: chartFonts.headlineColor, subtitle: [ 'Does not include approved loans in which zero jobs were reported', '\n', 'Source: Small Business Administration', '\n' ], subtitleFont: chartFonts.subtitleFont, subtitleFontSize: chartFonts.subtitleFontSize, subtitlePadding: chartFonts.subtitlePadding, subtitleColor: chartFonts.subtitleColor, subtitleFontWeight: chartFonts.FontWeight }, layer: [ { mark: 'circle', encoding: { tooltip: [ { field: 'key' }, { field: 'value.total' }, { field: 'value.jobsReported' } ], x: { title: 'No. of jobs reported', field: 'value.jobsReported', type: 'quantitative' }, y: { title: 'Amount of N.M. Loans', field: 'value.total', type: 'quantitative' } } }, { mark: { type: 'line', color: 'firebrick' }, transform: [ { regression: 'value.jobsReported', on: 'value.total' } ], encoding: { x: { field: 'value.jobsReported', type: 'quantitative' }, y: { field: 'value.total', type: 'quantitative' } } } ], width: 600, height: 450, config: { axis: { labelFont: chartFonts.labelFont, labelFontSize: chartFonts.labelFontSize, labelFontWeight: chartFonts.labelFontWeight, titleFont: chartFonts.titleFont, titleFontSize: chartFonts.titleFontSize, titleFontWeight: chartFonts.titleFontWeight } } })3a:T830,plotIndustryJobsCount = vl({ data: { values: sortedSectorApprovedCount }, // transform: [ // { // filter: "datum.jobs_reported > 0" // } // ], title: { text: ['No. of loans approved by sector compared to jobs reported'], orient: 'top', anchor: 'start', font: chartFonts.headlineFont, fontSize: chartFonts.headlineSize, color: chartFonts.headlineColor, subtitle: [ 'Does not include approved loans in which zero jobs were reported', '\n', 'Source: Small Business Administration', '\n' ], subtitleFont: chartFonts.subtitleFont, subtitleFontSize: chartFonts.subtitleFontSize, subtitlePadding: chartFonts.subtitlePadding, subtitleColor: chartFonts.subtitleColor, subtitleFontWeight: chartFonts.FontWeight }, layer: [ { mark: 'circle', encoding: { tooltip: [ { field: 'key' }, { field: 'value.jobsReported' }, { field: 'value.count' } ], x: { title: 'No. of jobs reported', field: 'value.jobsReported', type: 'quantitative' }, y: { title: 'No. of N.M. Loans', field: 'value.count', type: 'quantitative' } } }, { mark: { type: 'line', color: 'firebrick' }, transform: [ { regression: 'value.jobsReported', on: 'value.count' } ], encoding: { x: { field: 'value.jobsReported', type: 'quantitative' }, y: { field: 'value.count', type: 'quantitative' } } } ], width: 600, height: 450, config: { axis: { labelFont: chartFonts.labelFont, labelFontSize: chartFonts.labelFontSize, labelFontWeight: chartFonts.labelFontWeight, titleFont: chartFonts.titleFont, titleFontSize: chartFonts.titleFontSize, titleFontWeight: chartFonts.titleFontWeight } } })3b:T664,countLoansByMonth = vl({ data: { values: pppDataWorking }, title: { text: 'Count of PPP loans approved for New Mexico companies by month', orient: 'top', anchor: 'start', font: chartFonts.headlineFont, fontSize: chartFonts.headlineSize, color: chartFonts.headlineColor, subtitle: ['\n', 'Source: Small Business Administration', '\n'], subtitleFont: chartFonts.subtitleFont, subtitleFontSize: chartFonts.subtitleFontSize, subtitlePadding: chartFonts.subtitlePadding, subtitleColor: chartFonts.subtitleColor, subtitleFontWeight: chartFonts.FontWeight }, encoding: { tooltip: { aggregate: 'count', field: 'month_approved' }, x: { title: 'Count', aggregate: 'count', field: 'month_approved', type: 'quantitative' }, y: { title: 'Month of loan approval', field: 'month_approved', type: 'ordinal', sort: ['April', 'May', 'June', 'July', 'August'] } }, layer: [ { mark: 'bar' }, { mark: { type: 'text', align: 'left', baseline: 'middle', stroke: 'black', fontSize: 14, dx: 5 }, encoding: { text: { field: 'month_approved', aggregate: 'count' } } } ], width: 600, height: 450, config: { axis: { labelFont: chartFonts.labelFont, labelFontSize: chartFonts.labelFontSize, labelFontWeight: chartFonts.labelFontWeight, titleFont: chartFonts.titleFont, titleFontSize: chartFonts.titleFontSize, titleFontWeight: chartFonts.titleFontWeight } } })3c:T643,distributionOfLoanAmounts = vl({ data: { values: pppDataWorking }, title: { text: 'Count of PPP loans approved by loan size', orient: 'top', anchor: 'start', font: chartFonts.headlineFont, fontSize: chartFonts.headlineSize, color: chartFonts.headlineColor, subtitle: ['\n', 'Source: Small Business Administration', '\n'], subtitleFont: chartFonts.subtitleFont, subtitleFontSize: chartFonts.subtitleFontSize, subtitlePadding: chartFonts.subtitlePadding, subtitleColor: chartFonts.subtitleColor, subtitleFontWeight: chartFonts.FontWeight }, encoding: { tooltip: { aggregate: 'sum', field: 'approved_loan_amount', type: 'quantitative' }, x: { title: 'Amount of approved loans', field: 'approved_loan_amount', type: 'quantitative', bin: { maxbins: 25 } }, y: { title: 'No. of approved loans', aggregate: 'count' } }, layer: [ { mark: 'bar', binSpacing: 0 }, { mark: { type: 'text', baseline: 'middle', stroke: 'black', fontSize: 14, dy: -15 }, encoding: { text: { field: 'approved_loan_amount', aggregate: 'count' } } } ], width: 800, height: 450, config: { axis: { labelFont: chartFonts.labelFont, labelFontSize: chartFonts.labelFontSize, labelFontWeight: chartFonts.labelFontWeight, titleFont: chartFonts.titleFont, titleFontSize: chartFonts.titleFontSize, titleFontWeight: chartFonts.titleFontWeight } } })3d:T752,// countByLender = vl({ // data: { // values: pppDataWorking // }, // title: { // text: 'Count of PPP loans approved for New Mexico industry groups', // orient: 'top', // anchor: 'start', // font: chartFonts.headlineFont, // fontSize: chartFonts.headlineSize, // color: chartFonts.headlineColor, // subtitle: ['\n', 'Source: Small Business Administration', '\n'], // subtitleFont: chartFonts.subtitleFont, // subtitleFontSize: chartFonts.subtitleFontSize, // subtitlePadding: chartFonts.subtitlePadding, // subtitleColor: chartFonts.subtitleColor, // subtitleFontWeight: chartFonts.FontWeight // }, // encoding: { // // tooltip: { // // aggregate: 'count', // // field: 'naics_group' // // }, // x: { // title: 'Number of loans approved', // field: 'lender', // type: 'nominal', // aggregate: 'count' // }, // y: { // title: 'PPP lender', // field: 'lender', // // type: 'nominal', // sort: { // op: 'count', // field: 'lender', // order: 'descending' // } // } // }, // layer: [ // { // mark: 'bar' // }, // { // mark: { // type: 'text', // align: 'left', // baseline: 'middle', // stroke: 'black', // fontSize: 14, // dx: 5 // }, // encoding: { // text: { field: 'lender', aggregate: 'count' } // } // } // ], // width: 600, // height: 5000, // config: { // axis: { // labelFont: chartFonts.labelFont, // labelFontSize: chartFonts.labelFontSize, // labelFontWeight: chartFonts.labelFontWeight, // titleFont: chartFonts.titleFont, // titleFontSize: chartFonts.titleFontSize, // titleFontWeight: chartFonts.titleFontWeight // } // } // })3e:[{"id":"28ecf25c51f14cc5c95db17ae0193c1505565e795e4d01aa5d95fa2e9645e0d7391c03713fcf5ca9781bd851c308cbfd0c2bf2d6ab5b2b6a549038eacb8a53af","href":"https://static.observableusercontent.com/files/28ecf25c51f14cc5c95db17ae0193c1505565e795e4d01aa5d95fa2e9645e0d7391c03713fcf5ca9781bd851c308cbfd0c2bf2d6ab5b2b6a549038eacb8a53af","name":"ppp-data-dump - cleaned-data.csv","type":"text/csv","lastModified":1607093176826.085,"size":6390495}] 2e:{"id":"e949b7ff8cb27a90","slug":"exploring-ppp-data","title":"Exploring PPP loans approved for New Mexico companies","thumbnail":"cbcd3e961587e40ab7634d06a7387d4787cdc94c35f43a0f9aa74e8fc27e20f8","access_level":"public","create_time":"$D2020-12-01T18:54:46.263Z","update_time":"$D2021-06-17T17:21:09.471Z","view_time":null,"first_public_time":"$D2020-12-03T15:37:25.834Z","fork":false,"stars":1,"starred":false,"owner":{"type":"team","id":"b1060e5bd7932c0c","login":"chrislkeller","name":"Christopher L. Keller","avatar_url":"https://avatars-next.observableusercontent.com/480c1b063ca680d305e90abbeef687017de8de8a546032d2b65d715341bd861a","tier":"starter_2024"},"version":1323,"publish_version":1323,"role":null,"authored":false,"authors":[{"type":"individual","id":"7e214d36195dad3a","login":"chrislkeller","name":"Christopher L. Keller","avatar_url":"https://avatars-next.observableusercontent.com/480c1b063ca680d305e90abbeef687017de8de8a546032d2b65d715341bd861a","tier":null}],"members":[{"type":"individual","id":"7e214d36195dad3a","login":"chrislkeller","name":"Christopher L. Keller","avatar_url":"https://avatars-next.observableusercontent.com/480c1b063ca680d305e90abbeef687017de8de8a546032d2b65d715341bd861a","tier":null,"role":"editor"}],"comments":[],"custom_thumbnail":false,"trashed":false,"subscribed":null,"forks":0,"collection":null,"type":"notebook","body":{"title":"Exploring PPP loans approved for New Mexico companies","cells":[{"id":633,"value":"md`# Exploring PPP loans approved for New Mexico companies`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":18,"value":"tableOfContents = toc()","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":514,"value":"$30","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":378,"value":"totalApprovedLoans = pppDataWorking.length","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":370,"value":"totalApprovedLoanAmount = d3.sum(pppDataWorking, d => d.approved_loan_amount)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":1128,"value":"jobsCheck = {\n const lackJobs = pppDataWorking.filter(function(elem) {\n if (elem.jobs_reported === 0 || elem.jobs_reported === null) {\n return elem;\n }\n });\n\n const lackJobsPct = lackJobs.length / totalApprovedLoans;\n\n const hasJobs = pppDataWorking.filter(function(elem) {\n if (elem.jobs_reported != 0 && elem.jobs_reported != null) {\n return elem;\n }\n });\n\n const hasJobsPct = hasJobs.length / totalApprovedLoans;\n\n return {\n lackJobs: lackJobs,\n lackJobsPct: lackJobsPct,\n hasJobs: hasJobs,\n hasJobsPct: hasJobsPct\n };\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":1142,"value":"withJobsApprovedLoanAmount = d3.sum(\n jobsCheck.hasJobs,\n d => d.approved_loan_amount\n)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":1150,"value":"withoutJobsApprovedLoanAmount = d3.sum(\n jobsCheck.lackJobs,\n d => d.approved_loan_amount\n)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":975,"value":"$31","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":0,"value":"md`\n----\n# What do we know about lenders to New Mexico businesses?\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":300,"value":"sortedLenderCount = {\n const lenderMetrics = d3\n .nest()\n .key(d => d.lender)\n .rollup(v => ({\n count: v.length,\n percentOfCount: v.length / `${totalApprovedLoans}`,\n total: d3.sum(v, d => d.approved_loan_amount),\n percentOfTotal:\n d3.sum(v, d => d.approved_loan_amount) / `${totalApprovedLoanAmount}`,\n avg: d3.mean(v, d => d.approved_loan_amount),\n median: d3.median(v, d => d.approved_loan_amount),\n lenderApprovedLoans: v\n }))\n .entries(pppDataWorking);\n let d = [...lenderMetrics];\n d.sort(function(a, b) {\n return b.value.count - a.value.count;\n });\n return d;\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":832,"value":"sortedLenderTotal = {\n let d = [...sortedLenderCount];\n d.sort(function(a, b) {\n return b.value.total - a.value.total;\n });\n return d;\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":646,"value":"facetedLenderCount = {\n const output = [];\n sortedLenderCount.map(function(d, i) {\n output.push(d.key);\n });\n const includeList = output.slice(0, 12);\n const theseLenders = pppDataWorking.filter(function(d) {\n if (includeList.indexOf(d.lender) !== -1) {\n return d;\n }\n });\n return theseLenders;\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":852,"value":"md`**${\n sortedLenderTotal[0].key\n}** approved the highest total PPP loan amount, recording **${sortedLenderTotal[0].value.total.toLocaleString(\n 'en-US',\n {\n style: 'currency',\n currency: 'USD'\n }\n)}** in approved loans for New Mexico companies. That figure represents about **${sortedLenderTotal[0].value.percentOfTotal.toLocaleString(\n 'en-US',\n {\n style: 'percent',\n minimumFractionDigits: 2\n }\n)}** of the total amount approved for New Mexico companies.`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":313,"value":"html`

Top lenders in N.M. by number of approved amount

\n\n\n\n\n\n\n\n\n${sortedLenderTotal\n // .filter(d => d.value.count >= 100)\n .slice(0, 25)\n .map(\n ({ key, value }) =>\n html`\n\n\n\n`\n )}

Lender	No. of Loans	Median Loan	Total Anount
${key}	${value.count.toLocaleString(\"en-US\")}	${value.median.toLocaleString(\"en-US\", {\n style: \"currency\",\n currency: \"USD\"\n })}	${value.total.toLocaleString(\"en-US\", {\n style: \"currency\",\n currency: \"USD\"\n })}

`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":1314,"value":"DOM.download(serialize(sortedLenderTotal), null, \"Download CSV\")","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":858,"value":"md`\n----\n**${\n sortedLenderCount[0].key\n}** approved **${sortedLenderCount[0].value.count.toLocaleString(\n 'en-US'\n)}** Paycheck Protection Program loans for New Mexico companies, the most out of the **${\n sortedLenderCount.length\n}** unique lenders found in the New Mexico data. That figure represents about **${sortedLenderCount[0].value.percentOfCount.toLocaleString(\n 'en-US',\n {\n style: 'percent',\n minimumFractionDigits: 2\n }\n)}** of the **${totalApprovedLoans.toLocaleString()}** loans approved for New Mexico companies.\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":840,"value":"html`

Top lenders in N.M. by number of approved loans

\n\n\n\n\n\n\n\n\n${sortedLenderCount\n // .filter(d => d.value.count >= 100)\n .slice(0, 10)\n .map(\n ({ key, value }) =>\n html`\n\n\n\n`\n )}

Lender	No. of Loans	Pct of Count	Total
${key}	${value.count.toLocaleString(\"en-US\")}	${value.percentOfCount.toLocaleString(\"en-US\", {\n style: \"percent\",\n minimumFractionDigits: 2\n })}	${value.total.toLocaleString(\"en-US\", {\n style: \"currency\",\n currency: \"USD\"\n })}

`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":864,"value":"md`----`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":637,"value":"$32","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":878,"value":"$33","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":535,"value":"md`\n----\n# What do we know about New Mexico businesses?\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":524,"value":"sortedBusinessApprovedAmount = {\n const businessMetrics = d3\n .nest()\n .key(d => d.business_name)\n .rollup(v => ({\n count: v.length,\n loanAmount: d3.sum(v, d => d.approved_loan_amount),\n jobsReported: d3.sum(v, d => d.jobs_reported)\n }))\n .entries(pppDataWorking);\n let d = [...businessMetrics];\n d.sort(function(a, b) {\n return b.value.loanAmount - a.value.loanAmount;\n });\n return d;\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":541,"value":"html`\n\n\n\n\n\n\n\n\n${sortedBusinessApprovedAmount\n .filter(d => d.value.loanAmount >= 4000000)\n .map(\n ({ key, value }) =>\n html`\n\n\n\n`\n )}

Business	Loans	Total	Jobs Reported
${key}	${value.count.toLocaleString('en-US')}	${value.loanAmount.toLocaleString('en-US', {\n style: 'currency',\n currency: 'USD'\n })}	${value.jobsReported.toLocaleString('en-US')}

`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":443,"value":"$34","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":93,"value":"md`\n----\n# What do we know about lending in New Mexico by industry sector?\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":810,"value":"sortedSectorApprovedCount = {\n const sectorMetrics = d3\n .nest()\n .key(d => d.naics_group)\n .rollup(v => ({\n count: v.length,\n percentOfCount: v.length / `${totalApprovedLoans}`,\n total: d3.sum(v, d => d.approved_loan_amount),\n jobsReported: d3.sum(v, d => d.jobs_reported),\n percentOfTotal:\n d3.sum(v, d => d.approved_loan_amount) / `${totalApprovedLoanAmount}`,\n avg: d3.mean(v, d => d.approved_loan_amount),\n median: d3.median(v, d => d.approved_loan_amount),\n sectorApprovedLoans: v\n }))\n .entries(pppDataWorking);\n let d = [...sectorMetrics];\n d.sort(function(a, b) {\n return b.value.count - a.value.count;\n });\n return d;\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":1213,"value":"highestSectors = {\n const count = d3.sum(\n sortedSectorApprovedCount.slice(0, 6),\n d => d.value.percentOfCount\n );\n const total = d3.sum(\n sortedSectorApprovedCount.slice(0, 6),\n d => d.value.percentOfTotal\n );\n const totalAmount = d3.sum(\n sortedSectorApprovedCount.slice(0, 6),\n d => d.value.total\n );\n\n const output = {\n count: count,\n total: total,\n totalAmount: totalAmount,\n pctOfTotal: totalAmount / totalApprovedLoanAmount\n };\n return output;\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":1208,"value":"$35","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":1203,"value":"html`

N.M. sectors by percent of loan amount and percent of number of loans

\n\n\n\n\n\n\n\n${sortedSectorApprovedCount.map(\n ({ key, value }) =>\n html`\n\n\n`\n)}

Sector	Pct of Count	Pct of Total
${key}	${value.percentOfCount.toLocaleString('en-US', {\n style: 'percent',\n minimumFractionDigits: 2\n })}	${value.percentOfTotal.toLocaleString('en-US', {\n style: 'percent',\n minimumFractionDigits: 2\n })}

`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":37,"value":"$36","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":109,"value":"$37","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":892,"value":"$38","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":925,"value":"$39","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":971,"value":"$3a","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":191,"value":"md`\n----\n# When were most of the PPP loans approved for New Mexico companies?\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":168,"value":"$3b","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":621,"value":"md`\n----\n# What is the distribution of loan amounts?\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":554,"value":"$3c","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":1319,"value":"viewof summary_data = SummaryTable(pppDataWorking, { label: \"PPP Data\" })","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":22,"value":"md`# Data`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":27,"value":"// pppUrlWorking = \"https://cors-anywhere.herokuapp.com/https://docs.google.com/spreadsheets/d/e/2PACX-1vRZYyhIBZXdzkTv19dhbZTUmb2z9ZMplOrp9S7IhthMGUqtg8gjy25wnJTIptQSrzP0CAKR_lNXZZel/pub?gid=1271870510&single=true&output=tsv\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":31,"value":"// pppDataWorking = {\n// let objects = [];\n// const spreadsheet = await d3\n// .tsv(pppAttachedFile)\n// .then(data => data.forEach(d => objects.push(d)));\n// let popped = objects.slice(0, -3);\n// return popped;\n// }","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":916,"value":"pppDataWorking = {\n const text = await FileAttachment('ppp-data-dump - cleaned-data.csv').text();\n return d3.csvParse(text, d3.autoType);\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":5,"value":"md`# Imports`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":9,"value":"vl = require.alias({\n vega: \"vega@5.9.0\",\n \"vega-lite\": \"vega-lite@4.17.0\"\n})(\"vega-embed@6\")","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":10,"value":"d3 = require(\"d3@5\")","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":12,"value":"function toc(selector = \"h1,h2\") {\n return Generators.observe(notify => {\n let headings = [];\n function observed() {\n const h = Array.from(document.querySelectorAll(selector));\n if (h.length !== headings.length || h.some((h, i) => headings[i] !== h)) {\n notify(\n html`Table of Contents

${DOM.text(h.textContent)}`,\n { onclick: e => (e.preventDefault(), h.scrollIntoView()) }\n );\n })}`\n );\n }\n }\n const observer = new MutationObserver(observed);\n observer.observe(document.body, { childList: true, subtree: true });\n observed();\n return () => observer.disconnect();\n });\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":160,"value":"locale = {\n return {\n decimal: \",\",\n thousands: \".\",\n grouping: [3],\n currency: [\"€\", \"\"],\n dateTime: \"%a %b %e %X %Y\",\n date: \"%d.%m.%Y\",\n time: \"%H:%M:%S\",\n periods: [\"AM\", \"PM\"],\n days: [\n \"Sonntag\",\n \"Montag\",\n \"Dienstag\",\n \"Mittwoch\",\n \"Donnerstag\",\n \"Freitag\",\n \"Samstag\"\n ],\n shortDays: [\"So\", \"Mo\", \"Di\", \"Mi\", \"Do\", \"Fr\", \"Sa\"],\n months: [\n \"Januar\",\n \"Februar\",\n \"März\",\n \"April\",\n \"Mai\",\n \"Juni\",\n \"Juli\",\n \"August\",\n \"September\",\n \"Oktober\",\n \"November\",\n \"Dezember\"\n ],\n shortMonths: [\n \"Jan\",\n \"Feb\",\n \"Mär\",\n \"Apr\",\n \"Mai\",\n \"Jun\",\n \"Jul\",\n \"Aug\",\n \"Sep\",\n \"Okt\",\n \"Nov\",\n \"Dez\"\n ]\n };\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":1311,"value":"import {serialize} from \"@palewire/saving-csv\"","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":39,"value":"import { chartFonts } from \"@chrislkeller/number-of-samples-tested-for-covid-19-by-new-mexico-state-hea\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":44,"value":"import { chartAxis } from \"@chrislkeller/number-of-samples-tested-for-covid-19-by-new-mexico-state-hea\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":1317,"value":"import { SummaryTable } from \"@observablehq/summary-table\"","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":198,"value":"$3d","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":760,"value":"// lenderTotalFacet = {\n// const output = [];\n// sortedLenderTotal.map(function(d, i) {\n// output.push(d.key);\n// });\n// const includeList = output.slice(0, 12);\n// const theseLenders = pppDataWorking.filter(function(d) {\n// if (includeList.indexOf(d.lender) !== -1) {\n// return d;\n// }\n// });\n// return theseLenders;\n// }","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"}],"files":"$W3e","stdlib":"1","titling":"auto"}} 2f:"$2e:body"