<jupyter_start><jupyter_text>Asks for an user input<jupyter_code>input("Enter your name:")<jupyter_output>Enter your name:apple<jupyter_text>Class of the object<jupyter_code>x= "hello world"
# type gives you the class of the object
type(x)<jupyter_output><empty_output><jupyter_text>methods for a particular data type<jupyter_code># gets the method of an object; in this case the object is a string. the entries starting from capitalize are all methods
# that can be applied to te object string 
dir(x)<jupyter_output><empty_output><jupyter_text>String Functions<jupyter_code># string functions 

# finds a particular character/characters and returns the position of the character
fruit = 'banana'
fnd = fruit.find('na')
print(fnd)

# upper and lower case 
print(fruit.upper)
print(fruit.lower)

#replace characters
print(fruit.replace('a',0))

# strip whitespaces; loads of whitespaces before and after the string
fruit = '   banana         '
print(fruit.lstrip) # strips the whitespaces to the left of the string
print(fruit.rstrip) # strips the whitespaces to the right of the string
print(fruit.strip) # strips all the whitespaces


# prefix of a string
sentence = 'Please do this for me asshole!'
print(sentence.startswith('Please')) # this will return true as the sentence does start with "Please"
print(sentence.startswith('p')) # this will return false as the line doesn't start with lower case p

# extract email address only 
data = "<EMAIL> June 29/07/18 16:22"
x = data.find('@')# finds the @ postion in data and stores it in x
y= data.find(' ',x) #finds the first space after @ 
host= data[x+1:y]
print(host)<jupyter_output><empty_output><jupyter_text>Finds the position of a particular string<jupyter_code>data = "<EMAIL> June 29/07/18 16:22"
x = data.find('@')# finds the @ postion in data and stores it in x
y= data.find(' ',x) #finds the first space after @ 
host= data[x+1:y]
print(host)
# newline character
t = 'Hello\nWorld' # \n is used for newline/ next line 
print(t)<jupyter_output>Hello
World<jupyter_text>Read a File<jupyter_code># read a file
df = open('D:/Coursera/Python/code3/mbox-short.txt') # opens a file
dff = df.read() # reads the entire file
dff<jupyter_output><empty_output><jupyter_text>Exercises<jupyter_code>##### read a file that the user wants; for this code to run change the working directory to where the file is
# use mbox-short.txt as the input file
import os
os.chdir(r'D:\Coursera\Python\code3')
os.getcwd()
fname = input('Enter the name of the file: ')
try:
    fhand = open(fname)
except:
    print('File cannot be opened',fname)
    quit()
count = 0 
for line in fhand:
    if line.startswith('Subject:'):
        count = count + 1
print('There were', count, 'subject lines in', fname)
# reads a file, converts the contents of the file to uppercase and then strips all the additional newlines
# Use words.txt as the file name
import os
os.chdir(r'D:\Coursera\Python\code3')
os.getcwd()
fname = input("Enter file name: ")
fh = open(fname)
f= fh.read()
g= f.upper()
h = g.rstrip()
print(h)<jupyter_output>Enter file name: mbox-short.txt
FROM <EMAIL> SAT JAN  5 09:14:16 2008
RETURN-PATH: <<EMAIL>>
RECEIVED: FROM MURDER (MAIL.UMICH.EDU [172.16.31.10])
	 BY FRANKENSTEIN.MAIL.UMICH.EDU (CYRUS V2.3.8) WITH LMTPA;
	 SAT, 05 JAN 2008 09:14:16 -0500
X-SIEVE: CMU SIEVE 2.3
RECEIVED: FROM MURDER ([UNIX SOCKET])
	 BY MAIL.UMICH.EDU (CYRUS V2.2.12) WITH LMTPA;
	 SAT, 05 JAN 2008 09:14:16 -0500
RECEIVED: FROM HOLES.MR.ITD.UMICH.EDU (HOLES.MR.ITD.UMICH.EDU [141.211.14.79])
	BY FLAWLESS.MAIL.UMICH.EDU () WITH ESMTP ID M05EEFR1013674;
	SAT, 5 JAN 2008 09:14:15 -0500
RECEIVED: FROM PAPLOO.UHI.AC.UK (APP1.PROD.COLLAB.UHI.AC.UK [192.168.3.11])
	BY HOLES.MR.ITD.UMICH.EDU ID 477F90B0.2DB2F.12494 ; 
	 5 JAN 2008 09:14:10 -0500
RECEIVED: FROM PAPLOO.UHI.AC.UK (LOCALHOST [127.0.0.1])
	BY PAPLOO.UHI.AC.UK (POSTFIX) WITH ESMTP ID 5F919BC2F2;
	SAT,  5 JAN 2008 14:10:05 +0000 (GMT)
MESSAGE-ID: <<EMAIL>>
MIME-VERSION: 1.0
C[...]<jupyter_text>Get current working directory<jupyter_code>pwd
import os
os.chdir(r'D:\Coursera\Python\code3')
os.getcwd()
# read the contents of a file and extract any integers in a line which starts with X-DSPAM-Confidence:
# return the average of all the integers in a line, which start with X-DSPAM-Confidence:
# Use the file name mbox-short.txt as the file name
import numpy as np
fname = input("Enter file name: ")
if fname is not 'mbox-short.txt': # fail safe for any other file other than mbox-short.txt
    fname = 'mbox-short.txt'
fh = open(fname)
s = list()
for line in fh:
    if line.startswith("X-DSPAM-Confidence:") : 
        p = line.find('0')
        q = line.find(' ',p)
        r = float(line[p:q])
        s.append(r)
        a = np.mean(s)     
    continue
#    print(line)
print("Average spam confidence:", a)

# same thing without numpy and without using sum 
# Use the file name mbox-short.txt as the file name
fname = input("Enter file name: ")
if fname is not 'mbox-short.txt': # fail safe for any other file other than mbox-short.txt
    fname = 'mbox-short.txt'
fh = open(fname)
s = 0
count = 0
for line in fh:
    if line.startswith("X-DSPAM-Confidence:") : 
        p = line.find('0')
        q = line.find(' ',p)
        r = float(line[p:q])
        s = s + r
        count = count + 1
        a = s / count     
    continue
#    print(line)
print("Average spam confidence:", a)
# Same code as above with regex
import re
import numpy as np
fname = input("Enter file name: ")
if fname is not 'mbox-short.txt': # fail safe for any other file other than mbox-short.txt
    fname = 'mbox-short.txt'
fh = open(fname)
s = list()
for line in fh:
    line = line.rstrip()
    stuff = re.findall('X-DSPAM-Confidence: ([0-9.]+)',line)
    if len(stuff) is not 1 : 
        continue
    r = float(stuff[0])
    s.append(r)
    a = np.mean(s)     
    
#    print(line)
print("Average spam confidence:", a)
# split string, splits a sentence or paragraph and returns a list with all the separate strings
# the default delimter is a white space; it considers all white spaces between two strings as one 
abc= 'With three words'
print(abc.split())

# the same function with a different delimiter(;); NOTE: it also picks up the whitespace if any after the delimiter
abc= 'With; three; words'
print(abc.split(';'))

x = range(5)
type(x)

'''
 Open the file romeo.txt and read it line by line.
 For each line, split the line into a list of words using the split() method. 
 The program should build a list of words. For each word on each line check to see if 
 the word is already in the list and if not append it to the list. When the program completes, 
 sort and print the resulting words in alphabetical order.
 '''
# Make sure that you select the working directory in which the file is
fname = input('Enter the file name: ')
if fname is not 'romeo.txt': # fail safe for any other file other than romeo.txt
    fname = 'romeo.txt'
fhand = open(fname)
words = list()
for line in fhand: # take out all the lines and split them
    a=line.split() 
    for i in a: # choose each word from each line 
        if i in words: 
            continue # skip the word if it's already in the list
        words.append(i)
    words.sort()
print(words)
print(len(words))
'''
Open the file mbox-short.txt and read it line by line. When you find a line that starts with 'From ' like the following line:
From <EMAIL> Sat Jan  5 09:14:16 2008
You will parse the From line using split() and print out the second word in the line 
(i.e. the entire address of the person who sent the message). 
Then print out a count at the end.
'''
# Make sure that you select the working directory in which the file is
fname = input('Enter the file name: ')
if fname is not 'mbox-short.txt': # fail safe for any other file other than mbox-short.txt
    fname = 'mbox-short.txt'
fhand = open(fname)
count = 0
for line in fhand:
    if not line.startswith('From:'): 
        continue
    words = line.split()
    count = count + 1
    print(words[1])
print("There were", count, "lines in the file with From as the first word")

    


# Same exercise as above but keeping only unique email ids; this code takes into account any line starting 
# with "From" and not just "From:" like the one above. 

'''
Open the file mbox-short.txt and read it line by line. When you find a line that starts with 'From ' like the following line:
From <EMAIL> Sat Jan  5 09:14:16 2008
You will parse the From line using split() and print out the second word in the line 
(i.e. the entire address of the person who sent the message). 
Then print out a count at the end.
'''
# Make sure that you select the working directory in which the file is
fname = input('Enter the file name: ')
if fname is not 'mbox-short.txt': # fail safe for any other file other than mbox-short.txt
    fname = 'mbox-short.txt'
fhand = open(fname)
count = 0
words = list()
fin_words = list()
for line in fhand:
    if not line.startswith('From'): 
        continue
    wds = line.split()
    words.append(wds[1])
    for i in words:
        if i in fin_words:
            continue
        fin_words.append(i)
    count = count + 1
print(fin_words)
email = len(fin_words)
print("There were", count, "lines in the file with From as the first word and there are",email, "unique email addresses in those lines.")
pwd
help(list)<jupyter_output>Help on class list in module builtins:

class list(object)
 |  list() -> new empty list
 |  list(iterable) -> new list initialized from iterable's items
 |  
 |  Methods defined here:
 |  
 |  __add__(self, value, /)
 |      Return self+value.
 |  
 |  __contains__(self, key, /)
 |      Return key in self.
 |  
 |  __delitem__(self, key, /)
 |      Delete self[key].
 |  
 |  __eq__(self, value, /)
 |      Return self==value.
 |  
 |  __ge__(self, value, /)
 |      Return self>=value.
 |  
 |  __getattribute__(self, name, /)
 |      Return getattr(self, name).
 |  
 |  __getitem__(...)
 |      x.__getitem__(y) <==> x[y]
 |  
 |  __gt__(self, value, /)
 |      Return self>value.
 |  
 |  __iadd__(self, value, /)
 |      Implement self+=value.
 |  
 |  __imul__(self, value, /)
 |      Implement self*=value.
 |  
 |  __init__(self, /, *args, **kwargs)
 |      Initialize self.  See help(type(self)) for accurate signature.
 |  
 |  __iter__(self, /)
 |      Implement iter(self).
 |  
 |  __l[...]<jupyter_text>Dictionaries Have a key and value pair; are mutable; can have duplicate values but NOT duplicate keys<jupyter_code># count the number of names in a dictionary
counts = dict()
names = ['csev','cwen','csev','zqian','csev']
for name in names:
        counts[name]= counts.get(name,0) + 1 # the get method tells the program to add 1 if the name already 
        #exists in the dictionary otherwise it just returns 0 as a default value
print(counts)
# as dictionary is a subset of lists we can take out the keys, values separately and all of them together using a list method
print(list(counts)) # prints only the keys which are the names
print(counts.keys()) # prints only the keys which are the names 
print(counts.values()) # prints only the values which are the numbers corresponding to each name
print(counts.items()) # prints the keys and values as a tuple 
# instead of printing all the keys and values together with a items method we can use a for loop with two iterators 
for keys,values in counts.items():
    print(keys,values) # keys and values are two iterators and going through the dictonary counts

# counting the words in a file; use words.txt
fname = input('Enter the name of a file:')
if fname is not 'words.txt': # fail safe for any other file other than words.txt
    fname = 'words.txt'
fhand = open(fname)
counts = dict()
for line in fhand:
    words = line.split()
    for word in words:
        counts[word] = counts.get(word,0) + 1
bigcount = None
bigword = None
for keys,values in counts.items():
    if bigcount is None or values>bigcount:
        bigcount = values
        bigword = keys
    
print('The most recurring word is:"',bigword,'"and it appears', bigcount, 'times.')
'''
Write a program to read through the mbox-short.txt and figure out who has the sent the greatest number of mail messages. 
The program looks for 'From ' lines and takes the second word of those lines as the person who sent the mail. 
The program creates a Python dictionary that maps the sender's mail address to a count of the number of times
they appear in the file. After the dictionary is produced, the program reads through the dictionary
using a maximum loop to find the most prolific committer.
'''

fname= input('Enter the name of a file: ')
if fname is not 'mbox-short.txt': # fail safe for any other file other than mbox-short.txt
    fname = 'mbox-short.txt'
fhand = open(fname)
counts = dict()
words_email = list()
for line in fhand:
    if not line.startswith('From:'):
        continue
    words = line.split()
    words_email.append(words[1])
    
for word in words_email:
    counts[word] = counts.get(word,0) + 1
    
email = None
num_email = None
for keys,values in counts.items():
    if num_email is None or  values > num_email:
        email = keys
        num_email = values
print(email,num_email)<jupyter_output>Enter the name of a file: words.txt
<EMAIL> 5<jupyter_text>Tuples Tuples are immutable just like strings<jupyter_code># working with tuples; usually a dictionary is sorted according to the keys 
# so if the keys are strings then the dictionary is sorted according to the keys and not the integers(values) corresponding 
# to each key
print('sorting according to the keys')
c = {'a':10, 'b':1,'c':22}
print(sorted(c.items()))
print('the same thing but with a for loop')
# same thing with a for loop
for keys,values in sorted(c.items()):
    print(keys,values)


# sorting according to the values
print('sorting according to the values')
c = {'a':10, 'b':1,'c':22}
tmp=list()
for keys,values in c.items():
    tmp.append((values,keys))
    
print('printing the keys and values reversed',tmp)
tmp= sorted(tmp,reverse=True)
print(tmp)
# same exercise for a file called romeo.txt

# Make sure that you select the working directory in which the file is
fname = input('Enter the file name: ')
if fname is not 'romeo.txt': # fail safe for any other file other than romeo.txt
    fname = 'romeo.txt'
fhand = open(fname)
#words = list()
tmp = list()
counts = dict()
for line in fhand: # take out all the lines and split them
    a=line.split() 
    for i in a: # choose each word from each line 
        #words.append(i)
        counts[i]= counts.get(i,0) + 1
for keys,values in counts.items():
    tmp.append((values,keys))
tmp = sorted(tmp,reverse=True)

for values,keys in tmp[:10]:
    print(keys,values)<jupyter_output>Enter the file name: 
the 3
is 3
and 3
sun 2
yonder 1
with 1
window 1
what 1
through 1
soft 1<jupyter_text>List Comprehension i.e. creates a dynamic list.  In this example we are going to make a list of reversed tuples and then sort it The code where we reverse the keys & values of a dictionary to sort a dictionary according to values is done with ONE line of code Look at this elegant piece of code<jupyter_code># Make sure that you select the working directory in which the file is
fname = input('Enter the file name: ')
if fname is not 'romeo.txt': # fail safe for any other file other than romeo.txt
    fname = 'romeo.txt'
fhand = open(fname)
counts = dict()
for line in fhand: # take out all the lines and split them
    a=line.split() 
    for i in a: # choose each word from each line 
        counts[i]= counts.get(i,0) + 1


''' 
for keys,values in counts.items():
    tmp.append((values,keys))
tmp = sorted(tmp,reverse=True)
'''

# This is the one line of code which compresses the above code into one line
tmp= list(sorted([(values,keys) for keys,values in counts.items()],reverse=True))

# printing only top 10 values
for values,keys in tmp[:10]:
    print(keys,values)


'''
Write a program to read through the mbox-short.txt and figure out the distribution by hour of the day for each of the messages. 
You can pull the hour out from the 'From ' line by finding the time and then splitting the string a second time using a colon.
'''
# sorted by keys
fname= input('Enter the name of a file: ')
if fname is not 'mbox-short.txt': # fail safe for any other file other than mbox-short.txt
    fname = 'mbox-short.txt'
fhand = open(fname)
hours_email = list()
counts= dict()
for line in fhand:
    if not line.startswith('From'):
        continue
    x= line.find('@')
    #print('x',x)
    z = line.find(':',x)
    if z < 0 :
        continue
    #print('z',z)
    y= line[z-2:z]
    #print('y',y)
    hours_email.append(y)
for hours in hours_email:
    counts[hours] = counts.get(hours,0) +1
#tmp = list(sorted([(values,keys) for keys,values in counts.items()],reverse=True))
for keys,values in sorted(counts.items()):
    print(keys,values)
'''
Write a program to read through the mbox-short.txt and figure out the distribution by hour of the day for each of the messages. 
You can pull the hour out from the 'From ' line by finding the time and then splitting the string a second time using a colon.
'''
# sorted by values
fname= input('Enter the name of a file: ')
if fname is not 'mbox-short.txt': # fail safe for any other file other than mbox-short.txt
    fname = 'mbox-short.txt'
fhand = open(fname)
hours_email = list()
counts= dict()
for line in fhand:
    if not line.startswith('From'):
        continue
    x= line.find('@')
    #print('x',x)
    z = line.find(':',x)
    if z < 0 :
        continue
    #print('z',z)
    y= line[z-2:z]
    #print('y',y)
    hours_email.append(y)
for hours in hours_email:
    counts[hours] = counts.get(hours,0) +1
tmp = list(sorted([(values,keys) for keys,values in counts.items()],reverse=True))
for values,keys in tmp:
    print(keys,values)<jupyter_output>Enter the name of a file: 
11 6
16 4
10 3
04 3
17 2
15 2
09 2
19 1
18 1
14 1
07 1
06 1<jupyter_text>REGEX ^ a.ka. caret character says that it should start with eg. '^X' means that it should start with 'X' '.' represents any character '*' any number of times; this is a greedy approach i.e. it will return the biggest string that matches the regex '\S' any non-whitespace character '+' one or more times; this is a greedy approach i.e. it will return the biggest string that matches the regex ' ?' this is the non-greedy approach stating that take the smallest string that matches the regex '(' or ')' aren't part of the matching regex but tells from where to start and stop the extraction of the string that matches the regex '[]' represents non-blank character; same as '\S' '\' escape characters; when you want to include special characters which are part of the regex already eg. '\+'         Eg. '^X.*:' this reads as the string that we want to match should START WITH 'X' and then it can have any other characters after it and any number of times and should END WITH ':' .      Eg. X-DSPAM-Confidence: will return TRUE         Eg. '^X-\S+:' this reads as the string that we want to match should START WITH 'X' FOLLOWED BY '-' and SHOULD HAVE     ONE OR MORE NON-WHITESPACE(no spaces), which is represented by '\S+' and SHOULD END WITH ':'         Eg. X-Plane is behind schedule: will return FALSE because there is a space in between the words     X-DSPAM-Confidence: will return TRUE because there is no space in between the words and this string satisfies all conditions         Eg. '^X-.+:' this is the greedy approach so it will take the biggest string     Eg. '^X-.+?:' this is the non-greedy approach so it will take the shortest string; another way to write this is with a    whitespace Eg. '^X-\S+:' re.search() returns a TRUE/FALSE depending on whether the string matches the regex re.findall() matches the regex and extracts the string that matches with the regex Short Glossary     ^	    Matches the beginning of a line    $	    Matches the end of the line    .	    Matches any character; wild card expression    \s	    Matches whitespace    \S	    Matches any non-whitespace character    *	    Repeats a character zero or more times    *?	    Repeats a character zero or more times (non-greedy)    +	    Repeats a character one or more times    +?	    Repeats a character one or more times (non-greedy)[aeiou]	    Matches a single character in the listed set[^XYZ]	    Matches a single character not in the listed set[a-z0-9]	The set of characters can include a range    (	    Indicates where string extraction is to start    )	    Indicates where string extraction is to end For more information https://docs.python.org/3/howto/regex.html<jupyter_code># Extracting strings using regex
import re
x = 'My 2 favourite numbers are 19 and 42.'
y = re.search('[0-9]+',x) # it looks for one or more digits and returns TRUE/FALSE  
z = re.search('[0-9]*',x) # it looks for digits any number of times and returns TRUE/FALSE
v = re.findall('[0-9]+',x) # it looks for one or more digits and returns the string that matches
w = re.findall('[0-9]*',x) # it looks for digits any number of times and returns the string that matches
'''
the difference between using '+' or '*' is that in the case of the former the program will look through the string and will 
return only the strings that match the regex i.e. ['2', '19', '42']. However, the latter will return n-1 positions of the length 
of the string along with the strings that match the regex i.e. ['', '', '', '2', '', '', '', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '', '', '19', '', '', '', '', '', '42', '', '']

''' 
print('y',y,'\n','z',z,'\n','v',v,'\n','w',w)

# extract only email address
u = 'FROM <EMAIL> SAT JAN  5 09:14:16 2008'
s= re.findall('\S+@\S+',u) # returns only the email address
t = re.findall('^FROM (\S+@\S+)',u) # returns only the email address but here it matches the 'FROM' word also
print('t',t,'\n','s',s)

# extract the host
r = re.findall('@(\S+)',u)
q = re.findall('@([^ ]*)',u) # same thing but '[]' represents non-blank character; notice the space in between the '[]'
# it tells to start the extract with a single non-blank character till it meets the next whitespace
p = re.findall('^FROM .*@([^ ]*)',u) # this says that i want to start with 'FROM' and then i want a whitespace 
# and then any number of characters until i find '@' and i want to begin extracting all the non-blank characters and 
# i want to stop extracting as soon as i reach a space
print(r,'r','\n',q,'q','\n','p',p)
'''
In this assignment you will read through and parse a file with text and numbers.
You will extract all the numbers in the file and compute the sum of the numbers.

Data Format
The file contains much of the text from the introduction of the textbook except that random numbers are inserted 
throughout the text. Here is a sample of the output you might see:

Why should you learn to write programs? 7746
12 1929 8827
Writing programs (or programming) is a very creative 
7 and rewarding activity.  You can write programs for 
many reasons, ranging from making your living to solving
8837 a difficult data analysis problem to having fun to helping 128
someone else solve a problem.  This book assumes that 
everyone needs to know how to program ...


The sum for the sample text above is 27486. The numbers can appear anywhere in the line. 
There can be any number of numbers in each line (including none).

Handling The Data
The basic outline of this problem is to read the file, look for integers using the re.findall(), 
looking for a regular expression of '[0-9]+' and then converting the extracted strings to integers and summing up the integers.
'''
import re
fname = input("Enter file name: ")
if fname is not 'regex_sum_90153.txt': # fail safe for any other file other than regex_sum_90153.txt
    fname = 'regex_sum_90153.txt'
fh = open(fname)
s = list()
t = list()
for line in fh:
    stuff = re.findall('([0-9]+)',line)
    #print(stuff)
    if len(stuff)  < 1:
        continue
    s.extend(stuff)

for i in s:
    r = float(i)
    t.append(r)

          
print('There are a total of:',len(t),'numbers and the sum of all the numbers amounts to:',sum(t),'.')


 i = '''Why should you learn to write programs? 7746
12 1929 8827
Writing programs (or programming) is a very creative 
7 and rewarding activity.  You can write programs for 
many reasons, ranging from making your living to solving
8837 a difficult data analysis problem to having fun to helping 128
someone else solve a problem.  This book assumes that 
everyone needs to know how to program'''
x = re.findall('([0-9]+)',i)
print(x)
t= list()
for j in x:
    y = float(j)
    t.append(y)
sum(t)<jupyter_output>['7746', '12', '1929', '8827', '7', '8837', '128']<jupyter_text>Connecting to the internet and pulling a document<jupyter_code>import socket

mysock = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
mysock.connect(('data.pr4e.org', 80))
cmd = 'GET http://data.pr4e.org/romeo.txt HTTP/1.0\r\n\r\n'.encode()
mysock.send(cmd)

while True:
    data = mysock.recv(512)
    if len(data) < 1:
        break
    print(data.decode(),end='')

mysock.close()
import socket

mysock = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
mysock.connect(('data.pr4e.org', 80))
cmd = 'GET http://data.pr4e.org/intro-short.txt HTTP/1.0\r\n\r\n'.encode()
mysock.send(cmd)

while True:
    data = mysock.recv(512)
    if len(data) < 1:
        break
    print(data.decode(),end='')

mysock.close()<jupyter_output>HTTP/1.1 200 OK
Date: Wed, 08 Aug 2018 12:41:42 GMT
Server: Apache/2.4.18 (Ubuntu)
Last-Modified: Sat, 13 May 2017 11:22:22 GMT
ETag: "1d3-54f6609240717"
Accept-Ranges: bytes
Content-Length: 467
Cache-Control: max-age=0, no-cache, no-store, must-revalidate
Pragma: no-cache
Expires: Wed, 11 Jan 1984 05:00:00 GMT
Connection: close
Content-Type: text/plain

Why should you learn to write programs?

Writing programs (or programming) is a very creative 
and rewarding activity.  You can write programs for 
many reasons, ranging from making your living to solving
a difficult data analysis problem to having fun to helping
someone else solve a problem.  This book assumes that 
everyone needs to know how to program, and that once 
you know how to program you will figure out what you want 
to do with your newfound skills.<jupyter_text>Character Mapping     ASCII a.k.a American Standard Code for Information Interchange    It's a way to map each letter to a number as computers understand only code.         The general standard which is used frequently is ASCII or Unicode (UTF-8). The     speciality about unicode is that all the languages in the world are supported     translating to one computer being able to read messages and translate from another     computer in a different language.         UTF-8 is normally used because it has the capability to store any set of characters either     in 1-4 bytes.         When one is reading anything from the internet there is a necessity to decode the bytes into     strings for reading. This happens by the command bytes.decode(). The default value of such a     decode is UTF-8. When one computer is sending data then one needs to encode the strings using the     command str.encode(). The default value of such a encode is UTF-8.        Note: One can specify what type of encoding/decoding one wants by specifying it in the argument.<jupyter_code># ASCII/Unicode code to number conversion
print(ord('G'))
# number to ASCII or unicode conversion
print(chr(108),chr(105),chr(110),chr(101))

# this is a much simpler way to get the same text instead of using the socket code 
import urllib.request

fhand = urllib.request.urlopen('http://data.pr4e.org/romeo.txt')
for line in fhand:
    print(line.decode().strip()) # decode function is used to convert the bytes into a string
    
# the same code to compute the words
import urllib.request, urllib.parse, urllib.error

fhand = urllib.request.urlopen('http://data.pr4e.org/romeo.txt')

counts = dict()
for line in fhand:
    words = line.decode().split()
    for word in words:
        counts[word] = counts.get(word, 0) + 1
print(counts)

# This piece of code takes out all the links on a website using beautiful soup.

# To run this, you can install BeautifulSoup
# https://pypi.python.org/pypi/beautifulsoup4

# Or download the file
# http://www.py4e.com/code3/bs4.zip
# and unzip it in the same directory as this file

import urllib.request, urllib.parse, urllib.error
from bs4 import BeautifulSoup
import ssl

# Ignore Secure Socket Layer (SSL) certificate errors; this part is required for many web pages otherwise it won't pick up
# bad certificate tags
ctx = ssl.create_default_context()
ctx.check_hostname = False
ctx.verify_mode = ssl.CERT_NONE

url = input('Enter - ') # Enter this website: http://www.dr-chuck.com/
if url is not 'http://www.dr-chuck.com/':
    url = 'http://www.dr-chuck.com/'
html = urllib.request.urlopen(url, context=ctx).read() # here context is calling the ssl certificate fix
soup = BeautifulSoup(html, 'html.parser')

# Retrieve all of the anchor tags
tags = soup('a') # this gives all the anchor tags in the document i.e. whatever is between ""<a> </a>""
for tag in tags:
    print(tag.get('href', None))

# take out only the email-id from an HTML tag
a= '<p>Please click <a href="http://www.dr-chuck.com">here</a></p>'
import re
b = re.findall('href="(.+)"',a); b
'''
Scraping Numbers from HTML using BeautifulSoup In this assignment you will write a Python program similar to
http://www.py4e.com/code3/urllink2.py. The program will use urllib to read the HTML from the data files below, 
and parse the data, extracting numbers and compute the sum of the numbers in the file.

We provide two files for this assignment. One is a sample file where we give you the sum for your testing 
and the other is the actual data you need to process for the assignment.

Sample data: http://py4e-data.dr-chuck.net/comments_42.html (Sum=2553)
Actual data: http://py4e-data.dr-chuck.net/comments_90155.html (Sum ends with 97)
You do not need to save these files to your folder since your program will read the data directly from the URL. 
Note: Each student will have a distinct data url for the assignment - so only use your own data url for analysis.

Data Format
The file is a table of names and comment counts. You can ignore most of the data in the file except for lines like the following:

<tr><td>Modu</td><td><span class="comments">90</span></td></tr>
<tr><td>Kenzie</td><td><span class="comments">88</span></td></tr>
<tr><td>Hubert</td><td><span class="comments">87</span></td></tr>

You are to find all the <span> tags in the file and pull out the numbers from the tag and sum the numbers.
Look at the sample code provided. It shows how to find all of a certain kind of tag, loop through the tags and 
extract the various aspects of the tags.

...
# Retrieve all of the anchor tags
tags = soup('a')
for tag in tags:
   # Look at the parts of a tag
   print 'TAG:',tag
   print 'URL:',tag.get('href', None)
   print 'Contents:',tag.contents[0]
   print 'Attrs:',tag.attrs
You need to adjust this code to look for span tags and pull out the text content of the span tag, 
convert them to integers and add them up to complete the assignment.
'''

import urllib.request, urllib.parse, urllib.error, ssl, re
from bs4 import BeautifulSoup


# Ignore SSL certificate errors; this part is required for many web pages otherwise it won't pick up bad certificate tags
ctx = ssl.create_default_context()
ctx.check_hostname = False
ctx.verify_mode = ssl.CERT_NONE

url = input('Enter URL: ') # Enter this website: http://py4e-data.dr-chuck.net/comments_90155.html
if url is not 'http://py4e-data.dr-chuck.net/comments_90155.html':
    url = 'http://py4e-data.dr-chuck.net/comments_90155.html'
html = urllib.request.urlopen(url, context=ctx).read() # here context is calling the ssl certificate fix
soup = BeautifulSoup(html, 'html.parser')

dig = list()
digi = list()

#print(soup)
# Retrieve all of the td tags; which refers to the individual cells in a table;
# the data is stored in the table for this exercise. 
tags = soup('td') # this gives all the td tags in the document i.e. whatever is between ""<td> </td>""
for tag in tags:
    #print('Contents:',tag.contents[0]) # contents gives us all the values in the cells of the table
    #print('Attrs:',tag.attrs) # there are no attributes in this file
    #print('URL:',tag.get('href', None)) # returns URLs
    #print('TAG:',tag) # returns the tags; in our case the values are all in the specific tag
    dig.extend(re.findall('>([0-9]+)',str(tag)))  
    
print(dig)
for inte in dig:
    digi.append(float(inte))

total = sum(digi)
print('Sum',total,'Count',len(digi))
'''
In this assignment you will write a Python program that expands on http://www.py4e.com/code3/urllinks.py. 
The program will use urllib to read the HTML from the data files below, extract the href= values from the anchor tags, 
scan for a tag that is in a particular position relative to the first name in the list, follow that link and repeat 
the process a number of times and report the last name you find.

We provide two files for this assignment. One is a sample file where we give you the name for your testing and the other 
is the actual data you need to process for the assignment

Sample problem: Start at http://py4e-data.dr-chuck.net/known_by_Fikret.html 
Find the link at position 3 (the first name is 1). Follow that link. This means that from the first link select the link at the
position 3, which will redirect you to another page containing several other links. Do the same in the new page that you have 
loaded.
Repeat this process 4 times. 
The answer is the last name that you retrieve.
Sequence of names: <NAME> 
Last name in sequence: Anayah

Actual problem: Start at: http://py4e-data.dr-chuck.net/known_by_Harvinder.html 
Find the link at position 18 (the first name is 1). Follow that link. Repeat this process 7 times. 
The answer is the last name that you retrieve.
Hint: The first character of the name of the last page that you will load is: S

Strategy
The web pages tweak the height between the links and hide the page after a few seconds to make it 
difficult for you to do the assignment without writing a Python program. But frankly with a little 
effort and patience you can overcome these attempts to make it a little harder to complete the assignment 
without writing a Python program. But that is not the point. The point is to write a clever Python program to solve the program.

Sample execution

Here is a sample execution of a solution:

$ python3 solution.py
Enter URL: http://py4e-data.dr-chuck.net/known_by_Fikret.html
Enter count: 4
Enter position: 3
Retrieving: http://py4e-data.dr-chuck.net/known_by_Fikret.html
Retrieving: http://py4e-data.dr-chuck.net/known_by_Montgomery.html
Retrieving: http://py4e-data.dr-chuck.net/known_by_Mhairade.html
Retrieving: http://py4e-data.dr-chuck.net/known_by_Butchi.html
Retrieving: http://py4e-data.dr-chuck.net/known_by_Anayah.html
The answer to the assignment for this execution is "Anayah".
'''
import urllib.request, urllib.parse, urllib.error, ssl, re
from bs4 import BeautifulSoup


# Ignore SSL certificate errors; this part is required for many web pages otherwise it won't pick up bad certificate tags
ctx = ssl.create_default_context()
ctx.check_hostname = False
ctx.verify_mode = ssl.CERT_NONE

count = input('Enter Count: ')
if count is not '7':
    count = '7'
position = input('Enter Position: ')
if position is not '17':
    position = '17'
names = list()



for i in range(int(count)):
    if i == 0:
        url = input('Enter URL: ') # Enter this website: http://py4e-data.dr-chuck.net/known_by_Harvinder.html
        if url is not 'http://py4e-data.dr-chuck.net/known_by_Harvinder.html':
            url = 'http://py4e-data.dr-chuck.net/known_by_Harvinder.html'
        
        html = urllib.request.urlopen(url, context=ctx).read() # here context is calling the ssl certificate fix
        soup = BeautifulSoup(html, 'html.parser')
        temp= list()
        
       
        
        #print(soup)
        # Retrieve all of the td tags; which refers to the individual cells in a table;
        # the data is stored in the table for this exercise. 
        tags = soup('a') # this gives all the td tags in the document i.e. whatever is between ""<a> </a>""
        for tag in tags:
            
            temp.append(str(tag.get('href', None)))
        names.extend(re.findall('by_(.+).h',temp[int(position)]))
        print('Retrieving:',url)
        print('Retrieving:',temp[int(position)])
    else:
        url = temp[int(position)]
        html = urllib.request.urlopen(url, context=ctx).read() # here context is calling the ssl certificate fix
        soup = BeautifulSoup(html, 'html.parser')
        
        temp= list()
        
        #print(soup)
        # Retrieve all of the td tags; which refers to the individual cells in a table;
        # the data is stored in the table for this exercise. 
        tags = soup('a') # this gives all the td tags in the document i.e. whatever is between ""<a> </a>""
        for tag in tags:
            
            temp.append(str(tag.get('href', None)))
            
        names.extend(re.findall('by_(.+).h',temp[int(position)]))
        print('Retrieving:',temp[int(position)])
    
print('The answer to the assignment for this execution is "',names[-1],'".')<jupyter_output>Enter Count: 
Enter Position: 
Enter URL: 
Retrieving: http://py4e-data.dr-chuck.net/known_by_Harvinder.html
Retrieving: http://py4e-data.dr-chuck.net/known_by_Ciann.html
Retrieving: http://py4e-data.dr-chuck.net/known_by_Modoulamin.html
Retrieving: http://py4e-data.dr-chuck.net/known_by_Angelica.html
Retrieving: http://py4e-data.dr-chuck.net/known_by_Laurie.html
Retrieving: http://py4e-data.dr-chuck.net/known_by_Louise.html
Retrieving: http://py4e-data.dr-chuck.net/known_by_Clifford.html
Retrieving: http://py4e-data.dr-chuck.net/known_by_Susan.html
The answer to the assignment for this execution is " Susan ".<jupyter_text>Data over Networks      The way data is send from one computer across networks is  through XML(eXtensible Markup Language) format.    The difference between XML and HTML is that the former was designed to send data across the network whereas the     latter was designed to display data on one's computer. In XML there are no predefined tags such as   (paragraph    tag of HTML). It was invented in Feb 1998.         For sending data through XML, the origin computer must serialize(sending one character at a time) the data and the     destination computer must deserialize(decode the data one character at a time) to be able to view the data.         The 2 common types of serialization that are commonly used are XML and JSON.         SCHEMA    To validate the XML there needs to a be a validation structure(schema). The schema validates all the XML files so that     things work. The most common schema is designed by W3C(World Wide Web Cosortium) and the files containing a XML schema    is stored with the extension of XSD. The XML files are stored with the extension XML.         Nomenclature    There are tags, attributes and complex/simple elements.     Tags:      Attributes:      Complex Elements:                   Simple Elements:              Dates    International Organisation for Standardisation(ISO) 8601 is used for the date format. Eg. 2002-05-30    UTC (Coordinated Universal Time). It's a common time format. No DST is observed. Eg. 2002-05-30T09:30:10Z     Z(UTC)        JSON    Javascript Object Notation:    This is used for serialisation. The same as XML. The difference is that as the web uses primarily Javascript nowadays     it's easier to use a serialisation method which is a derivative of the language that the web uses.     It's a language containing dictionaries and nested lists, which translates to ease of use in any programming language as     all programming language data structures either support list or dictionaries. In the case of python dictionaries is used    as a data structure.<jupyter_code># worked out example of XML

import xml.etree.ElementTree as ET

data = '''
<person>
  <name>Chuck</name>
  <phone type="intl">
     +1 734 303 4456
   </phone>
   <email hide="yes"/>
</person>'''

tree = ET.fromstring(data)
print('Name:', tree.find('name').text)
print('Attr:', tree.find('email').get('hide'))

# more complex example with more data 

import xml.etree.ElementTree as ET

input = '''
<stuff>
    <users>
        <user x="2">
            <id>001</id>
            <name>Chuck</name>
        </user>
        <user x="7">
            <id>009</id>
            <name>Brent</name>
        </user>
    </users>
</stuff>'''

stuff = ET.fromstring(input)
lst = stuff.findall('users/user')
print('User count:', len(lst))

for item in lst:
    print('Name', item.find('name').text)
    print('Id', item.find('id').text)
    print('Attribute', item.get("x"))

# return latitude and longitude of a particular location
import urllib.request, urllib.parse, urllib.error
import xml.etree.ElementTree as ET

serviceurl = 'http://maps.googleapis.com/maps/api/geocode/xml?'

while True:
    address = input('Enter location: ')
    if len(address) < 1: break

    url = serviceurl + urllib.parse.urlencode({'address': address})
    print('Retrieving', url)
    uh = urllib.request.urlopen(url)
    data = uh.read()
    print('Retrieved', len(data), 'characters')
    print(data.decode())
    tree = ET.fromstring(data)

    results = tree.findall('result')
    lat = results[0].find('geometry').find('location').find('lat').text
    lng = results[0].find('geometry').find('location').find('lng').text
    location = results[0].find('formatted_address').text

    print('lat', lat, 'lng', lng)
    print(location)


'''
Extracting Data from XML

In this assignment you will write a Python program somewhat similar to http://www.py4e.com/code3/geoxml.py. 
The program will prompt for a URL, read the XML data from that URL using urllib and then parse and extract the comment counts
from the XML data, compute the sum of the numbers in the file.

We provide two files for this assignment. One is a sample file where we give you the sum for your testing and the other
is the actual data you need to process for the assignment.

Sample data: http://py4e-data.dr-chuck.net/comments_42.xml (Sum=2553)
Actual data: http://py4e-data.dr-chuck.net/comments_90157.xml (Sum ends with 82)
You do not need to save these files to your folder since your program will read the data directly from the URL.
Note: Each student will have a distinct data url for the assignment - so only use your own data url for analysis.

Data Format and Approach
The data consists of a number of names and comment counts in XML as follows:

<comment>
  <name>Matthias</name>
  <count>97</count>
</comment>

You are to look through all the <comment> tags and find the <count> values sum the numbers.
The closest sample code that shows how to parse XML is geoxml.py. But since the nesting of the elements in our data is
different than the data we are parsing in that sample code you will have to make real changes to the code.
To make the code a little simpler, you can use an XPath selector string to look through the entire tree of XML for any tag
named 'count' with the following line of code:

counts = tree.findall('.//count')
Take a look at the Python ElementTree documentation and look for the supported XPath syntax for details. 
You could also work from the top of the XML down to the comments node and then loop through the child nodes of the comments node.

Sample Execution

$ python3 solution.py
Enter location: http://py4e-data.dr-chuck.net/comments_42.xml
Retrieving http://py4e-data.dr-chuck.net/comments_42.xml
Retrieved 4189 characters
Count: 50
Sum: 2...
'''

import xml.etree.ElementTree as ET

url = input('Enter URL: ') # Enter this website: http://py4e-data.dr-chuck.net/comments_90157.xml
if url is not 'http://py4e-data.dr-chuck.net/comments_90157.xml':
    url = 'http://py4e-data.dr-chuck.net/comments_90157.xml'
uh = urllib.request.urlopen(url)
data = uh.read()
print('Retrieved', len(data), 'characters')
#print(data.decode())
stuff = ET.fromstring(data)
lst = stuff.findall('.//count') # this selects all the count tags; 
#another way to do the same is ./comments/comment/count
print('User count:', len(lst))


dig = list()

for i in lst:
    a = i.text
    dig.append(int(a))

print('Sum is:',sum(dig),'\n','Count is:',len(lst))<jupyter_output>Enter URL: 
Retrieved 4220 characters
User count: 50
Sum is: 2682 
 Count is: 50<jupyter_text>JSON<jupyter_code># this example imports the JSON library and uses it to return the  values of particular keys
# this contains nested dictionaries
import json

data = '''
{
  "name" : "Chuck",
  "phone" : {
    "type" : "intl",
    "number" : "+1 734 303 4456"
   },
   "email" : {
     "hide" : "yes"
   }
}'''

info = json.loads(data)
print('count',len(info)) # returns the number of dictionaries
print('Name:', info['name'])
print('Hide:', info['email']['hide'])
# same example as above but this time there are nested dictionaries in a list 
import json

data = '''
[
  { "id" : "001",
    "x" : "2",
    "name" : "Chuck"
  } ,
  { "id" : "009",
    "x" : "7",
    "name" : "Chuck"
  }
]'''

info = json.loads(data)
print('User count:', len(info))

for item in info:
    print('Name', item['name'])
    print('Id', item['id'])
    print('Attribute', item['x'])
# returns the latitude and longitude of a location
import urllib.request, urllib.parse, urllib.error
import json

# Note that Google is increasingly requiring keys for this API
serviceurl = 'http://maps.googleapis.com/maps/api/geocode/json?'

while True:
    address = input('Enter location: ')
    if len(address) < 1: break

    url = serviceurl + urllib.parse.urlencode(
        {'address': address})

    print('Retrieving', url)
    uh = urllib.request.urlopen(url)
    data = uh.read().decode()
    print('Retrieved', len(data), 'characters')

    try:
        js = json.loads(data)
    except:
        js = None

    if not js or 'status' not in js or js['status'] != 'OK':
        print('==== Failure To Retrieve ====')
        print(data)
        continue

    print(json.dumps(js, indent=4))

    lat = js["results"][0]["geometry"]["location"]["lat"]
    lng = js["results"][0]["geometry"]["location"]["lng"]
    print('lat', lat, 'lng', lng)
    location = js['results'][0]['formatted_address']
    print(location)
'''
Extracting Data from JSON

In this assignment you will write a Python program somewhat similar to http://www.py4e.com/code3/json2.py. 
The program will prompt for a URL, read the JSON data from that URL using urllib and then parse and extract 
the comment counts from the JSON data, compute the sum of the numbers in the file and enter the sum below:
We provide two files for this assignment. One is a sample file where we give you the sum for your testing and 
the other is the actual data you need to process for the assignment.

Sample data: http://py4e-data.dr-chuck.net/comments_42.json (Sum=2553)
Actual data: http://py4e-data.dr-chuck.net/comments_90158.json (Sum ends with 74)
You do not need to save these files to your folder since your program will read the data directly from the URL. 
Note: Each student will have a distinct data url for the assignment - so only use your own data url for analysis.

Data Format
The data consists of a number of names and comment counts in JSON as follows:

{
  comments: [
    {
      name: "Matthias"
      count: 97
    },
    {
      name: "Geomer"
      count: 97
    }
    ...
  ]
}
The closest sample code that shows how to parse JSON and extract a list is json2.py. 
You might also want to look at geoxml.py to see how to prompt for a URL and retrieve data from a URL.

Sample Execution

$ python3 solution.py
Enter location: http://py4e-data.dr-chuck.net/comments_42.json
Retrieving http://py4e-data.dr-chuck.net/comments_42.json
Retrieved 2733 characters
Count: 50
Sum: 2...
'''
import urllib.request, urllib.parse, urllib.error, json

serviceurl = 'http://py4e-data.dr-chuck.net/comments_90158.json'


address = input('Enter URL: ')
if address is not 'http://py4e-data.dr-chuck.net/comments_90158.json':
    address = 'http://py4e-data.dr-chuck.net/comments_90158.json'

url = serviceurl 
print('Retrieving', url)
uh = urllib.request.urlopen(url)
data = uh.read().decode()
print('Retrieved', len(data), 'characters')

try:
    js = json.loads(data)
except:
    js = None

    
#print(json.dumps(js, indent=2)) # displays the json file
    
temp = list()
for i in js['comments']:
    temp.append(float(i['count']))
    #print(i['count'])
total = sum(temp)
print('Count',len(temp))
print('TOTAL SUM', total)


'''
Calling a JSON API

In this assignment you will write a Python program somewhat similar to http://www.py4e.com/code3/geojson.py. 
The program will prompt for a location, contact a web service and retrieve JSON for the web service and parse that data,
and retrieve the first place_id from the JSON. A place ID is a textual identifier that uniquely identifies a place as 
within Google Maps.


API End Points

To complete this assignment, you should use this API endpoint that has a static subset of the Google Data:

http://py4e-data.dr-chuck.net/geojson?

This API uses the same parameter (address) as the Google API. This API also has no rate limit so
you can test as often as you like. If you visit the URL with no parameters, you get a list of all of the address values
which can be used with this API.
To call the API, you need to provide address that you are requesting as the address= parameter that is properly URL
encoded using the urllib.urlencode() fuction as shown in http://www.py4e.com/code3/geojson.py

Test Data / Sample Execution

You can test to see if your program is working with a location of "South Federal University" which
will have a place_id of "ChIJJ8oO7_B_bIcR2AlhC8nKlok".

$ python3 solution.py
Enter location: South Federal University
Retrieving http://...
Retrieved 2101 characters
Place id ChIJJ8oO7_B_bIcR2AlhC8nKlok

Turn In

Please run your program to find the place_id for this location:

Moscow Engineering-Physics Institute
Make sure to enter the name and case exactly as above and enter the place_id and your Python code below. 
Hint: The first seven characters of the place_id are "ChIJybD ..."
Make sure to retreive the data from the URL specified above and not the normal Google API.
Your program should work with the Google API - but the place_id may not match for this assignment.
'''
import urllib.request, urllib.parse, urllib.error, json

serviceurl = 'http://py4e-data.dr-chuck.net/geojson?'


address = input('Enter Location: ')
if address is not 'Moscow Engineering-Physics Institute':
    address = 'Moscow Engineering-Physics Institute'

url = serviceurl + urllib.parse.urlencode({'address': address})
print('Retrieving', url)
uh = urllib.request.urlopen(url)
data = uh.read().decode()
print('Retrieved', len(data), 'characters')

try:
    js = json.loads(data)
except:
    js = None

    

    

#print(json.dumps(js,indent=2))

lat = js['results'][0]['geometry']['location']['lng']
lng = js['results'][0]['geometry']['location']['lng']
place = js['results'][0]['place_id']
print('Place id:',place,'\n','Lat',lat,'\n','Long',lng)
place_name = js['results'][0]['formatted_address']
print('Lieu:',place_name)<jupyter_output>Enter Location: university of calcutta
Retrieving http://py4e-data.dr-chuck.net/geojson?address=Moscow+Engineering-Physics+Institute
Retrieved 3480 characters
Place id: ChIJybDUc_xKtUYRTM9XV8zWRD0 
 Lat 37.6173 
 Long 37.6173
Lieu: Moscow, Russia<jupyter_text>Object Oriented Programming    Class encompasses objects. So Dog is a class and Doberman is an object in the class dog.             Object a.k.a Instance        Method for the class Dog would be bark, eat, sit etc.        Short Definitions        Class: A template; Dog    Attribute: A variable within a class; x = 2    Method: A function within a class; bark()    Object: A particular instance of a class; x     Constructor: Code that runs when an object is created; y = Dog => stores the dog template in y    Inheritance: The ability to extend a class to make a new class. Let's say a new class animal is created by extending     dog. Therfore, the class animal has all the capabilities that the class dog has and much more.           Database        The idea of STRUCTURED QUERY LANGUAGE(SQL) came from the idea of applications being able to talk to databases. It's an API    and is used just like any SERVICE ORIENTED ARCHITECTURE.         SQL is just another way to talk to databases. SQL is a very simple and yet powerful language. The only shortcoming is that    it can handle very well gorgeous data. Any data pre-processing or unstructured data takes a long time to read. That's where    Python comes in. It makes the data gorgeous and then SQL reads it.         It has 4 major functions:        C: Create a table     R: Read data    U: Update data    D: Delete data        Common Database Systems        Oracle     MySQL - Freeware(acquired by Oracle) and Mariadb is also a databbase    SQLServer - From Microsoft (also Access)    Freeware - HSQL,SQLite,Postgress        SOME SQL CODE        CREATE a Table         CREATE TABLE Users(       name VARCHAR(128),       email VARCHAR(128)        )                INSERT a row         INSERT INTO Users (name, email) VALUES ('Kristin', '<EMAIL>')            Delete         DELETE FROM Users WHERE email='<EMAIL>'            UPDATE        UPDATE Users SET name='Charles' WHERE email='<EMAIL>'            SELECT rows and columns         SELECT * FROM Users WHERE email='<EMAIL>'            SORT the database         SELECT * FROM Users ORDER BY email    SELECT * FROM Users ORDER BY name DESC            SAMPLE CODE         Description - Creates a table called Ages and inserts two columns name and ages respectively. Returns the hexadecimal values of name or age from the table Ages and sorts it by the hexadecimal value. Displays the result        CREATE TABLE Ages (       name VARCHAR(128),       age INTEGER    )    DELETE FROM Ages;    INSERT INTO Ages (name, age) VALUES ('Korrin', 36);    INSERT INTO Ages (name, age) VALUES ('Harneet', 16);    INSERT INTO Ages (name, age) VALUES ('Maitlind', 15);    INSERT INTO Ages (name, age) VALUES ('Chymari', 27);    SELECT hex(name || age) AS X FROM Ages ORDER BY X<jupyter_code># example of sql in python

import sqlite3 # importing the SQL library to run SQL commands

conn = sqlite3.connect('emaildb.sqlite') # establish connection to the database or create the file
cur = conn.cursor() # this makes a handle through the connection and execute SQL commands through the cursor

cur.execute('DROP TABLE IF EXISTS Counts') # this will drop a database if it does exist and start with a fresh database
# in short it will overwrite the previous database

cur.execute('''
CREATE TABLE Counts (email TEXT, count INTEGER)''') # this creates a table with columns

fname = input('Enter file name: ')
if (len(fname) < 1): fname = 'mbox-short.txt'
fh = open(fname)
for line in fh:
    if not line.startswith('From: '): continue
    pieces = line.split()
    email = pieces[1]
    cur.execute('SELECT count FROM Counts WHERE email = ? ', (email,)) # this puts all the emails in a database 
    # the ? in the code is just a placeholder indicating what value should go in its place; if we have two ? then we can 
    # put two things such as email and name, this ? is followed by a tuple eg. (email,), here we are just going to replace 
    # the ? mark by email.
    row = cur.fetchone() # this fetches the keys or rows; in this case it's all the emails of people;
    if row is None:
        cur.execute('''INSERT INTO Counts (email, count)
                VALUES (?, 1)''', (email,)) # just like a dictionary adds 1 to the count of email and adds the email 
        # similar to updating the dictionary with a get command. here it would translate to counts.get(email,0) +1
    else:
        cur.execute('UPDATE Counts SET count = count + 1 WHERE email = ?',
                    (email,)) # if the email already exists then it just updates the counts of the email
    conn.commit() # writes the the data from memory to the disk

# https://www.sqlite.org/lang_select.html
sqlstr = 'SELECT email, count FROM Counts ORDER BY count DESC LIMIT 10' # this is the code to select the all the emails and 
# return their respective counts; it's stored in the form of a string

for row in cur.execute(sqlstr): # the string is used as an SQL code
    print(str(row[0]), row[1])

cur.close() # closing the connection to the database

'''
This application will read the mailbox data (mbox.txt) and count the number of email messages per organization 
(i.e. domain name of the email address) using a database with the following schema to maintain the counts.

CREATE TABLE Counts (org TEXT, count INTEGER)
When you have run the program on mbox.txt upload the resulting database file above for grading.
If you run the program multiple times in testing or with dfferent files, make sure to empty out the data before each run.

You can use this code as a starting point for your application: http://www.py4e.com/code3/emaildb.py.

The data file for this application is the same as in previous assignments: http://www.py4e.com/code3/mbox.txt.

Because the sample code is using an UPDATE statement and committing the results to the database as each record 
is read in the loop, it might take as long as a few minutes to process all the data. The commit insists on completely 
writing all the data to disk every time it is called.

The program can be speeded up greatly by moving the commit operation outside of the loop. In any database program,
there is a balance between the number of operations you execute between commits and the importance of not losing
the results of operations that have not yet been committed.

'''
import sqlite3, re # importing the SQL library to run SQL commands

conn = sqlite3.connect('emailhostdb.sqlite') # establish connection to the database or create the file
cur = conn.cursor() # this makes a handle through the connection and execute SQL commands through the cursor

cur.execute('DROP TABLE IF EXISTS Counts') # this will drop a database if it does exist and start with a fresh database
# in short it will overwrite the previous database

cur.execute('''
CREATE TABLE Counts (org TEXT, count INTEGER)''') # this creates a table with columns

fname = input('Enter file name: ')
if (len(fname) < 1): fname = 'mbox.txt'
fh = open(fname)
for line in fh:
    if not line.startswith('From: '): continue
    pieces = line.split()
    email = pieces[1]
    host = re.findall('@([^ ]*|$)',email)[0] # returns only the output string and not the [] &'' inlcuded in all 
    # the regex expressions this can be also used to return several instances of regex output. 
    # eg if the output is ['33','44'] then we can return the output 33 or 44 using the index after the regex 
    # i.e. re.findall('\d+|$', 'aa33bbb44')[0] will return 33 and re.findall('\d+|$', 'aa33bbb44')[1] will return 44
    cur.execute('SELECT count FROM Counts WHERE org = ? ', ((host),)) # this puts all the email hosts in a database 
    # the ? in the code is just a placeholder indicating what value should go in its place; if we have two ? then we can 
    # put two things such as email and name, this ? is followed by a tuple eg. (host,), here we are just going to replace 
    # the ? mark by email.
    row = cur.fetchone() # this fetches the keys or rows; in this case it's all the emails of people;
    if row is None:
        cur.execute('''INSERT INTO Counts (org, count)
                VALUES (?, 1)''', ((host),)) # just like a dictionary adds 1 to the count of email and adds the email 
        # similar to updating the dictionary with a get command. here it would translate to counts.get(email,0) +1
    else:
        cur.execute('UPDATE Counts SET count = count + 1 WHERE org = ?',
                    ((host),)) # if the email already exists then it just updates the counts of the email
conn.commit() # writes the the data from memory to the disk

# https://www.sqlite.org/lang_select.html
sqlstr = 'SELECT org, count FROM Counts ORDER BY count DESC' # this is the code to select the all the emails and 
# return their respective counts; it's stored in the form of a string

for row in cur.execute(sqlstr): # the string is used as an SQL code
    print(str(row[0]), row[1])

cur.close() # closing the connection to the database


# Relational Database example; makes a database of all the tracks along with their artists and albums respectively
# from iTunes Library

import xml.etree.ElementTree as ET
import sqlite3

conn = sqlite3.connect('trackdb.sqlite')
cur = conn.cursor()

# Make some fresh tables using executescript()
cur.executescript('''
DROP TABLE IF EXISTS Artist;
DROP TABLE IF EXISTS Album;
DROP TABLE IF EXISTS Track;

CREATE TABLE Artist (
    id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    name    TEXT UNIQUE
);

CREATE TABLE Album (
    id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    artist_id  INTEGER,
    title   TEXT UNIQUE
);

CREATE TABLE Track (
    id  INTEGER NOT NULL PRIMARY KEY 
        AUTOINCREMENT UNIQUE,
    title TEXT  UNIQUE,
    album_id  INTEGER,
    len INTEGER, rating INTEGER, count INTEGER
);
''')


fname = input('Enter file name: ')
if ( len(fname) < 1 ) : fname = 'Library.xml'

# <key>Track ID</key><integer>369</integer>
# <key>Name</key><string>Another One Bites The Dust</string>
# <key>Artist</key><string>Queen</string>
def lookup(d, key):
    found = False
    for child in d:
        if found : return child.text
        if child.tag == 'key' and child.text == key :
            found = True
    return None

stuff = ET.parse(fname)
all = stuff.findall('dict/dict/dict')
print('Dict count:', len(all))
for entry in all:
    if ( lookup(entry, 'Track ID') is None ) : continue

    name = lookup(entry, 'Name')
    artist = lookup(entry, 'Artist')
    album = lookup(entry, 'Album')
    count = lookup(entry, 'Play Count')
    rating = lookup(entry, 'Rating')
    length = lookup(entry, 'Total Time')

    if name is None or artist is None or album is None : 
        continue

    print(name, artist, album, count, rating, length)

    cur.execute('''INSERT OR IGNORE INTO Artist (name) 
        VALUES ( ? )''', ( artist, ) )
    cur.execute('SELECT id FROM Artist WHERE name = ? ', (artist, ))
    artist_id = cur.fetchone()[0]

    cur.execute('''INSERT OR IGNORE INTO Album (title, artist_id) 
        VALUES ( ?, ? )''', ( album, artist_id ) )
    cur.execute('SELECT id FROM Album WHERE title = ? ', (album, ))
    album_id = cur.fetchone()[0]

    cur.execute('''INSERT OR REPLACE INTO Track
        (title, album_id, len, rating, count) 
        VALUES ( ?, ?, ?, ?, ? )''', 
        ( name, album_id, length, rating, count ) )

conn.commit()

'''
Musical Track Database
This application will read an iTunes export file in XML and produce a properly normalized database with this structure:

CREATE TABLE Artist (
    id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    name    TEXT UNIQUE
);

CREATE TABLE Genre (
    id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    name    TEXT UNIQUE
);

CREATE TABLE Album (
    id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    artist_id  INTEGER,
    title   TEXT UNIQUE
);

CREATE TABLE Track (
    id  INTEGER NOT NULL PRIMARY KEY 
        AUTOINCREMENT UNIQUE,
    title TEXT  UNIQUE,
    album_id  INTEGER,
    genre_id  INTEGER,
    len INTEGER, rating INTEGER, count INTEGER
);
If you run the program multiple times in testing or with different files, make sure to empty out the data before each run.

You can use this code as a starting point for your application: http://www.py4e.com/code3/tracks.zip. 
The ZIP file contains the Library.xml file to be used for this assignment. You can export your own tracks from iTunes 
and create a database, but for the database that you turn in for this assignment, only use the Library.xml data that is provided.

To grade this assignment, the program will run a query like this on your uploaded database and look for the 
data it expects to see:

SELECT Track.title, Artist.name, Album.title, Genre.name 
    FROM Track JOIN Genre JOIN Album JOIN Artist 
    ON Track.genre_id = Genre.ID and Track.album_id = Album.id 
        AND Album.artist_id = Artist.id
    ORDER BY Artist.name LIMIT 3
    
The expected result of the modified query on your database is: (shown here as a simple HTML table with titles)
Track	Artist	Album	Genre
Chase the Ace	AC/DC	Who Made Who	Rock
D.T.	AC/DC	Who Made Who	Rock
For Those About To Rock (We Salute You)	AC/DC	Who Made Who	Rock

'''

import xml.etree.ElementTree as ET
import sqlite3

conn = sqlite3.connect('trackdb.sqlite')
cur = conn.cursor()

# Make some fresh tables using executescript()
cur.executescript('''
DROP TABLE IF EXISTS Artist;
DROP TABLE IF EXISTS Album;
DROP TABLE IF EXISTS Genre;
DROP TABLE IF EXISTS Track;

CREATE TABLE Artist (
    id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    name    TEXT UNIQUE
);

CREATE TABLE Genre (
    id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    name    TEXT UNIQUE
);

CREATE TABLE Album (
    id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    artist_id  INTEGER,
    title   TEXT UNIQUE
);

CREATE TABLE Track (
    id  INTEGER NOT NULL PRIMARY KEY 
        AUTOINCREMENT UNIQUE,
    title TEXT  UNIQUE,
    album_id  INTEGER,
    genre_id  INTEGER,
    len INTEGER, rating INTEGER, count INTEGER
);
''')


fname = input('Enter file name: ')
if ( len(fname) < 1 ) : fname = 'Library.xml'

# <key>Track ID</key><integer>369</integer>
# <key>Name</key><string>Another One Bites The Dust</string>
# <key>Artist</key><string>Queen</string>
def lookup(d, key):
    found = False
    for child in d:
        if found : return child.text
        if child.tag == 'key' and child.text == key :
            found = True
    return None

stuff = ET.parse(fname)
all = stuff.findall('dict/dict/dict')
print('Dict count:', len(all))
for entry in all:
    if ( lookup(entry, 'Track ID') is None ) : continue

    name = lookup(entry, 'Name')
    artist = lookup(entry, 'Artist')
    album = lookup(entry, 'Album')
    count = lookup(entry, 'Play Count')
    rating = lookup(entry, 'Rating')
    length = lookup(entry, 'Total Time')
    genre = lookup(entry, 'Genre')

    if name is None or artist is None or album is None or genre is None : 
        continue

    print(name,';', artist,';', album,';', count,';', rating,';', length,';',genre)

    cur.execute('''INSERT OR IGNORE INTO Artist (name) 
        VALUES ( ? )''', ( artist, ) )
    cur.execute('SELECT id FROM Artist WHERE name = ? ', (artist, ))
    artist_id = cur.fetchone()[0]
    
    cur.execute('''INSERT OR IGNORE INTO Genre (name) 
        VALUES ( ? )''', ( genre, ) )
    cur.execute('SELECT id FROM Genre WHERE name = ? ', (genre, ))
    genre_id = cur.fetchone()[0]

    cur.execute('''INSERT OR IGNORE INTO Album (title, artist_id) 
        VALUES ( ?, ? )''', ( album, artist_id ) )
    cur.execute('SELECT id FROM Album WHERE title = ? ', (album, ))
    album_id = cur.fetchone()[0]
    
    cur.execute('''INSERT OR REPLACE INTO Track
        (title, album_id, genre_id,len, rating, count) 
        VALUES ( ?, ?, ?, ?, ?, ? )''', 
        ( name, album_id, genre_id, length, rating, count ) )

conn.commit()
'''
This application will read roster data in JSON format, parse the file, and then produce an SQLite database that contains a 
User, Course, and Member table and populate the tables from the data file.

You can base your solution on this code: http://www.py4e.com/code3/roster/roster.py - this code is incomplete as you need 
to modify the program to store the role column in the Member table to complete the assignment.

Each student gets their own file for the assignment. Download this file and save it as roster_data.json. Move the downloaded 
file into the same folder as your roster.py program.

Once you have made the necessary changes to the program and it has been run successfully reading the above JSON data, 
run the following SQL command:
SELECT hex(User.name || Course.title || Member.role ) AS X FROM 
    User JOIN Member JOIN Course 
    ON User.id = Member.user_id AND Member.course_id = Course.id
    ORDER BY X


Find the first row in the resulting record set and enter the long string that looks like 53656C696E613333.
'''


import json, sqlite3


conn = sqlite3.connect('rosterdb.sqlite')
cur = conn.cursor()

# Do some setup
cur.executescript('''
DROP TABLE IF EXISTS User;
DROP TABLE IF EXISTS Member;
DROP TABLE IF EXISTS Course;



CREATE TABLE User (
    id     INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    name   TEXT UNIQUE
);

CREATE TABLE Course (
    id     INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    title  TEXT UNIQUE
);

CREATE TABLE Member (
    user_id     INTEGER,
    course_id   INTEGER,
    role        INTEGER,
    PRIMARY KEY (user_id, course_id)
)
''')

fname = input('Enter file name: ')
if len(fname) < 1:
    fname = 'roster_data.json'

#   [ "Charley", "si110", 1 ],
#   [ "Mea", "si110", 0 ],

str_data = open(fname).read()
json_data = json.loads(str_data)

for entry in json_data:

    name = entry[0];
    title = entry[1];
    number = entry[2];

    #print((name, title, number))

    cur.execute('''INSERT OR IGNORE INTO User (name)
        VALUES ( ? )''', ( name, ) )
    cur.execute('SELECT id FROM User WHERE name = ? ', (name, ))
    user_id = cur.fetchone()[0]

    cur.execute('''INSERT OR IGNORE INTO Course (title)
        VALUES ( ? )''', ( title, ) )
    cur.execute('SELECT id FROM Course WHERE title = ? ', (title, ))
    course_id = cur.fetchone()[0]
    

    cur.execute('''INSERT OR REPLACE INTO Member
        (user_id, course_id, role) VALUES ( ?, ?, ? )''',
        ( user_id, course_id, number ) )

conn.commit()

# geo data storing in a database 
# http://maps.googleapis.com/maps/api/geocode/json?
# https://maps.googleapis.com/maps/api/place/textsearch/json?

import urllib.request, urllib.parse, urllib.error, http, sqlite3, json, time, ssl, sys


api_key = False
# If you have a Google Places API key, enter it here
# api_key = 'AIzaSy___IDByT70'

if api_key is False:
    serviceurl = "http://py4e-data.dr-chuck.net/geojson?"
else :
    serviceurl = "http://maps.googleapis.com/maps/api/geocode/json?"

# Additional detail for urllib
# http.client.HTTPConnection.debuglevel = 1

conn = sqlite3.connect('geodata.sqlite')
cur = conn.cursor()

cur.execute(''' DROP TABLE IF EXISTS Locations
''')
cur.execute('''
CREATE TABLE IF NOT EXISTS Locations (address TEXT, geodata TEXT)''')

# Ignore SSL certificate errors
ctx = ssl.create_default_context()
ctx.check_hostname = False
ctx.verify_mode = ssl.CERT_NONE

fh = open("where.data")
count = 0
for line in fh:
    if count > 200 :
        print('Retrieved 200 locations, restart to retrieve more')
        break

    address = line.strip()
    print('')
    cur.execute("SELECT geodata FROM Locations WHERE address= ?",
        (memoryview(address.encode()), ))

    try:
        data = cur.fetchone()[0]
        print("Found in database ",address)
        continue
    except:
        pass

    parms = dict()
    parms["query"] = address
    if api_key is not False: parms['key'] = api_key
    url = serviceurl + urllib.parse.urlencode(parms)

    print('Retrieving', url)
    uh = urllib.request.urlopen(url, context=ctx)
    data = uh.read().decode()
    print('Retrieved', len(data), 'characters', data[:20].replace('\n', ' '))
    count = count + 1

    try:
        js = json.loads(data)
    except:
        print(data)  # We print in case unicode causes an error
        continue

    if 'status' not in js or (js['status'] != 'OK' and js['status'] != 'ZERO_RESULTS') :
        print('==== Failure To Retrieve ====')
        print(data)
        break

    cur.execute('''INSERT INTO Locations (address, geodata)
            VALUES ( ?, ? )''', (memoryview(address.encode()), memoryview(data.encode()) ) )
    conn.commit()
    if count % 100 == 0 :
        print('Pausing for a bit...')
        time.sleep(5)

print("Run geodump.py to read the data from the database so you can vizualize it on a map.")

# dumping the geo data in javascript file to show it in html

import sqlite3, json, codecs

conn = sqlite3.connect('geodata.sqlite')
cur = conn.cursor()

cur.execute('SELECT * FROM Locations')
fhand = codecs.open('where.js', 'w', "utf-8")
fhand.write("myData = [\n")
count = 0
for row in cur :
    data = str(row[1].decode())
    try: js = json.loads(str(data))
    except: continue

    if not('status' in js and js['status'] == 'OK') : continue

    lat = js["results"][0]["geometry"]["location"]["lat"]
    lng = js["results"][0]["geometry"]["location"]["lng"]
    if lat == 0 or lng == 0 : continue
    where = js['results'][0]['formatted_address']
    where = where.replace("'", "")
    try :
        print(where, lat, lng)

        count = count + 1
        if count > 1 : fhand.write(",\n")
        output = "["+str(lat)+","+str(lng)+", '"+where+"']"
        fhand.write(output)
    except:
        continue

fhand.write("\n];\n")
cur.close()
fhand.close()
print(count, "records written to where.js")
print("Open where.html to view the data in a browser")<jupyter_output>Dormitory no. 14 "Kapitol" of AGH University of Science and Technology in Cracov, Poland, Witolda Budryka 2, 30-072 Kraków, Poland 50.0676785 19.9070824
Warsaw, Poland 52.2296756 21.0122287
American University Housing District, Cairo Governorate, Egypt 30.0068972 31.5005988
State Ave, Tempe, AZ 85281, USA 33.4232051 -111.8879509
Information Technology Services, 2500 Daniels Bridge Rd, Athens, GA 30606, USA 33.9094658 -83.4604372
BITS, Pilani, Rajasthan 333031, India 28.3580163 75.5887989
Babcock Dr, Rochester, NY 14610, USA 43.1294077 -77.5479304
Banaras Hindu University Campus, Varanasi, Uttar Pradesh, India 25.2685304 82.9904737
Bangalore University Rd, Bengaluru, Karnataka 560056, India 12.9426914 77.5053145
Baylor Blvd, Fairbanks, AK 99709, USA 64.8852156 -147.8009743
Capital Normal University, Haidian, Beijing, China, 100037 39.9304437 116.3076925
Monument to Fallen Professors and Students of the Belarusian State University, Minsk, Belarus 53.89318369999999 27.547338
Belgrade Dr, [...]
{'fraction_non_alphanumeric': 0.1030520020382439, 'fraction_numerical': 0.017633491565424948, 'mean_word_length': 3.5207929194956353, 'pattern_counts': {'":': 0, '<': 177, '<?xml version=': 0, '>': 175, 'https://': 5, 'lorem ipsum': 0, 'www.': 17, 'xml': 21}, 'pii_count': 4, 'substrings_counts': 1, 'word_list_counts': {'cursed_substrings.json': 10, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '17', 'dir': 'jupyter-structured-clean-dedup', 'id': '74849', 'license': 'MIT', 'n_tokens_mistral': 24167, 'n_tokens_neox': 22765, 'n_words': 9481, 'path': 'Python Exercises for py4e.ipynb', 'repo_name': 'Shamik-07/pythonforeverybodynotes', 'size': '74816'}