<jupyter_start><jupyter_text>SIT742: Modern Data Science **(Week 03: Data Wrangling)**---- Materials in this module include resources collected from various open-source online repositories.- You are free to use, change and distribute this package.- If you found any issue/bug for this document, please submit an issue at [tulip-lab/sit742](https://github.com/tulip-lab/sit742/issues)Prepared by **SIT742 Teaching Team**--- Session 3D - Parsing Excel Files  Table of Content* Part 1. Introduction to Excel* Part 2. Parsing Excel with Pandas* Part 3. Summary* Part 4. Exercise---So far, you have learnt how to work with data in the formats that are machine readable, such as CSV, JSON and XML. The approaches used to import data in those formats are generally standard. However, not all data can easily be imported into Python or other programming languages without a fair amount of work.For example, with data stored in spreadsheets and PDFs. In these circumstances, data is generated purely for human consumption.The person who generated the data often tries to make it easily readable for human, disregarding the importance of releasing it in a machine readable format. We will provide some generic instructions on how to scrape data from excel files. You will find that the scraping process becomes much more difficult and time-consuming. But the ultimate goal stays the same, i.e., extracting data and converting it into a machine readable format. * * * 1. Introduction to ExcelExcel is a popular spreadsheet application originally developed for Windows. You can also find free alternatives that run on Mac OS and Linux,for example, LibreOffice Calc and OpenOffice Calc can both work with Excel files.An Excel document is also called a workbook. It is usually saved in a file with either .xlsx extension or .xls extension, depending on the Excel version you use.A workbook can contain multiple worksheets, each of which is a grid of cellswhere you keep and manipulate the data. Those cells are arranged in numbered rows and letter-named columns.Excel can display not only tabular data but also data like line graphs, histograms and charts.It also provides a set of data analysis functions for statistical, engineering and financial needs.Presumably, most of you know what a Excel file looks like. If not, please find some Excel files online and have a look or open the Excel file used in this tutorial.There are many ways of manipulating data stored in Excel spreadsheets. For instance, "[Working with Excel Files in Python](http://www.python-excel.org/)" contains pointers to the best information available about working with Excel files in Python. The website lists the following Python packages that deal with Excel:* `openpyxl`: Reads/writes Excel 2010 xlsx/xlsm/xltx/xltm files.* `xlsxwriter`: write text, numbers, formulas and hyperlinks to multiple worksheets in an Excel 2007+ XLSX file.* `xlrd`: Extracts data from Excel files (.xls and .xlsx, versions 2.0 onwards).* `xlwt`: Writes and formats Excel files compatible with Microsoft Excel versions 95 to 2003.* `xlutils`: Contains a set of advanced tools for manipulating Excel files (requires `xlrd` and `xlwt`).You would need to install each separately if you want to use them;however, in this tutorial we will use Pandas `ExcelFile` class that requires `xlrd` to demonstrate how to parse Excel files.Some tutorials on working with Excel files that might be of your interest:* [Working with Excel Spreadsheets](https://automatetheboringstuff.com/chapter12/): It utilizes openyxl to readdata from spreadsheets. Read the following sections:    * Reading Excel Documents 📖    * Project: Reading Data from a Spreadsheet 📖* [How to read Excel files with Python (xlrd tutorial)](https://www.youtube.com/watch?v=p0DNcTnreuY): a Youtube video on extracting data from a simple Excel file. (Optional)This tutorial will use a running example to showyou how to extract data from Excel spreadsheets step-by-step using Pandas.The example we use in this tutorial is "[Table 2: Nutrition](http://www.unicef.org/sowc2014/numbers/documents/excel/SOWC%202014%20Stat%20Tables_Table%202.xlsx)" from Unicef's report on [The State of the Worlds Children](http://www.unicef.org/sowc2014/numbers/) for 2014.The download link is located at the bottom of the webpage. Please download the Excel file, and store it in the same folder as where the notebook located.Our task is to extract the statistic data table on the child's issues of underweight, stunting, wasting and overweight prevalence in different countries.* * *  2. Parsing Excel with PandasIn this section we will walk through the process of parsing our example Excel file with Pandas.A short tutorial on how to use Pandas `read_excel` function and the ExcelFile class  can be found at Pandas [webpage on IO](http://pandas.pydata.org/pandas-docs/stable/user_guide/io.html). 📖  (Just read the section "Reading Excel Files".)Before we start parsing our Excel file, we need to first make sure the Python package `xlrd` is installed, as Pandas `ExcelFile` class makes use of `xlrd`. The `xlrd` package can be run on Linux and Mac as well as Windows.Here we assume you use either Linux or Mac. If you use Anaconda, you do not need to worry about this, as Anaconda includes the most popular Python packages for data analysis, including `xlrd`. Otherwise, you might need to install `xlrd` in order to run `read_excel`. To install `xlrd`, you can use [pip](https://pypi.python.org/pypi/pip), a Python package management system. In your command line, simply type```shell    pip install xlrd```Now to start our script, we need to import Pandas and open our Excel file by creating a Pandas `ExcelFile` object.<jupyter_code>!pip install wget
import wget

link_to_data = 'https://github.com/tulip-lab/sit742/raw/master/Jupyter/data/SOWC2014.xlsx'

DataSet = wget.download(link_to_data)
!ls
import pandas as pd
excel_data = pd.ExcelFile('SOWC2014.xlsx')
excel_data<jupyter_output><empty_output><jupyter_text>By running the code above, we have loaded the Excel file as a Pandas' ExcelFile object into Python. Are we ready to parse our Excel File? Before starting to parse the file,we probably need to ask ourselves a couple of questions. For instance,* How many sheets does our Excel file have?* Which data sheet does contain our data? What is the name of the sheet? Or what is the index of the sheet? Unlike CSV files, an Excel file can have multiple worksheets.For example, our Excel file contains two worksheets, one contains data notes,and the other contains the data we want.In order to get our data, we will just pull the sheet with the data we want.If your Excel file has a couple of worksheets and you can guess the index of the worksheet that contains the data you want, or you have been told from whichworksheet you are going to extract data, you can directly use Panda's [`read_excel`](http://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_excel.html`) fuction```python    pandas.read_excel()```This function reads an Excel table in a given worksheet into a Pandas DataFrame, where you can start further manipulating the data.However, in some cases, particularly while an Excel file has a lot of worksheets,it might be good to view all the sheets by their names.So, let's check out what the names of the sheets we have in our Excel file are:<jupyter_code>excel_data.sheet_names<jupyter_output><empty_output><jupyter_text>There are two worksheets in our Excel file.The one that we are looking for is "Table 2 ". So, let's read the second worksheet into a Pandas DataFrame.Note that there is an extra space in the worksheet name.Without this space, running the following parsing code will result in the following error```    XLRDError: No sheet named ```<jupyter_code>#Should be 'Table 2 ' 
#      Not 'Table 2'
#If you put the 'Table 2' here, it will show XLRDError: No sheet named <'Table 2'>
df = excel_data.parse('Table 2 ')
df
#It will show the rows and columns in this DataFrame
df.shape<jupyter_output><empty_output><jupyter_text>We have loaded the target worksheet into Python. There are 322 rows and 28 columns (You can use `df.shape` to see the dimensionality of the DataFrame).If you scroll through the output, you will notice that the loaded data table is quite messy.The messiness includes* Rows only contain missing values that are indicated by **NaN **in Pandas DataFrame.* Column heads are in three languages, i.e., English, French and Spanish.* Column heads in one language spread over multiple rows.* Country names also appear in three languages.* Notes shown in the original Excel file appear in rows towards the end of the data frame.Remember that our goal is to extract the data table in English. It is clear that we need to further process the data frame. For demonstration purpose,we will try to keep the example as simple as possible,so we will not extract column heads here. Instead, if you are interested in programmatically extracting column heads, you can try it by yourself.  Task 1 drop useless columns and rowsWe will start with removing country names in French and Spanish, which corresponds to remove two columns, labeled "Unnamed: 1" and "Unnamed: 2" in our data frame.To do this, we are going to use DataFrame's [`drop()`](http://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.drop.html) function, which returns a new object with labels in requested axis removed.We will frequently use this function later in this section.<jupyter_code>df = df.drop(['Unnamed: 0', 'Unnamed: 2', 'Unnamed: 3'], 1)
#You will find that two columns were dropped.
df.shape<jupyter_output><empty_output><jupyter_text>Now you should have 26 columns.Next we are going to remove all the rows and columns that are empty, i.e., only contains NaNs.<jupyter_code>#0, or ‘index’ : Drop rows which contain missing values.
#1, or ‘columns’ : Drop columns which contain missing value.
#how : {‘any’, ‘all’}, default ‘any’
#Determine if row or column is removed from DataFrame, when we have at least one NA or all NA.

#‘any’ : If any NA values are present, drop that row or column.
#‘all’ : If all values are NA, drop that row or column.
df = df.dropna(0, how = 'all')
df = df.dropna(1, how = 'all')

#You will find that 1 columns and 77 row were dropped 
df.shape<jupyter_output><empty_output><jupyter_text>Here we used the [`dropna`](http://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.dropna.html) function of DataFrame. The first argument is axis (0 means row, and 1 means column),and the second argument indicates deleting rows/columns with all NaNs. We further removed 77 rows and 1 column. The printout shows thatthe very first column in the data frame only contains NaNs.These NaNs are row indices.We cannot delete it directly.Instead, we are going to reset the row indices with a list of integers.<jupyter_code>df.index = range(len(df.index))
df.head(10)<jupyter_output><empty_output><jupyter_text>After resetting all the row indices, and if you print out thefirst 15 rows using the slicing method:```    df[:15]```You will find that the data we want starts from row index 9.The first 9 rows contain column heads in three different languages.As we mentioned before, to keep our script simple, we will not extract column heads here,rather we will delete them.Similarly, if we print out the last 50 rows,```    df[-50:]```The data we want ends at row 205. Therefore, we need to delete the first 9 rows and the last 39 rows, and then reindex all the rows left.<jupyter_code>df[:15]
df[-50:]
# Delete the first 9 rows
df = df.drop(df.index[0:9])
df
# Delete the last 39 rows
df = df.drop(df.index[-39:])
df[-39:]
# Reindex rows
df.index = range(len(df.index))
df<jupyter_output><empty_output><jupyter_text>Task 2 Set country indexSo far we have extracted all the records (or rows) for 196 countries in our Excel file. Let's set the country names as row indices, and reset the column labels.<jupyter_code># Set the country names as row indices
df = df.set_index(df['TABLE 2. NUTRITION'].values)
df
# Delete "TABLE 2. NUTRITION" column, it is now redundant.
df = df.drop('TABLE 2. NUTRITION', 1)

# Reindex columns
df.columns = list(range(len(df.columns))) 

#You will find that 'TABLE 2. NUTRITION column' was delete and index was indexed by the 'TABLE 2. NUTRITION column' vaule 
#The current column is also changed into 24
df.head()<jupyter_output><empty_output><jupyter_text>Task 3 Tidy up all columns However, those records are still messy. As you can see in the printout, there are a lot of NaNs, and cell values with both numbers and letters (e.g., "6 x", " 39 x,y",) spread over two columns.Therefore, we need to merge every two columns together. How can we do that?Let us have a look at the first 10 rows and 2 columns. Please recall how to use the iloc function on the WEEK 2 lab practices.<jupyter_code>df.iloc[:10, :2]<jupyter_output><empty_output><jupyter_text>A close look at the printout will give you the following patterns:* If the cell contains only a float or '-', the corresponding cell value in the odd-numbered columns is "NaN". See the rows labeled "**Afghanistan**" (-), "**Albania**"(3.6), "**Andorra**"(-) and "**Argentina**"(7.2) .* If the original cell contains a number and a couple of letters, the cell in the even-numbered columns contains a number, and the one in the odd-numbered columns contains the letters. See the rows labeled "**Algeria**"(6,x), "**Angola**"(12,x). etc.Assume that we are going to merge the two cells containing a float type and letters respectively.We need a FOR loop iterating over either the odd- or the even-numbered columns.Meantime, within this FOR loop, another FOR loop is needed to iterate **over rows.**For each row, we check if the cell in the odd-numbered columns contains NaN.If it does, we then merge it with the cell in the corresponding even-numbered column on the left.<jupyter_code># It shows there 24 columns and 197 rows before we start to merge the two cells.
df.shape

#A FOR loop over odd-numbered columns.
#range (start, stop, step)
#start	Optional. An integer number specifying at which position to start. Default is 0
#stop	Optional. An integer number specifying at which position to endt.
#step	Optional. An integer number specifying the incrementation. Default is 1

for col_idx in range(1, 24, 2): 
    # A For loop over rows
    for row_idx in range(len(df)):
        # A IF statement to check
        #    1. If the cell value in the odd-numbered column is not NaN, then merge it the cell value in 
        #       the even-numbered column.
        #    2. Otherwise, do nothing.
        if not pd.isnull(df.iloc[row_idx, col_idx]):
            df[col_idx-1][row_idx] = str(df[col_idx-1][row_idx]) + ' ' + str(df[col_idx ][row_idx])  
df.shape<jupyter_output><empty_output><jupyter_text>The next step is to remove the odd-numbered columns in the data frame, as they are redundant now.To do this, we are going to use DataFrame's `drop()` function again as follows<jupyter_code>for col_idx in range(1, 24, 2): 
        df.drop(col_idx, 1, inplace = True)
df.head()<jupyter_output><empty_output><jupyter_text>Now the data is in a pretty good shape aside from the column heads. We can extract the column heads from the Excel file using either manual or programmatic method.Here we are going to do it manually. Considering that we are going to save results in an csv file. we will use the long name from the raw data<jupyter_code>df.columns = ["Low birthweight (%)", \
              "Early initiation of breastfeeding (%)", \
              "Exclusive breastfeeding <6 months (%)", \
              "Introduction of solid, semi-solid or soft foods 6–8 months (%)", \
              "Breastfeeding at age 2 (%)", \
              "Underweight (%) moderate and severe", \
              "Underweight (%) severe", \
              "Stunting (%) moderate and severe", \
              "Wasting (%) moderate and severe",\
              "Overweight (%) moderate and severe", \
              "Vitamin A supplementation, full coverage(%)", \
              "Iodized salt consumption (%)" ]
df.head()<jupyter_output><empty_output><jupyter_text>Finally, we have extracted the data table from our Excel file, and put it into a Pandas DataFrame.The DataFrame has 197 rows and 12 columns, where rows correspond to records for individual countriesand columns are variables (or attributes). Our last step is to save the data table in a CSV file.<jupyter_code>df.to_csv('en_final_table_2.csv')<jupyter_output><empty_output><jupyter_text>What is the problem you get? Let's check the type of some values in the DataFrame using```    type(df.iloc[i,j])```where i indicates row index, and j indicates column index.You will find that DataFrame's `read_excel` method has parsed all strings and special characters,like '-', into Unicode objects.If you print the DataFrame, however, you'll get the printed version of the Unicode.In contrast, printing a value in a specific location, for example, ```python    df.iloc[0,0]```gives you the original Unicode,```    u'\u2013'```Therefore, you need to specify the encoding method while savingthe DataFrame into a CSV file.<jupyter_code>df.to_csv('en_final_table_2.csv', sep=',', encoding='utf-8')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.07531114327062229, 'fraction_numerical': 0.012040520984081041, 'mean_word_length': 4.470550981633946, 'pattern_counts': {'":': 0, '<': 59, '<?xml version=': 0, '>': 58, 'https://': 5, 'lorem ipsum': 0, 'www.': 4, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 3, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '14', 'dir': 'jupyter-structured-clean-dedup', 'id': '194823', 'license': 'MIT', 'n_tokens_mistral': 5004, 'n_tokens_neox': 4605, 'n_words': 2421, 'path': 'Jupyter/SIT742P03D-ParsingExcelFiles.ipynb', 'repo_name': 'jllemusc/sit742', 'size': '17275'}