Digging through Ashley Madison

After the release of the Ashley Madison dumps many of us downloaded them and searched through them. It wasn't very long there after that several web apps came up and allowed you to do it yourself, but most of those apps censor the entirety of the data for legal reasons. If you wanted to go through the dumps I can show you how in a few steps:

Get the torrent it is available on the pirate bay.

Once you have the link you will want to put the sql into a mysql database, you must have mysql or mariadb installed. To do so is a very simple step it will be something like:

If you don't know SQL it's probably a good idea to learn you some. I think most people actually go with mariadb these days.

# mysql -p am < *.dump

YMMV

After that you can fire up sql:

mysql

At the sql prompt you will then have to select the db:

use am;

Then you will have to construct a search, for example:

select * from am_am_member where city="Nashville" and gender="2" LIMIT 4;

This will select 4 males in Nashville, the gender for a woman, is "1".

It does appear that across all tables id, refers to the same people. So id 102 is id 102 throughout.


With the csv's one of the things I wanted to do is get unique email addresses, that represented people who were tech savvy enough to own their own domains so I wanted to filter out anything like gmail/yahoo/aol etc, but I left in comcast and clearwire just because they might be using their real names as their email addresses which struck me as interesting.


To do that you just navigate to the csv directory and then:

awk -F, '{print $21}' *.csv | grep -vi -P "(gmail.com|hotmail.com|aol.com|ymail.com|yahoo.com|live.com|\"US\"|\"P\"|\"TW\"|\"\"|\n)" | grep -i -P "(com|org|net|ninja|gov|edu)"

As you can see the 21st field is the email field in the Credit Card transaction csvs. I don't know why the country codes are getting in there I suspect my awk delimiter isn't quit right but I obtained a dirth of information with that. Approximately 134,949 unique email addresses, I almost reported that above a million because of all the recurring ones.

It might be interesting and certainly wouldn't require a lot of scripting to just sort by total amount spent on the site.


I certainly wouldn't count this guy out:

531 "ALN3269@AOL.COM", "79.00"


531 individual charges of 79 dollars.

That's $41,949. That's a lot of money to cheat on your spouse. Seems unnecessary. Remember my list doesn't include gmail accounts so this guy probably actually scores a bit farther down on the list.



I'm just doing some uniq -c sorting, so it's possible that email entity spent more money in other amounts, but like I said, I wouldn't count them out as a contender.

Cutting Up DraftKing's csv, with awk and sed.

On Outsider Math I've published a few articles about using math and the R programming language to pick line ups. Now I think I am going to break down the how, of using gnu tools to cut up a DraftKings nfl and nhl csv. These methods will work on mac as well, and if you need to do it on windows you can install cygwin to get it working. Here is a one liner for the nfl, you would just type in your terminal window on linux or mac, and in your cygwin window on windows:

awk -F "\"*,\"*" '{print $1,"," $2,"," $3,"," $5}' DKSalaries.csv | sed "s/\"QB /QB/g" | sed "s/\"RB /RB/g" | sed "s/\"WR /WR/g" | sed "s/\"TE /TE/g" | sed "s/\"DST /DST/g" > nflredux

I do end up manually editing the header after I'm all done.Ok so let's start with the first part of the awk:

awk -F "\"*,\"*"

This says, I want to use a comma(,) as the delimiter, but it also says I want to take the quotes out as I go. That being said it will not take out the first quote in the csv.

{print $1,"," $2,"," $3,"," $5}' DKSalaries.csv

This says I want to cut out the fields in the order of first field, second field, third, and fifth. With a space and then a comma.
The next bit:

| sed "s/\"QB /QB/g"

Turn quote QB space or '"QB ' into QB. Same thing with RB, WR, TE, and DST.The ">nflredux"is saying output to nflredux.
Your output will be like this (like I said I usually end up manually editing the header):

"Position ,Name ,Salary ,AvgPointsPerGame
WR,Antonio Brown ,9300 ,25.167
WR,Odell Beckham Jr. ,9000 ,23.779
WR,Julio Jones ,8500 ,25.080
WR,DeAndre Hopkins ,8400 ,22.213
WR,Brandon Marshall ,7900 ,22.773
WR,Allen Robinson ,7800 ,20.213
RB,Devonta Freeman ,7600 ,22.429
QB,Cam Newton ,7500 ,25.491
WR,A.J. Green ,7500 ,18.220

So for the nfl it's pretty much the same deal, and here's the example for you:

awk -F "\"*,\"*" '{print $1,"," $2,"," $3,"," $5}' DKSalaries.csv | sed "s/\"G /G/g" | sed "s/\"LW /W/g" | sed "s/\"C /C/" | sed "s/\"RW /W/g" | sed "s/\"D /D/" > dknhl

If you need more help, look no further than here.