FrankBaris.com

My friends Jeff & Marcus opened a retail store in Fort Collins called The Mineral Cache. I created the company website, MineralCache.com, and installed a digital security system that I built. I hope to have a marketable system after this project. Same thing happened with my Phone Log/SMDR system.

Well… I’m registered and working on the Netflix Prize. If you haven’t heard, Netflix is running a contest for all us geeks in the amount of $1MIL!! Yeah, a million. All you have to do is beat their recommendation system ‘CineMatch’ by 10%… sounds easy, right?? Well, all you get is 200 million old ratings(1-5), given to a set of movies, from a set of customers. Now, all I need to do is figure out how to “predict” what a customer will rate a movie in the future, based on the given data. Sounds easy enough. :-p
All I know is… if I win this contest… no more Quantum.

I thought this might come in handy while working with large sets of data as I do. It’s a simple shell script that takes a text file as an argument and returns each unique ‘word’ and the total occurences of that word(‘frequency’) on individual lines.
Remember to chmod +x or it won’t run.


[pound][bang][slash]bin[slash]bash
# wf: Crude word frequency analysis on a text file.
# Check for input file on command line.
ARGS=1
E_BADARGS=65
E_NOFILE=66
if [ $# -ne "$ARGS" ]
# Correct number of arguments passed to script?
then
echo "Usage: `basename $0` filename"
exit $E_BADARGS
fi
if [ ! -f "$1" ] # Check if file exists.
then
echo "File \"$1\" does not exist."
exit $E_NOFILE
fi
cat "$1" | xargs -n1 | \
# List the file, one word per line.
tr A-Z a-z | \
# Shift characters to lowercase.
sed -e 's/\.//g' -e 's/\,//g' -e 's/ /\
/g' | \
# Filter out periods and commas, and
#+ change space between words to linefeed,
sort | uniq -c | sort -nr
# Finally prefix occurrence count and sort numerically.
exit 0


Here is a VBA macro that will remove all the newline characters from an Excel file. They look like little squares [] or they add an Enter to a cell. I suggest saving this macro in your PERSONAL.xls file so it is available in all your Excel files. Then just run the macro and it will fix your file. This often happens when transfering data from Unix based systems to Windows based systems. The newline character is sometimes refered by ‘\n‘ or ‘\r‘ or even ‘\n\r‘. The trick is Excel refers to it by ‘vbNewLine‘, thats why your Find & Replace doesn’t work.

Sub RemoveNL()
'
' RemoveNL Macro shared by FrankBaris.com
'
s = vbNewLine
r = " "
Cells.Replace What:=s, Replacement:=r
'
End Sub

Hope this comes in handy!

Finding duplicates in a set of data can be pretty daunting at some points. I thought I’d share some of my knowledge, since I work with large sets of data everyday, and finding duplicates is something I have had to figure out.

First, let me show you how to find duplicate rows in a MySQL table. It’s actually quite easy. Just throw this statement in, replace t with the name of the table your looking in, and col with the field your looking for dupes in.

SELECT col, count(*)
FROM t
GROUP BY col,
HAVING count(*) > 1

Now, let me give you a VBA macro that you can use in Excel to find duplicate data. This macro is a good one to have saved in your PERSONAL.xls file, so that it is available in all your Excel files. You need to first sort the column, then select just the block of data you want to look for dupes in, and then run this macro… it will replace all the dupes (except the first one) with —. Then you can re-sort all your data by that column, and all the duplicate rows will be grouped together where the data was replaced with —.

Sub DeDupe()
'
' DeDupe Macro shared by FrankBaris.com
'
Dim RowNdx As Long
Dim ColNum As Integer
ColNum = Selection(1).Column
For RowNdx = Selection(Selection.Cells.Count).Row To _
Selection(1).Row + 1 Step -1
If Cells(RowNdx, ColNum).Value = Cells(RowNdx - 1, ColNum).Value Then
Cells(RowNdx, ColNum).Value = "----"
End If
Next RowNdx
End Sub

I hope these help some of you out, as I know, trying to figure these out on your own can really hurt ones brain :-0