Does anyone have CT/RI CD results broken down by county, rather than town?

[Calthrina950]

The question is as in the title. Does anyone have access to the 2016 presidential election results, by congressional district, in Rhode Island and Connecticut that are broken down by county, rather than by town or municipality? That is how Daily Kos breaks them down, but I'd rather have the results by county. I would greatly appreciate it.

[Calthrina950]

I'll clarify further. Specifically, I'm looking for the 2016 presidential election results by congressional districts in those two states, that are broken down by county, similar to how Daily Kos has done so for all other states. It would be nice if that included the total vote for each county portion as well. I wanted to use these numbers for an electoral scenario that I've worked on.

[Calthrina950]

Here's the data you've requested for Rhode Island.

RI-01
Bristol County
Clinton 14,609 (57.35%)
Trump 8,965 (35.19%)
Johnson 943 (3.70%)
Write-ins 607 (2.38%)
Stein 317 (1.24%)
De La Fuente 34 (0.13%)
Newport County
Clinton 22,851 (55.67%)
Trump 15,077 (36.73%)
Johnson 1,529 (3.73%)
Write-ins 946 (2.30%)
Stein 590 (1.44%)
De La Fuente 52 (0.13%)
Providence County
Clinton 92,217 (60.45%)
Trump 51,301 (33.63%)
Johnson 4,250 (2.79%)
Write-ins 2,640 (1.73%)
Stein 1,922 (1.26%)
De La Fuente 217 (0.14%)

RI-02
Kent County
Trump 38,336 (46.72%)
Clinton 37,788 (46.05%)
Johnson 2,943 (3.59%)
Write-ins 1,805 (2.20%)
Stein 1,049 (1.28%)
De La Fuente 132 (0.16%)
Providence County
Clinton 50,682 (52.83%)
Trump 39,581 (41.26%)
Johnson 2,522 (2.63%)
Write-ins 1,732 (1.81%)
Stein 1,263 (1.32%)
De La Fuente 147 (0.15%)
Washington County
Clinton 33,741 (50.84%)
Trump 27,230 (41.03%)
Johnson 2,543 (3.83%)
Write-ins 1,704 (2.57%)
Stein 1,062 (1.60%)
De La Fuente 89 (0.13%)

Thank you for this!

[Calthrina950]

The question is as in the title. Does anyone have access to the 2016 presidential election results, by congressional district, in Rhode Island and Connecticut that are broken down by county, rather than by town or municipality? That is how Daily Kos breaks them down, but I'd rather have the results by county. I would greatly appreciate it.

This is pretty simple to do with a spreadsheet - and it is how I draw district maps.

Assuming you have a row with the results for each town; add a column for the district and a column for the county for each town. For any towns split between districts, just treat the two parts as separate towns, with the same county, but different districts.

You can then use SUMIFS to sum the results for each combination of county and district.

I don't know if it matters for your scenario, but Connecticut does not have counties. The Census Bureau continues to tabulate data for the former counties for purposes of statistical comparability, both temporally, and for geographical comparability with the rest of the country.

I didn't know this. I suppose that Connecticut is similar to Massachusetts in that it does not have county governments. As for your instructions, I'm not too familiar with Microsoft Excel, but I'm going to try to see what I can do. Daily Kos has the results by town, and specifies which towns are in which congressional districts, but not the counties they are located in.

[Calthrina950]

The question is as in the title. Does anyone have access to the 2016 presidential election results, by congressional district, in Rhode Island and Connecticut that are broken down by county, rather than by town or municipality? That is how Daily Kos breaks them down, but I'd rather have the results by county. I would greatly appreciate it.

This is pretty simple to do with a spreadsheet - and it is how I draw district maps.

Assuming you have a row with the results for each town; add a column for the district and a column for the county for each town. For any towns split between districts, just treat the two parts as separate towns, with the same county, but different districts.

You can then use SUMIFS to sum the results for each combination of county and district.

I don't know if it matters for your scenario, but Connecticut does not have counties. The Census Bureau continues to tabulate data for the former counties for purposes of statistical comparability, both temporally, and for geographical comparability with the rest of the country.

I didn't know this. I suppose that Connecticut is similar to Massachusetts in that it does not have county governments. As for your instructions, I'm not too familiar with Microsoft Excel, but I'm going to try to see what I can do. Daily Kos has the results by town, and specifies which towns are in which congressional districts, but not the counties they are located in.

Do you have a link to the Daily Kos data?

Does it have town splits for
Glastonbury (CT-1, CT-2)
Middleton (CT-1, CT-3)
Shelton (CT-3, CT-4)
Torrington (CT-1, CT-5)
Waterbury (CT-3, CT-5)

Historically in New England, town governments provided local government. There might not be any county government at all, but they might represent part of the judicial system. Perhaps being used for trying felonies.

In Massachusetts, Middlesex got into financial trouble over mismanagement of a hospital and its government was abolished in 1997. There are still vestiges of the government. The District Attorney of the Northern District has jurisdiction over the area covered by Middlesex County. But District Attorneys represent the Commonwealth in criminal cases. Registry of Deeds continue to exist. These weren't really part of county government, but rather maintain records of property ownership. There are two registry of deeds in Middlesex County, one in Lowell for the northern part of the county, and one in Cambridge for the southern part of the county. Before dissolution of the county government fees on property transfers funded the county government. There is also a county sheriff elected countywide, who operates jails and other correctional facilities. There have been concerns that the elected sheriffs have no direct oversight, other than popular election every six years.

By 2000, county governments had been abolished in the 8 western and northern counties. County governments continue to exist in Norfolk, Bristol, Plymouth, Barnstable, Dukes, and Nantucket. Barnstable has a county charter, and its governance is somewhat more like counties in states to the west.

Here's a link to the Daily Kos data: https://docs.google.com/spreadsheets/d/10robkOZ4P2gutdfEaaKii-mL4Vbxv3hfGiRYeZZOSGA/edit#gid=0. Yes, it does include town splits for those towns which you listed. And I was aware, at least somewhat, of the situation in Massachusetts. Somewhat confusing to me, since I live in a state (Colorado) where county governments are fully intact and active.

[Calthrina950]

	1	1	2	2	3	3	4	4	5	5
	Obama	Romney	Obama	Romney	Obama	Romney	Obama	Romney	Obama	Romney
Fairfield	0	0	0	0	13954	9781	168285	132694	35202	32846
Hartford	184253	97128	15815	13741	0	0	0	0	44612	32389
Litchfield	8509	9708	0	0	0	0	0	0	35240	37430
Middlesex	8198	6146	23947	19941	15710	8504	0	0	0	0
New Haven	0	0	5293	5193	161632	92627	2599	3906	49567	36682
New London	0	0	67166	46142	0	0	0	0	0	0
Tolland	0	0	39366	30450	0	0	0	0	0	0
Windham	0	0	25957	19768	0	0	0	0	0	0

I screwed up and gave you the link to the 2012 results, not the 2016 results. Thank you for taking the time to assemble this though. Here's the correct link, to the 2016 results: https://docs.google.com/spreadsheets/d/17yr9mcAtuUdNjI9NEPYKxXsEldzzQ2ZaDwEAbnPRyS4/edit?pref=2&pli=1#gid=60704755.

[Calthrina950]

	1	1	2	2	3	3	4	4	5	5
	Obama	Romney	Obama	Romney	Obama	Romney	Obama	Romney	Obama	Romney
Fairfield	0	0	0	0	13954	9781	168285	132694	35202	32846
Hartford	184253	97128	15815	13741	0	0	0	0	44612	32389
Litchfield	8509	9708	0	0	0	0	0	0	35240	37430
Middlesex	8198	6146	23947	19941	15710	8504	0	0	0	0
New Haven	0	0	5293	5193	161632	92627	2599	3906	49567	36682
New London	0	0	67166	46142	0	0	0	0	0	0
Tolland	0	0	39366	30450	0	0	0	0	0	0
Windham	0	0	25957	19768	0	0	0	0	0	0

I screwed up and gave you the link to the 2012 results, not the 2016 results. Thank you for taking the time to assemble this though. Here's the correct link, to the 2016 results: https://docs.google.com/spreadsheets/d/17yr9mcAtuUdNjI9NEPYKxXsEldzzQ2ZaDwEAbnPRyS4/edit?pref=2&pli=1#gid=60704755.

(1) Open a new blank spreadsheet.

(2) Copy the data you want/need from the Daily Kos spreadsheet. You will need the district column, the town column, the Trump and Clinton columns, and the Total. I had skipped the total values, because I didn't realize that it included the third party votes. If you want the percentages, you can always reproduce them.

(3) Paste the data in your blank spreadsheet. Select cell A1, and then Paste. Choose the option to paste only data values.

Columns are:

A: District
B: Town (mislabeled as county, you can change this)
C: Blank
D: Clinton
E: Trump
F: Total

(4) Copy the district column (Column A) to all towns in the district. Select the range of towns with the same district then press Ctrl-D to fill down. Do this for all 5 districts.

(5) Delete the rows with the district totals, and the grand totals. They are in the way at this point and can be reproduced if necessary. I have 175 rows, including the header Row 1.

(6) Copy the column with the town names to a new column Since C is blank, we can copy the contents of Column B to Column C. Select Column C, and then press Ctrl-H for find and replace. For find text use: " (pt.)" and replace text use nothing. We want the town names to be clean when associating with counties, but it may be useful to retain the town splits.

The first 5 rows of my spreadsheet look like this.

CD	Town	Town	Clinton	Trump	Total
1	Barkhamsted	Barkhamsted	905	1,157	2,193
1	Berlin	Berlin	4,913	6,210	11,591
1	Bloomfield	Bloomfield	9,637	1,683	11,627
1	Bristol	Bristol	12,499	12,752	26,421

Thank you for the instructions. I will get to it.

[Calthrina950]

	1	1	2	2	3	3	4	4	5	5
	Obama	Romney	Obama	Romney	Obama	Romney	Obama	Romney	Obama	Romney
Fairfield	0	0	0	0	13954	9781	168285	132694	35202	32846
Hartford	184253	97128	15815	13741	0	0	0	0	44612	32389
Litchfield	8509	9708	0	0	0	0	0	0	35240	37430
Middlesex	8198	6146	23947	19941	15710	8504	0	0	0	0
New Haven	0	0	5293	5193	161632	92627	2599	3906	49567	36682
New London	0	0	67166	46142	0	0	0	0	0	0
Tolland	0	0	39366	30450	0	0	0	0	0	0
Windham	0	0	25957	19768	0	0	0	0	0	0

I screwed up and gave you the link to the 2012 results, not the 2016 results. Thank you for taking the time to assemble this though. Here's the correct link, to the 2016 results: https://docs.google.com/spreadsheets/d/17yr9mcAtuUdNjI9NEPYKxXsEldzzQ2ZaDwEAbnPRyS4/edit?pref=2&pli=1#gid=60704755.

(1) Open a new blank spreadsheet.

(2) Copy the data you want/need from the Daily Kos spreadsheet. You will need the district column, the town column, the Trump and Clinton columns, and the Total. I had skipped the total values, because I didn't realize that it included the third party votes. If you want the percentages, you can always reproduce them.

(3) Paste the data in your blank spreadsheet. Select cell A1, and then Paste. Choose the option to paste only data values.

Columns are:

A: District
B: Town (mislabeled as county, you can change this)
C: Blank
D: Clinton
E: Trump
F: Total

(4) Copy the district column (Column A) to all towns in the district. Select the range of towns with the same district then press Ctrl-D to fill down. Do this for all 5 districts.

(5) Delete the rows with the district totals, and the grand totals. They are in the way at this point and can be reproduced if necessary. I have 175 rows, including the header Row 1.

(6) Copy the column with the town names to a new column Since C is blank, we can copy the contents of Column B to Column C. Select Column C, and then press Ctrl-H for find and replace. For find text use: " (pt.)" and replace text use nothing. We want the town names to be clean when associating with counties, but it may be useful to retain the town splits.

The first 5 rows of my spreadsheet look like this.

CD	Town	Town	Clinton	Trump	Total
1	Barkhamsted	Barkhamsted	905	1,157	2,193
1	Berlin	Berlin	4,913	6,210	11,591
1	Bloomfield	Bloomfield	9,637	1,683	11,627
1	Bristol	Bristol	12,499	12,752	26,421

On a further note, I'm having trouble with the "find use" function. In the Find what field, I used (pt.) and in the Replace with field, I used nothing, as you said. But it's telling me that it cannot find any matches, and won't perform the operation.

[Calthrina950]

On a further note, I'm having trouble with the "find use" function. In the Find what field, I used (pt.) and in the Replace with field, I used nothing, as you said. But it's telling me that it cannot find any matches, and won't perform the operation.

Make sure you have selected Column D before you do the replace. The quotation marks aren't part of the " (pt.)"

Something I usually do is to select the text to be edited. So if you go down to

Glastonbury (pt.)

Then select the text beginning with the space after the y, through the closing parenthesis, and click on Ctrl-C. Press enter, which will leave the cell contents along. Select Column D, and the Ctrh-H, and Ctrl-V to paste the text to be removed.

We need a column with the County name in it. You can just use a map, and paste the county name for each town, but that can be tedious, and I would probably make mistakes, if I scan across on a row.

So I go to the Census Bureau website

Click Explore Data, then Data Tools and Apps, and scroll down to American Fact Finder. Click on Advanced Search and Show Me All.

Click on Geographies and in the pull down menu, select County Subdivision - 080

Select Connecticut, then Select all County subdivisions within Connecticut, then click Add to Your Selections. "All County Subdivisions within Connecticut" should appear in the Search Box in the upper left corner.

Close the Geographies Menu.

Select Topics,

Year, 2010,
Program, Decennial Census, and
People, Basic Count/Estimate, Population Total

Close the Topics Menu and then Check P1 Total Population and Click on View.

You should see a Table of Towns and populations.

Depending on what you are analyzing, you could also get VAP or racial/ethnic data.

Select Download, Use Data in a spreadsheet, and OK. Then click on Download.

Your browser should open a dialogue. I open the zip file with Windows Explorer. When I get the list of files, I double-click on the largest .csv file. I may have associated .csv files with Excel. This might not be the default.

GEO.id	GEO.id2	GEO.display-label	D001
Id	Id2	Geography	Total
0600000US0900100000	9E+08	County subdivisions not defined, Fairfield County, Connecticut	0
0600000US0900104720	9E+08	Bethel town, Fairfield County, Connecticut	18584
0600000US0900108070	9E+08	Bridgeport town, Fairfield County, Connecticut	144229
0600000US0900108980	9E+08	Brookfield town, Fairfield County, Connecticut	16452
0600000US0900118500	9E+08	Danbury town, Fairfield County, Connecticut	80893

The top of the file looks like this.

I tried this, but I still don't fully understand it, and haven't been able to copy over the information from Column B into Column C. I'm also not too familiar with how to open and transfer Zip files. Up to this point, I've only been able to complete Steps 1-4 in your initial post.

[Calthrina950]

-snip.-

I've been following your instructions, but it's still not allowing for me to make the change. I've attached a screenshot of the error message that I'm given when I attempt to do so:

[Calthrina950]

-snip.-

I've been following your instructions, but it's still not allowing for me to make the change. I've attached a screenshot of the error message that I'm given when I attempt to do so:

It is possible that you successfully edited the cells. If I do the replace, and then attempt to do a replace a second time, I get the same error message. The text about protected cells may be a red herring. You can protect a worksheet or portions of a worksheet so that other can not modify.

For example, you have a spreadsheet where a salesman can enter the amount of a sale, and the spreadsheet calculates the amount of his sales commission.

You enter the sales price in A1, and in B1 you enter the formula =A1*10%

You test the spreadsheet and it shows:

$123.45  and $12.35

You are satisfied that it works, and let the salesman use it. But he goes in and modifies B1 so it reads =A1*12%. He then enters his sale and it displays the "commission"

$137.45 and $16.49

The cashier who is none too bright in the first place, and doesn't understand how the commission is calculated, and couldn't multiply by 10% in their head, takes $16.49 out of the cash drawer.

When you do a replace you are editing the cells. If a cell was locked, you couldn't make a replacement, even if the text was found.

Go down to B11 and C11 and check whether B11 is Glastonbury (pt.) and C11 is Glastonbury

Both B11 and C11 are showing as "Glastonbury (pt.)". Is there anything that ought to be done to correct this? Or what are the next steps to take?

[Calthrina950]

Both B11 and C11 are showing as "Glastonbury (pt.)". Is there anything that ought to be done to correct this? Or what are the next steps to take?

Can you edit C11 by hand?

Select C11 and up at the top in the formula bar you should see Glastonbury (pt.). Move the cursor up to that and it will switch from a + cursor to an insertion cursor (vertical line with a short horizontal cap and base. Move that to the end of line after the closing parentheses and then backspace over the portion to be removed. Press enter. Hopefully you will get an error message.

Select H11

Enter =CELL(
You should get a drop-down menu, click on Protect and "protect" should appear.
Enter , (just a comma)
Select C11 and C11 should appear
Enter )  (a closing parentheses)
Press the Enter Key.
If C11 is protected the value of H11 will be 1.

You can also enter  =CELL("protect",C11) in H11, but Excel is pretty good about assisting you in creating formulas.

I noticed you had retained the colored formatting from the Daily Kos spreadsheet. I usually remove presentation formatting. It is easier to add back in when finished. And when cells are copied, the formatting is also copied.

I left out a step in my instructions. After you have copied the cells from the Daily Kos spreadsheet, click on the small arrow below the Paste icon on the empty spreadsheet. Two options will show up. The one on the left will retain source formatting. The one on the right will match the destination format - which in a blank spreadsheet will be default formatting.

But I don't think is the source of not being able to replace the (pt.). I tried it with colored text and it still worked.

Did you have Edit access to the Daily Kos spreadsheet? I just had View access. Or do you have more than one user for your computer?

I've followed your additional instructions here, and was able to successfully generate the "1" value. Here is a screenshot of what the worksheet looks like now:

I did not have Edit access to the Daily Kos spreadsheet, only View access, like you did. And there's only one user on my computer; that shouldn't affect anything with the spreadsheet.

[Calthrina950]

I think I figured out the problem.

After you have selected Column C, and pressed Ctrl-H to get the Search and Replace menu.

Then click on <<Options

If the Match Entire Cell Contents option is check-marked, uncheck it.

The search options are remembered from search to search. You can close the Options dialogue, but the options are not changed. I tend to close it, just so it takes up less space.

Something else you can do is use the Find Next and Replace buttons to selective replace. Sometimes it will be hard to figure out a search string that won't choose some extraneous results.

Perhaps you want to change President" to "Vice-President" You don't want to type in the whole thing so you replace "Pres" with "Vice-Pres".

After you finish, you will find that you now have "ReVice-Presentative" and you think, hmm, I never realized Representative has a "pres" in it.

What you can do is on the search and replace is press Find Next and it will find the next cell that matches the search string. If you want to change that cell, press Replace, and that cell will be changed and the next match will be highlighted. If you don't want to do a replace, just press Find Next and it will skip to the next match.

Instead of pressing Replace All, you could have pressed Find Next and Glastonbury (pt.) would have been highlighted. Press on Replace and it will be changed to Glastonbury, and advance to Middleton (pt.). You can do this a few more times until you are satisfied the replacement is what you wanted to do.

In the upper left corner there is a circular arrow pointing to the left or counter-clockwise. This is the Undo button. You can click multiple times to step back several steps. You can also use Ctrl-Z.

I've checked, and the "Match entire cell contents" box is unchecked. However, I'm confused about what you outline to do after that-I'm still not too familiar with how the "Replace All" option works.

Are you running in protected mode? When you download an Excel file from the internet, sometimes it doesn't allow you to edit the file at all. If you don't get out of protected mode (there should be a box on the top that says you're in it with a button to get out of it, or I think if you hit File, it might say you're in it), it affects your ability to edit that spreadsheet and sometimes really slows down and messes up editing other spreadsheets.

Also, click on options after you hit CTRL-H to search and replace, and make sure the Match case and match entire cell boxes aren't checked.

Finally, I think there's an issue with CTRL-H defaulting to look into Formulas instead of just Values when doing a search and replace. I can change a CTRL-F search to only search for values, but can't do it for CTRL-H for some bizarre reason.

No, it is not running in Protected Mode, and I merely copied and pasted the relevant parts of the Daily Kos spreadsheet onto a Excel spreadsheet, as jimrtex had earlier specified. And I've checked Options, and the boxes which you indicate are unchecked.

[Calthrina950]

No, it is not running in Protected Mode, and I merely copied and pasted the relevant parts of the Daily Kos spreadsheet onto a Excel spreadsheet, as jimrtex had earlier specified. And I've checked Options, and the boxes which you indicate are unchecked.

Is the Daily Kos spreadsheet still open in another window? That sometimes will affect the other windows. In my experience, having protected mode on in one Excel Workbook has a tendency to slow down and sometimes screw up other workbooks. It's probably best to close the Daily Kos spreadsheet, if it's still open.

The Daily Kos spreadsheet is not open at all. As I said, I just copied and pasted it into another workbook, and closed the original spreadsheet soon thereafter. I'm trying to determine the results by county for Connecticut's congressional districts (along with the total vote cast by each county within each district), similar to how you determined Alaska's presidential election results by county.

[Calthrina950]

No, it is not running in Protected Mode, and I merely copied and pasted the relevant parts of the Daily Kos spreadsheet onto a Excel spreadsheet, as jimrtex had earlier specified. And I've checked Options, and the boxes which you indicate are unchecked.

Is the Daily Kos spreadsheet still open in another window? That sometimes will affect the other windows. In my experience, having protected mode on in one Excel Workbook has a tendency to slow down and sometimes screw up other workbooks. It's probably best to close the Daily Kos spreadsheet, if it's still open.

The Daily Kos spreadsheet is not open at all. As I said, I just copied and pasted it into another workbook, and closed the original spreadsheet soon thereafter. I'm trying to determine the results by county for Connecticut's congressional districts (along with the total vote cast by each county within each district), similar to how you determined Alaska's presidential election results by county.

I understand what you're trying to do. I'm trying to troubleshoot why your Find and Replace CTRL-H function isn't working. In theory, it should work. I'm stumped now.

I'm not sure what's going on either. Maybe jimrtex will have some additional insights later.

[Calthrina950]

I've checked, and the "Match entire cell contents" box is unchecked. However, I'm confused about what you outline to do after that-I'm still not too familiar with how the "Replace All" option works.

Was it checked, and then you unchecked it?

If it was checked, then when you searched on " (pt.)" it would only find cells that consisted entirely of " (pt.)". Since there were no such cells, it was returning a warning message. Unchecking it should let it find the cell "Glastonbury (pt.)" and replace the " (pt.)" with nothing.

If you can select Column C, then Ctrl-H, and enter the find string of " (pt.)" and the replace string of nothing, when you press Replace All does it make 10 replacements (or 9 if you have changed "Glastonbury (pt.)" by hand?

If so you can go to the next step.

OR, you can press Ctrl-Z to undo the replacement. And then with Column C selected, then Ctrl-H, press Find Next and it will highlight "Glastonbury (pt.)" or "Middletown (pt.). Pressing Replace will make the replacement in that cell only, and then advance to the next cell containing the search string.

If you had some reason for not replacing every occurrence of " (pt.)" you could press Find Next, which will go to the next occurrence of a cell of " (pt.)" without making a replacement.

No, it was unchecked, but I ran the CTRL +H formula again, and was able to get 10 replacements using Replace All. This is what the worksheet looks like now:

[Calthrina950]

No, it was unchecked, but I ran the CTRL +H formula again, and was able to get 10 replacements using Replace All. This is what the worksheet looks like now:

Have you deleted the summation rows for each district? Once you've done that, we can get Census Data that has town-county relationships.
Go to the Census Bureau website

(0) Look at the picture of Toksook Bay, the Dixville Notch of the 2020 Census, and shiver a bit. Then Click on Explore Data; Explore Data Main; and the box that says go to Data.Census.Gov. Click on Advanced Search.

(1) Click on Years, Select 2010 (the Census Year). Notice your filters show up at the bottom of the page (this is a tiny bit laggy, it won't appear instantly).



(2) Click on Surveys, Select DEC Summary File 1 (you will have to scroll down a bit). DEC is the Decennial Census. Summary File 1 (or SF1) is the main population data file for the Census.




(3) Click on Geography, County Subdivisions, Connecticut, All County Subdivisions within Connecticut.


In New England, county subdivisions are towns and cities. As you go west, there may also be cities that are separate subdivisions. By the time you get to Colorado county subdivisions are census constructs and useless.

(4) Click on Topics, Population and People, and Population and People.



(5) Click on Search (lower right corner) and View All Tables



(6) Scroll down and select Total Population, you will have to Load More several times.



This is clunky does anyone know a more efficient way?

Notice you now have a table of Connecticut towns with total population. There is a scroll bar at the very bottom of the page.

(7) Click on Customize Table, and then the Download Icon.


[size=14.6667pt]( Make sure CSV is selected and then select Download.[/size]


(9) Then click on gray area below, to see progress bar. This may take a few seconds.



When it is 100% Complete click on Download Now.


(10) You will get this popup. Make sure Open With is selected, and you can click on Browse to confirm that Windows Explorer is the application that will open the Zip File. Click on OK.



It will take a bit of time to download. In Firefox, you can check the download progress by clicking the Down Arrow at the upper right of the browser window. A Zip File is a container for other files that are also compressed. Windows knows how to handle zip files, and you can pretty much treat it like a folder.


(11) When the download is complete, a Windows Explorer window will appear.



We want the largest file.

(12) Click on the largest file, holding the left mouse button down a drag the file to a directory for your current project.



(13) Right click on the .csv file, and Open with Excel.



If there is an Excel icon in front of the name, .csv files are associated with Excel, and you can just double click on the file.


(14) Your spreadsheet should look like this.


I have selected Cell B6, which shows that we have the town name and county name, which we will parse.

Column A is like an URL for Census Entities. 060 indicates it is a County Subdivision; US is for USA - there is at least an intent for Canada and Mexico to use the same standard format. 09 is the code for Connecticut. 001 is the code for the Fairfield County, and 18500 for Danbury.

Column B is the text identification. A county subdivision Danbury is a subdivision of Fairfield County, which is a county in Connecticut.

Column C is the 2010 Total Population of the town.

0600000US0900118500

Danbury town, Fairfield County, Connecticut

80893

I got all the way up to the stage with Window Explorer. However, I am confused by what you mean with "Directory" for my current project. Am I supposed to create a folder to contain the spreadsheets? Or is it something else that I do? I'm not exactly certain about this.

[Calthrina950]

I got all the way up to the stage with Window Explorer. However, I am confused by what you mean with "Directory" for my current project. Am I supposed to create a folder to contain the spreadsheets? Or is it something else that I do? I'm not exactly certain about this.

Sorry, I used confusing terminology. You had written:

   "I wanted to use these numbers for an electoral scenario that I've worked on."

It is not clear what you mean (and that is OK). But you might be preparing a presentation for a class; or just for Atlas, etc. I find it convenient to place the files related to some project in a folder.

Directory is an alternative terminology to folder. "directory" is in an older term used on Unix and MS-DOS, and refers to the directory to a file system, where you would put pointers to a related set of files in a directory, and would use the DIR command to list them (I guess on Unix you would use the ls command to list the files). Folder is more modern term and is a metaphor for a file folder in a filing cabinet. When I look at a listing of the contents of a folder including attributes of the file such as name, file type, creation date, size, it looks like a directory listing to me.

This is a personal project, centered around this scenario: https://fiction.fandom.com/wiki/United_States_presidential_election,_2016_(Ferguson_Scenario). And I understand now what you mean by directory. The spreadsheet is in a folder, but it's a folder that includes work for the rest of the scenario. Could you reiterate your instructions again?

[Calthrina950]

Bumping this up. I'm wondering if anyone else has insight regarding this question. I have the Rhode Island results already, but I'm trying to save time in obtaining those for Connecticut. Are there any other websites that might have the data for those districts?

[Calthrina950]

I'm pushing this forward. Again, are there any other data sources for how Connecticut's congressional districts voted, by county, in the 2016 presidential election?

[Calthrina950]

I hadn't posted this before. Starting with the file we downloaded from the Census Bureau, delete the rows with 0 population, that say they are not a county subdivision. These are in Long Island Sound, and are considered part of a county, but not a town.

(0) This shows the content of B3, and illustrates where we will divide it.



We are going to parse the string to the left of the first comma.

(1) Begin typing in D3:  =Le      As you type functions that match will appear.



(2) Double click on LEFT. This will complete the spelling of LEFT and add the opening paren.
Note that a help for the function arguments appears below. If this is in your way, you can drag it elsewhere.



(3) Click on the cell B3, which will enter its address in the function. Cells used for function arguments will be highlighted.



(4) Enter a comma, and type FI the first letters of FIND.



(5) Double Click on FIND, which adds the opening parentheses. Enter ",", (quote, comma, quote, comma) and click on cell B3.



(6) Enter )-1  (close paren, minus-sign, numeral 1)



(7) Enter ) (close paren)  Notice the completed expression of

=LEFT(B3,FIND(",",B3)-1)

We want the left portion of B3, before the first comma. FIND(",",B3) finds the position of the comma. We subtract one because we don't want the comma.



( Press the Enter Key, The function is now evaluated.



The value of D3 is:  Danbury town which is what we wanted.

I've done all of this, and all of what you outlined in the previous post. I renamed the file "Data Overlay", and saved it as an Excel Workbook. What is the next step?

[Calthrina950]

We are now going to copy the formula down column D.

(9A) Select Cell D3. Then while holding the shift-key down, go down with the down arrow or page down key or a combination of both.



(9B) While continuing to hold the shift-key down, continue down until you have reached the last entry. If you overshoot, you can back up with the up arrow. You have now selected the region D3:D171



(10) Release the shift key, and press CTRL-D (D as in down). This copies the formula in the top row of the selected area, to the bottom. Note that the formula is automatically incremented, so that D171 references C171.



(11) We are now going to convert the formulas to actual data values. While the area is still selected, press CTRL-C to copy to the clipboard. Then click on the little arrow below the Paste icon in the upper left, to get a pull down menu.



(12) Click on the icon with below Paste values that says 123. This converts the formula in the clipboard to the evaluated values. Note that the formula bar reads Orange Town rather than =LEFT(B171,FIND(",",B171)-1)



(13) We are now going to edit the " town" from each town. We want to match the values in the election results spreadsheet. This is an alternate way to select a region. Select cell D3 at the top of the column. Then while holding the shift-key press End then the Down Arrow. This goes to the last row that is not blank.



(14)  Press CTRL-H to get the find and replace menu. For the Find What: (sp)town
That is, one space before town. For the Replace With: make sure there is nothing. And select the Match Case option. We want to take a little bit of care that we don't convert "Middletown town" into "Middle". Click on Replace All, and Close the dialogue.

If you mess up, you can back up with CTRL-Z. In other states, not all of the county subdivisions will be towns. So you might have to also strip off " city". Connecticut has cities, but they are contained within a town, even if they are coterminous. In Massachusetts and New York some county subdivisions are cities rather than towns. For example Hudson city is treated as a county subdivision in Columbia County along with the other towns.



(15) We are now going to isolate the county from B3 etc,

In E3 construct the formula in the Formula Bar

=RIGHT(B3,LEN(B3)-FIND(",",B3)-1)

You can type the entire formula, but I find it simpler to construct formulas. This formula takes the right portion of the value in B3. The number of characters on the right is the length of the string minus the position of the first comma minus one).



(16) Press enter, and check that we got the right string. If we took one to few characters, we would have "airfield County, Connecticut", if we took too many their would be a leading space. If we make a mistake, CTRL-Z to undo, and correct the formula. You can click on the formula in the formula bar and use the backspace, delete, or ordinary keys. For example if we had typed -2 instead of -1, which gave us "airfield", simply select E3, click just behind "2" in the formula, backspace, and press 1. Then press enter.



(17) We are now going to copy the formula down Column E. This is an alternative way to select a region. If you only have 170 rows it is simple to quickly page to the end. If you have several 1000 rows it is not so quick.

First while E3 is selected, Hold the shift-key press end and the down arrow. You have selected E3:E1048576 which is the maximum row that Excel can handle.

That is not what you want. So Select D3, Then End and the down arrow. This will naviagte to D171. Then use the right arrow to move to E3. Holding the shift key, press end and the up arrow. This will take you up to through the first row that is not blank (i.e. E3). Press CTRL-D to copy the formula down the column.



Go ahead and convert the formulas to data values: (a) Select the region you want to convert. (b) CTRL-C to copy to clipboard, (c) Paste Values to same region.

(18) We are now going to strip off the (sp)County,(sp)Conecticut. Rather than typing that in. We copy it from the contents of E3, Select E3. Move the cursor to the Formula Bar, it will switch from the + cursor to a text entry cursor (vertical bar). Position after the d in Fairfield, and with a click hold move to the end of the string (it will be highlighted in gray Click on CTRL-C to copy the selected text.



(19) Select the region to be edited. Then press CTRL-H and then placing the cursor in the Find What box, press CTRL-V and then Replace All.



(20) We now have a file that contains town-county pairs. We can set this file aside, in case we have another data set organized by CD (let's say that someone posts the primary results, and you want to convert to county. Or maybe the SOS will post results by county and you want to convert that to CD,

But for now, we just want to associate the towns in your data file with their counties. We can work between two spreadsheets, but it is a little easier to work in a single spreadsheet.

I have simply copied the contents of the file we just edited into the data worksheet. Select A2, then while holding the shift-key End Down-Arrow to get to the end of Column A, then use the Right-Arrow to expand the region to the right. Press CTRL-C to copy. Go to the data spreadsheet, select cell N1 and press CTRL-V to paste it.

I was able to do it up to Step 13, but now I'm receiving an error message:

[Calthrina950]

I was able to do it up to Step 13, but now I'm receiving an error message:

What did the find and replace dialogue look like before the error message covered it up?

It looked as you had stated:

[Calthrina950]

I was able to do it up to Step 13, but now I'm receiving an error message:

What did the find and replace dialogue look like before the error message covered it up?

It looked as you had stated:

I confused you.

I meant " town" without the quotation marks. The (sp) was indicating that there was a leading non-visible space character.

I went back and got it, and this is what I have now:

I've done everything up to Step 16.

[Calthrina950]

I went back and got it, and this is what I have now:

I've done everything up to Step 16.

What is in Cell E3. Select E3, and show what is in the Formula Bar.

I managed to get past Step 16, and it now looks like this: