<jupyter_start><jupyter_text>1. The 10 countries with most projects<jupyter_code># loading json project file using with open() context manager
with open('data/world_bank_projects.json') as file:
    data = json.load(file)
# Creating a df from json data file
df = pd.DataFrame(data)
#df.head()
# creating another df using json_normalize for task 2 
df_themes = json_normalize(data,'mjtheme_namecode', ['id'])
print('Top 10 countries with most projects')
df['countryshortname'].value_counts().head(10)

#df.groupby(['countryshortname'])['countrycode'].value_counts().sort_values(ascending=False).head(10)
# Grouping the df by country name and also aggregating the count of project_name column to get the top 10 countries with most projects.
#print('Top 10 countries with most projects')
#df.groupby(['countryshortname']).agg({'countrycode':'count'}).sort_values(by='countrycode', ascending=False).head(10)
#df_themes.groupby('id').code.nunique().sort_values(ascending=False).head(10)
#df['mjtheme_namecode']<jupyter_output><empty_output><jupyter_text>2. The top 10 major project themes (using column 'mjtheme_namecode')<jupyter_code># Some projects include code repeats. For example, the fourth project includes Code 7 twice. 
#df['mjtheme_namecode'][3]
#print(df.mjtheme_namecode[3][0]['code'])
#print(df.mjtheme_namecode[3][1]['name'])
#int(df.mjtheme_namecode[3][0]['code'])<jupyter_output><empty_output><jupyter_text>The counting method below will make sure that multiple projects from one code are only counted once.<jupyter_code>'''# code_nums is the highest number of mjtheme codes - 1 - 11
code_nums = 11

# dic will store the number of projects that refer to each code number.

dic = { }

# Initialize each code's count at zero. 
for i in range(1, code_nums+1):
    dic[i] = 0
    
    
# Move through each set of stored codes for each project to count the number of projects that include each code. 
for x in range(len(df)):
    # c stores the codes used for each project
    c = {}
    for y in range(len(df.mjtheme_namecode[x])):
        c[int(df.mjtheme_namecode[x][y]['code'])] = 1
        #print(c.keys())
    
    # Adds the c to overall count in md
    for i in c.keys():
        dic[i] += 1
    #print(dic)

#mj_df, stores the number of projects that refer to each code number, converting dic to a series 
# that I can now use Pandas to analyze the top occurences.
 
mj_df = pd.Series(dic)
mj_df.sort_values(ascending=False).head(10)'''
# using json_normalized method and then using the value_count() method to get
# the top 10 major projects - This method counts the duplicates
print('Top 10 Major World Bank Project Themes:')
df_themes.name.value_counts().head(10)<jupyter_output>Top 10 Major World Bank Project Themes:<jupyter_text>3. In 2. above you will notice that some entries have only the code and the name is missing.<jupyter_code># The project_names list is in order from index [0] which is blank to index [11] which is the highest major theme code. 
# I will loop through the df and whereever the name is missing, I will use the code(index) and grab the name with the 
#same index from this project_names list.
project_names = ['','Economic management','Public sector governance','Rule of law',
                 'Financial and private sector development','Trade and integration',
                 'Social protection and risk management','Social dev/gender/inclusion',
                 'Human development','Urban development','Rural development',
                 'Environment and natural resources management']
#project_names2 = []
#for i in range(len(df['mjtheme'])):
#               project_names2.append(df['mjtheme'][i])
#project_names2
#df['mjtheme'].unique()
#df['mjtheme'].value_counts()
df_new = df.copy()
# Looks through each code name, and if it is empty, stores the proper name in its place
for x in range(len(df_new.mjtheme_namecode)):
    for y in range(len(df_new.mjtheme_namecode[x])):
        if(df_new.mjtheme_namecode[x][y]['name'] == ""):
            df_new.mjtheme_namecode[x][y]['name'] = project_names[int(df_new.mjtheme_namecode[x][y]['code'])]
# looping through the df to make sure there are no missing names
for x in range(len(df_new.mjtheme_namecode)):
    for y in range(len(df_new.mjtheme_namecode[x])):
        print(df_new.mjtheme_namecode[x][y])
# Changing the original df mjtheme_namecode column with the new df mjtheme_namecode column
df['mjtheme_namecode'] = df_new['mjtheme_namecode']<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11480902389993299, 'fraction_numerical': 0.012731740004467277, 'mean_word_length': 4.838331160365058, 'pattern_counts': {'":': 0, '<': 16, '<?xml version=': 0, '>': 16, 'https://': 0, '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': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '205707', 'license': 'MIT', 'n_tokens_mistral': 1458, 'n_tokens_neox': 1381, 'n_words': 459, 'path': 'data_wrangling_json/World Bank Mini Project.ipynb', 'repo_name': 'atalebizadeh/DS-Career-Track', 'size': '4477'}