Internal page for COVID-19 in Osaka / SUGIMOTO Tatsuo

md`# COVID-19 Patients Table by Ward in Osaka`

osakaPatients.length

populationChart = {

const svg = d3

.create('svg')

.attr('viewBox', [0, 0, width, height])

.attr('font-family', 'Roboto Condensed, Helvetica Neue, Hiragino Sans');

const populationX = d3

.scaleLinear()

.domain([0, 600000])

.range([0, sexX.bandwidth()]);

const g = svg.append('g');

// .attr('transform', `translate(${margin.left}, ${margin.top})`);

g.append('rect')

.classed('bg', true)

.attr('width', width)

.attr('height', height)

.attr('fill', 'hsl(42, 100%, 98%)');

const startDate = new Date('2020-08-16');

const endDate = new Date('2020-08-22');

const data = ageSexPatientsOfDateRange(startDate, endDate);

const ageG = g

.selectAll('g.age')

.data(data)

.join('g')

.classed('age', true)

.attr('transform', d => `translate(0, ${ageY(d.key)})`);

const sexG = ageG

.selectAll('g')

.data(d => d.values)

.join('g')

.attr('transform', d => `translate(${sexX(d.key)}, 0)`);

sexG

.selectAll('rect.bar')

.data(d => [d])

.join('rect')

.classed('bar', true)

.attr('x', d =>

d.key === '男性' ? sexX.bandwidth() - populationX(d.population) : 0

)

.attr('y', 0)

.attr('width', d => populationX(d.population))

.attr('height', ageY.bandwidth())

.attr('fill', 'hsl(19, 100%, 90%)')

.attr('opacity', 0.5);

const rowNum = 6;

const circleMargin = (ageY.bandwidth() - rowNum) * 0.05;

const r = (ageY.bandwidth() - (rowNum + 1) * circleMargin) / rowNum / 2;

const circleX = (d, i) => {

let x = Math.floor(i / rowNum) * (r * 2 + circleMargin) + r + circleMargin;

if (d.sex === '男性') {

x = sexX.bandwidth() - x;

}

if (d.sex === '調査中') {

x = width / 2;

}

return x;

};

const circleY = (d, i) => {

if (d.sex === '調査中') {

i = i + 4;

}

return (i % rowNum) * (r * 2 + circleMargin) + r + circleMargin;

};

const circleTransform = (d, i) => {

let transform;

switch (d.sex) {

case '男性':

transform = `translate(${sexX.bandwidth() -

(Math.floor(i / rowNum) * (r * 2 + circleMargin) +

r +

circleMargin)},

${(i % rowNum) * (r * 2 + circleMargin) + r + circleMargin})`;

break;

case '女性':

transform = `translate(${Math.floor(i / rowNum) *

(r * 2 + circleMargin) +

r +

circleMargin},

${(i % rowNum) * (r * 2 + circleMargin) + r + circleMargin})`;

break;

case '調査中':

default:

transform = `translate(${width / 2 -

r +

Math.floor(i / rowNum) * (r * 2 + 1) +

r +

circleMargin},

${((i + 4) % rowNum) * (r * 2 + circleMargin) + r + circleMargin})`;

break;

}

return transform;

};

const patientStroke = '#000';

const patientStrokeWidth = d => (d.statusSort <= 6 ? 1 : 0.5);

sexG

.selectAll('circle')

.data(d => d.values)

.join('circle')

.attr('class', d => `status-${transitionIndex(d)}`)

.attr('r', r)

// .attr('r', 0)

.attr('fill', patientColor)

.attr('stroke', patientStroke)

.attr('stroke-width', patientStrokeWidth)

.attr('stroke-opacity', 0.75)

.attr('transform', circleTransform);

const lastIndex = d => Math.ceil(d.values.length / rowNum) * rowNum;

// sexG

// .append('text')

// .classed('num-patients', true)

// .attr('transform', (d, i) => {

// return `translate(${circleX(d.values[0], lastIndex(d))},

// ${circleY(d.values[0], lastIndex(d)) + 16 * 1.8})`;

// })

// .attr('opacity', d => (d.values[0].sex === '調査中' ? 0 : .75))

// .attr('text-anchor', d => (d.values[0].sex === '男性' ? 'end' : 'start'))

// .selectAll('tspan')

// .data(d =>

// [

// `${d.rate === Infinity ? '' : d3.format('.2f')(d.rate)}`,

// `${d.values.length}人`

// ].map(

// (e, i) =>

// `${e}${

// d.key === '女性' && d.values[0].age === 80

// ? i === 0

// ? '（10万人あたり）'

// : '（実数）'

// : ''

// }`

// )

// .join('tspan')

// .attr('x', (d, i) => `0em`)

// .attr('dx', (d, i) => `0em`)

// .attr('dy', (d, i) => `${i * 1.2}em`)

// .attr('font-size', (d, i) => (i === 0 ? 16 : 12))

// .attr('font-weight', (d, i) => (i === 0 ? 600 : 300))

// .text(d => d);

g.call(addAgeLabels);

g.selectAll('text.sex')

.data(ageSexPatients[0].values)

.join('text')

.classed('sex', true)

.text(d => d.key)

.attr('text-anchor', d => (d.key === '男性' ? 'end' : 'start'))

.attr('font-family', 'sans-serif')

.attr('font-size', '16px')

.attr('font-weight', 'bold')

.attr('transform', d =>

d.key === '男性'

? `translate(${sexX(d.key) + sexX.bandwidth()}, ${margin.top})`

: `translate(${sexX(d.key)}, ${margin.top})`

);

const status = g

.append('g')

.selectAll('g')

.data(statuses.map(d => ({ status: d, statusSort: statusSort(d) })))

.join('g')

.attr('class', d => `status-${transitionIndex(d)}`)

.attr(

'transform',

(d, i) => `translate(${margin.left / 2}, ${margin.top + 40 + i * 20})`

);

// .attr('opacity', 0);

status

.append('circle')

.attr('cx', r)

.attr('cy', r)

.attr('r', r)

.attr('fill', patientColor)

.attr('stroke', patientStroke)

.attr('stroke-width', patientStrokeWidth)

.attr('stroke-opacity', 0.75);

status

.append('text')

.text(d => d.status)

.attr('font-family', 'sans-serif')

.attr('font-size', '12px')

.attr('dy', '8px')

.attr('transform', `translate(${r * 2 + circleMargin}, 0)`);

const texts = [

`集計期間：${d3.timeFormat('%Y/%m/%d')(startDate)} – ${d3.timeFormat(

'%Y/%m/%d'

)(endDate)} (7日間)`,

'データ',

`患者数：大阪府(2020年${updateDate.getMonth() +

1}月${updateDate.getDate()}日更新)`,

'　　人口：総務省統計局人口推計(2018年10月)',

// '　　正規｜非正規雇用労働者：平成29年就業構造基本調査',

// '　　就業者数：総務省統計局労働力調査(2019年平均)',

// `府発表${d3.format(',')(patients.length)}人のうち居住地大阪府外${

// cityPatients.find(d => d.key == '大阪府外').values.length

// }人を除く`,

`作図：SUGIMOTO Tatsuo`

];

g.append('text')

.text('大阪府新型コロナウイルス感染確認者の年代性別')

.attr('transform', `translate(${margin.left / 2}, ${margin.top / 2})`)

.attr('font-family', 'Hiragino Sans')

.attr('font-size', '30px')

.attr('font-weight', '300')

.attr('letter-spacing', '.1em');

g.append('rect')

.attr('x', margin.left / 2)

.attr('y', margin.top / 2 + 28 + 3 * 18 - 10)

.attr('width', 24)

.attr('height', 12)

.attr('fill', 'hsl(19, 100%, 90%)')

.attr('opacity', 0.5);

// g.append('rect')

// .attr('x', margin.left / 2)

// .attr('y', margin.top / 2 + 28 + 3 * 18 - 10)

// .attr('width', 12)

// .attr('height', 12)

// .attr('fill', 'hsl(220, 100%, 80%)')

// .attr('opacity', 0.5);

// g.append('rect')

// .attr('x', margin.left / 2 + 12)

// .attr('y', margin.top / 2 + 28 + 3 * 18 - 10)

// .attr('width', 12)

// .attr('height', 12)

// .attr('fill', 'hsl(220, 100%, 90%)')

// .attr('opacity', 0.5);

const description = g

.append('g')

.selectAll('text.desc')

.data(texts)

.join('text')

.classed('desc', true)

.text(d => d)

.attr(

'transform',

(d, i) => `translate(${margin.left / 2}, ${margin.top / 2 + 28 + i * 18})`

)

.attr('font-family', 'sans-serif')

.attr('font-size', '12px')

.attr('opacity', 0.8);

// for (let i = 0; i < 5; i++) {

// sexG

// .selectAll(`circle.status-${i}`)

// .transition()

// .delay((d, j) => 5000 + i * 3000 + j * 10)

// .attr('r', r);

// g.selectAll(`g.status-${i}`)

// .transition()

// .delay((d, j) => 5000 + i * 3000 + j * 10)

// .attr('opacity', 1);

// }

return svg.node();

}

updateDate = dates.slice(-1)[0]

height = 800

ageY = d3

.scaleBand()

.domain(ages)

.range([margin.top, height - margin.bottom])

.padding(0.1)

sexX = d3

.scaleBand()

// .domain(ageSexPatients[0].values.map(d => d.key))

.domain(['男性', '女性'])

.range([margin.left, width - margin.right])

.padding(0.15)

ages = ageSexPatients.map(d => d.key)

addAgeLabels = g => {

const label = g

.selectAll('text.age')

.data(

ages.map(d => ({

key: d,

num: d.replace(/\D+/, ''),

suffix: d.replace(/\d+/, '')

}))

)

.join('text')

.classed('age', true)

.attr('text-anchor', 'middle')

.attr('font-weight', '600')

.attr('font-family', `Roboto Condensed`)

.attr(

'transform',

d => `translate(0, ${ageY(d.key) + ageY.bandwidth() / 2})`

);

label

.append('tspan')

.attr('font-size', '20px')

.attr('x', `${width / 2}`)

// .attr('dy', '1.2em')

.text(d => d.num);

label

.append('tspan')

.attr('font-size', '12px')

.attr('font-weight', '400')

.attr('x', `${width / 2}`)

.attr('dy', '1.2em')

.text(d => d.suffix);

}

patientColor = d => {

let color;

return (

d3

.scaleOrdinal()

.domain(statuses)

// .range(['gray', 'green', 'black', 'lightgray', 'yellow'])(d.症状);

.range([

'hsl(0, 0%, 0%)',

'hsl(20, 60%, 50%)',

'hsl(20, 60%, 70%)',

'hsl(60, 70%, 40%)',

'hsl(60, 70%, 70%)',

'hsl(160, 80%, 50%)',

// 'hsl(160, 80%, 70%)',

'hsl(0, 0%, 80%)',

'hsl(0, 0%, 100%)',

'hsl(0, 0%, 100%)'

])(d.status)

);

// 0: "死亡"

// 1: "入院中"

// 2: "入院調整中"

// 3: "宿泊療養"

// 4: "宿泊療養調整中"

// 5: "自宅療養"

// 6: "自宅療養調整中"

// 7: "退院"

// 8: "宿泊療養解除"

// 9: "自宅療養解除"

// 10: "確認中"

}

transitionIndex = d => {

let r = 0;

switch (d.statusSort) {

case 0:

r = 0;

break;

case 1:

case 2:

r = 1;

break;

case 3:

case 4:

r = 2;

break;

case 5:

case 6:

r = 3;

break;

case 7:

case 8:

case 9:

case 10:

r = 4;

break;

}

return r;

}

_patientColor = d => {

let color;

return (

d3

.scaleOrdinal()

.domain(symptoms)

// .range(['gray', 'green', 'black', 'lightgray', 'yellow'])(d.症状);

.range([

'hsl(0, 100%, 10%)',

'hsl(20, 60%, 30%)',

'hsl(40, 60%, 60%)',

'hsl(50, 90%, 60%)',

'hsl(0, 100%, 100%)',

'hsl(0, 0%, 90%)'

])(d.symptom)

);

}

d3

.nest()

.key(d => d.sex)

.entries(osakaPatients)

statuses = d3

.nest()

.key(d => d.status)

.entries(osakaPatients)

.sort((a, b) => a.values[0].statusSort - b.values[0].statusSort)

.map(d => d.key)

symptoms = d3

.nest()

.key(d => d.symptom)

.entries(osakaPatients)

.map(d => d.key)

// .sort((a, b) => d3.ascending(a, b))

table = {

const div = d3.create('div').classed('table', true);

const startDate = new Date('2020-08-16');

const endDate = new Date('2020-08-22');

const data = osakaDataOfDateRange(startDate, endDate);

const numMunic = 43;

const numRow = 3;

const group = d3.range(numRow).map(d => ({

title: '市町村',

begin: d * Math.ceil(numMunic / numRow),

end: (d + 1) * Math.ceil(numMunic / numRow)

}));

const mData = group.map(g =>

data

.filter(

d => new RegExp(`[${g.title}]\$`).test(d.municipality) && !d.islands

)

.slice(g.begin, g.end)

);

const headdiv = div.append('div').style('margin-bottom', '1em');

// .classed('container', true);

headdiv

.append('h3')

.text('大阪府居住地別新型コロナウイルス感染確認者数')

.style('font-size', '26px')

.style('font-weight', '300');

const texts = [

`集計期間：${d3.timeFormat('%Y/%m/%d')(startDate)} – ${d3.timeFormat(

'%Y/%m/%d'

)(endDate)} (7日間)`,

`データ：大阪府（2020年${updateDate.getMonth() +

1}月${updateDate.getDate()}日時点）大阪府推計人口（2020年3月）`,

`<strong>人口比</strong>＝10万人あたりの感染者数`,

`作表：SUGIMOTO Tatsuo`

];

headdiv

.selectAll('div.desc')

.data(texts)

.join('div')

.classed('desc', true)

.html(d => d)

.style('font-family', 'sans-serif')

.style('font-size', '12px');

const container = div.append('div').classed('container', true);

const column = container

.selectAll('div')

.classed('column', true)

.data(mData)

.join('div');

const table = column.append('table');

const haederRow = table.append('thead').append('tr');

haederRow.append('th').text((d, i) => group[i].title);

haederRow.append('th').text('人口比');

haederRow

.selectAll('td')

.data(['実数', '重症', '死亡'])

.join('td')

.text(d => d);

const tr = table

.append('tbody')

.selectAll('tr')

.data(d => d)

.join('tr')

.on('mouseover', function() {

d3.select(this).style('background-color', '#fff');

})

.on('mouseout', function(d, i) {

d3.select(this).style(

'background-color',

i % 2 ? 'transparent' : `hsla(15, 20%, 90%, 0.5)`

);

});

const spacing = d =>

d.municipality.length === 2 ? 2 : d.municipality.length === 3 ? 0.5 : 0;

tr.append('th')

.text(d => d.municipality)

.style('letter-spacing', d => `${spacing(d)}em`)

.style('margin-right', d => `-${spacing(d) - 0.2}em`)

.style('transform', d =>

d.municipality.length >= 5 ? `scale(0.8, 1)` : `none`

)

.style('transform-origin', `left center`);

tr.append('td')

.classed('num', true)

.text(d => d3.format('.2f')(d.rate));

tr.append('td')

.classed('num', true)

.classed('sub', true)

.text(d => `${d3.format('.4')(d.case)}`);

tr.append('td')

.classed('num', true)

.classed('sub', true)

.text(d => `${d3.format('.3')(d.danger)}`);

tr.append('td')

.classed('num', true)

.classed('sub', true)

.text(d => `${d3.format('.3')(d.death)}`);

return div.node();

}

circleColor = d =>

d.left ? 'white' : d.sex === '男性' ? 'steelblue' : 'maroon'

dates = d3

.nest()

.key(d => d.date)

.entries(osakaPatients)

.map(d => new Date(d.key))

.sort((a, b) => a.getTime() - b.getTime())

_patients = xlsx.utils.sheet_to_json(sheet)

statusSort = status =>

status === '死亡退院'

? 0

: status === '入院中'

? 1

: status === '入院調整中'

? 2

: status === '宿泊療養'

? 3

: status === '宿泊療養調整中'

? 4

: status === '自宅療養'

? 5

: status === '自宅療養調整中'

? 6

: status === '退院'

? 7

: status === '宿泊療養解除'

? 8

: status === '自宅療養解除'

? 9

: status === '確認中'

? 10

: status === '府外'

? 11

: 99

osakaPatients = patients

.filter(d => d.居住地 !== '大阪府外')

.filter(d => d.退院 !== '府外')

// .filter(d => d.年代 !== '調査中')

.map(d => ({

sex: d.性別,

age:

d.年代 == 90 || d.年代 == 100

? 80

: d.年代 === '未就学児'

? 0

: d.年代 === '就学児'

? 6

: d.年代,

symptom: d.症状 === '―' ? '―（退院?）' : d.症状,

symptomSort:

d.症状 === '死亡'

? 0

: d.症状 === '重症'

? 1

: d.症状 === '軽症'

? 2

: d.症状 === '無症状'

? 3

: d.症状 === '―（退院?）'

? 4

: 99,

status: d.退院,

statusSort: statusSort(d.退院),

municipality: d.居住地,

date: new Date(d.報道提供日),

...d

}))

.sort((a, b) => a.symptomSort - b.symptomSort)

.sort((a, b) => a.statusSort - b.statusSort)

sheet['!ref']

patients = {

function ec(r, c) {

return xlsx.utils.encode_cell({ r: r, c: c });

}

function delete_row(ws, row_index) {

var variable = xlsx.utils.decode_range(ws["!ref"]);

for (var R = row_index; R < variable.e.r; ++R) {

for (var C = variable.s.c; C <= variable.e.c; ++C) {

ws[ec(R, C)] = ws[ec(R + 1, C)];

}

variable.e.r--;

ws['!ref'] = xlsx.utils.encode_range(variable.s, variable.e);

}

var worksheet = wb.Sheets[wb.SheetNames[1]];

delete_row(worksheet, 0);

return xlsx.utils.sheet_to_json(worksheet);

}

patientsJSON = (await d3.json(

'https://raw.githubusercontent.com/codeforosaka/covid19/development/data/data.json',

d3.autoType

)).patients.data

xlsx.read(data, {

type: "array",

cellDates: true,

cellNF: false,

cellText: false

})

data = new Uint8Array(

await d3.buffer(

"https://raw.githubusercontent.com/codeforosaka/covid19/development/data/patients_and_inspections.xlsx"

)

wb = loadExcelFile(

"https://raw.githubusercontent.com/codeforosaka/covid19/development/data/patients_and_inspections.xlsx"

// 'https://github.com/codeforosaka/covid19/raw/development/data/patients_and_inspections.xlsx'

// 'https://github.com/codeforosaka/covid19/blob/development/data/patients_and_inspections.xlsx?raw=true'

)

function loadExcelFile(url) {

return new Promise((resolve, reject) => {

var req = new window.XMLHttpRequest();

req.open("GET", url, true);

req.responseType = "arraybuffer";

req.onload = function(e) {

var data = new Uint8Array(req.response);

var workbook = xlsx.read(data, {

type: "array",

cellDates: true,

cellNF: false,

cellText: false

});

resolve(workbook);

// XLSX.utils.sheet_to_json(workbook.Sheets[workbook.SheetNames[1]])

// );

};

req.send();

});

}

array = new Uint8Array(await FileAttachment("osaka20200504.xlsx").arrayBuffer())

// array = new Uint8Array(

// await FileAttachment("patients_and_inspections(1)@1.xlsx").arrayBuffer()

// )

workbook = xlsx.read(array, {

// type: 'buffer'

type: 'array',

cellDates: true,

cellNF: false,

cellText: false

})

// workbook = _xlsx.read(

// await FileAttachment("osaka20200415.xlsx").arrayBuffer(),

// {

// type: 'buffer'

// }

// )

ageSexPatientsOfDateRange(new Date('2020-08-09'), new Date('2020-08-15'))

ageSexPatientsOfDateRange = (startDate, endDate) => {

return (

d3

.nest()

.key(d => d.age)

.key(d => d.sex)

// .entries(testdata)

.entries(

osakaPatients.filter(

d =>

d.年代 !== '調査中' &&

d.date.getTime() >= startDate.getTime() &&

d.date.getTime() <= endDate.getTime()

)

.sort((a, b) => +b.key - +a.key)

.map(d => ({

key:

d.key === '0'

? '未就学児'

: d.key === '6'

? '就学児'

: d.key === '80'

? `${d.key}代以上`

: `${d.key}代`,

values: d.values.map(sex => ({

population: population(d.key, sex.key),

rate: (sex.values.length / population(d.key, sex.key)) * 100000,

...sex

}))

);

}

ageSexPatients = d3

.nest()

.key(d => d.age)

.key(d => d.sex)

// .entries(osakaPatients.filter(d => d.居住地 !== '大阪府外'))

.entries(osakaPatients.filter(d => d.年代 !== '調査中'))

.sort((a, b) => +b.key - +a.key)

.map(d => ({

key:

d.key === '0'

? '未就学児'

: d.key === '6'

? '就学児'

: d.key === '80'

? `${d.key}代以上`

: `${d.key}代`,

values: d.values.map(sex => ({

population: population(d.key, sex.key),

rate: (sex.values.length / population(d.key, sex.key)) * 100000,

...sex

}))

agePatients = d3

.nest()

.key(d => d.age)

// .key(d => d.sex)

// .entries(osakaPatients.filter(d => d.居住地 !== '大阪府外'))

.entries(osakaPatients.filter(d => d.年代 !== '調査中'))

.sort((a, b) => +b.key - +a.key)

.map(d => ({

key:

d.key === '0'

? '未就学児'

: d.key === '6'

? '就学児'

: d.key === '80'

? `${d.key}代以上`

: `${d.key}代`,

values: d.values

}))

osakaPopulation = d3.csvParse(

await FileAttachment("osaka-population.csv").text(),

d3.autoType

)

osakaPatientsOfDateRange(new Date('2020-08-09'), new Date('2020-08-15'))

osakaPatientsOfDateRange = (startDate, endDate) =>

osakaPatients.filter(

d =>

d.date.getTime() >= startDate.getTime() &&

d.date.getTime() <= endDate.getTime()

)

osakaDataOfDateRange = (startDate, endDate) => {

const data = osakaPatients.filter(

d =>

d.date.getTime() >= startDate.getTime() &&

d.date.getTime() <= endDate.getTime()

);

return osakaPopulation

.filter(d => d.municipality !== 'total')

.map(d => ({

case: data.filter(e => e.居住地 === d.municipality).length,

death: data.filter(

e => e.居住地 === d.municipality && e.symptom === '死亡'

).length,

danger: data.filter(

e => e.居住地 === d.municipality && e.symptom === '重症'

).length,

...d

}))

.map(d => ({

rate: (d.case / d.population) * 100000,

...d

}))

.sort((a, b) => b.rate - a.rate);

}

osakaData = osakaPopulation

.filter(d => d.municipality !== 'total')

.map(d => ({

case: osakaPatients.filter(e => e.居住地 === d.municipality).length,

death: osakaPatients.filter(

e => e.居住地 === d.municipality && e.symptom === '死亡'

).length,

danger: osakaPatients.filter(

e => e.居住地 === d.municipality && e.symptom === '重症'

).length,

...d

}))

.map(d => ({

rate: (d.case / d.population) * 100000,

...d

}))

.sort((a, b) => b.rate - a.rate)

cityPatients = d3

.nest()

.key(d => d.居住地)

.entries(osakaPatients)

.map(d => ({

municipality: d.key,

case: d.values.length,

population: osakaPopulation.find(e => e.municipality === d.key)

? osakaPopulation.find(e => e.municipality === d.key).population

: 0

}))

.map(d => ({

rate: (d.case / d.population) * 100000,

...d

}))

swap = (arr, a, b) => {

const tmp = arr[a];

arr[a] = arr[b];

arr[b] = tmp;

}

population = (age, sex) => {

if (age === '0' || age === '6' || age === '90' || age === '100') {

return 0;

}

const startAge = age === '10歳未満' ? 0 : +age.substr(0, 2);

return (

d3.sum(

ageData.filter(d => d.age >= startAge && d.age < startAge + 10),

d => d[sex]

) * 1000

);

}

ageData = d3.csvParse(

await FileAttachment("osaka-population-2018-10.csv").text(),

d3.autoType

)

margin = ({

top: 140,

right: 60,

bottom: 30,

left: 60

})

Purpose-built for displays of data