<repo_name>giss-ignacio/imdbdownloader<file_sep>/README.md
# imdbdownloader
Download movies database to csv for further data science analysis.

## Movies database processing:
<p align="center">
<a href="http://www.youtube.com/watch?feature=player_embedded&v=lsX6UijPygM
" target="_blank"><img src="http://img.youtube.com/vi/lsX6UijPygM/0.jpg" 
alt="Movies database processing " width="240" height="180" border="10" /></a></p>

## Movies database conversion to csv:
<p align="center">
<a href="http://www.youtube.com/watch?feature=player_embedded&v=AiNXFbdqpHI
" target="_blank"><img src="http://img.youtube.com/vi/AiNXFbdqpHI/0.jpg" 
alt="Movies database csv" width="240" height="180" border="10" /></a></p>





![alt tag](https://s4.postimg.org/kil22muyl/8257_10208296303390974_3322366975959709238_n.jpg)<file_sep>/download.rb
require 'net/ftp'
require 'rubygems/package'
require 'zlib'

FTP_SITE = "ftp.fu-berlin.de"
FTP_DIR = "/pub/misc/movies/database"
DLD_DIR = "data"

def download_files
	ftp = Net::FTP::new(FTP_SITE)	
	ftp.passive = true
	ftp.login("ftp", "guest")
		
	
	ftp.chdir(FTP_DIR)
	fileList = ftp.list('*.gz')	
	fileList.each do |file|
		filename = file.split.last
		puts "Downloading " + filename		

		filesize = ftp.size(filename)		
		filesizemb = filesize/1048576		
		puts "File size: #{filesizemb} MB"
		progressbar = ProgressBar.create
		progressbar.total = filesize
		
		ftp.getbinaryfile(filename, "#{DLD_DIR}/#{filename}", 1024) { |data|
		progressbar.progress += data.size		
		}		
	end
	ftp.close
end

def extract_files
	Dir.glob("#{DLD_DIR}/*.gz") do |gz_file|
		Zlib::GzipReader.open(gz_file) do | input_stream |
			puts "Extracting #{gz_file} "
			File.open(gz_file.chomp('.gz'), "w") do |output_stream|
				IO.copy_stream(input_stream, output_stream)
			end
		end	
	end	
end

def download_and_extract
	download_files
	extract_files
end<file_sep>/db_handle.rb
require 'sqlite3'

DBNAME = "im_db.sqlite"

def create_db
	File.delete(DBNAME) if File.exists?DBNAME

	db = SQLite3::Database.new( DBNAME )
	db.execute("CREATE TABLE Movies (
				id INTEGER PRIMARY KEY,
				title varchar(250),
				year integer,
				budgeting integer,
				running_times integer,
				ratings float,
				votes integer,
				rating_votes varchar(10),
				mpaa_ratings varchar(5),
				is_series numeric
				)")
				
				
	db.execute("CREATE TABLE Ratings (id INTEGER PRIMARY KEY, movie_id integer, score varchar(10), outof10 float, votes integer)")
	db.execute("CREATE TABLE Genres (id INTEGER PRIMARY KEY , movie_id integer, genre varchar(50))")

	db.execute("CREATE INDEX title on Movies (title)")
	db.execute("CREATE INDEX year on Movies (year)")
	db.execute("CREATE INDEX titleyear on Movies (title, year)")
	db.execute("CREATE INDEX id on Movies (id)")
	db.execute("CREATE INDEX rid on Ratings (id)")
	db.execute("CREATE INDEX rmid on Ratings (movie_id)")
	db.execute("CREATE INDEX gid on Genres (id)")
	db.execute("CREATE INDEX gmid on Genres (movie_id)")
	
	return db

end

<file_sep>/db_to_csv.rb
require "rubygems"
require "arrayfields"
require "sqlite3"
require "set"
require "ruby-progressbar"

DB_CSV_NAME = "im_db.csv"

$genres_nm = ["Action", "Adventure", "Animation", "Comedy", "Drama", "Documentary", "Horror", "Mystery", "Romance", "Short", "Thriller", "Sci-Fi"]
$maprat = {"." => 0, "0" => 4.5, "1" => 14.5, "2" => 24.5, "3" => 34.5, "4" => 44.5, "5" => 45.5, "6" => 64.5, "7" => 74.5, "8" => 84.5, "9" => 94.5, "*" => 100}


def ratings_numeric(ratings)
	ratings[0..ratings.length].to_s.split(//).map{|s| $maprat[s]}
end

def set_genres(id, data)	
	genres = data.execute("SELECT genre FROM Genres where movie_id = #{id};").flatten.to_set
	$genres_nm.map { |genre| (genres.include? genre) ? 1 : 0}
end



def db_to_csv(data)
	db_com = "SELECT Movies.* 
	FROM Movies
	ORDER BY title"
	
	db_com_count = "SELECT COUNT(*) FROM Movies;"
	tot_count  = data.execute(db_com_count)
	total_fl = tot_count.first.first	
	progressbar = ProgressBar.create
	progressbar.total = total_fl
	
	File.open("im_db.csv", "w") do |out|
		out << [
			'title', 'year', 'length', 'budget', 
			'rating', 'votes', (1..10).map{|i| "r" + i.to_s}, 
			'mpaa', $genres_nm , 'is_series'
		].flatten.join(",") + "\n"
		data.execute(db_com) do |row| 
			out << [
				row[1], 
				row[2], 
				row[4], 
				row[3], 
				row[5], row[6], ratings_numeric(row[7]), 
				row[8], set_genres(row[0], data), row[9]
			].flatten.join(",") + "\n" rescue nil
			
			progressbar.increment
			
		end
	end
	
	tot_count = IO.readlines( DB_CSV_NAME ).size  - 1
	puts "Total csv entries: #{tot_count} "
	
end<file_sep>/main.rb
require 'rubygems'
require "highline/import"
require_relative 'download'
require_relative 'db_handle'
require_relative 'db_to_csv'

MOVIES = "movies"
RUNNING_TIMES = "running-times"
BUDGETING = "budgeting"
TITLE = "title"
HYPHENS = /(-{79})/i
MPAA_RAT = "mpaa-ratings-reasons"
RATINGS = "ratings"
GENRES = "genres"




def regex_form(category)
	reg_form = /(.*?)/i
	case category
	when MOVIES
		reg_form = /^(.+) \s+ \([0-9]+\) \s? (\{.+\})? (\(.+\))? \s+ ([0-9]+(-[0-9\?]+)?)$/ix
	when TITLE
		reg_form = /MV:\s+(.+)? \s \(([0-9]+)\)/ix
	when RUNNING_TIMES
		reg_form = /^(.+) \s+ \(([0-9]+)\) \s+ (?:[a-z]+:)?([0-9]+)/ix
	when BUDGETING
		reg_form = /BT:\s+USD\s+([0-9,.]+)/ix
	when MPAA_RAT
		reg_form = /RE: Rated (.*?) /i
	when RATINGS
		reg_form = /([0-9.\*]+) \s+ ([0-9]+) \s+ ([0-9.]+) \s+ (.+)? \s+ \(([0-9]+)\)/ix
	when GENRES
		reg_form = /^(.+)? \s+ \(([0-9]+)\) (?:\s*[({].*[})])*  \s+(.*?)$/ix	
	end
	
	return reg_form

end

def add_movies(data)	
	movies_reg = regex_form(MOVIES)
		
	match_prev = ['', '', '', '', '']
	series = ""

	datacom = data.prepare("INSERT INTO Movies (title, year, is_series) VALUES (?, ?, ?);")
	i = 0
	data.transaction do
		data.execute "DELETE FROM Movies;"
		
		filename = "#{DLD_DIR}/#{MOVIES}.list"
		tot_count = IO.readlines(filename).size  
		progressbar = ProgressBar.create
		progressbar.total = tot_count
		
	
		File.open(filename, "r:Windows-1252").each_line do |l|
			progressbar.increment
			if match = movies_reg.match(l)			
				unless match[match.length - 1].nil?
				series = match[1]
				datacom.execute!(match[1].tr(',".',''), match[match.length - 2][0..3].to_i, 1)			
				else 
				if ((match[1] != match_prev[1]) || (match[match.length - 2] != match_prev[match_prev.length - 2])) && (match[1] != series)
					datacom.execute!(match[1].tr(',".',''), match[match.length - 2][0..3].to_i, 0)
				end
				end
			end
		end	
	end
		
end

def add_running_times(data)
	running_times_reg = regex_form(RUNNING_TIMES)	

	datacom = data.prepare("UPDATE Movies set running_times=? WHERE title=? AND year=?;")
	i = 0
  data.transaction do 
		filename = "#{DLD_DIR}/#{RUNNING_TIMES}.list"
		tot_count = IO.readlines(filename).size  
		progressbar = ProgressBar.create
		progressbar.total = tot_count
		
		File.open(filename, "r:Windows-1252").each_line do |l|
			progressbar.increment	
			if match = running_times_reg.match(l)
				datacom.execute!(match[3].to_i, match[1].tr(',".',''), match[2].to_i)
			end
		end	
  end
		
end


def add_budgeting(data)
	budgeting_reg = regex_form(BUDGETING)
	title_reg = regex_form(TITLE)	
	
	datacom = data.prepare("UPDATE Movies set budgeting=? WHERE title=? AND year=?;")
	filename = "#{DLD_DIR}/business.list"
	business = File.open(filename, "r:Windows-1252").read.split(HYPHENS)
	tot_count = business.map.size  
	progressbar = ProgressBar.create
	progressbar.total = tot_count
		
	data.transaction do 		
		business.map do |l|			
			progressbar.increment
			if match = title_reg.match(l.to_s) and bt = budgeting_reg.match(l.to_s)
				datacom.execute!(bt[1].gsub!(",","").to_i, match[1].tr(',".',''), match[2].to_i) 
			end
		end
	end	
end

def add_mpaa_ratings_reasons(data)	
	mpaa_reg = regex_form(MPAA_RAT)
	title_reg = regex_form(TITLE)

	datacom = data.prepare("UPDATE Movies set mpaa_ratings=? WHERE title=? AND year=?;")
	filename = "#{DLD_DIR}/#{MPAA_RAT}.list"
	
	mpaa_ratings = File.open(filename, "r:Windows-1252").read.split(HYPHENS)	
	
	tot_count = mpaa_ratings.map.size  	
		progressbar = ProgressBar.create
		progressbar.total = tot_count
	
	data.transaction do		
		mpaa_ratings.map do |l|
			progressbar.increment
			if match = title_reg.match(l.to_s) and rt = mpaa_reg.match(l.to_s)
				datacom.execute!(rt[1], match[1].tr(',".',''), match[2].to_i)
			end
		end
	end
end

def add_genres(data)	
	genres_reg = regex_form(GENRES)	
	
	datacom = data.prepare("INSERT INTO Genres (genre, movie_id) VALUES (?, (SELECT id FROM Movies WHERE title=? AND year=?));")
	data.transaction do 
		filename = "#{DLD_DIR}/#{GENRES}.list"
		tot_count = IO.readlines(filename).size  
		progressbar = ProgressBar.create
		progressbar.total = tot_count
		
		data.execute "DELETE FROM Genres;"
		
		File.open(filename, "r:Windows-1252").each_line do |l|	
			progressbar.increment
			if match = genres_reg.match(l)				
				datacom.execute!(match[3], match[1].tr(',".',''), match[2].to_i)
			end
		end
		puts
	end
end

def add_ratings(data)	
	ratings_reg = regex_form(RATINGS)

	datacom = data.prepare("UPDATE Movies set votes=?, ratings=?, rating_votes=? WHERE title=? AND year=?;")
	data.transaction
		filename = "#{DLD_DIR}/#{RATINGS}.list"
		tot_count = IO.readlines(filename).size  
		progressbar = ProgressBar.create
		progressbar.total = tot_count
	File.open(filename, "r:Windows-1252").each_line do |l|
			progressbar.increment
		if match = ratings_reg.match(l)
			rating, votes, outof10, title, year = match[1], match[2], match[3], match[4], match[5]
			datacom.execute!(votes, outof10, rating, title.tr(',".',''), year)
		end
	end
	data.commit
end

def add_all
	data = create_db
	
	puts "Processing movies database"
	
	puts "Adding movies"
	add_movies(data)
	
	puts "Adding running times"
	add_running_times(data)
	
	puts "Adding budgeting"
	add_budgeting(data)
	
	puts "Adding mpaa ratings reasons"
	add_mpaa_ratings_reasons(data)
	
	puts "Adding ratings"
	add_ratings(data)
	
	puts "Adding genres"
	add_genres(data)
end

def database_to_csv
	database = SQLite3::Database.new( DBNAME )
	db_to_csv(database)	
end

def do_everything
	download_and_extract
	add_all
	database_to_csv
end


def show_menu
	Gem.win_platform? ? (system "cls") : (system "clear")
	puts
	loop do
		choose do |menu|
			puts "\n\nImdbDownloader \n\n"
			menu.prompt = "Select an option: "
			menu.choice(:'Do everything') { do_everything }
			menu.choice(:'Download files') { download_and_extract }
			menu.choice(:'Add all movies to database') { add_all }
			menu.choice(:'Export database to csv') { database_to_csv }
			menu.choice(:Quit, "Exit program.") { exit }
		end
	end
end


if __FILE__ == $0
	
	# Main Menu #
	show_menu	
	#data = SQLite3::Database.new( DBNAME )
	
	
end

