<jupyter_start><jupyter_text>**We'd learn how to import data from a large variety of import data sources. For example:-1. Flat files i.e .txt, .csv2. Files native to other software such as .xls, Stata, SAS and Matlab files3. Relational Databases such as Sqlite, Postgresql.All these and some will be covered.** First we start with basic text files, which are of two types:- The plain text such as a novel script and those containing records such as titanic.csv in which each row is a unique observation, and each column is a characteristic or feature such as titanic-passenger-class or sex or cabin.  Chapter One: Introduction and Flat Files.  Reading a Text file:<jupyter_code># Let's upload the moby_dick.txt file into colab.

from google.colab import files
uploaded = files.upload()<jupyter_output><empty_output><jupyter_text>Let's inspect the first 5 lines of the txt file<jupyter_code>! head -5 'moby_dick.txt'
file_link = 'moby_dick.txt'
with open(file_link, 'r') as new_file:
    read_file = new_file.read()
print(read_file)<jupyter_output>CHAPTER 1. Loomings.

Call me Ishmael. Some years ago--never mind how long precisely--having
little or no money in my purse, and nothing particular to interest me on
shore, I thought I would sail about a little and see the watery part of
the world. It is a way I have of driving off the spleen and regulating
the circulation. Whenever I find myself growing grim about the mouth;
whenever it is a damp, drizzly November in my soul; whenever I find
myself involuntarily pausing before coffin warehouses, and bringing up
the rear of every funeral I meet; and especially whenever my hypos get
such an upper hand of me, that it requires a strong moral principle to
prevent me from deliberately stepping into the street, and methodically
knocking people's hats off--then, I account it high time to get to sea
as soon as I can. This is my substitute for pistol and ball. With a
philosophical flourish Cato throws himself upon his sword; I quietly
take to the ship. There is nothing surprising in this. If th[...]<jupyter_text>Let'see the files in our current working directory<jupyter_code>! ls<jupyter_output>drive  moby_dick.txt  sample_data<jupyter_text>Importing text files line by lineFor large files, we may not want to print all of their content to the shell: you may wish to print only the first few lines. Enter the readline() method, which allows you to do this. When a file called file is open, you can print out the first line by executing file.readline(). If you execute the same command again, the second line will print, and so on.<jupyter_code># Read & print the first 3 lines of moby_dict.txt
with open('moby_dick.txt', 'r') as files:
    for i in range(3):
        print(files.readline())<jupyter_output>CHAPTER 1. Loomings.



Call me Ishmael. Some years ago--never mind how long precisely--having<jupyter_text>The importance of flat files in data science After working with plain text files like moby_dick.txt, now we would look at flat files such as titanic.csv in which each row is a unique passenger on board and each column a feature or characteristic of each passenger such as age, gender, e.t.c.**Flat Files:** Are basic text files containing records, that is table data without structured relationships. Flatfiles consist of recordsThis is in contrast to a relational database for example in which columns of distinct tables can be related. Note that a flatfile can have a header, which is usually the first row and describes the contents of the data columns.The reason that flat files are so important in Data Science is that Data Scientists really like to think in records or rows of attributes. Values in flat files may be separated by a tab or a comma or any other character. These separating characters are called a **delimiter**.  How do we import Flat files? If they consist entirely of numbers and we want to store them in a numpy array, we could very well use numpy. If instead we want to store the data in a dataframe, we can use pandas. **Why we like flat files and the Zen of Python...**In PythonLand, there are currently hundreds of Python Enhancement Proposals, commonly referred to as PEPs. PEP8, for example, is a standard style guide for Python, written by our sensei <NAME> himself. It is the basis for how we here at DataCamp ask our instructors to style their code. Another one of my favorites is PEP20, commonly called the Zen of Python. Its abstract is as follows:Import the zen of python below.<jupyter_code>import this<jupyter_output>The Zen of Python, by <NAME>

Beautiful is better than ugly.
Explicit is better than implicit.
Simple is better than complex.
Complex is better than complicated.
Flat is better than nested.
Sparse is better than dense.
Readability counts.
Special cases aren't special enough to break the rules.
Although practicality beats purity.
Errors should never pass silently.
Unless explicitly silenced.
In the face of ambiguity, refuse the temptation to guess.
There should be one-- and preferably only one --obvious way to do it.
Although that way may not be obvious at first unless you're Dutch.
Now is better than never.
Although never is often better than *right* now.
If the implementation is hard to explain, it's a bad idea.
If the implementation is easy to explain, it may be a good idea.
Namespaces are one honking great idea -- let's do more of those!<jupyter_text>Importing Flat files using Numpy If all data in the given flatfile are numerical, we could use the package numpy. First: numpy arrays are the python standard for storing numerical data. they are efficient, fast and clean.Second: numpy arrays are often essential for other packages such as scikit learn, a popular python ML library. Enter the numpy functions **loadtxt()** and **genfromtxt()** With numpy we can import pure numeric data or the numeric columns from a mixed data set by passing some attributes to the import statement```import numpy as npfile_name  = 'MNIST.txt'data = np.loadtxt(file_name, delimiter=',', skiprows=1, usecols=[0,2])```The above codes imports the MNIST.txt data set as file_name, then it skips row 1, which is the string column-header row, since all we want is numeric data. Then we also specify that we import only the first and third columns. ```file_name  = 'MNIST.txt'data = np.loadtxt(file_name, delimiter=',', skiprows=1, usecols=[0,2], dtype=str)```Adding the dtype paarmeter gives us the ability to specify the output type of the data we are importing.Lets see this in practice<jupyter_code>import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
titanic_df = sns.load_dataset('titanic')
titanic_df.head()<jupyter_output><empty_output><jupyter_text>So to import this data in numpy, let's do so without the column header row and just the:- survived, Pclass and Fare cols<jupyter_code># First let's save the data frame as a CSV file
titanic_df.to_csv('titanic.csv', index=False)
titanic_arr = np.loadtxt('titanic.csv', delimiter = ',', skiprows=1, usecols=[0,1,4])  # Note that usecols param takes only integer values
titanic_arr
titanic_arr.dtype<jupyter_output><empty_output><jupyter_text>The above loads the titanic data set without the column header and with only the specified column index _**Note that although numpy can handle data of mixed types, it's not best practice to do so. Put mixed data types in a pandas data frame instead.**_  Working with mixed datatypes (1)Much of the time you will need to import datasets which have different datatypes in different columns; one column may contain strings and another floats, for example. The function np.loadtxt() will freak at this. There is another function, np.genfromtxt(), which can handle such structures. If we pass dtype=None to it, it will figure out what types each column should be<jupyter_code>try:
    data = np.genfromtxt('titanic.csv', delimiter=',', names=True, dtype=None, encoding='utf8')
except Exception as e:
    print(type(e))<jupyter_output><empty_output><jupyter_text>Here, the first argument is the filename, the second specifies the delimiter , and the third argument names tells us there is a header. Because the data are of different types, data is an object called a structured array. Because numpy arrays have to contain elements that are all the same type, the structured array solves this by being a 1D array, where each element of the array is a row of the flat file imported. You can test this by checking out the array's shape in the shell by executing np.shape(data).<jupyter_code>np.shape(data)
# Let's see the elements of the first row of data
data[0]
# Let's see the elements of the first 5 rows of data
data[:5]
# Let's see the first 0 elements in the fare column of data
data['fare'][:50]<jupyter_output><empty_output><jupyter_text>Working with mixed datatypes (2)You have just used np.genfromtxt() to import data containing mixed datatypes. There is also another function np.recfromcsv() that behaves similarly to np.genfromtxt(), except that its default dtype is None. In this exercise, you'll practice using this to achieve the same result. Import titanic.csv using the function np.recfromcsv() and assign it to the variable, d. You'll only need to pass file to it because it has the defaults delimiter=',' and names=True in addition to dtype=None!<jupyter_code># function np.recfromcsv() has the defaults delimiter=',' and names=True in addition to dtype=None!
# So we only pass it the file to read.
d = np.recfromcsv('titanic.csv')
d['fare'][:50]<jupyter_output><empty_output><jupyter_text>Using pandas to import flat files as DataFrames (1)In the last exercise, you were able to import flat files containing columns with different datatypes as numpy arrays. However, the DataFrame object in pandas is a more appropriate structure in which to store such data and, thankfully, we can easily import files of mixed data types as DataFrames using the pandas functions read_csv() and read_table().<jupyter_code>titanic_df = pd.read_csv('titanic.csv')
titanic_df.head()<jupyter_output><empty_output><jupyter_text>Using pandas to import flat files as DataFrames (2)In the last exercise, you were able to import flat files into a pandas DataFrame. As a bonus, it is then straightforward to retrieve the corresponding numpy array using the attribute values. You'll now have a chance to do this using the MNIST dataset, which is available as digits.csv.<jupyter_code>! wget 'https://assets.datacamp.com/production/repositories/487/datasets/d6d1b84ef06151ff913b4173e2eca8e6d5fa959b/mnist_kaggle_some_rows.csv'<jupyter_output>--2020-06-07 12:53:24--  https://assets.datacamp.com/production/repositories/487/datasets/d6d1b84ef06151ff913b4173e2eca8e6d5fa959b/mnist_kaggle_some_rows.csv
Resolving assets.datacamp.com (assets.datacamp.com)... 172.16.58.3, 172.16.58.3, 192.168.127.12, ...
Connecting to assets.datacamp.com (assets.datacamp.com)|172.16.58.3|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 338477 (331K)
Saving to: ‘mnist_kaggle_some_rows.csv’


          mnist_kag   0%[                    ]       0  --.-KB/s               
mnist_kaggle_some_r 100%[===================>] 330.54K  --.-KB/s    in 0.04s   

2020-06-07 12:53:25 (7.86 MB/s) - ‘mnist_kaggle_some_rows.csv’ saved [338477/338477]<jupyter_text>Import the first 5 rows of the file into a DataFrame using the function pd.read_csv() and assign the result to data. You'll need to use the arguments nrows and header (there is no header in this file).<jupyter_code>data = pd.read_csv('mnist_kaggle_some_rows.csv', header=None, nrows=5)
data
# Now convert data to a numpy array
data_array = data.values
type(data_array)<jupyter_output><empty_output><jupyter_text>Customizing your pandas importThe pandas package is also great at dealing with many of the issues you will encounter when importing data as a data scientist, such as comments occurring in flat files, empty lines and missing values. Note that missing values are also commonly referred to as NA or NaN. To wrap up this chapter, you're now going to import a slightly corrupted copy of the Titanic dataset titanic_corrupt.txt, which:-contains comments after the character ''is tab-delimited.``` Import file: datadata = pd.read_csv(file, sep='\t', comment='', na_values='Nothing')```  Chapter Two: Importing data from other file types  Introduction to Other File Types: This chapter involves learning how to import:1. Excel flies2. MATLAB files3. SAS files4. Stata files5. HDF5 files... _these are becoming a more prevalent way to store large data sets. LIGO researchers used it to store their data._6. Pickled files... _this is a file type native to python. the concept of pickling a file is motivated by the fact that while it may be easy to save a numpy array or a pandas dataframe to a flat flie, there are many other data types such as dictionaries and lists, which isn't so obvious how to store them... Pickle to the rescue!_If we want files to be human-readable it makes sense to save them in a clever manner. **JSON** files are appropriate for python dictionaries._Pickled files are serialized. This means they're converted into a sequence of bytes or bytestream._ We'd learn how to import Pickled files too.  Importing excel Sheets```import pandas as pdfile = 'urbanpop.xlsx'data = pd.ExcelFile(file)print(data.sheet_names)```The data.sheet_names gives us the names of the sheets in the Excel file. for eaxmple,```['1960-1966', '1967-1974', '1975-2011']```If we want to load any of the above sheets as a pandas data frame, all we need do is either use the sheet name or its'index:```df1 = data.parse('1967-1974')  pass the sheet name as a stringdf2 = data.parse(0)  Here we pass the index of the sheet name as an Int.```Pandas is clever enough to know if you're telling it the sheet name or the index.  We'd also learn how toA. Customize import statetmentsB. Skip rowsC. Import certain columnsD. Change column names... _and more_  Not so Flat anymore In Chapter 1, you learned how to use the IPython magic command ! ls to explore your current working directory. You can also do this natively in Python using the library os, which consists of miscellaneous operating system interfaces.The first line of the following code imports the library os, the second line stores the name of the current directory in a string called wd and the third outputs the contents of the directory in a list to the shell.<jupyter_code># lets import the OS module
import os
# lets save the name of the current working directory to a variable
wd = os.getcwd()
print(wd)

# Finally let's list the files in the current working directory
os.listdir(wd)<jupyter_output>/content<jupyter_text>Loading a pickled fileThere are a number of datatypes that cannot be saved easily to flat files, such as lists and dictionaries. If you want your files to be human readable, you may want to save them as text files in a clever manner. JSONs, which you will see in a later chapter, are appropriate for Python dictionaries.However, if you merely want to be able to import them into Python, you can serialize them. All this means is converting the object into a sequence of bytes, or a bytestream.In this exercise, you'll import the pickle package, open a previously pickled data structure from a file and load it.``` Import pickle packageimport pickle Open pickle file and load data: dwith open('data.pkl', 'rb') as file:    d = pickle.load(file) Print dprint(d) Print datatype of dprint(type(d))``` Let's load a huge movie ratings data set as a pickle object.<jupyter_code># First let's download the file from Github
! rm ratings.csv
! rm moviedataset.zip
! rm movies.csv
! rm README.txt 
! rm tags.csv 
!wget -O moviedataset.zip https://s3-api.us-geo.objectstorage.softlayer.net/cf-courses-data/CognitiveClass/ML0101ENv3/labs/moviedataset.zip
print('unziping ...')
!unzip -o -j moviedataset.zip
! rm moviedataset.zip
%ls
# Now that we've downloaded the ratings.csv file let's save it as a pickle object
import pickle
 
# Let's create a byte-stream placeholder object named 'ratings.pickle'
pickle_out = open('ratings.pickle','wb')

# Now let's dump the 'ratings.csv' data set into 'ratings.pickle'
pickle.dump('ratings.csv',pickle_out)

# Finally, let's close the connection
pickle_out.close()
# Now we can see the ratings.pickle object saved in our workspace.
%ls 
# Let's see what type of data structure the ratings.pickle object is
print(type('ratings.pickle'))<jupyter_output><class 'str'><jupyter_text>Let's define a method for comparing the sizes of our data files in bytes<jupyter_code>def compare_file_sizes(file_name1, file_name2):
    """Compare bytes size of file_name1 to file_name2.

    @Param:
    file_name1 is a string object that is the name of an existing file
    file_name2 is a string object that is the name of an existing file

    @Return
    Returns a float that indicates the number of times that
    file_name1 is smaller than file_name2

    It also prints out the sizes of file_name1 and file_name2
    
    """

    import os
    size_one = os.path.getsize('/content/'+file_name1)
    size_two = os.path.getsize('/content/'+file_name2)

    print(f'{file_name1} has {size_one} bytes, while {file_name2} has {size_two} bytes of data.')
    print(f'{file_name2} is bigger than {file_name1} by:-')
    
    return (100 / ((size_one / size_two) * 100)), 'times.' 

compare_file_sizes('ratings.pickle', 'ratings.csv')<jupyter_output>ratings.pickle has 21 bytes, while ratings.csv has 620204630 bytes of data.
ratings.csv is bigger than ratings.pickle by:-<jupyter_text>Next, let's unpickle the ratings.pickle object into a Pandas Data Frame<jupyter_code># Open pickle file and load data:
with open('ratings.pickle', 'rb') as f:
    ratings_data = pickle.load(f)
 
# Print ratings_data
print(ratings_data)
 
# Print datatype of ratings_data
print(type(ratings_data))
ratings_df = pd.read_csv(ratings_data)
ratings_df.head()
# Let's see the shape of the ratings_df
ratings_df.shape<jupyter_output><empty_output><jupyter_text>Listing sheets in Excel filesWhether you like it or not, any working data scientist will need to deal with Excel spreadsheets at some point in time. You won't always want to do so in Excel, however!Here, you'll learn how to use pandas to import Excel spreadsheets and how to list the names of the sheets in any loaded .xlsx file.Recall from the video that, given an Excel file imported into a variable `spreadsheet`, you can retrieve a list of the sheet names using the attribute `spreadsheet.sheet_names`. This data contains age-adjusted mortality rates due to war in various countries over several years.<jupyter_code>!curl -o battle_link.xlsx -L https://assets.datacamp.com/production/repositories/487/datasets/5e8897e4624f8577ed0d33aeafbe7bd88bfc424b/battledeath.xlsx
%ls<jupyter_output>Album.csv         InvoiceLine.csv                    ratings.csv
Artist.csv        ja_data2.mat                       ratings.parquet
battle_link.xlsx  links.csv                          ratings.pickle
Chinook.sqlite    L-L1_LOSC_4_V1-1126259446-32.hdf5  README.txt
Customer.csv      MediaType.csv                      sales.sas7bdat
disarea.dta       mnist_kaggle_some_rows.csv         [0m[01;34msample_data[0m/
[01;34mdrive[0m/            moby_dick.txt                      tags.csv
Employee.csv      movies.csv                         titanic.csv
Genre.csv         Playlist.csv                       Track.csv
Invoice.csv       PlaylistTrack.csv<jupyter_text>Let's load the excel file into an io.excel base file<jupyter_code>xls = pd.ExcelFile('battle_link.xlsx')
type(xls)
# Let's print the sheet names 
xls.sheet_names<jupyter_output><empty_output><jupyter_text>Importing sheets from Excel filesIn the previous exercises, you saw that the Excel file contains two sheets, '2002' and '2004'. The next step is to import these.In this exercise, you'll learn how to import any given sheet of your loaded .xlsx file as a DataFrame. You'll be able to do so by specifying either the sheet's name or its index. First let's load a sheet by its name<jupyter_code># Load a sheet into a DataFrame by name: df1
df1 = xls.parse('2004')

# Print the head of the DataFrame df1
df1.head()<jupyter_output><empty_output><jupyter_text>Next let's load a sheet by its index<jupyter_code># Load a sheet into a DataFrame by index: df2
df2 = xls.parse(0)

# Print the head of the DataFrame df2
df2.head()<jupyter_output><empty_output><jupyter_text>Customizing your spreadsheet importHere, you'll parse your spreadsheets and use additional arguments to skip rows, rename columns and select only particular columns.As before, you'll use the method parse(). This time, however, you'll add the additional arguments skiprows, names and usecols. These skip rows, name the columns and designate which columns to parse, respectively. All these arguments can be assigned to lists containing the specific row numbers, strings and column numbers, as appropriate.<jupyter_code># Parse the first sheet and rename the columns: df1
# Note that The values passed to skiprows and names all need to be of type list.
df1 = xls.parse(0, skiprows=[1], names=['Country','AAM due to War (2002)'])

# Print the head of the DataFrame df1
df1.head()
# Parse the first column of the second sheet and rename the column: df2
# Note The argument passed to usecols also needs to be of type list.
df2 = xls.parse(1, usecols=[0], skiprows=[1], names=['Country'])

# Print the head of the DataFrame df2
df2.head()<jupyter_output><empty_output><jupyter_text>Using pd.read_excel() function and customising it for imports<jupyter_code>test = pd.read_excel('battle_link.xlsx')
test.head()
data_2 = pd.read_excel('battle_link.xlsx', sheet_name='2004', usecols=[0], skiprows=0, names=['War_Country'])
data_2.head()<jupyter_output><empty_output><jupyter_text>Importing SAS/Stata files using pandasSAS files stand for Statistical Analysis SoftwareSTATA is a contraction for for Statistics and Data  Importing SAS filesIn this exercise, you'll figure out how to import a SAS file as a DataFrame using SAS7BDAT and pandas<jupyter_code># first let's import the SAS file library
try:
    from sas7bdat import SAS7BDAT
except:
    ! pip install sas7bdat
    from sas7bdat import SAS7BDAT<jupyter_output><empty_output><jupyter_text>Let's load a SAS file from Data Camp<jupyter_code>! rm sales.sas7bdat
! wget 'https://assets.datacamp.com/production/repositories/487/datasets/0300d44b3ac77accc4b9706af86e33037bda6861/sales.sas7bdat'<jupyter_output>--2020-06-07 13:56:10--  https://assets.datacamp.com/production/repositories/487/datasets/0300d44b3ac77accc4b9706af86e33037bda6861/sales.sas7bdat
Resolving assets.datacamp.com (assets.datacamp.com)... 192.168.127.12, 172.16.17.32, 172.16.58.3, ...
Connecting to assets.datacamp.com (assets.datacamp.com)|192.168.127.12|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 5120 (5.0K)
Saving to: ‘sales.sas7bdat’


sales.sas7bdat        0%[                    ]       0  --.-KB/s               
sales.sas7bdat      100%[===================>]   5.00K  --.-KB/s    in 0s      

2020-06-07 13:56:10 (816 MB/s) - ‘sales.sas7bdat’ saved [5120/5120]<jupyter_text>We shall use the with() to read in the SAS7BDAT file object and pass it the to_data_frame() func<jupyter_code>with SAS7BDAT('sales.sas7bdat') as files:
    df_sas = files.to_data_frame()
df_sas.head()<jupyter_output><empty_output><jupyter_text>Lets plot a histogram of the P column<jupyter_code>df_sas.P.hist()
plt.ylabel('count')
plt.title('Histogram of P-Values')
plt.show()<jupyter_output><empty_output><jupyter_text>Using read_stata to import Stata filesLet's now load a STATA file from Datacamp<jupyter_code>! rm disarea.dta
! wget https://assets.datacamp.com/production/repositories/487/datasets/c4129edae533cf2683d8995f6dcdbcf5f41520ba/disarea.dta
# we use the pd.read_stata function directly on the file
df = pd.read_stata('disarea.dta')
df.head()
# Plot histogram of one column of the DataFrame
df.disa10.hist()
plt.xlabel('Extent of disease')
plt.ylabel('Number of countries')
plt.title('Histogram of disa10 values')
plt.show()<jupyter_output><empty_output><jupyter_text>Importing HDF5 files into pandasAccording to the 2013 O'reilly book `Python and HDF5 by <NAME>`, in the Python world, concensus is rapidly converging on `Hierarchical Data Format Version 5` AKA `HDF5` as the standard mechanism for storing large quantities of numerical data. It;s now becoming common to deal with data sets hundreds of Gigabytes or Terabytes in size. `HDF5` itself can scale up to **Exabytes** of data. What is the structure of HDF5 files?We can explore its hierarchical structure just like Python dictionaries using the method keys(). ```for key in data.keys():    print(key)```This would return the existing keys in the data set. Each of these is an HDF group. They are like directories. Note that the HDF5 project is actively maintained by the HDF Group based in Champaign Illinois and formerly part of the university of Illinois, Urbana Champaign. Let's load the LIGO HDF5 dta set. LIGO stands for `Laser Interferometer Gravitational-Wave Observatory Project`. This project lead to the validation of Einsteins Theory of Gravitational waves.<jupyter_code>! rm L-L1_LOSC_4_V1-1126259446-32.hdf5
! wget https://assets.datacamp.com/production/repositories/487/datasets/ab9107b749b832daada36bfaa718d9a591a0d69c/L-L1_LOSC_4_V1-1126259446-32.hdf5<jupyter_output>--2020-06-07 13:56:25--  https://assets.datacamp.com/production/repositories/487/datasets/ab9107b749b832daada36bfaa718d9a591a0d69c/L-L1_LOSC_4_V1-1126259446-32.hdf5
Resolving assets.datacamp.com (assets.datacamp.com)... 192.168.127.12, 172.16.17.32, 172.16.58.3, ...
Connecting to assets.datacamp.com (assets.datacamp.com)|192.168.127.12|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 994831 (972K)
Saving to: ‘L-L1_LOSC_4_V1-1126259446-32.hdf5’


          L-L1_LOSC   0%[                    ]       0  --.-KB/s               
L-L1_LOSC_4_V1-1126 100%[===================>] 971.51K  --.-KB/s    in 0.06s   

2020-06-07 13:56:25 (15.7 MB/s) - ‘L-L1_LOSC_4_V1-1126259446-32.hdf5’ saved [994831/994831]<jupyter_text>Using h5py to import HDF5 filesThe hdf5 file is already in your working directory. In this exercise, you'll import it using the h5py library. You'll also print out its datatype to confirm you have imported it correctly. You'll then study the structure of the file in order to see precisely what HDF groups it contains.<jupyter_code>try:
    import h5py
    print('h5py imported')
except:
    ! pip install h5py
    import h5py
file_ = 'L-L1_LOSC_4_V1-1126259446-32.hdf5'
# Let's load the file as read only into the variable data
data = h5py.File(file_, 'r')
# Print the datatype of the loaded file
type(data)
# Print the keys of the file
data.keys()<jupyter_output><empty_output><jupyter_text>Extracting data from your HDF5 fileIn this exercise, you'll extract some of the LIGO experiment's actual data from the HDF5 file and you'll visualize it.To do so, you'll need to first explore the HDF5 group 'strain'.<jupyter_code>type(data['strain'])
group_strain = data['strain']

for key in group_strain.keys():
    print(key)
strain = data['strain']['Strain'].value  # Let's convert the Strain data to a numpy array.
strain
type(strain)
# Let's see the shape of the array
strain.shape<jupyter_output><empty_output><jupyter_text>Let's see a data frame of the Strain values<jupyter_code>df = pd.DataFrame(strain, columns=['value'])
df.head()<jupyter_output><empty_output><jupyter_text>Plotting the data<jupyter_code># Set number of time points to sample: num_samples
num_samples = 10000

# Set time vector
time = np.arange(0, 1, 1/num_samples)
# Plot data
plt.plot(time, strain[:num_samples])
plt.xlabel('GPS Time (s)')
plt.ylabel('strain')
plt.title('Time/Strain Chart')
plt.show()<jupyter_output><empty_output><jupyter_text>Importing Matlab filesMatlab, short for Matrix Laboratory is a numerical computing environment that is an industry standard in the disciplines of engineering and science.**SCIPY to the rescue!!**We import .mat files from matlab into python using Scipy```scipy.io.loadmat()   read .mat filesscipy.io.savemat()   write .mat files```Another example```import scipy.iofile_name = 'workspace.mat'mat = scipy.io.loadmat(filename)print(type(mat)) How this dict object relates to a Matlab workspace is straight forward:- The keys of the python dict are the Matlab variable names and the values of the python dict are the objects that are assigned to the variables.```  Loading .mat filesIn this exercise, you'll figure out how to load a MATLAB file using scipy.io.loadmat() and you'll discover what Python datatype it yields.Download the 'albeck_gene_expression.mat' is in your working directory. This file contains gene expression data from the Albeck Lab at UC Davis. You can find the data and some great documentation here. Let's download a .mat file from DataCamp home<jupyter_code>! rm ja_data2.mat
! wget https://assets.datacamp.com/production/repositories/487/datasets/2fc0beea2d8cc7c93d79e79344a6e9e66f65d1fe/ja_data2.mat
import scipy.io
# let's load the .mat file in
mat = scipy.io.loadmat('ja_data2.mat')
# Print the type of mat object
print(type(mat))<jupyter_output><class 'dict'><jupyter_text>The structure of .mat in PythonHere, you'll discover what is in the MATLAB dictionary that you loaded in the previous exercise.The file 'albeck_gene_expression.mat' is already loaded into the variable mat. The following libraries have already been imported as follows:Once again, this file contains gene expression data from the Albeck Lab at UCDavis. You can find the data and some great documentation here.<jupyter_code># It's a dictionary so let's see the keys or variable names.

for key in mat.keys():
    print(key)
# Let's see what type of object the CyratioCyt object is
print(type(mat['CYratioCyt']))
# Since it's a Numpy array, lets see its shape and possibly read it in pandas
mat['CYratioCyt'].shape
Cy_df = pd.DataFrame(mat['CYratioCyt'])
Cy_df.head()<jupyter_output><empty_output><jupyter_text>Subset the array and plot it<jupyter_code>data = mat['CYratioCyt'][25, 5:]
fig = plt.figure()
plt.plot(data)
plt.xlabel('time (min.)')
plt.ylabel('normalized fluorescence (measure of expression)')
plt.show()<jupyter_output><empty_output><jupyter_text>Loading and Importing Parquet Files in Python:<jupyter_code>import pandas as pd

def write_parquet_file(file_name):
    """Convert a file to a Data Frame, then to a Parquet file object.
    
    @Param:
    file_name is the string name of a file plus its extension

    @Return:
    Returns None, but saves the parquet file in current working directory.
    """
    df = pd.read_csv(file_name)
    df.to_parquet(file_name.split('.')[0] + '.parquet')
write_parquet_file('ratings.csv')
# Let's check for the ratings.parquet file in our workspace.
%ls<jupyter_output>Album.csv         InvoiceLine.csv                    ratings.csv
Artist.csv        ja_data2.mat                       ratings.parquet
battle_link.xlsx  links.csv                          ratings.pickle
Chinook.sqlite    L-L1_LOSC_4_V1-1126259446-32.hdf5  README.txt
Customer.csv      MediaType.csv                      sales.sas7bdat
disarea.dta       mnist_kaggle_some_rows.csv         [0m[01;34msample_data[0m/
[01;34mdrive[0m/            moby_dick.txt                      tags.csv
Employee.csv      movies.csv                         titanic.csv
Genre.csv         Playlist.csv                       Track.csv
Invoice.csv       PlaylistTrack.csv<jupyter_text>Next, let's compare the size of ratings.parquet to ratings.csv<jupyter_code>compare_file_sizes('ratings.parquet', 'ratings.csv')<jupyter_output>ratings.parquet has 149275049 bytes, while ratings.csv has 620204630 bytes of data.
ratings.csv is bigger than ratings.parquet by:-<jupyter_text>We can see that ratings.parquet file is slightly over 4 times smaller than the original ratings.csv file.Next, let's see how to read the ratings.parquet file as a Pandas Data Frame. Luckily pandas has a built-in pandas.read_parquet() function.<jupyter_code>parquet_df = pd.read_parquet('/content/ratings.parquet')
parquet_df.head()<jupyter_output><empty_output><jupyter_text>Chapter Three: Working with relational databases in Python<jupyter_code>import numpy as np
import pandas as pd
import matplotlib.pyplot as plt<jupyter_output><empty_output><jupyter_text>What is a relational database?It's a type of data base that's based on the relational model of data, first described by `<NAME>` in the late 1960s. A data base consists of tables. A table generally represents one entity type.A table does look like a data frame and that's the point. In a relational data base table, each row or record represents an instance of the entity type, each column represents an attribute of each instance. In this sense,  a table is entirely analogous to a Data Frame.It's essential that each row has a Unique identifier known as a primary key, to access the row in case. Recall that a database consists of many tables, the really cool thing about a relational DB is not just the bunch of tables, but the fact that the tables are linked. The relational model has been widely adopted.`Codds' 12 Rules` also known as `Codds 12 commandments`which he developed in the early 1980s' describe what a relational DBMS should adhere to eb considered relational. His rules are actually 13, but it's zero indexed so first rule is index zero.Amongst the most popular RDBMS are `PostgreSQL`, `MySQL` and `SQLite`, all of which use the SQL query language.   Creating a database engineHere, you're going to fire up your very first SQL engine. You'll create an engine to connect to the SQLite database 'Chinook.sqlite', which is in your working directory. Remember that to create an engine to connect to 'Northwind.sqlite', Hugo executed the commandengine = create_engine('sqlite:///Northwind.sqlite')Here, 'sqlite:///Northwind.sqlite' is called the connection string to the SQLite database Northwind.sqlite. A little bit of background on the Chinook database: the Chinook database contains information about a semi-fictional digital media store in which media data is real and customer, employee and sales data has been manually created. The term 'Querying' is just a fancy way of saying, getting data out from te database.<jupyter_code>! rm Chinook.sqlite
! wget https://assets.datacamp.com/production/repositories/487/datasets/ec8aa8bc9ffea6b4e2729e1a0a2d4aea2f300b3a/Chinook.sqlite
# Import necessary module
from sqlalchemy import create_engine
# Create engine: engine
engine = create_engine('sqlite:///Chinook.sqlite')<jupyter_output><empty_output><jupyter_text>What are the tables in the database?save the table names to a list using the method table_names() on the engine and then you will print the list.<jupyter_code>table_names = engine.table_names()
table_names<jupyter_output><empty_output><jupyter_text>Now to read data from the Chinnok Database we downloaded with wget, we need the following1. To instantiate a create_engine_object() which we have done 2. A connection object to the engine object3. A connection execute object to pass the sql query to the connection object 4. pandas to store the retrieved data as a data frame5. Close the connectionSee an example of the steps below:```from sqlalchemy import create engineengine = create_engine('sqlite:///Chinook.sqlite')  con = engine.connect()rs = con.execute('select * from Album')df = pd.DataFrame(rs.fetchall())df.columns = rs.keys()  To set column names to the header in the tablecon.close()``` Let's do so in code<jupyter_code># Open engine connection: con
con = engine.connect()

# Perform query: rs
rs = con.execute("select * from Album")

# Save results of the query to DataFrame: df
album_df = pd.DataFrame(rs.fetchall())

# Close connection
con.close()

# Print head of DataFrame df
album_df.head()<jupyter_output><empty_output><jupyter_text>It looks okay, but the column names dont seem right. Let's set it to the headers in the table<jupyter_code># Open engine connection: con
con = engine.connect()

# Perform query: rs
rs = con.execute("select * from Album")

# Save results of the query to DataFrame: df
album_df = pd.DataFrame(rs.fetchall())

# set column names
album_df.columns = rs.keys()

# Close connection
con.close()

# Print head of DataFrame df
album_df.head()<jupyter_output><empty_output><jupyter_text>Perfect!!  Customizing the Hello World of SQL QueriesCongratulations on executing your first SQL query! Now you're going to figure out how to customize your query in order to:Select specified columns from a table;Select a specified number of rows;Import column names from the database table.The code looks like this```engine = create_engine('sqlite:///Northwind.sqlite')with engine.connect() as con:    rs = con.execute("SELECT OrderID, OrderDate, ShipName FROM Orders")    df = pd.DataFrame(rs.fetchmany(size=5))   we use fetchmany to select num of rows    df.columns = rs.keys()``` Let's see the Employee table column names so we can select a few of them<jupyter_code># Establish a connection to the sqlite engine
con = engine.connect()

# Execute an sql query ad save the result in rs
rs = con.execute('Select * from Employee')

# Print the keys
print(rs.keys())<jupyter_output>['EmployeeId', 'LastName', 'FirstName', 'Title', 'ReportsTo', 'BirthDate', 'HireDate', 'Address', 'City', 'State', 'Country', 'PostalCode', 'Phone', 'Fax', 'Email']<jupyter_text>Now Execute the SQL query that selects the columns FirstName, LastName and Title from the Employee table. Store the results in the variable rs.<jupyter_code>with engine.connect() as con:
    rs = con.execute("select FirstName, LastName, Title from Employee")
    employee_df = pd.DataFrame(rs.fetchmany(5), columns=rs.keys()) # we fetch only 5 rows with their headers
employee_df
len(employee_df)<jupyter_output><empty_output><jupyter_text>Now let's read in the entire Employee table<jupyter_code>with engine.connect() as con:
    rs = con.execute("select * from Employee")
    employee_df = pd.DataFrame(rs.fetchall(), columns=rs.keys()) # we fetch all rows
employee_df
# let's see the shape
employee_df.shape<jupyter_output><empty_output><jupyter_text>Filtering your database records using SQL's WHEREYou can now execute a basic SQL query to select records from any table in your database and you can also perform simple query customizations to select particular columns and numbers of rows.There are a couple more standard SQL query chops that will aid you in your journey to becoming an SQL ninja.Let's say, for example that you wanted to get all records from the Customer table of the Chinook database for which the Country is 'Canada'. You can do this very easily in SQL using a SELECT statement followed by a WHERE clause as follows:```SELECT * FROM Customer WHERE Country = 'Canada'```In fact, you can filter any SELECT statement by any condition using a WHERE clause. This is called filtering your records.Execute the query that selects all records from the Employee table where 'EmployeeId' is greater than or equal to 6. Use the >= operator and assign the results to rs.Apply the method fetchall() to rs in order to fetch all records in rs. Store them in the DataFrame df.Using the rs object, set the DataFrame's column names to the corresponding names of the table columns.<jupyter_code># As usual lets establish a connection to the engine
with engine.connect() as con:
    rs = con.execute('select * from Employee where EmployeeId >= 6')  # select all records where EmployeeID >= 6
    df = pd.DataFrame(rs.fetchall(), columns=rs.keys())
df<jupyter_output><empty_output><jupyter_text>Ordering your SQL records with ORDER BYYou can also order your SQL query results. For example, if you wanted to get all records from the Customer table of the Chinook database and order them in increasing order by the column SupportRepId, you could do so with the following query:`"SELECT * FROM Customer ORDER BY SupportRepId"`In fact, you can order any SELECT statement by any column.In this interactive exercise, you'll select all records of the Employee table and order them in increasing order by the column BirthDate.<jupyter_code># Let's establish a connection to the data base
with engine.connect() as con:
    rs = con.execute('select * from Employee order by BirthDate asc')
    df = pd.DataFrame(rs.fetchall(), columns=rs.keys())
df<jupyter_output><empty_output><jupyter_text>Querying relational databases directly with pandaswith just 4 lines of code we can read data from a relational database straight to pandas. that's cool. But imagine reading it in one line!!Introducing pandas: `pd.read_sql_query()` function. It takes 2 arguments. The first will be the quey string and the second the database connection engine object `engine` that we want to connect to. Pandas and The Hello World of SQL Queries!Here, you'll take advantage of the power of pandas to write the results of your SQL query to a DataFrame in one swift line of Python code!<jupyter_code># let's re-create the connection to the database engine
engine = create_engine('sqlite:///Chinook.sqlite')

# let's connect to the engine using pandas to read in the Customer table staright to a data frame
customer_df = pd.read_sql_query('select * from Customer', engine)
customer_df.head()
customer_df.shape<jupyter_output><empty_output><jupyter_text>Notice that pandas reads it in fully with column names in place. Pandas for more complex queryingHere, you'll become more familiar with the pandas function read_sql_query() by using it to execute a more complex query: a SELECT statement followed by both a WHERE clause AND an ORDER BY clause.You'll build a DataFrame that contains the rows of the Customer table for which the CustomerId is greater than or equal to 35 and you'll order these entries by Country descending alphabetically.<jupyter_code>df = pd.read_sql_query('select * from Customer where CustomerId >= 35 order by Country desc', engine)
# the above code selects customer data for customers with Ids >= 35 and orders by Country names alphabetically from Z to A.
df.head()<jupyter_output><empty_output><jupyter_text>Let's read all the tables into individual data frames using pd.read_sql_query()<jupyter_code>for table in table_names:
    query = 'select * from ' + table
    df = pd.read_sql_query(query, engine)
    df.to_csv(table+'.csv',encoding='utf-8')
# let's check for the files
! ls<jupyter_output>Album.csv	  InvoiceLine.csv		     ratings.csv
Artist.csv	  ja_data2.mat			     ratings.parquet
battle_link.xlsx  links.csv			     ratings.pickle
Chinook.sqlite	  L-L1_LOSC_4_V1-1126259446-32.hdf5  README.txt
Customer.csv	  MediaType.csv			     sales.sas7bdat
disarea.dta	  mnist_kaggle_some_rows.csv	     sample_data
drive		  moby_dick.txt			     tags.csv
Employee.csv	  movies.csv			     titanic.csv
Genre.csv	  Playlist.csv			     Track.csv
Invoice.csv	  PlaylistTrack.csv<jupyter_text>So yay!! we can see all our tables saved away as CSV files that we can load in pandas. Let's load the Genre.csv file<jupyter_code>genre_df = pd.read_csv('Genre.csv')
genre_df.head()<jupyter_output><empty_output><jupyter_text>We seem to be getting double index. let's do it again but set index to false<jupyter_code>for table in table_names:
    query = 'select * from ' + table
    df = pd.read_sql_query(query, engine)
    df.to_csv(table+'.csv',encoding='utf-8', index=False)
# let's see the genre table again
genre_df = pd.read_csv('Genre.csv')
genre_df.head()<jupyter_output><empty_output><jupyter_text>Much better!! let's also check the Invoice table<jupyter_code>invoice_df = pd.read_csv('Invoice.csv')
invoice_df.head()<jupyter_output><empty_output><jupyter_text>Advanced querying: exploiting table relationships The power of SQL lies in relationships between tables: INNER JOINHere, you'll perform your first INNER JOIN! You'll be working with your favourite SQLite database, Chinook.sqlite. For each record in the Album table, you'll extract the Title along with the Name of the Artist. The latter will come from the Artist table and so you will need to INNER JOIN these two tables on the ArtistID column of both.Recall that to INNER JOIN the Orders and Customers tables from the Northwind database, Hugo executed the following SQL query:`"SELECT OrderID, CompanyName FROM Orders INNER JOIN Customers on Orders.CustomerID = Customers.CustomerID"` Assign to rs the results from the following query: select all the records, extracting the Title of the record and Name of the artist of each record from the Album table and the Artist table, respectively. To do so, INNER JOIN these two tables on the ArtistID column of both.In a call to pd.DataFrame(), apply the method fetchall() to rs in order to fetch all records in rs. Store them in the DataFrame df.Set the DataFrame's column names to the corresponding names of the table columns.<jupyter_code># let's see the two tables briefly
print(album_df.head())
print()
artist_df = pd.read_csv('Artist.csv')
print(artist_df.head())
engine<jupyter_output><empty_output><jupyter_text>First let's use the connection syntax<jupyter_code># Lets set the connection
with engine.connect() as con:
    rs = con.execute('select Album.Title, Artist.Name from Album inner join Artist on Album.ArtistId = Artist.ArtistId')
    df = pd.DataFrame(rs.fetchall(), columns=rs.keys())
df.head()<jupyter_output><empty_output><jupyter_text>Next let's use pandas read-sql-query<jupyter_code># Lets set the connection

query = 'select Album.Title, Artist.Name from Album inner join Artist on Album.ArtistId = Artist.ArtistId'
df = pd.read_sql_query(query, engine)
df.head()<jupyter_output><empty_output><jupyter_text>Filtering your INNER JOINCongrats on performing your first INNER JOIN! You're now going to finish this chapter with one final exercise in which you perform an INNER JOIN and filter the result using a WHERE clause.Recall that to INNER JOIN the Orders and Customers tables from the Northwind database, Hugo executed the following SQL query:```"SELECT OrderID, CompanyName FROM Orders INNER JOIN Customers on Orders.CustomerID = Customers.CustomerID"``` Use the pandas function read_sql_query() to assign to the variable df the DataFrame of results from the following query: select all records from PlaylistTrack INNER JOIN Track on PlaylistTrack.TrackId = Track.TrackId that satisfy the condition Milliseconds < 250000<jupyter_code>playlist_track_df = pd.read_csv('PlaylistTrack.csv')
print(playlist_track_df.head())
print()
track_df = pd.read_csv('Track.csv')
print(track_df.head())
track_df.Milliseconds.head()
query = 'select * from PlaylistTrack inner join Track on PlaylistTrack.TrackId = Track.TrackId where Milliseconds < 250000'
df = pd.read_sql_query(query, engine)
df.head()<jupyter_output><empty_output><jupyter_text>Do project,PROJECT: TV, HALFTIME SHOWS, AND THE BIG GAME<jupyter_code><jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.0776752806834402, 'fraction_numerical': 0.02215641577366369, 'mean_word_length': 4.734180307842658, 'pattern_counts': {'":': 0, '<': 233, '<?xml version=': 0, '>': 239, 'https://': 11, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 12, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 6, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '60', 'dir': 'jupyter-structured-clean-dedup', 'id': '531513', 'license': 'MIT', 'n_tokens_mistral': 14812, 'n_tokens_neox': 13744, 'n_words': 5811, 'path': 'intro_to_importing_data_in_python.ipynb', 'repo_name': 'Lawrence-Krukrubo/Effective_Python', 'size': '46966'}