<jupyter_start><jupyter_text>geonameid : integer id of record in geonames databasename : name of geographical point (utf8) varchar(200)asciiname : name of geographical point in plain ascii characters, varchar(200)alternatenames : alternatenames, comma separated, ascii names automatically transliterated, convenience attribute from alternatename table, varchar(10000)latitude : latitude in decimal degrees (wgs84)longitude : longitude in decimal degrees (wgs84)feature class : see http://www.geonames.org/export/codes.html, char(1)feature code : see http://www.geonames.org/export/codes.html, varchar(10)country code : ISO-3166 2-letter country code, 2 characterscc2 : alternate country codes, comma separated, ISO-3166 2-letter country code, 200 charactersadmin1 code : fipscode (subject to change to iso code), see exceptions below, see file admin1Codes.txt for display names of this code; varchar(20)admin2 code : code for the second administrative division, a county in the US, see file admin2Codes.txt; varchar(80)admin3 code : code for third level administrative division, varchar(20)admin4 code : code for fourth level administrative division, varchar(20)population : bigint (8 byte int)elevation : in meters, integerdem : digital elevation model, srtm3 or gtopo30, average elevation of 3''x3'' (ca 90mx90m) or 30''x30'' (ca 900mx900m) area in meters, integer. srtm processed by cgiar/ciat.timezone : the iana timezone id (see file timeZone.txt) varchar(40)modification date : date of last modification in yyyy-MM-dd format<jupyter_code>import pandas as pd

columns = ['geonameid', 'name', 'asciiname', 'alternatenames',
           'latitude', 'longitude', 'feature_class', 'feature_code',
           'country code', 'cc2', 'admin1_code', 'admin2 code', 'admin3 code',
           'admin4 code', 'population', 'elevation', 'dem', 'timezone', 'modification_date']

df = pd.read_csv('BF.txt', delimiter='\t', header=None)
df = df.set_axis(columns, axis=1, inplace=False)

df.head(5)<jupyter_output><empty_output><jupyter_text>3. Opérations de prétraitement et filtres nécéssairesOn gardera uniquement* Identifiants, Nom de lieux, latitudes et longitudes<jupyter_code>keptCols = df[['geonameid', 'name', 'latitude', 'longitude']]
keptCols.head(5)<jupyter_output><empty_output><jupyter_text>* Renommage avec les nom suivant *ID*, *location_name*, *lat*, *long*<jupyter_code>colNames = ['ID', 'location_name', 'lat', 'long']
keptCols.columns = colNames
keptCols.head(5)<jupyter_output><empty_output><jupyter_text>* Sauvegarder les données dans un fichier .csv *burkina_location.csv*<jupyter_code>keptCols.to_csv('burkina_location.csv')<jupyter_output><empty_output><jupyter_text>4. Opérations sur le fichier csv *burkina_location.csv* * Extraire les données contenant le nom *'gounghin'* et l'enregistrer sous *'gounghin.csv'*<jupyter_code>burkinaDf = pd.read_csv('burkina_location.csv')
gounghinDf = burkinaDf[burkinaDf['location_name'].str.contains('Gounghin')]
gounghinDf.to_csv('gounghin.csv')
gounghinDf.head(5)<jupyter_output><empty_output><jupyter_text>* Extraction des sous-parties de la BD(fichier *burkina_location.csv*), dont les premières lettres des noms de lieux sont compris entre 'A' et 'P'.<jupyter_code>import string
letters = [x for x in string.ascii_uppercase]
filteredLetters = letters[letters.index('A') : letters.index('Q')]
firstLetterBtwAPDf = burkinaDf[burkinaDf['location_name'].str[0].isin(filteredLetters)]
firstLetterBtwAPDf.head(5)
firstLetterBtwAPDf.tail(5)<jupyter_output><empty_output><jupyter_text>- Identification respective de la latitude, longitude minimale et les noms de lieux correspondants dans le fichier *burkina_location.csv*<jupyter_code>#Latitude minimale
burkinaDf['lat'].min()
# Nom de lieu
burkinaDf.loc[burkinaDf['lat'] == burkinaDf['lat'].min()]
#Longitude minimale
burkinaDf['long'].min()
# Nom de lieu
burkinaDf.loc[burkinaDf['long'] == burkinaDf['long'].min()]<jupyter_output><empty_output><jupyter_text>* Lieux dont les coords sont entre $lat \geq 11$ et $long \leq 0.5$<jupyter_code>burkinaDf.loc[(burkinaDf['lat'] >= 11) & (burkinaDf['long'] <= .5)]<jupyter_output><empty_output><jupyter_text>5. Sorties Excel * Créer un fichier Excel nommé *mini_projet** Créer une feuille *gounghin* et enregistrer les données contenant le nom *'gounghin'** Créer une seconde feuille dans le même fichier, du nom de *'A_to_P'*<jupyter_code>with pd.ExcelWriter('mini_projet.xlsx') as writer:
  gounghinDf.to_excel(writer, sheet_name='gounghin')
  firstLetterBtwAPDf.to_excel(writer, sheet_name='A_to_P')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1208479020979021, 'fraction_numerical': 0.020323426573426572, 'mean_word_length': 7.276672694394214, 'pattern_counts': {'":': 0, '<': 38, '<?xml version=': 0, '>': 38, 'https://': 0, 'lorem ipsum': 0, 'www.': 2, '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}}
{'chain_length': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '172264', 'license': 'CC-BY-4.0', 'n_tokens_mistral': 1689, 'n_tokens_neox': 1597, 'n_words': 396, 'path': 'code.ipynb', 'repo_name': 'IbrahimOued/uvbf-data-processing-mini-project', 'size': '4576'}