<jupyter_start><jupyter_text>Reading/Writing using native python functionsnatively we write and read data using lists Opening a file - open()* you don’t need to import a library in order to read and write files* use Python’s built-in `open` function to get a **file object*** Different modes in which a file can be opened:    * `'w'` means write     * `'r'` for read    * `'a'` for append     * `'r+'` for read and write together<jupyter_code>f = open('io data/workfile','w')<jupyter_output><empty_output><jupyter_text>* `mode` attribute of a file object tells you which mode a file was opened in* `name` attribute tells you the name of the file that the file object has opened<jupyter_code>print(f.mode)
print(f.name)<jupyter_output>w
io data/workfile<jupyter_text>Closing a file - close()* When you’re done working, you can use the `f.close()` command to end things. * What this does is close the file completely, terminating resources in use, in turn freeing them up for the system to deploy elsewhere.<jupyter_code>f.close()<jupyter_output><empty_output><jupyter_text>Writing to a file - write()* writing to the file is quite simple using the `write()` function<jupyter_code>f = open('io data/workfile','w')
f.write('Hello World') 
f.write('\nThis is our new text file') 
f.write('\nand this is another line') 
f.write('\nWhy? Because we can')
f.close()
g = open('io data/hello.txt','w') 
lines_of_text = ['One line of text here', '\nand another line here', '\nand yet another here', '\nand so on and so forth'] 
g.writelines(lines_of_text) 
g.close() 
cat 'io data/hello.txt'<jupyter_output>One line of text here
and another line here
and yet another here
and so on and so forth<jupyter_text>Reading from a file - cat, read(), readline(), readlines(), recursivelyWe can read from a file in a couple of different ways:`cat` can be used to display the entrie contents of the file* to view the contents with cat we dont need to open the file<jupyter_code>cat 'io data/workfile'<jupyter_output>Hello World
This is our new text file
and this is another line
Why? Because we can<jupyter_text>`read()` is another method to read the contents of a file<jupyter_code>f = open('io data/workfile', 'r') 
print(f.read())
f.close()<jupyter_output>Hello World
This is our new text file
and this is another line
Why? Because we can<jupyter_text>* if we supply a number to the `read()` function, then it reads those many characters from the begenning of the file<jupyter_code>f = open('io data/workfile', 'r') 
print(f.read(5))
f.close()<jupyter_output>Hello<jupyter_text>`readline()`: If you want to read a file line by line – as opposed to pulling the content of the entire file at once – then you use the `readline()` function * since we write it twice, it reads first 2 lines<jupyter_code>f = open('io data/workfile', 'r') 
print(f.readline())
print(f.readline())
f.close()<jupyter_output>Hello World

This is our new text file<jupyter_text>`readlines()`: If you want to read all the lines of a file in a list you can use `f.readlines()`* This reads lines and can store them in a list<jupyter_code>f = open('io data/workfile', 'r') 
print(f.readlines())
f.close()<jupyter_output>['Hello World\n', 'This is our new text file\n', 'and this is another line\n', 'Why? Because we can']<jupyter_text>* Since all lines can be read into a list, you can choose to see whichever line you want<jupyter_code>f = open('io data/workfile', 'r') 
g = f.readlines()
print(g[1])
f.close()<jupyter_output>This is our new text file<jupyter_text>`Recursively`: Looping over a file object* For reading lines from a file, you can loop over the file object. * This is memory efficient, fast, and leads to simple code<jupyter_code>f = open('io data/workfile', 'r') 
for line in f: 
    print(line)
f.close()<jupyter_output>Hello World

This is our new text file

and this is another line

Why? Because we can<jupyter_text>With Statement* bonus of using this method is that any files opened will be closed automatically after you are done<jupyter_code>with open('io data/hello.txt','r') as f:
    f.read() 
f.closed # Check if the file was automatically closed
with open('io data/hello.txt') as f:
    for line in f: 
        print(line)<jupyter_output>One line of text here

and another line here

and yet another here

and so on and so forth<jupyter_text>***  Reading/Writing using pandas CSV and Text files* The two workhorse functions for reading text files (a.k.a. flat files) are read_csv() and read_table()<jupyter_code>import numpy as np
import pandas as pd
from io import StringIO
data = 'col1,col2,col3\na,b,1\na,b,2\nc,d,3'
pd.read_csv(StringIO(data))
pd.read_csv(StringIO(data), usecols=lambda x: x.upper() in ['COL1', 'COL3'])
pd.read_csv(StringIO(data), skiprows=lambda x: x % 2 != 0)
data = 'a,b,c\n1,2,3\n4,5,6\n7,8,9'
print(data)
df = pd.read_csv(StringIO(data), dtype=object)
print(df)
print(df['a'][0])
df = pd.read_csv(StringIO(data), dtype={'b': object, 'c': np.float64})
print(df)
df.dtypes
data = "col_1\n1\n2\n'A'\n4.22"
print(data)
df = pd.read_csv(StringIO(data), converters={'col_1':str})
print(df)
df['col_1'].apply(type).value_counts()
df2 = pd.read_csv(StringIO(data))
df2['col_1'] = pd.to_numeric(df2['col_1'], errors='coerce')
print(df2)
df2['col_1'].apply(type).value_counts()
import sqlite3 as lite
import sys
 
con = None
 
try:
    con = lite.connect('io data/test.db') # connect to a new database called test.db
    # Once you have a Connection, you can create a Cursor object 
    # and call its execute() method to perform SQL commands
    cur = con.cursor()    
    cur.execute('SELECT SQLITE_VERSION()') # query the database management system with the command
    data = cur.fetchone()
    print ("SQLite version: %s" % data)                
except lite.Error:   
    print ("Error %s:" % e.args[0])
    sys.exit(1)
finally:    
    if con:
        con.close()
import sqlite3 as lite
import sys
 
con = lite.connect('io data/user.db') # database can hold many different tables
 
with con:
 # Once you have a Connection, you can create a Cursor object 
 # and call its execute() method to perform SQL commands
    cur = con.cursor()    
    #cur.execute("drop table Users")
    cur.execute("CREATE TABLE Users(Id INT, Name TEXT)") # table defines the structure of the data and can hold the data
    cur.execute("INSERT INTO Users VALUES(1,'Michelle')") # add  records into the table
    cur.execute("INSERT INTO Users VALUES(2,'Sonya')") # add  records into the table
    cur.execute("INSERT INTO Users VALUES(3,'Greg')") # add  records into the table
    
    cur.execute("CREATE TABLE Jobs(Id INT, Uid INT, Profession TEXT)")
    cur.execute("INSERT INTO Jobs VALUES(1,1,'Scientist')")
    cur.execute("INSERT INTO Jobs VALUES(2,2,'Marketeer')")
    cur.execute("INSERT INTO Jobs VALUES(3,3,'Developer')")
import sqlite3 as lite
import sys
 
con = lite.connect('io data/user.db') # database can hold many different tables
 
with con:    
 # Once you have a Connection, you can create a Cursor object 
 # and call its execute() method to perform SQL commands
    cur = con.cursor()    
    cur.execute("SELECT * FROM Users")
 
    rows = cur.fetchall()
 
    for row in rows:
        print (row)
import sqlite3 as lite
import sys
 
con = lite.connect('io data/user.db') # database can hold many different tables
 
with con:    
 # Once you have a Connection, you can create a Cursor object 
 # and call its execute() method to perform SQL commands
    cur = con.cursor()    
    cur.execute("SELECT Users.name, Jobs.profession FROM Jobs INNER JOIN Users ON Users.ID = Jobs.uid")
 
    rows = cur.fetchall()
 
    for row in rows:
        print (row)
rows
type(rows)
df = pd.DataFrame(rows)
df
import pandas as pd
labels=['Name','Profession']
labels_rows=range(1,len(rows)+1)
df = pd.DataFrame(rows,columns=labels,index=labels_rows)
df
df.head(1)<jupyter_output><empty_output><jupyter_text>Another attempt to read/write using pandas read_csv<jupyter_code>import pandas as pd
dataset = pd.read_csv('io data/Data.csv')
dataset<jupyter_output><empty_output><jupyter_text>Splitting the dataset<jupyter_code>x = dataset.iloc[:, :-1].values
y = dataset.iloc[:, 3].values
x # predictors
y # response variable
# we create a copy of the data.csv file and replace all , with ;
# now when we try to import, the data is not neatly separated into columns
import pandas as pd
dataset = pd.read_csv('io data/Data (copy).csv')
dataset
import pandas as pd
dataset = pd.read_csv('io data/Data (copy).csv',sep=';')
dataset<jupyter_output><empty_output><jupyter_text>read_excel<jupyter_code>import pandas as pd
orders = pd.read_excel('io data/Superstore.xls', sheet_name='Orders')
returns = pd.read_excel('io data/Superstore.xls', sheet_name='Returns')
people = pd.read_excel('io data/Superstore.xls', sheet_name='People')
orders.head(1)
returns.head()
people.head()<jupyter_output><empty_output><jupyter_text>read_html* read_html() function can accept an HTML string/file/URL and will parse HTML tables into list of pandas DataFrames* read_html returns a list of DataFrame objects, even if there is only a single table contained in the HTML content.<jupyter_code>import pandas as pd
url = 'http://www.fdic.gov/bank/individual/failed/banklist.html'
dfs = pd.read_html(url)
dfs
dfs[0].head()
url_mcc = 'https://en.wikipedia.org/wiki/Mobile_country_code'
dfs = pd.read_html(url_mcc)
dfs
dfs[0]
dfs[1]
dfs[2]
dfs[3]<jupyter_output><empty_output><jupyter_text>Reading and writing using Numpy savetxt, loadtxt<jupyter_code>import numpy as np<jupyter_output><empty_output><jupyter_text>* Saving textfiles with savetxt* we define an array x and save it as a textfile with savetxt* complete syntax `savetxt(fname, X, fmt='%.18e', delimiter=' ', newline='\n', header='', footer='', comments=' ')`<jupyter_code>x = np.array([[1, 2, 3], 
              [4, 5, 6],
              [7, 8, 9]], np.int32)
np.savetxt('io data/nptest.txt', x, delimiter=",")
y = np.loadtxt('io data/nptest.txt', delimiter=",", usecols=(0,2))
print(y)<jupyter_output>[[1. 3.]
 [4. 6.]
 [7. 9.]]<jupyter_text>save, load<jupyter_code>x = np.arange(10)
x
np.save('io data/nptest2.npy', x)
y = np.load('io data/nptest2.npy')
y<jupyter_output><empty_output><jupyter_text>genfromtxt<jupyter_code>z = np.genfromtxt('io data/Data.csv', delimiter=",", dtype=None, encoding=None)
z
z[0]
z[1]<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12396295581709435, 'fraction_numerical': 0.009068107273779664, 'mean_word_length': 4.743490304709141, 'pattern_counts': {'":': 0, '<': 74, '<?xml version=': 0, '>': 74, 'https://': 1, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '21', 'dir': 'jupyter-structured-clean-dedup', 'id': '415376', 'license': 'MIT', 'n_tokens_mistral': 3612, 'n_tokens_neox': 3500, 'n_words': 1183, 'path': 'Python-Programming/Teaching-Material/reading-and-writing-data.ipynb', 'repo_name': 'vivekparasharr/Learn-Programming', 'size': '10366'}