3 ms·
This looks like great fun...Judging by some of the sample entries, it will also be an instructive example of the limitations of CSV and why serious analysts who
by danso 14y ago
This looks like great fun...Judging by some of the sample entries, it will also be an instructive example of the limitations of CSV and why serious analysts who want to work with unstructured data need to know a scripting language, or at least regexes.
Sample description field:
> 20020905_SF@NYG,1,59,20,NYG,SF,3,11,81,(14:20) (Shotgun) K.Collins pass intended for T.Barber INTERCEPTED by T.Parrish (M.Rumph) at NYG 29. T.Parrish to NYG 23 for 6 yards (T.Barber).,0,0,2002
In the comments section of the OP, someone posted this sample Excel function:
=IF(ISNUMBER(SEARCH("right tackle",J2)),"rush",IF(ISNUMBER(SEARCH("right
guard",J2)),"rush",IF(ISNUMBER(SEARCH("left
guard",J2)),"rush",IF(ISNUMBER(SEARCH("up the
middle",J2)),"rush",IF(ISNUMBER(SEARCH("left
tackle",J2)),"rush",IF(ISNUMBER(SEARCH("left
end",J2)),"rush",IF(ISNUMBER(SEARCH("right
end",J2)),"rush",IF(ISNUMBER(SEARCH("pass",J2)),"pass",IF(ISNUMBER(SEARCH("kneel",J2)),"kneel",IF(ISNUMBER(SEARCH("punt",J2)),"punt",IF(ISNUMBER(SEARCH("kicks",J2)),"kickoff",IF(ISNUMBER(SEARCH("extra
point",J2)),"extrapoint",IF(ISNUMBER(SEARCH("sacked",J2)),"sack",IF(ISNUMBER(SEARCH("PENALTY",J2)),"penalty",IF(ISNUMBER(SEARCH("field
goal",J2)),"fieldgoal",IF(ISNUMBER(SEARCH("FUMBLES",J2)),"fumble",IF(ISNUMBER(SEARCH("spiked",J2)),"spike",IF(ISNUMBER(SEARCH("scrambles",J2)),"rush","rush"))))))))))))))))))
Dear god, at what point do people finally realize that it's worth learning some simple scripting to work with text files?
- HelloMcFly 14y agoThe Excel function looks ridiculous, but it probably didn't take more than 10 minutes to make, tops. Nested conditionals are easy. At any rate, what would you recommend most to accomplish the task? I'm learning Python and know R a bit, so I was just wondering how I was going to about combing through the data.
- danso 14y agoPython or Ruby is fine...the main trick is to be able to process those fields with regular expressions...which, IIRC, requires throwing in VBscript if you were to handle it solely in Excel. Python and Ruby would also allow for more elegant-looking -- i.e. more maintainable -- functions to handle that field.
- pseut 14y agoR is fine too: it has regular expressions and probably excels if you plan to do statistics using all of that data. Python seems to have reasonable statistics functionality as well (with pandas, etc) but I haven't used it personally.
- HelloMcFly 14y agoThanks for the note. I've not really looked into how one would do that with R (doing it in Python seems more clear), but am checking it out now. If anyone else is looking I'm finding this PDF helpful: http://biostat.mc.vanderbilt.edu/wiki/pub/Main/SvetlanaEdenRFiles/regExprTalk.pdf http://biostat.mc.vanderbilt.edu/wiki/pub/Main/SvetlanaEdenR...
- jdminhbg 14y agoI think the point is less "can this be done in X minutes?" and more "is there an easier way to do this?" This, at least, is what frustrates me about people who are Excel fanatics but refuse to learn a couple days' worth of Python, Ruby, VBA, whatever. Nested conditionals might be easy, but a case statement is vastly easier to write and understand. There are lots of people who would say "oh I could never program" and then write Excel functions too complex for me to understand.
- Groxx 14y agoThere are limitations to CSV? As long as it's properly escaped, it works as well as any other I'm aware of. What would you suggest, XML?