Expert Coders: Is this possible or conceivable? Coding votes into binary

[dguenther]

Excel 2007 on Windows 7

Hi,
I don't know if what I am about to ask is even possible. I kind of think not. But, Mr Excel people have done amazing things for me so far.

Here is the situation:

I have hundreds of files like this one:
https://my.syncplicity.com/share/bbxisyzjvs/WV_macro_applied.xlsm

The first sheet is a bunch of legal case information, spread out over hundreds of rows in column A.

The second sheet is always called "Report". It has taken the information from the first sheet and nicely put every case into its own row, separating data into it proper column. Super kudos to Rick Rothstein and Sektor for those codes.

One further transformation is necessary. I have resigned myself to thinking I will have to do this by hand, but maybe someone can surprise me. The last column of the second sheet contains the voting records of the judges on the case. Those need to be coded into 1,0, and 9.

A 1 means that the judge voted in the majority (or concurred)in the case.
A 0 means that the judge voted in the minority (dissented) in the case.
A 9 means that the judge did not vote or did not participate.

In a separate sheet, needs to be the following:

The list of docket numbers (always going to be column B from the "report" sheet) transposed to go on row A of the new "Binary" sheet.

Then magic occurs. The judges’ votes (always in column E of the Report sheet) in each case need to be put into the 0,1,9 category. There can be no other information in the "binary" sheet (per the requirements of the statistical program later used to process this stuff).

Problem is, the formatting is not beautiful. And they vary state by state. Here are three example files where I translated by hand, the judges’ votes from the “Report” sheet to the “Binary” sheet I created. (On two of them, I only did the first 5)

https://my.syncplicity.com/share/6xwkn0vp31/FL_cases_macro_applied.xlsm
https://my.syncplicity.com/share/gbe5eiem8e/VT_cases_macro_applied.xlsm
https://my.syncplicity.com/share/bbxisyzjvs/WV_macro_applied.xlsm

They can look like anything from the typical (I would venture to guess 75% of them look like this):

Adkins, C.J., and Roberts, Overton and Hatchett, JJ., concur. Boyd, J., dissents....

{So, obviously Adkins, Roberts, Overton, and Hatchett would all get a 1. Boyd would get a 0.

- to a little more complex

England, Justice. Boyd, Overton and Sundberg, JJ., concur. Adkins, C.J., dissents with an opinion, with which Chappell, Circuit Judge, concurs. ...


{Here, Boyd, Overton, and Sundberg get 1s, while Adkins and Chappell get 0s.}

- to really complicated

Adkins, Chief Justice. McCain, J., and McCrary and Lee, Circuit Court Judges, concur. Overton, J., concurring in part and dissenting in part with opinion with which Drew (Retired), J., concurs.

{Here, Adkins, McCain, McCrary and Lee get 1s. Overton and Drew get 0s. Even a partial dissent is to be coded as dissent.}

=====

So from those example files you can see what an end product needs to be. 1,0, and 9s. Deceivingly simple sounding.

I will update this post with my thoughts and experiences as I try to apply a good solution to save me weeks, possibly months! of time.

Even anything that can automate or help part of this problem would be extremely appreciated. Even just thoughts about how to go about approaching it in the most efficient way would help.


Questions, thoughts, encouragement:
Reply to this post and/or
dsguenth [at] wustl.edu

 

[dguenther]

Let's give it a whirl.

 

[dguenther]

Good morning,

So, I am trying to process
https://my.syncplicity.com/share/pbxzq5vb4d/NM_State_Cases,_Combined2011-06-21_14-17.XLSM


I removed all of the old modules and macros.

I added a new module with the latest code.

I made an entirely new workbook with nothing in it and called it Book2.

I put in the two sheets "parameters" and "binary" as instructed.

I run the GetJudgesNames macro

I select the folder where the New Mexico cases file I linked to exists.

Judges last names get populated into the binary sheet in my Book2 workbook. Nice!

-
It gets hung up when I try to run GetVotingResults macro.

{EDIT}
Wait, I think I know what I'm doing wrong.

{EDIT2}
No, that wasn't it.

I'm getting a runtime error 9 subscript out of range.

Now, does the new mexico file I am trying to process need to have a sheet called results?
{EDIT3}
It shouldn't I don't think, because in the parameters, it looks like it pulls from the sheet "Report" which it does have.

 

[al_b_cnu]

Hi Dany,

Just tried it in Excel 2007, seems to work fine (if wrong results)

Can you re-try, but ensure ALL other workbooks are closed before running.

I noticed that the online W/Book has a sheet named 'Result', but does not have a sheet named 'Report' - you'll have to rename it.

Thinking about the Voting Records, can I assume that all judge initials are in upper case, and will immediately follow the surname, may be 1 or 2 characters long, optionally with a full stop ?
We need to split the voting record into sentences I think, and process each sentence seperately.

 

[dguenther]

Having that sheet named "results" instead of report was my fault. I tried changing it to make sure it didn't have to be called results, and forgot to change it back.

With the online New Mexico file now correctly containing a "Report" sheet with case number in B and Judges votes in E, I tried running again but with the same error. All workbooks closed except for "Book2" which has the parameters and binary sheets.

Is a sheet called "results" created by the macro and put into "Book2" sheet, or do I need to have it created already?

 

[al_b_cnu]

Hi The w/book containing the macros must contain 2 sheets - 'Parameters' and 'Binary'.
GetJudgeNames populates 'Binary' with, well, Judge names, 'GetVotingResults' populates the rest of the 'Binary' sheet.

 

[dguenther]

I'll keep trying to figure out what is going wrong.

Then, I am going to look through 1 example for each state to see what the typical vote section looks like so I can give something like:

FORMAT TYPE #1

JUDGENAME1. JUDGENAME2, JUDGENAME3, JUDGENAME4, concur. JUDGE NAME5 respectfully dissents.


FORMAT TYPE #2

JUDGENAME1, wrote the opinion. Concurring: JUDGENAME2, JUDGENAME3. Dissenting: JUDGENAME4, JUDGENAME5...

 

[dguenther]

Hmm...heuristics document is taking a long time to come up with...will continue in the morning!

 

[al_b_cnu]

Hi Danny, I suspected it would :¬/

The function needs to be a bit cleverer.
I am looking at changing it to process each sentence of the voting record seperately. The 'Format #1', 'format #2' etc. approach is a good idea, but in addition I'm looking for generalised rules.
We will need to confirm that:
1) Judge initials are always uppercase, may be 1 or 2 characters long, and each initial may be followed by a full stop
2)Judge initials always appear AFTER the surname
3) If 'Concur(s)' / 'Concurring' / 'Dissent(s)' / 'Dissenting' are followed by a colon, it refers to subsequent judge names in the sentence, if no colon, it refers to prior judge names in the sentence.

Have you got the GetVotingResults macro to work? If not I can send you my version for you to try.

 

[dguenther]

Good day,

There have been about half a dozen heuristics that I don't even know how to code by hand! I've sent them out to my colleagues though and we should have the answer soon.

Get votes macro never worked for me
Danny.xls that you email me works beautifully! In fact I am going to spend some time playing with it to see its functionality and accuracy on various trials.

Then more heuristics work which will allow me to accurately answer your three questions that need confirmation.

-Danny

 

[dguenther]

News:

Got answers on my heuristics questions. Yay.

Tried the danny.xls:

*ran 50 state-years through the get judge name part. added a bunch of words to the noise filter.

*ran a full fledged trial of this file

https://my.syncplicity.com/share/z579fkrmhg/AL_State_Cases.XLSM

(I promise I will keep this file permalinked so it doesn't die like half of the other ones in this thread that I inadvertently killed by moving the file location on my computer...)

(1) Get judgesnames worked 100%. No chaff.

(2) The cases along the first row populated 100% correctly.

(3) As far accuracy goes:

(a) Something went wrong that dissents got marked as 1 and concurrences marked as 0's. Exactly backwards- but it seems like an easy fix.

(b) I flipped the results and just turned what the program found and changed the 1's to 0's. When I did that, accuracy was actually quite good when compared to my hand coding.

(c) there were 3 errors (but out of 90 votes, that's awesome)

(i) it did not correctly find that Steagall dissented here:

"Hornsby, C.J., and Jones, Shores, and Adams, JJ., concur. Kennedy, J., concurs in the result. Steagall, J., concurs in part and dissents in part. Maddox and Houston, JJ., dissent...."

(ii) similarly, it did not find that Steagall dissented here:

"Hornsby, C.J., and Jones, Shores and Adams, JJ., concur. Maddox, Houston, and Kennedy, JJ., concur in the result. Steagall, J., concurs in part and dissents in part"

(iii) while not exactly an error, it was unsure of Houston in this case:

"Shores, Justice. Hornsby, C. J., and Jones, Almon, Adams, and Kennedy, JJ., concur. Maddox, Houston, and Steagall, JJ., dissent, with opinion by Houston, J"

==
doing this actually gives me a good idea of where it gets confused or screwed up.
This file has the code's output, the code's output reversed, and the hand coded version.

-Danny