def load_data():
sales_data = sd.get_sales_data()
anagrafica = an.create_anagrafica(3, file='AllData/anagrafica_AI.xlsx')
market_data = md.get_market_data().drop(columns='Name Type',
axis=1).fillna(0)
market_data = md.remove_dupplicates(market_data.drop(columns='Unnamed: 0'))
anagrafica['Material'] = anagrafica['Material'].astype(int)
integration = pd.read_csv('AllData/crtSAPIMS.csv', sep=';')
market_data_pb = md.get_probiotici_csv()
integration_probiotici = pd.read_csv('AllData/crtSAPQlik.csv', sep=';')
return sales_data, anagrafica, market_data, market_data_pb, integration, integration_probiotici
def load_anagrafica():
return an.create_anagrafica(3, file='AllData/anagrafica_AI.xlsx')
Created on Wed Jan 16 14:28:05 2019
@author: arlind
"""
from openpyxl import load_workbook
import pandas as pd
import ai_analysis.anagrafica as an
import ai_analysis.data_market as md
import ai_analysis.automatic_integration.integration_utils as iu
wb = load_workbook(filename='ai_analysis/Integration.xlsx')
pack_dataframe_tct = iu.create_df_fromsheet(wb['Dosage Form'], 1)
molecule_dataframe_tct = iu.create_df_fromsheet(wb['Molecule'], 1)
molecule_dataframe_tct = molecule_dataframe_tct[
molecule_dataframe_tct['external'].notnull()][['external', 'internal']]
anagrafica = an.create_anagrafica(
1, file='Supply&Demand/anagrafica_AI_moreInfo.xlsx', sheet_name='needed')
anagrafica = iu.transform_anagrafica(anagrafica)
market_data = md.create_market_data_from_csv(
filepath='Market Data/OnlyNecDataSubsetMolecules.csv', separator=';')
market_data = iu.trasform_market_data(market_data)
anagrafica_external_mol = anagrafica.merge(
molecule_dataframe_tct.rename(columns={'internal': 'GMD AS ID'}),
how='inner',
on='GMD AS ID')
anagrafica_external_mol_pack = anagrafica_external_mol.merge(
pack_dataframe_tct.rename(columns={'internal': 'GMD Dosage Form'}),
how='inner',
on='GMD Dosage Form')
anagrafica_external_mol_pack[
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Created on Tue Jan 15 10:41:11 2019
@author: arlind
"""
import pandas as pd
import ai_analysis.anagrafica as an
import ai_analysis.data_market as dm
a = an.create_anagrafica(2,
file='Supply&Demand/anagrafica_AI_moreInfo1.xlsx',
sheet_name='report Bi')
a = a[a['ECC - Local Product Status'] == '40']
molecules = pd.DataFrame(a['GMD AS ID'].unique())
molecules.columns = ['Molecule']
molecules['Molecule1'] = molecules['Molecule']
dt_market_data = dm.create_market_data_from_csv()
un_mol_market_data = pd.DataFrame(dt_market_data['Molecule'].unique())
un_mol_market_data.columns = ['Molecule']
un_mol_market_data['Molecule1'] = un_mol_market_data['Molecule']
diff = molecules.merge(un_mol_market_data,
how='left',
suffixes=('_internal', '_external'),
on=('Molecule'))
diff = diff[diff['Molecule1_external'] == diff['Molecule1_internal']]
splitted_mol_an = molecules
def create_lineage_csv(file_path=''):
anagrafica = an.create_anagrafica(3,
file='AllData/anagrafica_AI.xlsx',
sheet_name='report Bi')
anagrafica_dub = anagrafica[anagrafica.duplicated(subset=[
'GMD AS ID', 'GMD Tot.pack size', 'Strength - Text', 'Brand',
'GMD Dosage Form'
])][[
'GMD AS ID', 'GMD Tot.pack size', 'Strength - Text', 'Brand',
'GMD Dosage Form'
]].drop_duplicates()
anagrafica_to_modify = anagrafica[[
'Material', 'ECC - Local Product Status', 'GMD AS ID',
'GMD Tot.pack size', 'Strength - Text', 'Brand', 'Descrizione',
'GMD Dosage Form'
]].merge(anagrafica_dub,
on=[
'GMD AS ID', 'GMD Tot.pack size', 'Strength - Text', 'Brand',
'GMD Dosage Form'
],
how='inner')
unique_gmd = anagrafica_to_modify[
(anagrafica_to_modify['ECC - Local Product Status'] == '40') |
(anagrafica_to_modify['ECC - Local Product Status'] == '35')].groupby(
[
'GMD AS ID', 'GMD Tot.pack size', 'Strength - Text', 'Brand',
'ECC - Local Product Status', 'GMD Dosage Form'
],
as_index=False)['Material'].min()
unique_gmd = unique_gmd.merge(
unique_gmd.groupby([
'GMD AS ID', 'GMD Tot.pack size', 'Strength - Text', 'Brand',
'GMD Dosage Form'
],
as_index=False)['ECC - Local Product Status'].max())
to_replace = dlt.create_dict_to_replace_by_mol_pack_size(
anagrafica_to_modify, unique_gmd)
tosave_dict = {'old': [''], 'new': ['']}
for x in to_replace.keys():
tosave_dict['old'].append(x)
tosave_dict['new'].append(to_replace[x])
dataframetosave = pd.DataFrame.from_dict(tosave_dict)
dataframetosavewithdescription = dataframetosave.rename(
columns={
'old': 'MaterialOld',
'new': 'MaterialNew'
}).merge(anagrafica[['Descrizione', 'Material', 'GMD FDF ID',
'Brand']].rename(
columns={
'Material': 'MaterialOld',
'Descrizione': 'DescrizioneOld',
'GMD FDF ID': 'GMD FDF ID old',
'Brand': 'Brand old'
})).merge(anagrafica[[
'Descrizione', 'Material', 'GMD FDF ID',
'Brand'
]].rename(
columns={
'Material': 'MaterialNew',
'Descrizione': 'DescrizioneNew',
'GMD FDF ID': 'GMD FDF ID new',
'Brand': 'Brand New'
}))
dataframetosavewithdescripitonfiltered = dataframetosavewithdescription[(
dataframetosavewithdescription['GMD FDF ID old'] !=
dataframetosavewithdescription['GMD FDF ID new'])]
dataframetosavewithdescription.to_csv(path_or_buf=file_path +
'AllTransformations.csv',
sep=';')
dataframetosavewithdescripitonfiltered.to_csv(path_or_buf=file_path +
'ParticularCases.csv',
sep=';')