User:Waitak/H5N1 graphs

I've been maintaining two templates regarding H5N1:

• Template:H5N1_case_graph is a graph of the cumulative number of human cases and deaths of H5N1 since January 1, 2004.
• Template:H5N1 Human Mortality graphs the cumulative mortality rate, since February, 2004.

Following the general principle of "Buses considered harmful", it seems to me a really good idea of somebody besides me knows how to maintain these - particularly because I may have occasion to be away from a computer for a week or two from time to time. Updating these shouldn't have to wait until I'm available. The following is a set of detailed directions on how to construct these graphs and update the template.

Don't be intimidated by the length of these instructions! The whole process takes about 15 minutes, once you've learned how to do it.

You'll need two tools:

• OpenOffice Calc, v. 1.1.5 or better, downloadable here on Linux, Windows or MacOS.
• GIMP, any version, downloadable from here on Linux or Windows.

These are both wonderful tools, and I highly recommend them whether or not you want to use them for this purpose.

The data that is used in these graphs is derived from the WHO Confirmed Human Cases of Avian Influenza A(H5N1) page. Both must be updated whenever a new entry is made on this Web site - ideally within a few hours. The format of the data provided varies over time. The entry for May 23, 2006 looks like this:

Country	2003		2004		2005		2006		Total
	cases	deaths	cases	deaths	cases	deaths	cases	deaths	cases	deaths
Azerbaijan	0	0	0	0	0	0	8	5	8	5
Cambodia	0	0	0	0	4	4	2	2	6	6
China	0	0	0	0	8	5	10	7	18	12
Djibouti	0	0	0	0	0	0	1	0	1	0
Egypt	0	0	0	0	0	0	14	6	14	6
Indonesia	0	0	0	0	17	11	25	22	42	33
Iraq	0	0	0	0	0	0	2	2	2	2
Thailand	0	0	17	12	5	2	0	0	22	14
Turkey	0	0	0	0	0	0	12	4	12	4
Viet Nam	3	3	29	20	61	19	0	0	93	42
Total	3	3	46	32	95	41	74	48	218	124

The remainder of this document assumes that you have access to the spreadsheet H5N1.sxc, which you'll have to obtain from me personally. This spreadsheet is an OpenOffice Calc spreadsheet, contructed using version 1.1.5. You can use a later version (version 2.x is very nice) but please don't save your work as anything later than a version 1.1.5 spreadsheet, so that people with older copies can continue to edit it.

The steps to updating the templates are:

1. Add a row to the spreadsheet for the current update on the WHO site

• Add a column for any countries that have been newly added
• Add any new cases
• Extend the summary rows

1. Adjust the data range on the "Chart" worksheet
2. Screen capture the cases chart
3. Screen capture the mortality chart
4. Use GIMP to crop the charts and make the background transparent
5. Upload the charts to Wikipedia
6. Edit the "As of" dates on the templates

et voila!

Updating the data

As you look at the Data worksheet on the spreadsheet, you'll see that there is one row for every entry in the WHO cases page. The first step is to see if any new countries have been added to the report. If so, add two columns in the appropriate spot on the worksheet, so that the list of countries across the top remains in alphabetical order. Merge the cell holding the country name with the cell next to it, and center the value.

Next, incorporate the data from the latest entry into the spreadsheet. Look down the list of cumulative totals for numbers that are higher on the WHO chart than they are on the spreadsheet. This is a bit tedious, but that's the value that we're adding to the WHO raw data. When you find a difference, add the difference between the two numbers as new cases or deaths (respectively) in the row that you just added. For example, if there are 33 deaths on the spreadsheet for Indonesia, but the WHO site lists 35, put 2 in the cell for deaths in Indonesia in the new row. The total in the top row should change to the correct number.

Third, left click on the leftmost cell in the row above the one you just added, then how down the shift key and left click on the cell in column F in the same row. This should select all of the cells in a single row, in columns A through F. You'll see a small, black square in the lower righthand corner of the area that you've just selected. If you place the mouse cursor over the square, it will change to a plus sign. After doing so, press and hold the left mouse button. You'll notice a red outline appear around the area that you've just highlighted. Move the mouse down until the red outline surround the corresponding cells in the new row. Release the mouse and you should see value in the new row that match the totals on the WHO site. If they don't match, you've made a mistake in the data that you entered in the other columns, and must correct them until they match.

All of these instructions may sound complicated, but all of the above takes less than a minute!

Updating the charts

The Chart worksheet must be manually updated to correct the data range. This is done by:

• Left clicking once on the chart to select it
• Right clicking once to bring up a context menu
• Select "Modify data range" from the menu

The range will say something like $Data.$A$2:$A$100;$Data.$D$2:$D$100;$Data.$E$2:$E$100. You need to change each occurence of the end of the range to the right row. If, for example, you've just created row 101, then you'd change:

$Data.$A$2:$A$100;$Data.$D$2:$D$100;$Data.$E$2:$E$100

to

$Data.$A$2:$A$101;$Data.$D$2:$D$101;$Data.$E$2:$E$101

Be sure to change all three, or the chart will now display properly.

You're almost ready to grab the image!

Capturing the image

Click on some other cell in the worksheet so that you won't have any "select" lines in the captured image. You can use any screen capture program you like to grab the image itself. GIMP has one of its own. To use GIMP:

• After running GIMP, select File->Acquire->Screen shot
• Select "A single window" and click OK
• You'll see a cross appear in a popup window. Left click on the cross. Hold the mouse button down, and move the mouse cursor so that it's over the OpenOffice window. It doesn't matter where in the window the cross it, but the cross must be directly over the window somewhere.
• Release the mouse button

GIMP with churn for a bit, and pop up an image of the screen that you were just looking at. Switch to the Mortality worksheet in the spreadsheet, and repeat these steps to generate a screen grab of the mortality chart.

At this point, it's probably a good idea to minimize Open Office so you don't get confused about which window is the spreadsheet, and which window is an image of the exact same thing.

Making the final image

The image processing directions that follow assume that you're using GIMP. If you're a guru with something else, please feel free to use whatever you're comfortable with, but please create images that are comparable to what's already being done - PNG's with transparent background.

The first step is to crop the image. I find it easier to set the zoom to 1:1 first, so that I don't accidentally clip off part of a caption or something. You crop the image as follows:

• Click the rectangular selection tool in the GIMP menu
• Left click and hold at the lower left hand corner of the graph itself. Be sure that you don't click inside what should be the graph. Clicking a bit outside is fine - you can always crop twice.
• Move the mouse to the upper right hand corner of the graph, again being sure that you have the whole thing.
• Once you're happy with the selection, click "Image->Crop image" in the menu bar of the image itself.

You should have a bit of white space around the image at this point. Next, you're going to select all of the non-white parts of the image. You do so as follows:

• Click on Select->By color
• The default threshold isn't fine enough to select only white, so you'll need to set it to a lower value. You should see a tool-specific form that has "Mode", "Antialiasing" etc. In the box for "Threshold", enter 4.
• Now left click on any part of the image background - that is the white part. You should basically see that it's selected all of the interesting bits of the graph...
• ... or so it appears. It's actually selected all of the uninteresting bits, so we have to reverse the selection. You can do so by typing CTRL-i. You'll see a visible difference in the selection.
• Now type CTRL-C, and then CTRL-V to create a copy of just the parts we want

Next, you're going to create a new layer with just the interesting parts, and delete the original layer. You do this as follows:

• Type CTRL-L to bring up the layers tool.
• You'll see a layer called "Pasted layer" in the layers box. Click on the "Add" icon, which looks like a piece of paper with a green plus sign in it, to turn the "Pasted layer" into a normal layer
• Next click on the bottom (original layer), which should be entitled "Background"
• Click the trash can icon in the layer tool to delete the background layer

Now go back to the window with the actual window.

• Select Layer->Layer to image size. You'll need this for the next step to work
• Select Image->Transform->Autocrop

What you should see at this point is that the image is exactly the size of the interesting parts. If it's bigger, you'll need to figure out what the extra bits are and delete them. You do this with the rectangular select tool, to select the parts you want to delete, then CTRL-X to actually erase what you've selected. Then you'll need to autocrop again. (There's no need to set the later to image size again - once is enough.)

The final image should be transparent where there was background on the original. Save it as a PNG, and repeat for the mortality graph.

Uploading the graph

This part is easy.

• First click on the graph in Wikipedia
• Then click on "Upload a new version of this file"
• Use the "Browse" button to find the file you've just created
• Select "GFDL (self made)" as the license, and "Ignore any warnings" (because you're uploading a file that already exists)
• Click "Upload file" and you're done!

Again, do the same for both graphs.

Finishing up

The final step is to edit the template to change the date. That should be pretty obvious if you're Wikisavvy, so I won't go through the steps to do so. Once you're all done, go back to the template and make sure it looks okay. You may have to do a Shift-Ctrl-R to see the new image. Finally, go to Global spread of H5N1 and Transmission and infection of H5N1 (again, you might need to do Shift-Ctrl-R to see everything properly), and make sure that everything's okay.

Once you're done, please save the spreadsheet and send me a current copy so that I can maintain it as well. Of course, if some kind soul would like to take over maintaining all together, you can do so with my blessing and gratitude!