In [ ]:
import pandas as pd
In [ ]:
appellation = "AOC Crémant d'Alsace"
#appellation = "AOC Alsace blanc"
vci_2018 = pd.read_csv("registre_2018.csv", delimiter=";", decimal=",", dtype={'CVI': 'str', 'SIRET': 'str', 'Produit': 'str'})
vci_2017 = pd.read_csv("registre_2017.csv", delimiter=";", decimal=",", dtype={'CVI': 'str', 'SIRET': 'str', 'Produit': 'str'})
drev_2018 = pd.read_csv("drev_2018.csv", delimiter=";", decimal=",", dtype={'CVI Opérateur': 'str'})
infos = pd.concat([vci_2018,vci_2017], ignore_index=True)
In [ ]:
vci_2018.head(5)
In [ ]:
vci_2018.columns.tolist()
In [ ]:
vci_2018[vci_2018['Produit'].str.contains(appellation)]
In [ ]:
vci_2018_group = vci_2018[vci_2018['Produit'].str.contains(appellation)].iloc[:,[1,10,11,12,13,14,15,16,17]].groupby(['CVI', 'Stockage']).agg('sum').reset_index()
vci_2017_group = vci_2017[vci_2017['Produit'].str.contains(appellation)].iloc[:,[1,10,11,12,13,14,15,16,17]].groupby(['CVI', 'Stockage']).agg('sum').reset_index()
In [ ]:
vci_2018_group.head()
In [ ]:
vci_2018_group.describe()
In [ ]:
vci_2017_group.head()
In [ ]:
vci_2017_group.describe()
In [ ]:
registres = pd.merge(vci_2017_group, vci_2018_group, how='outer', on=['CVI', 'Stockage'])
In [ ]:
registres.head()
In [ ]:
registres.describe()
In [ ]:
type_ligne="Revendication"
drev_2018['type_ligne'] = drev_2018['Type de ligne']
drev_2018.query("Produit == @appellation and type_ligne == @type_ligne")
In [ ]:
drev_2018_group = drev_2018.query("Produit == @appellation and type_ligne == @type_ligne").iloc[:,[1,10,11,12,13]].reset_index()
In [ ]:
drev_2018_group.head()
In [ ]:
drev_2018_group.describe()
In [ ]:
bilan = pd.merge(registres, drev_2018_group, how='left', left_on='CVI', right_on='CVI Opérateur')
In [ ]:
infos.head()
In [ ]:
infos_unique = infos.reindex(columns=["CVI", "SIRET", "Raison sociale", "Adresse", "Code postal", "Commune"]).drop_duplicates().reset_index();
In [ ]:
infos_unique.head()
In [ ]:
bilan_infos = pd.merge(bilan, infos_unique, how='left', on='CVI')
In [ ]:
bilan_infos.head()
In [ ]:
bilan_infos.describe()
In [ ]:
bilan_infos['titre'] = ""
bilan_infos['raison_sociale'] = bilan_infos["Raison sociale"]
bilan_infos['adresse'] = bilan_infos["Adresse"]
bilan_infos['commune'] = bilan_infos["Commune"]
bilan_infos['code_postal'] = bilan_infos["Code postal"]
bilan_infos['siret'] = bilan_infos["SIRET"]
bilan_infos['stock_vci_n-1'] = bilan_infos["Constitue_x"] + bilan["Stock précédent_x"]
bilan_infos['dr_surface'] = bilan_infos["Superficie revendiqué"]
bilan_infos['dr_volume'] = bilan_infos["Volume revendiqué"]
bilan_infos['dr_vci'] = bilan_infos["Constitue_y"]
bilan_infos['vci_complement'] = bilan_infos["Complément_x"]
bilan_infos['vci_substitution'] = bilan_infos["Substitution_x"]
bilan_infos['vci_rafraichi'] = bilan_infos["Rafraichi_x"]
bilan_infos['vci_desctruction'] = bilan_infos["Destruction_x"]
bilan_infos['drev_revendique_n'] = bilan_infos["Volume revendiqué"] - bilan["Volume revendiqué issu du VCI"]
bilan_infos['drev_revendique_n-1'] = bilan_infos["Volume revendiqué issu du VCI"]
bilan_infos['stock_vci_n'] = bilan_infos["Stock_y"]
In [ ]:
bilan_final = bilan_infos.reindex(columns=["titre", "raison_sociale", "adresse", "commune", "code_postal", "CVI", "siret", "Stockage", "stock_vci_n-1", "dr_surface", "dr_volume", "dr_vci", "vci_complement", "vci_substitution", "vci_rafraichi", "vci_desctruction", "drev_revendique_n", "drev_revendique_n-1", "stock_vci_n"])
In [ ]:
bilan_final.describe()
In [ ]:
bilan_final.head()
In [ ]:
bilan_final.to_csv("bilan_2018_"+appellation+".csv", sep=";", index=False, decimal=",")
In [ ]:
In [ ]:
In [ ]: