# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

# # Workshop: Data Management with Python
#
# ## Prerequisites
# ### Installation:
# Minimum requirement is Python3.
#
# I personally do prefer the installation of the Anaconda Distribution. 
# https://www.anaconda.com/products/individual
#
# Basically all packages you need to get started are included, as well as jupyter and spider editor. Also when installing new packages with conda
# ```python
# conda install PACKAGE_NAME
# ```
# the compatibility is taken care of.

# in case a package is missing and you recognise when you want to import it, 
# you can do the installation also from within the notebook
# !pip install pandas
# #!conda install pandas

# For installation on Windows the command might need to look like:
# !{sys.executable} -m pip install pandas
# !{sys.executable} -m pip install xl...

# ### Packages:
# For the data management tutorial we need packages for importing and exporting the data and for data wrangling. The most common package, which covers both is the **pandas module**. This
# Python open source library provides high-performance, easy-to-use data structures and data analysis tools.
# Another tool we need is the **numpy module**. This
# Python open source library makes handling of vectors, matrices and big multidimensional arrays easy.
#
# So after the installation of numpy and pandas package, you have to import them at the beginning of your python script or notebook to use their functionalities
#
# Good Practice tip: import as "np" and "pd" because it's faster to write "pd" than "pandas"
# ```python
# import pandas as pd
# import numpy as np
# ```
#
# ### Data:
# At the very beginning we will look at a minimal Excel example Basics.xlsx to get to know some basic operations.
#
# Later on we will take a look at two examples of experimental data:
# - Tensile Test
# - Bubble Column
#
# ### Plan B:
# If something went wrong with the installation or you cannot access the datafiles, you can view this notebook at nbviewer (passive) or at binder (interactive). Also you can view it directly on github.
#
# Links:
# - binder https://mybinder.org/v2/gh/astridwalle/python_jupyter_basics/HEAD?filepath=DataManagement%2F1_DataManagement.ipynb
# - nbviewer https://nbviewer.jupyter.org/github/astridwalle/python_jupyter_basics/blob/main/DataManagement/1_DataManagement.ipynb
# - github https://github.com/astridwalle/python_jupyter_basics
#
# ## Now let's get started! What is this about? Why Python?
# ### Separation of Data and Analysis!
# For example you have performed experimental analyses and now you have loads of raw data:
#
# There is actually no need to copy this data for analyzing and to store different analysis versions...
#
# Well-known szenarios are:
#
# - Multiple Excel files floating around on share drives
# - Lots of versions and local copies
# - Hard or even impossible to keep overview and trace changes
# - Changing the underlying data (add columns, change format, ...) can break everything
#
#
# ### Typical Usecases for which Python has advantages over Excel, Matlab, ...
# For large datasets Excel becomes unhandy and slow. This is no problem for python due to the paradigm of separating the data from the analysis. If you want to try and store multiple approaches and analyses, this becomes a problem in Excel due to large file copies.
# With python your different analysis files stay small (just textfiles) and can also be version controlled (e.g. with git), which is great for collaboration.
# You don't end up with ..._final ..._final_final_
#
# With Python:
# - Import data and with that
# - Decouple Business Logic, Computations, Visualisation from the data
# - Access databases, xlsx tabels and more sources without changing them.
# - Export your analysis afterwards to every format you like (database, xlsx, ...)
#
#
# ### Huge community of users and contributors
# You can google everything!

# ## Jupyter Basics to get started:
# Check out the following short-cut and menu bar functionalities: 
# - Markdown vs. Code
# - Coding Environment
# - auto-complete ```tab```
# - Running a cell: ```Run``` or ```Shift+Enter``` 
# - ? in front of function call to get some help
#
# ## Important for this course:
# The intention is to have an interactive working document, so
# - Add as many code cells as you like!
# - Try evrything!
# - Don't be shy, you cannot break anything!

# ## Example files for this class
# Basic sample data: 
# ```
# Data/Basics.xlsx
# ```
#
# Real data from testing:
# - Tensile Testing
# ```
# Data/TensileTest/AIMg3.txt Data/TensileTest/HDPE.txt Data/TensileTest/Staht.txt
# ```
# - Bubble colum
# ```
# Data/BubbleColumn/Test_01.xlsx Data/BubbleColumn/Test_02.xlsx Data/BubbleColumn/Test_03.xlsx 
# ```
#
# ## Importing basic libraries
#
# **pandas module**
#
# Python open source library providing high-performance, easy-to-use data structures and data analysis tools. Built-in great capabilities for data import and export! (csv, xlsx, ...)
#
# Good Practice tip: import as "np" and "pd" because it's faster to call "pd" than "pandas"
#
# **numpy module** (We don't need it today)
#
# Python open source library for easy handling of vectors, matrices, big multidimensional arrays and mathematical operations
#
# ## General notes
# ### Modules
#
# You can either import a complete module, or just single classes or functions of a module.
#
# ```python
# import pandas as pd
#
# pd.read_csv("file.csv")
# ```
#
# ```python
# import matplotlib.pyplot as plt
#
# plt.plot(x,y)
# ```
#
# ## Classes and functions
# Python is a multi-paradigm programming language. You can do object oriented programming, but also structured, sequential programming. You can create objects with classes or just use functions.

# ## Let's go!
# At first just one statement in a code cell to get a nice output. (This enables the print of multiple variables per coding cell.)

# make all print statements in a cell appear in output
from IPython.core.interactiveshell import InteractiveShell
InteractiveShell.ast_node_interactivity = "all"

# ## Now it is your turn!
# 1. Import the modules
# 2. The functions of these modules are then called with pd.XXX
# 3. Check which functions are available and how to use them
# 4. Try pd. tab/autocomplete to see all available options
# 5. Try inserting a ? in front of the function name

# ## Press ```+``` and get started :)





# import module
import pandas as pd
import numpy as np





# ### Our first Testcases - Basics.xlsx / Tensile Testing
# If you have multiple tables, e.g. from different test runs, which you want to combine to one datasource for analyzing, that is pretty easy.
#
# We will do this for:
# 1. One xlsx file with multiple sheets as raw data
# 2. Multiple txt files as raw data
#
# Stitching together rowwise or columnwise is easy.
#
# ### Exploratory Data Analysis
# We always start with an exploratory data analysis to ensure we know how our data looks like.
#
# ### Our main datastructure: Pandas DataFrame
# For data analysis the most common module is pandas with the main tabular data structure DataFrame, which enables for endless operations.

# +
# We start with reading in an Excel File.
# To check the usage of the function we can put the questionmark in front of the function.
# 
# #?pd.ExcelFile
#??pd.ExcelFile
# -

# 1. Read xlsx file - Variant 1 --> reads all the sheets in the file
xls1=pd.ExcelFile("../Data/Basics.xlsx")

xls1.sheet_names

# 2. Read xlsx file - Variant 2 --> reads just the first sheet!
xls2=pd.read_excel("../Data/Basics.xlsx")
xls2

# We use Variant 1 --> Parse the sheets into dataframes 
df_test1=xls1.parse("Test1")
df_test2=xls1.parse("Test2")
df_test3=xls1.parse("Test3")

# +
# Check the results of the parsing on your own!
# Always use autocompletion!
# -

# show results: have a look at the resulting dataframes
# --> check the additional index column, which was added automatically
df_test1
df_test2
df_test3
# show no of columns and rows with df.shape
df_test1.shape
df_test2.shape
df_test3.shape

# ### pd.concat - join dataframes
#
# **Task**
#
# At first we want to join the two dataframes df_test1 and df_test2 into one big DataFrame, which has 
# 1. all columns
# 2. all rows
# Let's find out the caveats here...
#
# **hints**
# - have a look again at the dataframes:
#     - just type the name of the df and shift+enter
#     - have a look at number of rows and columns using df.shape
# - use **?** to find out how to use pd.concat
# - ask google

df_test1

df_test2

# 1. all columns
df_all_cols=pd.concat([df_test1,df_test2],axis=1,ignore_index=False)

df_all_cols

# show resulting dataframe
df_all_rows=pd.concat([df_test1,df_test2],axis=0,ignore_index=True)
df_all_rows

# ### Your turn: try around with
# - ?pd.concat
# - Change the axis
# - Change ignore_index
# - Try other options with autocomplete...

# ## Combine different testruns into one dataframe
#
# If you want to merge different "cases", present in different tables, e.g. each representing another test or another parameter varation. pd.concat can join them into one df, adding an additional index, a so called multiindex
#
# We will do this now with our test tables:

#df_all_Params=pd.concat([df_Param1,df_Param2,df_Param3],axis=0,ignore_index=True)
df_all_Params=pd.concat([df_test1,df_test2],keys=["Test1","Test2"],axis=0,names=["Param","Row_Index"],ignore_index=False)

df_all_Params

# ### Your turn: try out join / merge: 
# Join and merge are also extremely interesting functionalities for dataframes. --> check it out now or later with
# ```
# df_test1.join()
# df_test1.merge()
# ```
# Ask google or add **?** to find out more. Use autocompletion!



# ## Now let's have a look at some real testing data
# ```
# Data/TensileTest/AIMg3.txt
# ```
# ```
# Data/TensileTest/HDPE.txt
# ```
# ```
# Data/TensileTest/Stahl.tx`
# ```
#
# ### Your turn:
#
# - Read in all 3 files with the function pd.read_csv()
# - What are the caveats here?
# - Hint: read_csv can detect commas and whitespaces automatically, but here are tabs...

# +
# 1. Read in multiple txt files
# Try autocomplete- for everything! Paths, Variable names, functions, options...
df_AlMg3=pd.read_csv("../Data/TensileTest/AlMg3.txt", sep="\t", skiprows=0, header=None)

# See the whole dataframe 
df_AlMg3

# See the beginning or the end
df_AlMg3.head(15)
df_AlMg3.tail(10)
# See a slice of the df
df_AlMg3[5:15]
# -

# This looks a bit messy, so let's do some data wrangling
# Split it up into metadata and actual data
df_AlMg3_meta=df_AlMg3[1:9]
df_AlMg3_data=df_AlMg3[12::]
df_AlMg3_meta
df_AlMg3_data

# And some more wrangling
# Set menaningful columnnames
df_AlMg3_meta.columns=["key","value","unit"]
df_AlMg3_data.columns=["Standardweg [mm]","Standardkraft [N]","None"]
df_AlMg3_meta
df_AlMg3_data

# and at last we will cut the last column from the data, as this is empyt anyway:
df_AlMg3_data.drop(columns=["None"]).reset_index()
# ATTENTION! Make sure that the action was really APPLIED and not just executed!
df_AlMg3_data
# so either you write the df new, or set the inplace option or copy
# df_AlMg3=df_AlMg3_data.drop(columns=["None"]).reset_index()
df_AlMg3_data=df_AlMg3_data.drop(columns=["None"])
df_AlMg3_data.reset_index(inplace=True)
# And we drop the empty rows of the metadata df


df_AlMg3_data

# ## Now that the data looks good, we can apply the transforming steps to the other datasets as well:
# ### Option 1. Read one by one
# ### Option 2. Read files in a loop

# ! ls -al ../Data/TensileTest/

import os
# we can assign dynamic variable names with the loop index, but this is a bad idea. It is better 
# to write it into a so called dictionary. Later on we can access it by the name
tests={}
#for i in ["HDPE","Stahl"]:
for i in os.listdir("../Data/TensileTest/"):
    i=i.strip(".txt")
    df=pd.read_csv("../Data/TensileTest/"+i+".txt", sep="\t", skiprows=0, header=None)
    df_meta=df[1:9]
    df_data=df[12::]
    df_meta.columns=["key","value","unit"]
    df_data.columns=["Standardweg [mm]","Standardkraft [N]","None"]
    df_data=df_data.drop(columns=["None"])
    df_data.reset_index(inplace=True)
    key_data=i+"_data"
    value_data=df_data
    key_meta=i+"_meta"
    value_meta=df_meta
    tests[key_data]=value_data
    tests[key_meta]=value_meta

# Now we check the keys of the dictionary
tests["AlMg3_meta"]["key"]

# And now we combine all the testing data into one dataframe, using just the data values of the dictionary.
df_tensile_tests=pd.concat([tests["AlMg3_data"],tests["Stahl_meta"]],keys=["AlMg3","Stahl"],axis=0,
                  names=["Material","Row_Index"],ignore_index=False)

# Let's check the result
df_tensile_tests

# ## Example 2: Data Exploration with large datafiles...
# We always start by looking at the data. Get to know your data
#
# **Does the data look ok?**
#
# **Does it look as we expected it to be?**
#
# In the following we will list a few commands, which are helpful for data exploration!
#
# For this example we will use some data from the bubblecolumn.
#

df_bub=pd.read_excel("../Data/BubbleColumn/Test_01.xlsx")
df_bub

# The column names to not look nice. Let's check the original fiel and adapt our function call.
#
# We need to give some extra options for reading in the files. --> Check the read_excel function with ?

# ?pd.read_excel

# we give the first 2 lines as header --> then a multiindex is created.
df_bub=pd.read_excel("../Data/BubbleColumn/Test_01.xlsx",header=[0,1])
df_bub

# ## Now we will do some data exploration
# - shape
# - describe
# - slicing
# - accessing columns / rows

df_bub.shape

df_bub.describe()

df_bub.columns

# To get the "correct" names for slicing and for looping
df_bub.columns.values

# access specific columns --> there are multiple ways to do so.
df_bub.cam0
df_bub.cam0["Waddel Disk Diameter"]
df_bub["cam0"]["Waddel Disk Diameter"]

# ## Get an overview

df_bub.shape
df_bub.head(5)
df_bub.tail(5)
df_bub.index
len(df_bub)

# ## Slicing

# +
# 1 row
df_bub[4:5]

#multiple rows
df_bub[10:20]

#ignore last 100 rows
df_bub[:-100]

# 1 column
df_bub["cam1"]["Bounding \nleft"]

# multiple columns
df_bub[df_bub.columns.values[:3]]
# -

# ## Filtering

df_bub[df_bub["cam1"]["Bounding \nleft"]>32]

# ### Your task: 
# Try filtering options with Boolean Algebra:
# - < / >
# - == / !=
# - multiple options combined with ```&```



# ## Some more filtering - groupby
#
# Filter data by categorical values
#
# Applies if you want to get single dataframes for specific groups.
#
# Example: RKI Covid Case Data - 1 row per day per Landkreis. To get all rows only for one Landkreis, you can use groupby.

# you can also read the csv directly from url!
df_rki=pd.read_csv("https://www.arcgis.com/sharing/rest/content/items/f10774f1c63e40168479a1feb6c7ca74/data")
df_rki

# +
# Grouping works great to get separate DataFrames for different categories.
df_grouped=df.groupby("Landkreis")

for name, dataframe in df_grouped:
    print(name, len(dataframe))

# -

# ## Min / Max / Mean

df_bub["cam1"]["Bounding \nleft"].min()
df_bub["cam1"]["Bounding \nleft"].max()
df_bub["cam1"]["Bounding \nleft"].mean()

# ## Find unique values

# Makes only sense for categorical values...
df_bub["cam1"]["Bounding \nleft"].unique()

df_rki["Bundesland"].unique()

# ## Get specific rows, columns, elements
# By names (loc),  indices (iloc)

# +
# loc - gets you data by column and row name
# get one specific element by column_name and row_index
df_bub.loc[6,("cam1","Bounding \nleft")]

# get numerical index of column:
idx=df_bub.columns.get_loc(("cam1","Bounding \nleft"))
idx

# iloc - gets you data by index
# get one specific element by column index and row index
df_bub.iloc[6,idx]
# -

# ## And what do we now do with that? More ideas... Try it!
# ### Add other tests and combine to one big dataframe
# ### Add columns with postprocessed values
# ### Plotting
# ### ...



# ## Visualize results
#
# For the bubblecolumn test we plot v_pins over time
#
# More about data visualization in the next session! 
#
# ### Hint: You can also plot only a portion of the original data and apply the filtering functions upfront.

df_bub.columns.values

# +
import matplotlib.pyplot as plt
# %matplotlib inline 
import datetime

x=df_bub["erg","Zeit [ms]"]

y1=(df_bub["erg","z_bild "].shift(1)-df_bub["erg","z_bild "])/(df_bub["erg","t_Bilder LabV"].shift(1)-df_bub["erg","t_Bilder LabV"])
y2=df_bub["erg","z_bild "]

plt.figure(figsize=(15, 10))
plt.scatter(x,y1)
plt.ylim(0,0.5)
plt.xlabel("Zeit [ms]")
plt.ylabel("v_pins")
# -

# ## Export
# ### to Excel

df_all_cols

# just a tiny example. Of course you can do all kinds of formatting etc...
writer = pd.ExcelWriter("../Data/df_all_columns.xlsx",engine='xlsxwriter',options={'remove_timezone': True}) 
df_all_cols.to_excel(writer,sheet_name="all cols",startrow=1 , startcol=1, index=False)
writer.save()

# ## Export notebooks also as
# - pdf
# - latex
# - py
#
# --> check out in the menu: File --> Export

# # More python:
#
# Some useful functionalities:
# - zip() - combines multiple iterables into one data structure by grouping them together according to their index (0 pairs with 0, 1 with 1, etc)  
# - map() -  map iterates over an array and executes a function on each element. It's an elegant and concise way to loop through data   
# - filter() - filters through your array and returns all elements who pass your condition  
# - reduce() - you can perform cumulative tasks on the elements of your list, for example the sum of all elements or calculating the product of all entries (has to be imported from functools for python 3)   
# - lambdas - Lambdas are locally defined functions you can use without having to define them globally
#

# +
# %%time
from functools import reduce

arr = [1, 2, 3, 4]

letters = ['A', 'B', 'C', 'D']

def someFunction(arg1, arg2):
    result = arg1 ** arg2
    return(result)

print(someFunction(2, 3))

outputZip = list(zip(arr, letters))
print(outputZip)

outputMap = list(map(lambda x: x*2, arr))
print(outputMap)

outputFilter = list(filter(lambda x: x % 2 == 0, arr))
print(outputFilter)

outputReduce = reduce(lambda x, y: x + y, arr)
print(outputReduce)
# -

# # More hacks and best practices:
# ## Command mode
# ```esc``` and then navigate around with arrows
#
#
# ## Shell commands
# ```python
# # !ls
# ```
# ## Use virtual environments for more complex projects
# One big disadavantage of python is it's volatility and dynamic. So lots of functions keep changing and packages are not compatible with each other, depending on the versions.
# ```python
# python3 -m venv --system-site-packages NAME_ENV
# ```
# ## Use the virtual env with jupyter notebook:
# ```python
# pip install --user ipykernel
# python -m ipykernel install --user --name=myenv
# source env/bin/activate
# ```
#
# ## Get a working environment
# requirements.txt
# pip freeze
# Besides 
# ## Reuse the same structure for your projects --> Cookiecutter templates
# The way from raw to processed data is well documented, comprehensible and repeatable. 
#
# ### Hacks:
# - https://www.dataquest.io/blog/jupyter-notebook-tips-tricks-shortcuts/
# - https://github.com/astridwalle/python_jupyter_basics/blob/main/JupyterHacks/Jupyter-Hacks.ipynb
#
# ### Combination of tools:
# https://sehyoun.com/blog/20180904_using-matlab-with-jupyter-notebook.html

# ## Check out markdown possibilities
# e.g. include pics and gif's easily
#
# ![Alt Text](https://media.giphy.com/media/vFKqnCdLPNOKc/giphy.gif)

# Check out your variables of a specific type, here now we check all DataFrames in our notebook
# %who DataFrame



StarcoderdataJupyterScriptsDedupFiltered