<jupyter_start><jupyter_text>Notebook 1, Module 1, Data and Data Management, CAS Applied Data Science, 2020-08-19, <NAME>, University of Bern.   1. Data ManagementEstimated study time is up to 2 hours. According to your background and how much you want to learn, you may need more or less. You are supposed to google, read manuals and chat with others during working through this notebook in order to benefit fully.**Learning outcomes - after completion you**- Know about data sources, types and formats (see lecture slides)- Able to import and export data in Python- Able to do simple things with dataframes in Python- Know about data volumes, metadata and quality- Able to plot histograms and scatter plots in Python (tomorrow)**Documentation on Pandas DataFrame**- Python: https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.html Outline     0. About data management     1. Getting used to Jupyter lab     2. Import datasets into a Python dataframe     3. Indexing on a dataframe     4. Sorting      5. Filtering     6. Exporting     7. Missing and bad data     8. Metadata     9. Working on the filesystem  0. About data management Handling or managing data involves many steps and technologies at many levels. Data my be colleccted by sensors. It can be a camera, a temperature sensor, a telescope, a microscope (with a camera), a microphone, a particle detector etc. Normally the data is then digitised, maybe preprocessed and written to some media in a certain format, e.g. hard disk. This part of the data management is normally taken care of by engineers. Data may also be collected from all sorts of databases, so data already collected somehow. Time series of financial data, customers, passengers, facebook likes, twitter tweets etc. This is data which is normally already on a media with some interface for access, e.g. paper to be read by a camera, a file on youtube, a table on wikipedia etc. We will look at some ways to collect such data. Some programming and computer skills are needed to do so. It may be that this part of the data management is taken care of by specialised computer scientists, but it may also be expected from a data scientist to have these skills. Analysing data with statisitical and machine learning tools, requires that the data is colleceted, cleaned, prepared for the tools. This is very often a very large part of a data analytics project and a prerequisite. It may involve removing bad data, filter out redundant and noisy data, unify the formats and types, transform the data etc. Thus, a data scientist must be able to perform this part of the data management. This notebook shows the basic operations with Python pandas. With other tools the concepts and operations are very similar.After the data analysis, after the extraction of information and the creation of knowledge, the data is often stored or archived for the future (if this seems cheaper than regenerating the data at a later point). In larger institutions this part of the data management may include educated librarians and others, not necessarily the data scientist.    1. Getting used to Jupyter lab and computational notebooksJupyter lab is a "laboratory" where you can write rich text notebooks with executable code via your browser. There are several so-called kernels or computational back ends, i.e Python, R, Julia, bash etc can be supported. The text is written as Markdown. Latex is also supported (good for math). You can export the notebook in various formats, e.g. html. Everything can be done via the various tabs, however, the key shortcuts make you faster. *Useful key combinations*- Shift Enter or Control Enter = Run cell- Option Enter = Run cell and Insert new cell below*Exercises (10 min)*- Change and run this cell- Add a new cell and execute some python statement in it- Study the tabs in the menu of your jupyter (lab) notebook<jupyter_code># Write some Python here

print('<NAME>')<jupyter_output><empty_output><jupyter_text>2. Import a dataset into a Python DataFramePandas is a Python Module/Library for data analysis and manipulation. It has the data structure DataFrame which is quite powerful. Features- DataFrame object for data manipulation with integrated indexing.- Tools for reading and writing data between in-memory data structures and different file formats.- Data alignment and integrated handling of missing data.- Reshaping and pivoting of data sets.- Label-based slicing, fancy indexing, and subsetting of large data sets.- Data structure column insertion and deletion.- Group by engine allowing split-apply-combine operations on data sets.- Data set merging and joining.- Hierarchical axis indexing to work with high-dimensional data in a lower-dimensional data structure.- Time series-functionality: Date range generation and frequency conversion, moving window statistics, moving window linear regressions, date shifting and lagging.The module is highly optimized for performance, with critical code paths written in Cython or C. Documentation here: https://pandas.pydata.org/pandas-docs/stable/api.htmlFor input/output (I/O) there are methods for reading EXCEL, SQL databases, HTML tables, clipboard, SAS, STATA etc.<jupyter_code>import pandas
# import the python module pandas with the abbreviation pd
import pandas as pd
# Read the data in the iris.csv file into a dataframe
dataframe = pd.read_csv('iris.csv',dtype='str',names=['slength','swidth','plength','pwidth','species']) # data type is a string (str), i.e. not converted into numbers
dataframe # print data<jupyter_output><empty_output><jupyter_text>The notebook shows 50 rows per default. We can change this as we like:<jupyter_code>pd.set_option('display.max_rows', 100) # or whatever number you like or None  
dataframe
dataframe.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 150 entries, 0 to 149
Data columns (total 5 columns):
 #   Column   Non-Null Count  Dtype 
---  ------   --------------  ----- 
 0   slength  150 non-null    object
 1   swidth   150 non-null    object
 2   plength  150 non-null    object
 3   pwidth   150 non-null    object
 4   species  150 non-null    object
dtypes: object(5)
memory usage: 6.0+ KB<jupyter_text>Find out on wikipedia what this Iris data is about.   HintThere are some ways to get help about modules and methods in Jupyter:<jupyter_code>dataframe. #(tab completion - type tab after the dot to see all methods)<jupyter_output><empty_output><jupyter_text>Get help about this object, if there is any:<jupyter_code>dataframe?<jupyter_output><empty_output><jupyter_text>And, what you will probably use the most, the online package information and examples by googling.  Exercise 1.1 (10 min)Read the Swiss BAG Corona data into a dataframe and look at it. The URL is'https://www.bag.admin.ch/dam/bag/de/dokumente/mt/k-und-i/aktuelle-ausbrueche-pandemien/2019-nCoV/covid-19-datengrundlage-lagebericht.xlsx.download.xlsx/200325_Datengrundlage_Grafiken_COVID-19-Bericht.xlsx'.You will probably need to consult https://pandas.pydata.org/docs/reference/io.html in order to find out how to read a excel sheets into a pandas dataframe.<jupyter_code>pd.read_excel?
url = 'https://www.bag.admin.ch/dam/bag/de/dokumente/mt/k-und-i/aktuelle-ausbrueche-pandemien/2019-nCoV/covid-19-datengrundlage-lagebericht.xlsx.download.xlsx/200325_Datengrundlage_Grafiken_COVID-19-Bericht.xlsx'
df = pd.read_excel(url, skiprows = 6)
df = pd.read_excel(url, header = 6)
df
df.info<jupyter_output><empty_output><jupyter_text>3. Indexing on a DataFrameBy methods:<jupyter_code>dataframe
dataframe.iat[149,1] # by integer numbers (row 149, column 1)
dataframe.at[149,'slength'] # by row number and column name
dataframe.iat[149,1]=10 # Assign a new value<jupyter_output><empty_output><jupyter_text>Indexing a group/subset of the dataframe<jupyter_code>dataframe.iloc[148:-1]
sdf = dataframe.iloc[0:10,0:5]
print(sdf)<jupyter_output>slength swidth plength pwidth      species
0     5.1    3.5     1.4    0.2  Iris-setosa
1     4.9      3     1.4    0.2  Iris-setosa
2     4.7    3.2     1.3    0.2  Iris-setosa
3     4.6    3.1     1.5    0.2  Iris-setosa
4       5    3.6     1.4    0.2  Iris-setosa
5     5.4    3.9     1.7    0.4  Iris-setosa
6     4.6    3.4     1.4    0.3  Iris-setosa
7       5    3.4     1.5    0.2  Iris-setosa
8     4.4    2.9     1.4    0.2  Iris-setosa
9     4.9    3.1     1.5    0.1  Iris-setosa<jupyter_text>**Important** When you assign a (subset) of dataframe to a new one like above, no copy is made. This means that if you change values of the new frame, also the orginal frame will be changed. If you want a copy, you need to use the copy method. Indexing by column names...<jupyter_code>sdf['slength']<jupyter_output><empty_output><jupyter_text>Selecting (filtering) by column valuess_df = dataframe[dataframe['species']=='Iris-setosa']<jupyter_code>s_df = dataframe[dataframe['species']=='Iris-setosa']
s_df<jupyter_output><empty_output><jupyter_text>Vectorized dataframe manipulations...<jupyter_code>sdf['half slength']=sdf['slength'].astype('float')/2
sdf
s_df2 = sdf[(sdf['half slength'] > 2.3) & sdf['swidth'].astype('float') < 3.2]
s_df2<jupyter_output><empty_output><jupyter_text>4. Sorting on a DataFrame...<jupyter_code>sdf.sort_values(['slength','swidth'])<jupyter_output><empty_output><jupyter_text>You may measure the time needed for the execution, e.g. with the operating system command "time". System commands are executed with a %:<jupyter_code>%time s_df[s_df['species']=='Iris-setosa'].sort_values('slength')
%ls -  # list the file in your current directory with the linux command ls<jupyter_output>Le volume dans le lecteur C s’appelle Local Disk
 Le numéro de série du volume est 0816-45FC

 Répertoire de C:\Users\lione\DataScience\M1


 Répertoire de C:\Users\lione\DataScience\M1


 Répertoire de C:\Users\lione\DataScience\M1


 Répertoire de C:\Users\lione\DataScience\M1


 Répertoire de C:\Users\lione\DataScience\M1


 Répertoire de C:\Users\lione\DataScience\M1


 Répertoire de C:\Users\lione\DataScience\M1


 Répertoire de C:\Users\lione\DataScience\M1


 Répertoire de C:\Users\lione\DataScience\M1


 Répertoire de C:\Users\lione\DataScience\M1


 Répertoire de C:\Users\lione\DataScience\M1


 Répertoire de C:\Users\lione\DataScience\M1


 Répertoire de C:\Users\lione\DataScience\M1<jupyter_text>5. Filtering on a DataFrame...<jupyter_code>sdf['slength'].astype('float')>5.0 # Returns a vector with booleans 
# We can assign the filtering result to a new dataframe
sdf_large = sdf[sdf['slength'].astype('float')>5.0]
# And print it
sdf_large<jupyter_output><empty_output><jupyter_text>6. Missing or bad dataDatasets, in particular before they are "cleaned", may contain missing or wrongly formated values. There are DataFrame methods to deal with this:- DataFrame.dropna([axis, how, thresh, …])	Remove missing values.- DataFrame.fillna([value, method, axis, …])	Fill NA/NaN values using the specified method- DataFrame.replace([to_replace, value, …])	Replace values given in to_replace with value.- DataFrame.interpolate([method, axis, limit, …])	Interpolate values according to different methods.Retrieving and cleaning data is often the most time consuming part in a data science project.  7. Exporting dataframesDataFrame has several export methods. (html, hdf5, ascii, excel etc). Let's write our file to a text file in a csv format.<jupyter_code>sdf.to_csv('~/Desktop/myout.txt')<jupyter_output><empty_output><jupyter_text>Now we can look at the myout.txt file with some regulare txt viewer.  8. MetadataMetadata is data about the data, e.g. when was it collected, under which conditions, calibration etc. Metadata is normally not part of the statistical data analysis, however, needed for understanding and reproducibilty. DataFrame is not really made for storing metadata (should be done separately), but one can add new attributes to a dataframe:<jupyter_code>sdf.myinstrument_name = 'Binky'
sdf.myinstrument_name<jupyter_output><empty_output><jupyter_text>Exercise 2Write down here some minimal metadata about the Iris dataset:- column names (description, features, observable, inputs,...)- units/dimensions*- Author/source/reference/version- Manipulation of data, with which version of Pythonsummary: everything needed for reproduction(*The dimentions of a numpy table or dataframe is often called the shape - 150x5)  9. Working on the filesystem with the os moduleWhen managing large datasets, one often has to organise files in the file system. This includes finding them, moving and copying them, creating new folders/directories, renaming them etc. This can easily be done from Python with the os (operating system) module. Try to understand the following code and alter it to do something else.<jupyter_code>import os

new_dir = os.getcwd() +'/newnbdir' # Get the current directory into a string, add /newnbdir to the string
os.makedirs(new_dir) # Create the folder
files = os.listdir() # List the files in the current folder
print(files)
nb_files = [] # Create an empty list
for file in files:
    if 'ipynb' in file and file[0]!='.':
        nb_files.append(file)
print(nb_files)
for file in nb_files:
    os.system('cp '+file+' '+new_dir+'/')
import shutil, os
new_dir = os.getcwd() +'/newnbdir' # Get the current directory into a string, add /newnbdir to the string
os.makedirs(new_dir) # Create the folder
files = os.listdir()
nb_files = []
for file in files:
    if '.ipynb' in file and file[0] != '.':
        nb_files.append(file)

for i in nb_files:
    shutil.copy(i, 'newnbdir')<jupyter_output><empty_output><jupyter_text>Exercise 3Read in the iris dataset from iris.csv into a dataframe. Set the values in column 1 in row 39, 49 and 100 to NaN (use the nan method from the numpy package). Then replacethe NaN values to the average value of the respective column. Depending on how you do it, this may be about 10 lines of Python code.<jupyter_code># Solve your exercise here<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08038907166356744, 'fraction_numerical': 0.01902446002002575, 'mean_word_length': 4.423972071373157, 'pattern_counts': {'":': 0, '<': 74, '<?xml version=': 0, '>': 76, 'https://': 5, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '18', 'dir': 'jupyter-structured-clean-dedup', 'id': '315481', 'license': 'CC0-1.0', 'n_tokens_mistral': 4365, 'n_tokens_neox': 4132, 'n_words': 1723, 'path': 'DataSet_Meteo_Original_EnviDat/M1/02-M1-D1-DM.ipynb', 'repo_name': 'lionelperret76/M1-M2_Project', 'size': '13988'}