# How to import only the data table from the census bureau

I have a project that requires me to import data from the census bureau and then take the average of the first two population columns

https://www.census.gov/population/international/data/worldpop/table_history.php

How will I go about doing that?

I first did this

Import["https://www.census.gov/population/international/data/worldpop/\
table_history.php", "Data"] // TableForm


but it put the entire page in table form. I just want the actual population table data.

Thank you P.S Btw I'm new to Mathematica so please explain it for me

Short answer: Use Position to locate your data then use Part to extract it. For example, we can evaluate this code

url = "https://www.census.gov/population/international/data/worldpop/table_history.php";

page = Import[url, "Data"];
Position[page, "10000 BC"]
(*  {{3, 1, 2, 2, 1, 1}}  *)


Based on those subscripts, we can look at page[[3]] then look at page[[3,1]], etc. Finally we decide to look at

table = page[[3, 1, 2 ;;, 2]][[1]];
table[[All, 1 ;; 3]] // TableForm
(*
{
{"10000 BC", 1, 10},
{"8000 BC", 5, 5},
{"6500 BC", 5, 10},
{"5000 BC", 5, 20},
{"4000 BC", 7, 7}
}  *)


The actual data goes down farther than shown.

Explanation of the Code (beginners only)

Detailed descriptions of what the Part command and the Position command do can be found in the documentation. The Part command is usually written as [[ ]]. There is a lot of information in the Mathematica documentation about searching in lists. You might start with Testing and Searching List Elements, but look at all of the related guides and tutorials also. What you need to know is not in just one place.

In the above code, we separated the URL string from the Import command for readability. Import returns a nested list, which is a list of lists. If the evaluate the command Length[page], we find that Import has given us a list contain 3 more lists.

When we evaluate Position[ page, "10000 BC"] we get back another nested list, {{3, 1, 2, 3, 1, 1}}. This means the data we are looking for can be found somewhere in the 3rd element of page, which is itself a list. Then, in that 3rd element, our data is somewhere in 1 element, which is another list. Within that list, our data is some where in 2nd element, which is still another list.

Note that if we evaluated Position[ page, 10000 ], Mathematica would look for the integer 10000 and not find it. Our data is the string "10000 BC".

We could examine all of the elements by using Part and looking at a = page[[3]] and then b = a[[1]] and then c = b[[2]], etc. We know what subscripts to use, because Position has told us exactly where to find our data.

To find a range of data, we use ;; in our subscripts. The meaning can be found in the documentation for Part.

• Hi, I tried running it but I keep enchanting errors? How do I use the Part function? – Frimps-Akua O-A Sep 27 '17 at 13:02
• @Fri, hard to say anything useful if you don't say what these errors are. – J. M.'s technical difficulties Sep 27 '17 at 13:12
• @J.M., It says the following: FetchURL::httperr: The request to URL census.gov/popluation/international/data/worldpop/… was not successful. The server returned the HTTP status code 404 ("Not Found"). $Failed Part::partd: Part specification$Failed[[3,1,2;;All,2]] is longer than depth of object. – Frimps-Akua O-A Sep 27 '17 at 13:25
• Continuing: Symbol::argx: Symbol called with 0 arguments; 1 argument is expected. Symbol[ ] – Frimps-Akua O-A Sep 27 '17 at 13:29
• @Frimps-AkuaO-A Regarding the 404 ("Not Found") error: the Import command worked before and gave you the full page of data. What changed? Check the URL in a web browser to make sure the URL is right and the website is providing the page we want. If the website is reachable, the Import command should work for you exactly as it did before. – LouisB Sep 27 '17 at 18:31

here is another approach, using @LoiusB's page

data=Cases[ page , {s_String /;
StringMatchQ[s,
NumberString ~~ " " ~~ "BC" | "AD"], ___}, Infinity]


However

If you look at the page you see there are many empty fields in the table and Import[..,"Data"] has shifted everything to the left. Importing "FullData" seems to work:

page = Import[url, "FullData"];


this is just some of the rows:

{{"10000 BC", 1, 10, "", "", "", "", 4, 1, 10, "", "", "", ""}, {"500 BC", 100, "", "", "", "", "", 100, "", "", "", "", "", ""}, {"700 AD", 207, 210, 207, "", "", "", 210, "", "", "", "", "", ""}, {"1340 AD", 443, "", 443, "", "", "", "", "", "", "", "", "", ""}, {"1850 AD", "1,128", "1,402", "1,241", "", "", "1,265", "1,200", "1,200", "", "1,128", "1,402", "1,260", ""}}

note as another bit of fun the "thousands" numbers are strings, so we need to fix that. the average of the first column is:

tonumber[v_Integer] = v
tonumber[v_String] := ToExpression[StringReplace[v, "," -> ""]]
Mean[tonumber /@ data[[All, 2]]] // N


541.718

I'm not sure how to handle the second column since its not fully populated.