<jupyter_start><jupyter_text>Exploring specifically the Input Text Data for Training and Verification<jupyter_code>import pandas as pd
import numpy as np
import os

import matplotlib.pyplot as plt

%pylab inline
pd.set_option('display.max_rows', 200)
pd.set_option('display.max_columns', 200)
pd.set_option('display.width', 100)<jupyter_output><empty_output><jupyter_text>Loading Data<jupyter_code>path_file = os.path.join(os.path.dirname(os.getcwd()), "data", "raw", "CRS2018data.txt")
df = pd.read_csv(path_file, sep='|', encoding='ISO-8859-1')
df.head()
for item in df.columns:
    print(item)<jupyter_output>Year
DonorCode
DonorName
AgencyCode
AgencyName
CrsID
ProjectNumber
InitialReport
RecipientCode
RecipientName
RegionCode
RegionName
IncomegroupCode
IncomegroupName
FlowCode
FlowName
Bi_Multi
Category
Finance_t
Aid_t
USD_Commitment
USD_Disbursement
USD_Received
USD_Commitment_Defl
USD_Disbursement_Defl
USD_Received_Defl
USD_Adjustment
USD_Adjustment_Defl
USD_AmountUntied
USD_AmountPartialTied
USD_AmountTied
USD_AmountUntied_Defl
USD_AmountPartialTied_Defl
USD_Amounttied_Defl
USD_IRTC
USD_Expert_Commitment
USD_Expert_Extended
USD_Export_Credit
CurrencyCode
Commitment_National
Disbursement_National
GrantEquiv
USD_GrantEquiv
ShortDescription
ProjectTitle
PurposeCode
PurposeName
SectorCode
SectorName
ChannelCode
ChannelName
ChannelReportedName
ParentChannelCode
Geography
ExpectedStartDate
CompletionDate
LongDescription
SDGfocus
Gender
Environment
PDGG
Trade
RMNCH
DRR
Nutrition
Disability
FTC
PBA
InvestmentProject
AssocFinance
Biodiversity
ClimateMitigation
ClimateAdaptation
Desertification
C[...]<jupyter_text>Checking SDGs<jupyter_code>df['SDGfocus'] = df['SDGfocus'].replace(np.nan, 'EMPTY_SDG').apply(str)
df['SDGfocusArray'] = df['SDGfocus'].apply(lambda x: [item.split('.')[0] for item in x.split(';')])
df['SDGfocusNumber'] = df['SDGfocusArray'].apply(lambda x: len(x))<jupyter_output><empty_output><jupyter_text>Finding Specific Assignment of SDG per Project<jupyter_code>df['EMPTY_SDG'] = df['SDGfocusArray'].apply(lambda x: True if 'EMPTY_SDG' in x else False)
df['SDG1'] = df['SDGfocusArray'].apply(lambda x: True if '1' in x else False)
df['SDG2'] = df['SDGfocusArray'].apply(lambda x: True if '2' in x else False)
df['SDG3'] = df['SDGfocusArray'].apply(lambda x: True if '3' in x else False)
df['SDG4'] = df['SDGfocusArray'].apply(lambda x: True if '4' in x else False)
df['SDG5'] = df['SDGfocusArray'].apply(lambda x: True if '5' in x else False)
df['SDG6'] = df['SDGfocusArray'].apply(lambda x: True if '6' in x else False)
df['SDG7'] = df['SDGfocusArray'].apply(lambda x: True if '7' in x else False)
df['SDG8'] = df['SDGfocusArray'].apply(lambda x: True if '8' in x else False)
df['SDG9'] = df['SDGfocusArray'].apply(lambda x: True if '9' in x else False)
df['SDG10'] = df['SDGfocusArray'].apply(lambda x: True if '10' in x else False)
df['SDG11'] = df['SDGfocusArray'].apply(lambda x: True if '11' in x else False)
df['SDG12'] = df['SDGfocusArray'].apply(lambda x: True if '12' in x else False)
df['SDG13'] = df['SDGfocusArray'].apply(lambda x: True if '13' in x else False)
df['SDG14'] = df['SDGfocusArray'].apply(lambda x: True if '14' in x else False)
df['SDG15'] = df['SDGfocusArray'].apply(lambda x: True if '15' in x else False)
df['SDG16'] = df['SDGfocusArray'].apply(lambda x: True if '16' in x else False)
df['SDG17'] = df['SDGfocusArray'].apply(lambda x: True if '17' in x else False)
# https://stackoverflow.com/questions/33388867/creating-circular-flow-charts-circos
# https://vega.github.io/vega/examples/edge-bundling/
columns_sdg = ['EMPTY_SDG', 'SDG1', 'SDG2', 'SDG3', 'SDG4', 'SDG5', 'SDG6', 'SDG7', 'SDG8', 'SDG9', 
               'SDG10', 'SDG11', 'SDG12', 'SDG13', 'SDG14', 'SDG15', 'SDG16', 'SDG17']
list_sdgs = df[columns_sdg].sum()
list_sdgs_per = df[columns_sdg].sum()/len(df)*100
list_sdgs.plot.bar(figsize=(16, 6))<jupyter_output><empty_output><jupyter_text>Working Only with SDG Data<jupyter_code>df_sdg = df[ df['EMPTY_SDG'] == False ]
len(df_sdg)
list_sdgs = df_sdg[columns_sdg].sum()
list_sdgs.plot.bar(figsize=(16, 6))<jupyter_output><empty_output><jupyter_text>Aid (MONEY) per SDG - NEED TO CHECK<jupyter_code>usd_dict = {}
for sdg in columns_sdg:
    if sdg != 'EMPTY_SDG':
        df_only = df_sdg[ df_sdg[sdg] == True]
        usd_dict[sdg] = df_only['USD_Commitment'].sum()
usd_series = pd.DataFrame.from_dict(usd_dict, orient='index')
usd_series.plot.bar(figsize=(16, 6))
usd_series<jupyter_output><empty_output><jupyter_text>TOP Donors<jupyter_code>for sdg in columns_sdg:
    if sdg != 'EMPTY_SDG':
        df_only = df_sdg[ df_sdg[sdg] == True]
        print('####### TOP DONORS FOR:', sdg)
        print(df_only.groupby(['DonorName']).count()['Year'].sort_values(ascending=False).head())<jupyter_output>####### TOP DONORS FOR: SDG1
DonorName
Switzerland    2104
Canada         1118
France          995
Australia       703
UNDP            498
Name: Year, dtype: int64
####### TOP DONORS FOR: SDG2
DonorName
Switzerland    2406
Canada         1330
Spain           699
UNICEF          520
Australia       487
Name: Year, dtype: int64
####### TOP DONORS FOR: SDG3
DonorName
Canada                             5164
Switzerland                        1552
UNICEF                             1230
Bill & Melinda Gates Foundation    1178
Spain                               621
Name: Year, dtype: int64
####### TOP DONORS FOR: SDG4
DonorName
Spain          2480
Switzerland    1588
UNICEF         1368
France         1313
Canada         1043
Name: Year, dtype: int64
####### TOP DONORS FOR: SDG5
DonorName
Canada                          1662
Switzerland                     1177
BBVA Microfinance Foundation     861
Spain                            769
Australia                        512
Name: Year, dtype: i[...]<jupyter_text>Bill & Melinda Gates<jupyter_code>bill_gates = df_sdg[ df_sdg['DonorName'].str.contains('Gates') ]
bill_gates.head()
bill_gates[columns_sdg].sum().plot.bar(figsize=(16, 6))<jupyter_output><empty_output><jupyter_text>Per Region<jupyter_code>region_list = []
for sdg in columns_sdg:
    if sdg != 'EMPTY_SDG':
        df_only = df_sdg[ df_sdg[sdg] == True]
        # Get totals for SDG
        region_total = df_only.groupby(['RegionName'])['USD_Commitment'].sum()
        # Initialise
        row = {"SDG": sdg}
        for region in region_total.iteritems():
            row[region[0]] = region[1]
        # Append to Final List
        region_list.append(row)
region_list_df = pd.DataFrame.from_dict(region_list)
region_list_df.head()
region_list_df.set_index('SDG').plot.bar(stacked=True, figsize=(16, 6))
region_list_df.head()
# https://github.com/fengwangPhysics/matplotlib-chord-diagram
# https://stackoverflow.com/questions/33388867/creating-circular-flow-charts-circos<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13076697032030563, 'fraction_numerical': 0.03423449897149574, 'mean_word_length': 4.644278606965174, 'pattern_counts': {'":': 1, '<': 35, '<?xml version=': 0, '>': 35, 'https://': 4, '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}}
{'chain_length': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '199671', 'license': 'MIT', 'n_tokens_mistral': 2679, 'n_tokens_neox': 2500, 'n_words': 550, 'path': 'notebooks/4.1-pb-ExploratoryDataAnalysisTestColumn.ipynb', 'repo_name': 'WisConT/oecd_financial_flows', 'size': '6806'}