miniconda anaconda configuratie

-hoe activeer je environment in een Anaconda prompt

activate  O:\Input\03_ZichtenGrip\python\environments\ENV_flask_dash

let op UNC  paden

\\swappams2820.basis.lan\IVS_BI_Ontwikkel\data\Input\03_ZichtenGrip\python\environments\ENV_flask_dash

- Hoe wordt anaconda prompt aangeroepen

call "%windir%\System32\cmd.exe "/K" C:\Users\wagene002\AppData\Local\Continuum\miniconda3\Scripts\activate.bat C:\Users\wagene002\AppData\Local\Continuum\miniconda3"

- Hoe wordt anaconda prompt aangeroepen met eigen Environment (startConda Environment.bat)

echo "hi"
call "%windir%\System32\cmd.exe "/K"  C:\Users\wagene002\AppData\Local\Continuum\miniconda3\Scripts\activate.bat O:\Input\03_ZichtenGrip\python\environments\ENV_flask_dash\
C:\Users\wagene002\Desktop\runit.bat
echo "bye"


For Windows, use the following script in your batch file to execute a Python script. Simply change your personal file paths.

cmd /c C:\ProgramData\Anaconda3\condabin\conda.bat run "C:\ProgramData\Anaconda3\python.exe" "C:\Users\User Name\Path to your Python File\Python File.py"

hoe start je een python script

cmd /c C:\Users\wagene002\AppData\Local\Continuum\miniconda3\condabin\conda.bat run "\\swappams2820.basis.lan\IVS_BI_Ontwikkel\data\Input\03_ZichtenGrip\python\environments\ENV_flask_dash\python.exe" "\\swappams2820.basis.lan\IVS_BI_Ontwikkel\data\Input\03_ZichtenGrip\python\scripts\CheckStagingWMON.py"

hoe start je de juiste miniconda omgeving en geef je daarna het juiste python commanda

call "%windir%\System32\cmd.exe "/K"  C:\Users\wagene002\AppData\Local\Continuum\miniconda3\Scripts\activate.bat O:\Input\03_ZichtenGrip\python\environments\ENV_flask_dash\  & cmd /c C:\Users\wagene002\AppData\Local\Continuum\miniconda3\condabin\conda.bat run "\\swappams2820.basis.lan\IVS_BI_Ontwikkel\data\Input\03_ZichtenGrip\python\environments\ENV_flask_dash\python.exe" "\\swappams2820.basis.lan\IVS_BI_Ontwikkel\data\Input\03_ZichtenGrip\python\scripts\CheckStagingWMON.py"

let op de essentie zit hem in eerst de shel starten van miniconda en daarna na de & het commando geven

conda offline installation

conda install <package-file-name>.tar.bz2

 C:\Users\wagene002\Desktop>conda install O:\Input\03_ZichtenGrip\pandas-datareader-0.8.1-py_0.tar.bz2

Vergelijken dataframes

Vergelijken van 2 dataframes met elkaaar


def VergelijkTabellen(dfBase,dfCurrent) :
    dfC=pd.merge(dfBase,dfCurrent, on='table_name', how='outer',suffixes=('_old', '_new'))
#    Analyses
    logger.info('------------------------------' + str(datetime.now()) + '------------------------------' )
  
    logger.info('tabellen alleen in old' )
    t=dfC[(dfC['aantal_old'].isna() &  dfC['aantal_new'].notna())]
    logger.info(t.to_string(columns=['table_name'],index=False))
    logger.info('------------------------------')
    logger.info('tabellen alleen in new' )
    t=dfC[(dfC['aantal_old'].notna() &  dfC['aantal_new'].isna())]
    logger.info(t.to_string(columns=['table_name'],index=False))
    t=dfC[(dfC['aantal_old'].notna() &  dfC['aantal_new'].notna())]
    dfC['Verschil']=dfC.apply(lambda x : x['aantal_new'] - x['aantal_old'], axis=1)
  
    lstValues=[(dfC['aantal_new'] - dfC['aantal_old']) /(dfC['aantal_old'])]
    lstConditions = [dfC['aantal_old'].notna() & dfC['aantal_new'].notna() & dfC['aantal_old']!=0 ]
    dfC['Stijging']= np.select(lstConditions,lstValues,'nvt')
    dfC['Stijging'] = dfC['Stijging'].map(lambda x : '{percent:.2%}'.format(percent=float(x)) if x!= 'nvt' else x)
  
    logger.info(dfC.to_string(index=False))
    return dfC

Percentages printen van strings.

    dfC['Stijging'] = dfC['Stijging'].map(lambda x : '{percent:.2%}'.format(percent=float(x)))

Percentage berekenen

     lstValues=[(dfC['aantal_new'] - dfC['aantal_old']) /(dfC['aantal_old'])]
    lstConditions = [dfC['aantal_old'].notna() & dfC['aantal_new'].notna() & dfC['aantal_old']!=0 ]
    dfC['Stijging']= np.select(lstConditions,lstValues,'nvt')

    dfC['Stijging'] = dfC['Stijging'].map(lambda x : '{percent:.2%}'.format(percent=float(x)) if x!= 'nvt' else x)

Create pandas columns based on multiple conditions

Create columns based on other values in the row

 df1['Verschil']=df1.apply(lambda x : x['aantal_new'] - x['aantal_old'], axis=1)

Create pandas columns based on multiple conditions

With pandas and numpy we barely have to write our own functions, especially since our own functions will perform slow because these are not vectorized and pandas + numpy provide a rich pool of vectorized methods for us.

In this case your are looking for np.select since you want to create a column based on multiple conditions:

definieer 2 lijsten van dezelfde lengte
1 lijst met de where clauses
1 lijst met de values


lstValues=[(df1['aantal_new'] - df1['aantal_old']) /(df1['aantal_old']),'99999999999']

lstConditions = [df1['aantal_old'].notna() & df1['aantal_new'].notna(), df1['aantal_old'].isna() &  
df1['aantal_new'].isna()]
 
df1['Stijging2']= np.select(lstConditions,lstValues,'99')

How to iterate over rows in a DataFrame in Pandas?

How to iterate over rows in a DataFrame in Pandas?


Iterating through pandas objects is generally slow. In many cases, iterating manually over the rows is not needed [...].

Answer: DON'T!

Iteration in pandas is an anti-pattern, and is something you should only do when you have exhausted every other option. You should not use any function with "iter" in its name for more than a few thousand rows or you will have to get used to a lot of waiting.
Do you want to print a DataFrame? Use DataFrame.to_string().
Do you want to compute something? In that case, search for methods in this order (list modified from here):

1. Vectorization
2. Cython routines
3. List Comprehensions (vanilla for loop)
4. DataFrame.apply(): i)  Reductions that can be performed in cython, ii) Iteration in python space
5. DataFrame.itertuples() and iteritems()
6. DataFrame.iterrows()

iterrows and itertuples (both receiving many votes in answers to this question) should be used in very rare circumstances, such as generating row objects/nametuples for sequential processing, which is really the only thing these functions are useful for.

Faster than Looping: Vectorization, Cython

A good number of basic operations and computations are "vectorised" by pandas (either through NumPy, or through Cythonized functions). This includes arithmetic, comparisons, (most) reductions, reshaping (such as pivoting), joins, and groupby operations. Look through the documentation on Essential Basic Functionality to find a suitable vectorised method for your problem.
If none exists, feel free to write your own using custom cython extensions.

---

Next Best Thing: List Comprehensions

List comprehensions should be your next port of call if 1) there is no vectorized solution available, 2) performance is important, but not important enough to go through the hassle of cythonizing your code, and 3) you're trying to perform elementwise transformation on your code. There is a good amount of evidence to suggest that list comprehensions are sufficiently fast (and even sometimes faster) for many common pandas tasks.
The formula is simple,

# iterating over one column - `f` is some function that processes your data
result = [f(x) for x in df['col']]
# iterating over two columns, use `zip`
result = [f(x, y) for x, y in zip(df['col1'], df['col2'])]
# iterating over multiple columns
result = [f(row[0], ..., row[n]) for row in df[['col1', ...,'coln']].values]

If you can encapsulate your business logic into a function, you can use a list comprehension that calls it. You can make arbitrarily complex things work through the simplicity and speed of raw python.

logging

Voorbeeld mbt  logging gebruiken:

"""
voorbeeld van een loghandler die output wegschrijft naar een file.
gebruikt een log handler en overschrijft de file. Wel na herstart spyder anders schrijft ie bij
"""
import logging



logger = logging.getLogger(__name__) 
 
# set log level
logger.setLevel(logging.INFO)
 
# define file handler and set formatter. Overwrite logfile w+
file_handler = logging.FileHandler(r'C:\Users\wagene002\Documents\Python\grip\vergelijkinglZenG\\testlog.log','w+')
 
#formatter    = logging.Formatter('%(asctime)s : %(levelname)s : %(name)s : %(message)s')
formatter    = logging.Formatter()
file_handler.setFormatter(formatter)
logger.addHandler(file_handler)
 

#logging.basicConfig(level=logging.INFO, filename='vergelijkBIster_datamodel.log',filemode='w')

logger.info('Er heeft een verandering plaatsgevonden mbt de volgende tabellen: ')




links
https://www.loggly.com/ultimate-guide/python-logging-basics/
https://tutorialedge.net/python/python-logging-best-practices/
https://realpython.com/python-logging/

BSN valid

# maakt gebruik van stdnum module;   conda install -c hargup/label/pypi python-stdnum
import numpy as np
import pandas as pd
import os
from stdnum.nl import bsn
#------INITIALISATI

def BSN_is_valid(tbsn):
    from stdnum.nl import bsn   
    if bsn.is_valid(tbsn) :
        return 1
    else:
        return 0

Profiling van dataframe

met het handje

# ==> Analyse
df1.info()
df1.nunique()
print(df1.describe())


df1[df1['VALID_BSN'].isnull()]
df1[df1['VALID_BSN']=='0'].info()


df1[df1['BSN'].isnull()]
df1[df1['BSN']=='999999999']


dubbele records
df1[df1.duplicated(subset=None)]





df1=xls_file.parse(0,skiprows=0,dtype=str)

df1=df1.astype({'VD_Ingangsdatum': 'datetime64', 'VD_Einddatum': 'datetime64'})

dictVeldnamen={'Valid_BSN':'VALID_BSN','LR_BSN': 'BSN', 'VD_Ingangsdatum': 'BEGIN_DATUM','VD_Einddatum':'EIND_DATUM','Code_Bron': 'CODE_BRON'}

df1.rename(columns=dictVeldnamen,inplace='true')

df1.drop_duplicates(subset=None, keep='last', inplace=True)

alternatief pandas-profiling 

# importing required packages
import pandas as pd
import pandas_profiling
import numpy as np

# importing the data
df = pd.read_csv('/Users/lukas/Downloads/titanic/train.csv')

pandas_profiling.ProfileReport(df)