<commit_before># -*- coding: UTF-8 -*-
#!/usr/bin/env python3.4
from __future__ import (unicode_literals, print_function)
# -----------------------------------------------------------------------------
# Name:         Parser Geocoder from Excel file
# Purpose:      todo
#
# Author:       <NAME> (https://twitter.com/geojulien)
#
# Python:       2.7.x
# Created:      14/05/2015
# Updated:      15/05/2015
#
# Licence:      GPL 3
# ------------------------------------------------------------------------------

# #############################################################################
# ########## Libraries #############
# ##################################

# Standard library
from calendar import timegm
from datetime import datetime
import locale
from time import mktime

# Python 3 backported

# 3rd party libraries
from openpyxl import load_workbook
from geojson import dump, Feature, FeatureCollection, Point
import pytz

# Custom modules

# #############################################################################
# ######### Main program ##########
# #################################

# ############################# PARTICIPANTS

# Structure attendue ##################################
# col_idx   col_name        description
# 0         ID
# 1         NOM
# 2         TYPE
# 3         DESCR_FR
# 4         DESCR_EN
# 5         WEBSITE
# 6         ADR_NUM
# 7         ADR_CMPLT
# 8         ADR_TYP
# 9         ADR_LIB
# 10        ADR_CP
# 11        ADR_CITY
# 12        ADR_COUNTRY
# 13        ADR_CONCAT
# 14        TEL
# 15        OSM_URL
# 16        GMAPS_URL
# 17        ed_01_2014
# 18        ed_02_2015
# 19        ed_03_2016
# 20        ed14_URL_FR
# 21        ed15_URL_FR
# 22        ed15_URL_EN
# 23        ed16_URL_FR
# 24        ed16_URL_EN
# 25        URL_INSTA
# 26        URL_FB
# 27        URL_TWITTER
# 28        URL_GPLUS
# 29        URL_THUMB
# 30        URL_MAIL
# 31        x_longitude
# 32        y_latitude
# 33        X_NOMINATIM
# 34        Y_NOMINATIM
# 35        LI_ID_EVT
# 36        URL_CITYMAPPER

# /Structure attendue ##################################

# liste pour stocker les objets
li_objs = []

# ouverture du fichier des participants en lecture
wb = load_workbook(filename='ParisBeerWeek_participants.xlsx',
                   read_only=True,
                   guess_types=True,
                   data_only=True,
                   use_iterators=True
                   )

# noms des onglets
# print(wb.get_sheet_names())

ws = wb.worksheets[0]  # ws = première feuille

row_count = ws.get_highest_row() - 1
column_count = ws.get_highest_column() + 1

print(row_count)
print(column_count)

for row in ws.iter_rows(row_offset=1):
    # extraire le nom
    nom = row[1].value

    # vérification qu'il s'agit bien d'une ligne remplie
    if not nom:
        print('\nFin du tableau')
        break
    else:
        pass

    # si l'adresse n'est pas renseignée, on s'arrache
    if not row[26].value and not row[27].value:
        print('\nCoordinates NR, use Geocoder before: ' + str(row[0].value))
        continue
    else:
        pass

    # extraire l'adresse
    # libelle = str(row[6].value) + " " + row[8].value + " " + row[9].value
    # ville = row[11].value
    # addr = nom + ", " + ville + ", France"
    addr = row[13].value

    # extraction des coordonnées
    print(row[31].value)
    longitude = row[31].value
    latitude = row[32].value

    # 
    if not longitude or not latitude:
        continue
    else:
        pass

    # extraction du téléphone
    if row[14].value:
        tel = row[14].value
    else:
        tel = "NR"

    # stockage dans des objets en vue de la sérialisation
    point = Point((longitude, latitude))
    obj = Feature(geometry=point,
                  id=row[0].value,
                  properties={"ADR_COUNTRY": row[12].value,
                              "NAME": nom,
                              "TYPE": row[2].value,
                              "DESCR_FR": row[3].value,
                              "DESCR_EN": row[4].value,
                              "ADDRESS": addr,
                              "TEL": tel,
                              "WEBSITE": row[5].value,
                              "FACEBOOK": row[22].value,
                              "TWITTER": row[23].value,
                              "PBW_16_FR": row[23].value,
                              "PBW_16_EN": row[24].value,
                              "THUMBNAIL": row[25].value,
                              "OSM": row[15].value,
                              "GMAPS": row[16].value,
                              "CITYMAPPER": row[36].value
                              })
    li_objs.append(obj)

# sérialisation en GeoJSON
featColl = FeatureCollection(li_objs)
with open("../ParisBeerWeek_participants.geojson", "w") as outfile:
    dump(featColl, outfile, sort_keys=True)


# ############################## EVENEMENTS

# Structure attendue ##################################
# col_idx   col_name        description
# 0         ID
# 1         EVT_NOM
# 2         TYPE
# 3         DESCR_FR
# 4         DESCR_EN
# 5         ADR_ID
# 6         ADR_NOM
# 7         ADR_PARTIC
# 8         ADR_NUM
# 9         ADR_COMPL
# 10        ADR_TYP
# 11        ADR_LIB
# 12        ADR_CP
# 13        ADR_CITY
# 14        ADR_COUNTRY
# 15        ADR_CONCAT
# 16        ADR_IMG<commit_msg>adding foursquare and untappd ids<commit_after># -*- coding: UTF-8 -*-
#!/usr/bin/env python3.4
from __future__ import (unicode_literals, print_function)
# -----------------------------------------------------------------------------
# Name:         Parser Geocoder from Excel file
# Purpose:      todo
#
# Author:       <NAME> (https://twitter.com/geojulien)
#
# Python:       2.7.x
# Created:      14/05/2015
# Updated:      15/05/2015
#
# Licence:      GPL 3
# ------------------------------------------------------------------------------

# #############################################################################
# ########## Libraries #############
# ##################################

# Standard library
from calendar import timegm
from datetime import datetime
import locale
from time import mktime

# Python 3 backported

# 3rd party libraries
from openpyxl import load_workbook
from geojson import dump, Feature, FeatureCollection, Point
import pytz

# Custom modules

# #############################################################################
# ######### Main program ##########
# #################################

# ############################# PARTICIPANTS

# Structure attendue ##################################
# col_idx   col_name        description
# 0         ID
# 1         NOM
# 2         TYPE
# 3         DESCR_FR
# 4         DESCR_EN
# 5         WEBSITE
# 6         ADR_NUM
# 7         ADR_CMPLT
# 8         ADR_TYP
# 9         ADR_LIB
# 10        ADR_CP
# 11        ADR_CITY
# 12        ADR_COUNTRY
# 13        ADR_CONCAT
# 14        TEL
# 15        OSM_URL
# 16        GMAPS_URL
# 17        ed_01_2014
# 18        ed_02_2015
# 19        ed_03_2016
# 20        ed14_URL_FR
# 21        ed15_URL_FR
# 22        ed15_URL_EN
# 23        ed16_URL_FR
# 24        ed16_URL_EN
# 25        URL_INSTA
# 26        URL_FB
# 27        URL_TWITTER
# 28        URL_GPLUS
# 29        URL_THUMB
# 30        URL_MAIL
# 31        x_longitude
# 32        y_latitude
# 33        X_NOMINATIM
# 34        Y_NOMINATIM
# 35        LI_ID_EVT
# 36        URL_CITYMAPPER

# /Structure attendue ##################################

# liste pour stocker les objets
li_objs = []

# ouverture du fichier des participants en lecture
wb = load_workbook(filename='ParisBeerWeek_participants.xlsx',
                   read_only=True,
                   guess_types=True,
                   data_only=True,
                   use_iterators=True
                   )

# noms des onglets
# print(wb.get_sheet_names())

ws = wb.worksheets[0]  # ws = première feuille

row_count = ws.get_highest_row() - 1
column_count = ws.get_highest_column() + 1

print(row_count)
print(column_count)

for row in ws.iter_rows(row_offset=1):
    # extraire le nom
    nom = row[1].value

    # vérification qu'il s'agit bien d'une ligne remplie
    if not nom:
        print('\nFin du tableau')
        break
    else:
        pass

    # si l'adresse n'est pas renseignée, on s'arrache
    if not row[26].value and not row[27].value:
        print('\nCoordinates NR, use Geocoder before: ' + str(row[0].value))
        continue
    else:
        pass

    # extraire l'adresse
    # libelle = str(row[6].value) + " " + row[8].value + " " + row[9].value
    # ville = row[11].value
    # addr = nom + ", " + ville + ", France"
    addr = row[13].value

    # extraction des coordonnées
    print(row[31].value)
    longitude = row[31].value
    latitude = row[32].value

    # 
    if not longitude or not latitude:
        continue
    else:
        pass

    # extraction du téléphone
    if row[14].value:
        tel = row[14].value
    else:
        tel = "NR"

    # stockage dans des objets en vue de la sérialisation
    point = Point((longitude, latitude))
    obj = Feature(geometry=point,
                  id=row[0].value,
                  properties={"ADR_COUNTRY": row[12].value,
                              "NAME": nom,
                              "TYPE": row[2].value,
                              "DESCR_FR": row[3].value,
                              "DESCR_EN": row[4].value,
                              "ADDRESS": addr,
                              "TEL": tel,
                              "WEBSITE": row[5].value,
                              "FACEBOOK": row[22].value,
                              "TWITTER": row[23].value,
                              "PBW_16_FR": row[23].value,
                              "PBW_16_EN": row[24].value,
                              "THUMBNAIL": row[25].value,
                              "OSM": row[15].value,
                              "GMAPS": row[16].value,
                              "CITYMAPPER": row[36].value,
                              "FS_ID": row[37].value,
                              "UTP_ID": row[38].value
                              })
    li_objs.append(obj)

# sérialisation en GeoJSON
featColl = FeatureCollection(li_objs)
with open("../ParisBeerWeek_participants.geojson", "w") as outfile:
    dump(featColl, outfile, sort_keys=True)


# ############################## EVENEMENTS

# Structure attendue ##################################
# col_idx   col_name        description
# 0         ID
# 1         EVT_NOM
# 2         TYPE
# 3         DESCR_FR
# 4         DESCR_EN
# 5         ADR_ID
# 6         ADR_NOM
# 7         ADR_PARTIC
# 8         ADR_NUM
# 9         ADR_COMPL
# 10        ADR_TYP
# 11        ADR_LIB
# 12        ADR_CP
# 13        ADR_CITY
# 14        ADR_COUNTRY
# 15        ADR_CONCAT
# 16        ADR_IMG
{'fraction_non_alphanumeric': 0.18899807144825054, 'fraction_numerical': 0.035999632656809624, 'mean_word_length': 1.6272617611580218, 'pattern_counts': {'":': 34, '<': 5, '<?xml version=': 0, '>': 5, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '5687768', 'n_tokens_mistral': 3690, 'n_tokens_neox': 3311, 'n_words': 761}