Introduction to data science in python assignment 3

About the Course

This course will introduce the learner to the basics of the python programming environment, including fundamental python programming techniques such as lambdas, reading and manipulating csv files, and the numpy library. The course will introduce data manipulation and cleaning techniques using the popular python pandas data science library and introduce the abstraction of the Series and DataFrame as the central data structures for data analysis, along with tutorials on how to use functions such as groupby, merge, and pivot tables effectively. By the end of this course, students will be able to take tabular data, clean it, manipulate it, and run basic inferential statistical analyses. This course should be taken before any of the other Applied Data Science with Python courses: Applied Plotting, Charting & Data Representation in Python, Applied Machine Learning in Python, Applied Text Mining in Python, Applied Social Network Analysis in Python....

Top reviews

PK

May 9, 2020

The course had helped in understanding the concepts of NumPy and pandas. The assignments were so helpful to apply these concepts which provide an in-depth understanding of the Numpy as well as pandans

YY

Sep 28, 2021

This is the practical course.There is some concepts and assignments like: pandas, data-frame, merge and time. The asg 3 and asg4 are difficult but I think that it's very useful and improve my ability.

I have some problem with Assignment 3 (https://github.com/AparaV/intro-to-data-science-with-python/tree/master/assignment-03). I expect there are no 'Nan' in result. but there are 'Nan' value. this is my first time to study programming language. It would be great if someone could tell me what is wrong with my python code. here is the picture of wanted result and result using codes below

import pandas as pd
import numpy as np
import re
def answer_one():
    def energy():
        energy = pd.read_excel('Energy Indicators.xls',sheet_name='Energy')
        energy = energy.iloc[16:243]
        energy.drop(['Unnamed: 0','Unnamed: 1'], axis ='columns',inplace=True)
        energy.columns = ['Country', 'Energy Supply', 'Energy Supply per Capita', '% Renewable']

        energy = energy.replace('...',np.nan)
        energy['Energy Supply'] = energy['Energy Supply']*1000000

        energy = energy.replace('Republic of Korea','South Korea')
        energy = energy.replace('United States of America','United States')
        energy = energy.replace('United Kingdom of Great Britain and Northern Ireland','United Kingdom')
        energy = energy.replace('China, Hong Kong Special Administrative Region','Hong Kong')

        energy['Country'] =  energy['Country'].str.replace(" \(.*\)","")

        energy = energy.reset_index()
        energy = energy[['Country', 'Energy Supply', 'Energy Supply per Capita', '% Renewable']]
        return energy

    def GDP():
        GDP= pd.read_csv('world_bank.csv')
        s=(GDP.iloc[3].values)[:4].astype(str).tolist() + (GDP.iloc[3].values)[4:].astype(int).astype(str).tolist()
        GDP = GDP[4:]
        GDP.columns = s
        GDP = GDP[['Country Name','2006', '2007', '2008', '2009', '2010', '2011', '2012', '2013', '2014', '2015']]
        GDP.columns = ['Country','2006', '2007', '2008', '2009', '2010', '2011', '2012', '2013', '2014', '2015']

        GDP = GDP.replace('Korea, Rep.','South Korea')
        GDP = GDP.replace('Iran, Islamic Rep','Iran')
        GDP = GDP.replace('Hong Kong SAR, China','Hong Kong')
        return GDP

    def ScimEn():
        ScimEn = pd.read_excel('scimagojr-3.xlsx')
        return ScimEn

    e = energy() 
    g = GDP()
    s = ScimEn()

    df=pd.merge(e,g,how = 'outer',left_on='Country',right_on='Country')
    df=pd.merge(s,df,how='outer',left_on='Country',right_on='Country')
    df.sort_values(by=['Rank'], inplace = True)
    df.set_index('Country',inplace=True)
    res = df.head(15)
    return res
answer_one()

preface

This chapter is the third Assignment of Introduction to Data Science in Python. The main task of this chapter is to select the appropriate method of summarizing and merging data tables based on the three tables given and on the understanding of various merging processes, that is, row data summary, clean and merge the data, and cluster the data by using the function of DataFrame, Calculate and view the statistical description value, find the maximum and minimum items and other data processing, which requires skilled use of various technologies.

1, Q1

1.1 problem description
Load the energy data from the file assets/Energy Indicators.xls, which is a list of indicators of energy supply and renewable electricity production from the United Nations for the year 2013, and should be put into a DataFrame with the variable name of Energy.

Keep in mind that this is an Excel file, and not a comma separated values file. Also, make sure to exclude the footer and header information from the datafile. The first two columns are unneccessary, so you should get rid of them, and you should change the column labels so that the columns are:

['Country', 'Energy Supply', 'Energy Supply per Capita', '% Renewable]

Convert Energy Supply to gigajoules (Note: there are 1,000,000 gigajoules in a petajoule). For all countries which have missing data (e.g. data with "...") make sure this is reflected as np.NaN values.

Rename the following list of countries (for use in later questions):

"Republic of Korea": "South Korea",
"United States of America": "United States",
"United Kingdom of Great Britain and Northern Ireland": "United Kingdom",
"China, Hong Kong Special Administrative Region": "Hong Kong"

There are also several countries with parenthesis in their name. Be sure to remove these, e.g. 'Bolivia (Plurinational State of)' should be 'Bolivia'.

Next, load the GDP data from the file assets/world_bank.csv, which is a csv containing countries' GDP from 1960 to 2015 from World Bank. Call this DataFrame GDP.

Make sure to skip the header, and rename the following list of countries:

"Korea, Rep.": "South Korea", 
"Iran, Islamic Rep.": "Iran",
"Hong Kong SAR, China": "Hong Kong"

Finally, load the Sciamgo Journal and Country Rank data for Energy Engineering and Power Technology from the file assets/scimagojr-3.xlsx, which ranks countries based on their journal contributions in the aforementioned area. Call this DataFrame ScimEn.

Join the three datasets: GDP, Energy, and ScimEn into a new dataset (using the intersection of country names). Use only the last 10 years (2006-2015) of GDP data and only the top 15 countries by Scimagojr 'Rank' (Rank 1 through 15).

The index of this DataFrame should be the name of the country, and the columns should be ['Rank', 'Documents', 'Citable documents', 'Citations', 'Self-citations',
'Citations per document', 'H index', 'Energy Supply',
'Energy Supply per Capita', '% Renewable', '2006', '2007', '2008',
'2009', '2010', '2011', '2012', '2013', '2014', '2015'].

This function should return a DataFrame with 20 columns and 15 entries, and the rows of the DataFrame should be sorted by "Rank".

1.2 problem analysis
The first question mainly introduces the data contents of three data sets, namely:

1. Energy: the data is taken from 'energy indicators' Xls', energy index, the task takes the fields ['Country', 'Energy Supply', 'Energy Supply per capital', 'Renewable'].
2. GDP: data from 'world'_ bank. CSV ', this task takes the GDP of the top 10 countries from 2006 to 2015.
3. ScimEn: the data is taken from 'scimagojr-3 Xlsx ', ranking them mainly according to the journal contributions of countries in the above fields, and taking all fields.

Next, standardize the data of each data set, normalize the names in the Energy and GDP tables, for example, change "Republic of Korea" to "South Korea", remove the () and [] comments in the country names in the Energy table, convert the units of 'Energy Supply', change the column names of Energy and GDP, and finally merge the three tables together.

1.3 code and detailed explanation

#Set the function to change the name
def change_country_values(item):
    dicts={"Republic of Korea": "South Korea",
            "United States of America": "United States",
            "United Kingdom of Great Britain and Northern Ireland": "United Kingdom",
            "China, Hong Kong Special Administrative Region": "Hong Kong",
            
            "Korea, Rep.": "South Korea", 
            "Iran, Islamic Rep.": "Iran",
            "Hong Kong SAR, China": "Hong Kong"}
    #Using the iterator of dicts for matching
    for key,values in dicts.items():
        if item==key:
            return values
    return item
            
def answer_one():
    #Create Energy's DataFrame
    Energy=pd.read_excel('assets/Energy Indicators.xls',skiprows=16,usecols=[2,3,4,5])
    Energy=Energy[1:228]
    
    #Change column name
    Energy.columns=['Country', 'Energy Supply', 'Energy Supply per Capita', '% Renewable']
    #Remove the tail patch
    Energy.replace(to_replace=' \(.*\)$',value='',regex=True,inplace=True)
    Energy.replace(to_replace='[\d]+$',value='',regex=True,inplace=True)
    Energy.replace(to_replace='^[\.]+$',value=np.nan,regex=True,inplace=True)
    #Change unit
    Energy['Energy Supply']=Energy['Energy Supply'].apply(lambda x:x*1000000)
    #Change country name
    Energy['Country']=Energy['Country'].apply(change_country_values)
    #print(Energy)
    
    #Create DataFrame for GDP
    GDP=pd.read_csv('assets/world_bank.csv',skiprows=4)
    GDP['Country Name']=GDP['Country Name'].apply(change_country_values)
    GDP.rename(columns={'Country Name':'Country'},inplace=True)
    #print(GDP.head())
    
    #Create ScimEn's DataFrame
    ScimEn=pd.read_excel('assets/scimagojr-3.xlsx')
    #print(ScimEn)
    
    #Summarize the data columns to go into a list
    year=list(range(2006,2016))
    GDP_year=[str(i) for i in year]
    GDP_year.append('Country')
    
    #Take out the data from 2006 to 2015
    GDP=GDP.loc[:,GDP_year]
    #print(GDP_year)
    
    #Merge DataFrame by country name
    merge1=pd.merge(Energy,GDP,left_on='Country',right_on='Country',how='inner')
    #print(merge1.columns)
    
    #Merge DataFrame by country name
    merge2=pd.merge(ScimEn,merge1,on='Country',how='inner')
    #print(len(merge2))
    
    #Take the top 15 after merger
    merge2=merge2[merge2['Rank']<16]
    
    #Set index
    merge2.set_index('Country',inplace=True)
    #print(merge2.columns)
    
    #Returns the merged DataFrame
    return merge2
answer_one()

• Get the merged DataFrame

2, Q2

2.1 problem description

The previous question joined three datasets then reduced this to just the top 15 entries. When you joined the datasets, but before you reduced this to the top 15 items, how many entries did you lose?

This function should return a single number.

2.2 problem analysis

Some key fields only exist in one of the two tables. During internal connection, this field will not be connected and will be deleted; When making external connections, these fields will be retained. If there is no other table, NaN will be returned. In order to see how many rows of data have been deleted, just make external connections for all three tables and subtract the number of lists of internal connections. The code body is similar to the previous question.

2.3 code and detailed explanation

def answer_two():
    Energy=pd.read_excel('assets/Energy Indicators.xls',skiprows=16,usecols=[2,3,4,5])
    Energy=Energy[1:228]
    Energy.columns=['Country', 'Energy Supply', 'Energy Supply per Capita', '% Renewable']
    Energy.replace(to_replace=' \(.*\)$',value='',regex=True,inplace=True)
    Energy.replace(to_replace='[\d]+$',value='',regex=True,inplace=True)
    Energy.replace(to_replace='^[\.]+$',value=np.nan,regex=True,inplace=True)
    Energy['Energy Supply']=Energy['Energy Supply'].apply(lambda x:x*1000000)
    Energy['Country']=Energy['Country'].apply(change_country_values)
    #print(Energy)
    
    GDP=pd.read_csv('assets/world_bank.csv',skiprows=4)
    GDP['Country Name']=GDP['Country Name'].apply(change_country_values)
    GDP.rename(columns={'Country Name':'Country'},inplace=True)
    #print(GDP.head())
    
    ScimEn=pd.read_excel('assets/scimagojr-3.xlsx')
    #print(ScimEn)
    
    year=list(range(2006,2016))
    GDP_year=[str(i) for i in year]
    GDP_year.append('Country')
    GDP=GDP.loc[:,GDP_year]
    #print(GDP_year)
    
    merge1=pd.merge(Energy,GDP,left_on='Country',right_on='Country',how='inner')
    #print(len(merge1))
    
    #Three intra table connections
    merge2=pd.merge(ScimEn,merge1,on='Country',how='inner')
    #print(len(merge2))

    merge3=pd.merge(Energy,GDP,left_on='Country',right_on='Country',how='outer')
    #print(len(merge3))
    
    #Three off sheet connections
    merge4=pd.merge(ScimEn,merge3,on='Country',how='outer')
    #print(len(merge4))
    ans=len(merge4)-len(merge2)
    #print(ans)
    #print(merge2.columns)
    return ans
answer_two()

The result is 158

3, Q3

3.1 problem description

What are the top 15 countries for average GDP over the last 10 years?

This function should return a Series named avgGDP with 15 countries and their average GDP sorted in descending order.

3.2 problem analysis

Calculate the average value of 10 years. Use the apply() function to calculate the average value of the data of each year using the anonymous function.
In descending order, sort in DataFrame can be used_ Values() function.

3.3 code

def answer_three():
    df=answer_one()
    year=list(range(2006,2015))
    year=[str(i) for i in year]
    df['avgGDP']=df[year].apply(lambda x: np.nanmean(x),axis=1)
    df=df.sort_values(ascending=False,by='avgGDP')
    return df['avgGDP']
answer_three()

4, Q4

4.1 problem description

By how much had the GDP changed over the 10 year span for the country with the 6th largest average GDP?

This function should return a single number.

4.2 problem analysis

How much has the sixth largest GDP economy changed in 10 years?

4.3 code

def answer_four():
    df=answer_one()
    year=list(range(2006,2015))
    year=[str(i) for i in year]
    df['avgGDP']=df[year].apply(lambda x: np.nanmean(x),axis=1)
    df=df.sort_values(ascending=False,by='avgGDP')
    return df.iloc[5]['2015']-df.iloc[5]['2006']
answer_four()  

246702696075.3999

5, Q5

5.1 problem description

What is the mean energy supply per capita?

This function should return a single number.

5.2 problem analysis

What is the average per capita energy supply?

5.3 codes

def answer_five():
    df=answer_one()
    return df['Energy Supply per Capita'].mean()
answer_five()

157.6

6, Q6

6.1 problem description

What country has the maximum % Renewable and what is the percentage?

This function should return a tuple with the name of the country and the percentage.

6.2 problem analysis

Which country has the largest 'Renewable' value? What is the maximum value?
Here, I first convert the data in DataFrame into float, and then use the data in Series idmax() found the country where the maximum value is located

6.3 code

def answer_six():
    df=answer_one()
    return (df['% Renewable'].astype(float).idxmax(),df['% Renewable'].astype(float).max())
answer_six()

('Brazil', 69.65)

7, Q7

7.1 problem description

Create a new column that is the ratio of Self-Citations to Total Citations. What is the maximum value for this new column, and what country has the highest ratio?

This function should return a tuple with the name of the country and the ratio.

7.2 problem analysis

Use the self quotation amount / total quotation amount to obtain the quotation ratio, and find the country with the largest data and the largest value. The method is the same as that in the previous question

7.3 codes

def answer_seven():  
    df=answer_one()
    df['ratio']=df['Self-citations']/df['Citations']
    return (df['ratio'].idxmax(),df['ratio'].max())
answer_seven()

('China', 0.69)

8, Q8

8.1 problem description

Create a column that estimates the population using Energy Supply and Energy Supply per capita. What is the third most populous country according to this estimate?

This function should return the name of the country

8.2 problem analysis

To estimate the population, you can use 'Energy Supply' / 'Energy Supply per capita'. The population can be obtained by dividing the total data by the per capita. The third largest can be arranged in descending order, and then the third data can be obtained

8.3 code

def answer_eight():
    df=answer_one()
    df['population']=df['Energy Supply']/df['Energy Supply per Capita']
    df.sort_values(by='population',ascending=False,inplace=True)
    return df.iloc[2].name
answer_eight()

'United States'

9, Q9

9.1 problem description

Create a column that estimates the number of citable documents per person. What is the correlation between the number of citable documents per capita and the energy supply per capita? Use the .corr() method, (Pearson's correlation).

This function should return a single number.

(Optional: Use the built-in function plot9() to visualize the relationship between Energy Supply per Capita vs. Citable docs per Capita)

9.2 problem analysis

The calculation of Pearson correlation coefficient is relatively simple. The code for choosing questions has been given, and the image can be obtained by running

9.3 code

def answer_nine():
    df=answer_one()
    df['population']=df['Energy Supply']/df['Energy Supply per Capita']
    df['citable documents per capita']=df['Citable documents']/df['population']
    return df['citable documents per capita'].astype(float).corr(df['Energy Supply per Capita'].astype(float))
answer_nine()

0.7940010435442946

10, Q10

10.1 problem description

Create a new column with a 1 if the country's % Renewable value is at or above the median for all countries in the top 15, and a 0 if the country's % Renewable value is below the median.

This function should return a series named HighRenew whose index is the country name sorted in ascending order of rank.

10.2 problem analysis

Find the median. If it is greater than or equal to the median, it returns 1, otherwise it returns 0

10.3 codes

def answer_ten():
    df=answer_one()
    df['flagRenew']= df['% Renewable']-df['% Renewable'].median() 
    df['HighRenew']= df['flagRenew'].apply(lambda x:1 if x>=0 else 0)
    return df['HighRenew']
answer_ten()

11, Q11

11.1 problem description

Use the following dictionary to group the Countries by Continent, then create a DataFrame that displays the sample size (the number of countries in each continent bin), and the sum, mean, and std deviation for the estimated population of each country.

ContinentDict  = {'China':'Asia', 
                  'United States':'North America', 
                  'Japan':'Asia', 
                  'United Kingdom':'Europe', 
                  'Russian Federation':'Europe', 
                  'Canada':'North America', 
                  'Germany':'Europe', 
                  'India':'Asia',
                  'France':'Europe', 
                  'South Korea':'Asia', 
                  'Italy':'Europe', 
                  'Spain':'Europe', 
                  'Iran':'Asia',
                  'Australia':'Australia', 
                  'Brazil':'South America'}

This function should return a DataFrame with index named Continent ['Asia', 'Australia', 'Europe', 'North America', 'South America'] and columns ['size', 'sum', 'mean', 'std']

11.2 problem analysis

Add the region information for each country, and calculate the size, sum, average and standard deviation according to the region. You can use the aggregation function, and the syntax is visible Merging, grouping and aggregation of DataFrame and PivotTable report (Chapter III grouping and aggregation)

11.3 codes

def region(row):
    ContinentDict  = {'China':'Asia', 
                  'United States':'North America', 
                  'Japan':'Asia', 
                  'United Kingdom':'Europe', 
                  'Russian Federation':'Europe', 
                  'Canada':'North America', 
                  'Germany':'Europe', 
                  'India':'Asia',
                  'France':'Europe', 
                  'South Korea':'Asia', 
                  'Italy':'Europe', 
                  'Spain':'Europe', 
                  'Iran':'Asia',
                  'Australia':'Australia', 
                  'Brazil':'South America'}
    #print(row.name)
    for key,values in ContinentDict.items():
        if row.name == key:
            row['region']=values
    return row
def answer_eleven():
    df=answer_one()
    df['population']=df['Energy Supply']/df['Energy Supply per Capita']
    df=df.apply(region,axis=1)
    new_df=df.groupby('region',axis=0).agg({'population':(np.size,np.nansum,np.nanmean,np.nanstd)})
    #new_df['size']=df.groupby('region',axis=0).size()
    new_df.columns=['size','sum','mean','std']
    #print(new_df)
    return new_df
answer_eleven()

12, Q12

12.1 problem description

Cut % Renewable into 5 bins. Group Top15 by the Continent, as well as these new % Renewable bins. How many countries are in each of these groups?

This function should return a Series with a MultiIndex of Continent, then the bins for % Renewable. Do not include groups with no countries.

12.2 problem analysis

Divide the '% Renewable' into 5 boxes and calculate the size of each box. You can use PD Cut () to divide the box, and the calculated size is similar to the previous question

12.3 code

def region(row):
    ContinentDict  = {'China':'Asia', 
                  'United States':'North America', 
                  'Japan':'Asia', 
                  'United Kingdom':'Europe', 
                  'Russian Federation':'Europe', 
                  'Canada':'North America', 
                  'Germany':'Europe', 
                  'India':'Asia',
                  'France':'Europe', 
                  'South Korea':'Asia', 
                  'Italy':'Europe', 
                  'Spain':'Europe', 
                  'Iran':'Asia',
                  'Australia':'Australia', 
                  'Brazil':'South America'}
    #print(row.name)
    for key,values in ContinentDict.items():
        if row.name == key:
            row['Continent']=values
    return row

def answer_twelve():
    df=answer_one()
    df=df.apply(region,axis=1)
    df['% Renewable']=pd.cut(df['% Renewable'],5)
    new_df=df.groupby(['Continent','% Renewable'])['Continent'].agg(np.size).dropna()
    #new_df=new_df.set_index(['Continent','intervals'])
    return new_df

answer_twelve()

13, Q13

13.1 problem description

Convert the Population Estimate series to a string with thousands separator (using commas). Use all significant digits (do not round the results).

e.g. 12345678.90 -> 12,345,678.90

This function should return a series PopEst whose index is the country name and whose values are the population estimate string

13.2 problem analysis

Change data format

13.3 code

def answer_thirteen():
    df=answer_one()
    df['population']=df['Energy Supply']/df['Energy Supply per Capita']
    return df['population'].apply('{:,}'.format)
answer_thirteen()

What is data science in Python?

What are Python data science topics?

What are the five steps to learn Python for data science?

What is the most important reason why Python is famous in the field of data science?