Help:Tables and locations

From the AARoads Wiki: Read about the road before you go
Jump to navigation Jump to search

This page documents various ways to use a table to display information related to physical locations (e.g. countries or US states).

Convert US state abbreviations to full names

If you have a list of all 50 US state abbreviations, then you can copy the full names from the show/hide boxes below. See Help:Table/Advanced#Copy column to table. Add or remove the District of Columbia (D.C.) as necessary. Make sure the two lists are in identical order with the same number of rows. Be sure the lists are in matching alphabetical order (whether by abbreviations or full names). You can work in your sandbox. Open both tables below to see highlighted differences in alphabetization. In the end the full names will need to be in alphabetical order. See: Help:Table/Advanced#Sort. Spreadsheet & VE.

Example: You may have a sandbox or spreadsheet of state data using state abbreviations. Open this section for editing via the visual editor (VE). Delete the D.C. row if necessary, but do not save the changes. Select and copy (Ctrl+C) the full state names. You can usually paste them directly into the sandbox or spreadsheet over the abbreviations. Use Ctrl+V with the visual editor in the sandbox. Then alphabetize the full names manually or in a spreadsheet. If spreadsheet, copy it directly to VE. If that doesn't work, copy to Excel2wiki first.

Some state lists will include the 5 permanently inhabited US territories: AS = American Samoa. GU = Guam. MP = Northern Mariana Islands. PR = Puerto Rico. VI = U.S. Virgin Islands. You will have to remove them temporarily. Or fill in their names first, and paste the other full names around them.

All 50 states and D.C. Alphabetical order by full names
Full name 2-letter USPS
Alabama AL
Alaska AK
Arizona AZ
Arkansas AR
California CA
Colorado CO
Connecticut CT
Delaware DE
District of Columbia DC
Florida FL
Georgia GA
Hawaii HI
Idaho ID
Illinois IL
Indiana IN
Iowa IA
Kansas KS
Kentucky KY
Louisiana LA
Maine ME
Maryland MD
Massachusetts MA
Michigan MI
Minnesota MN
Mississippi MS
Missouri MO
Montana MT
Nebraska NE
Nevada NV
New Hampshire NH
New Jersey NJ
New Mexico NM
New York NY
North Carolina NC
North Dakota ND
Ohio OH
Oklahoma OK
Oregon OR
Pennsylvania PA
Rhode Island RI
South Carolina SC
South Dakota SD
Tennessee TN
Texas TX
Utah UT
Vermont VT
Virginia VA
Washington WA
West Virginia WV
Wisconsin WI
Wyoming WY
All 50 states and D.C. Alphabetical order by abbreviations
Full name 2-letter USPS
Alaska AK
Alabama AL
Arkansas AR
Arizona AZ
California CA
Colorado CO
Connecticut CT
District of Columbia DC
Delaware DE
Florida FL
Georgia GA
Hawaii HI
Iowa IA
Idaho ID
Illinois IL
Indiana IN
Kansas KS
Kentucky KY
Louisiana LA
Massachusetts MA
Maryland MD
Maine ME
Michigan MI
Minnesota MN
Missouri MO
Mississippi MS
Montana MT
North Carolina NC
North Dakota ND
Nebraska NE
New Hampshire NH
New Jersey NJ
New Mexico NM
Nevada NV
New York NY
Ohio OH
Oklahoma OK
Oregon OR
Pennsylvania PA
Rhode Island RI
South Carolina SC
South Dakota SD
Tennessee TN
Texas TX
Utah UT
Virginia VA
Vermont VT
Washington WA
Wisconsin WI
West Virginia WV
Wyoming WY

2 or 3-letter country codes to full names

Some source data tables only use the codes. Wrap them in brackets {{ABC}} to create full-name country links. To do so click on the wikitext source editing link. Click on "Advanced" in the editing toolbar. Then click on the search and replace icon on the right. Put a check in the box called "Treat search string as a regular expression." Fill in the "Search for" box with:

(\|-.*\n\|\s*)([^\|\n]*)

Make sure there is no space on the end.

Fill in the "replace with" box with:

$1{{$2}}

Save the page. Copy it to Excel2Wiki to strip all the templates out, and keep the full names. Copy to a sandbox. Or skip excel2wiki and copy to a spreadsheet such as LibreOffice Calc: Edit menu > paste special > paste unformatted text. Alphabetize by the full names: Data menu > Sort ascending. This is necessary because alphabetization of codes is different from full names. Copy the table back to a sandbox via VE.

There is another way to convert the codes to full names. After putting the table in a sandbox use VE to copy just the code column as previously described at § Copy column from one table to another. Copy that list to a converter such as this one. Since it is a column list pick "new-line separated" from the first dropdown menu. And be sure to pick the correct direction in the second dropdown menu. Since the converted column is going to be pasted back into the table with the exact same order and length pick "include invalid", "original order", and "capitalize country names". Copy the list of results and put it into a separate one-column table as described in the previous section. Then copy that column and use it to replace the code column as described here: §§ Help:Tables​ and Copy column from one table to another. The table will need to be alphabetized again since the codes alphabetize differently versus the full names.

Add flags. Link countries, states, etc. in tables

This is fast. It works with compact or long table wikitext. Single or double bars between cells.
Note: User:PrimeHunter (Talk) provided the code. Ask him for help, or ask at WP:VPT.
Note: In editing preferences check the box: "enable the editing toolbar. This is sometimes called the '2010 wikitext editor'."

Make sure the countries, states, provinces, or cities, etc. are in the first column. The regular expression wraps all the text in the first paragraph in the first cell of each row with the {{flaglist}} template. {{flaglist|Country name}} So do this before adding any styling to the first column. If there is other info besides the location name in those first cells, separate it with a blank line. See example table.

Click on the wikitext source editing link. Click on "Advanced" in the editing toolbar. Then click on the search and replace icon on the right. Put a check in the box called "Treat search string as a regular expression." Fill in the "Search for" box with:

(\|-.*\n\|\s*)([^\|\n]*)

Make sure there is no space on the end.

Fill in the "replace with" box with:

$1{{flaglist|$2}}

Other flag template names can be used too. If you want links without flags replace with:

$1[[$2]]

Then click "Replace all". Nearly all countries, states, etc. will be linked.

Create links without flags first. If there are red links create redirects. This will also take care of all the red links in the flag lists. Then create another table with flag links. If there are any country/state links without flags open the whole page in wikitext source mode. Go to the bottom of the page and look for red-linked country data templates. Redirect them to the correct country data templates. In the edit summary of such redirects, add something like this: "Please do not delete this redirect. It is sometimes used in updates of [[NAME OF ARTICLE WITH TABLE]]." The country data template redirects may show up temporarily for a day or two here:

And the corresponding flags may take a day or two to show up. Be patient. Template redirects can take time to come into effect. After that happens you may need to correct the alphabetization of a few countries.

If it is a US state list, change 'Georgia' to 'Georgia (U.S. state)'. Otherwise 'Georgia' will be treated as the country by the flag templates. Use this:

{{flaglist|Georgia (U.S. state)|name=Georgia}}

{{flaglist}} does not work with abbreviated country or state names. But if you intend to use a specialized article name (as in the next section) then it is OK to use abbreviated country or state names in this section, and then go on to the next section.

Adding specialized country/state links

For example:

Once you have a table with {{flaglist}}, and/or other flag template such as {{flag}}, it is easy to convert the links to specialized links. So instead of Oklahoma you would have Incarceration in Oklahoma, or if that article does not exist, then Crime in Oklahoma.

Following the principle of least astonishment articles with specialized country and state links (and so on) should indicate just before the table what those links will be. This is especially important where some links have asterisks for the specialized links, meaning the other links are just standard links. See examples in the previous list links, and in the list links in the following sections.

The following works with full country or US state names. Also with abbreviated (3-letter) country names, and abbreviated (2-letter) US state names.

Use a global find-and-replace as previously described, but without regular expressions. Replace:

{{flag|

or

{{flaglist|

with

{{flagg|us*eft|pref=Incarceration in|pref2=Crime in|

The bar after flag or flaglist is necessary to avoid problems in case you have to replace 2 flag templates.

The {{flagg}} template looks for the articles. If it finds one of the two choices it adds the link and an asterisk after the location name. If it does not find either article, it just adds the standard link.

Substitute your preferred topics in the pref= and pref2= spots.

{{flagg|us*eft| usually left aligns the text of that column (due to the "t" at the end of the code). For some unknown reason that is not always true. In that case each cell in that column will need style=text-align:left in it. Or use the whole column code described here: #Column alignment and here: {{Table alignment}}. See example table.

Washington, D.C. - Have to force the full name to be shown:

{{flagg|us*eft|pref=Crime in|Washington, D.C.|name=Washington, D.C.}}

This particular use of {{flagg}} parameters is expensive and can only be used on a few hundred links per article. As a rule of thumb, only use one big country table per article. Other country tables in the same article should use standard links.

In List of countries by incarceration rate the {{flagg}} template creates a link to Incarceration in United States. That link is a redirect to Incarceration in the United States. Add "the" as necessary to create redirect pages. Bots and admins will rapidly delete redirects that are not linked from within Wikipedia. So if you need to create redirects be sure to use them right away in an article table. Or keep them in a sandbox, or the article talk page, so that they are in use even if removed later during table changes. See examples. See list.

{{Flagg}} removes the names of the countries behind territories, subnational areas, etc.. This makes for more compact tables. Other flag templates (such as {{flaglist}}) do not do that. But that template can not create specialized country links.

See Global Search at Toolforge. Search for
"{{flagg|us*eft" - in quotes. To get transclusion count and list of articles.

The {{flagg}} template does not work if colspan is used before the country/state name. {{flag+link}} works. See: Template talk:Flagg#Does not work with colspan.

Without asterisks after location names

When all the locations in a table list have specialized articles, then there is no need for the asterisks to distinguish those specialized location articles.

Replace {{flagg|us*eft with {{flagg|uspeft to remove the asterisks, and lower the expensive server load.

See Global Search at Toolforge. Search for
"{{flagg|uspeft" - in quotes. To get transclusion count and list of articles.

Without asterisks. Only one preference

See this version of List of countries by hospital beds.

Replace:

{{flaglist

with

{{flag+link|Health in

Convert rows to columns and columns to rows

Sometimes there is a need to transpose columns and rows (move rows to columns, and columns to rows). For simple tables, this can be done via the "transpose rows and columns" function of Copy & Paste Excel-to-Wiki, or via the "transpose" feature of a third-party spreadsheet program such as Microsoft Excel, the free web-based Google Sheets, or the free downloadable software LibreOffice Calc.

To transpose the table with a third-party spreadsheet program, copy the published table on the Wikipedia page and paste it into a new blank document in your spreadsheet program. While the pasted cells are still selected in the spreadsheet, copy them again by right-clicking and choosing "Copy" from the context menu. Open a new blank spreadsheet, click in the upper-left cell, right click on it, and choose "Paste Special". In Microsoft Excel, check the "Transpose" box at the bottom of the dialogue and hit Okay. In Google Sheets, choose "Transpose" from the sub-menu. in LibreOffice Calc, choose "Transpose" from the sub-menu. Perform any required editing of the transposed table, and copy the new table directly from the spreadsheet program into visual editor, or into Excel2Wiki.

For more complicated operations, such as consolidating multiple rows with the same header into a single column, you can use the "pivot table" feature of an external spreadsheet program. For example; the data for the overdose rates table by state for United States drug overdose death rates and totals over time comes from a csv file and is converted to wikitable format via one of the previously mentioned csv converters. The year headers in the left table below need to become the column headers in the right table.

Year State Rate
2019 AL 16.3
2019 AK 17.8
2019 AZ 26.8
2018 AL 16.6
2018 AK 14.6
2018 AZ 23.8
2017 AL 18
2017 AK 20.2
2017 AZ 22.2
State 2017 2018 2019
AL 18 16.6 16.3
AK 20.2 14.6 17.8
AZ 22.2 23.8 26.8

To re-arrange the table using pivot tables, copy and paste the table into a spreadsheet program such as freeware LibreOffice Calc.

In LibreOffice Calc, first make sure the headers are in the top row. Then select a data cell. Then click on the "Pivot Table" command from the Insert menu. Click OK in the popup box. In the next dialog box drag "Year" to the "Column Fields" box, and drag "State" to the "Row Fields" box. Drag "Rate" to the "Data Fields" box. In the options menu decide whether you want the rows or columns totaled. Click OK. The table will convert to the new format with the years as column headers. To avoid problems copy the table to a new sheet before further editing. For help see: LibreOffice: Pivot Tables and LibreOffice Help: Pivot Table. If necessary, convert state or country abbreviations to full names.

When done editing, copy the new table (if small) directly from the Calc page into visual editor, or into Excel2Wiki first, and then into VE. Then use VE to delete the summation column and row if necessary.


Separate counts and rates to 2 columns

Location Year Type Value
Afghanistan 2021 Counts 1613
Afghanistan 2021 Rates 4.0
Albania 2021 Counts 66
Albania 2021 Rates 2.3

See List of countries by intentional homicide rate. Its data reference in table form comes with counts and rates alternating in the same column. See it here. That page explains how to put them in 2 columns.

Copy the table to a spreadsheet such as freeware LibreOffice Calc. Select a single cell with "Counts" in it. Then: Data menu > More Filters > Standard Filter > Filter Criteria. Enter "Counts" in Value spot. Click OK. Only rows with "Counts" in them will be kept. Change column head of "Value" column to "Counts". Delete column that only contains the word "Counts". Add a thousands separator to count numbers. Copy and paste to new Calc file to get accurate row numbering. Note the number of rows. Copy to a sandbox.

Start over and do the same except for "Rates." Number of rows should be the same as for counts table. Copy to a sandbox. Using VE insert a blank column to the right of the rates column. Copy the counts column and paste it into that blank column.

Section link or map link to a row anchor

To enable a section link's anchor (or a map link's anchor), referencing a specific row within a table, an id="section link anchor name" parameter needs to be added to the row start |- or <tr>:

|- id="section link anchor name"
<tr id="section link anchor name">

Note that each section link anchor name must be different from every other in the page (this includes heading names), to create valid XHTML and allow proper linking.

Example of a map link to a row

When a country label, containing a link, is clicked on the map, the link coded, for example, as [[#Table row 11|Slovenia]] that references the anchor (within the table), coded as |- id="Table row 11", makes the page scroll so selected row of the table is at the top of the browser view. Here, we use the template family {{Image label begin}}, {{Image label small}}, and {{Image label end}} to lay out such a table for us:

Click on a country name on the map to go to that name in the table

Avaaz members by percent of population.
The list can be scrolled manually or interactively.
Country Population Avaaz %

Andorra 85,000 3,316   3.90
Luxembourg 498,000 14,228   2.86
France 64,768,000 1,827,517   2.82
Belgium 10,423,000 292,530   2.81
Iceland 309,000 7,667 2.48
Switzerland 7,623,000 182,814 2.40
Malta 407,000 9,129 2.24
Austria 8,214,000 167,132 2.03
Liechtenstein 36,000 718 1.99
Spain 46,506,000 810,680 1.74
Slovenia 2,003,000 27,780 1.39
Sweden 9,074,000 125,248 1.38
Germany 81,644,000 1,082,972 1.33
Italy 60,749,000 796,634 1.31
Ireland 4,623,000 58,504 1.27
United Kingdom 62,348,000 781,025 1.25
Portugal 10,736,000 132,219 1.23
Netherlands 16,574,000 191,608 1.16
Romania 21,959,000 211,867 0.96
Norway 4,676,000 36,483 0.78
Denmark 5,516,000 41,377 0.75
Bulgaria 7,149,000 52,296 0.73
Greece 10,750,000 78,874 0.73
Latvia 2,218,000 14,967 0.67
Estonia 1,291,000 8,535 0.66
Croatia 4,487,000 28,950 0.65
Lithuania 3,545,000 21,721 0.61
Finland 5,255,000 28,836 0.55
Hungary 9,992,000 51,684 0.52
Poland 38,464,000 162,643 0.42
Slovakia 5,470,000 22,588 0.41
Czech Republic 10,202,000 39,358 0.39
Macedonia 2,072,000 3847 0.19
Bosnia and Herz. 4,622,000 8,436 0.18
Serbia 7,345,000 12,369 0.17
Montenegro 667,000 1,101 0.17
Albania 2,987,000 3,300 0.11
Moldova 3,732,000 2,134 0.06
Russia 139,390,000 62,932 0.05
Belarus 9,613,000 2,643 0.03
Ukraine 45,416,000 13,002 0.03

See also

  • Help:Tables for general information about using tables. It also has many links in its "See also" and "External links" sections. Rather than duplicating them here.