<jupyter_start><jupyter_text>Sorting and cleaning ---In order to effectively analyse a dataset, often we need to prepare it first. Before a dataset is ready to be analysed we might need to:  * sort the data (can be a series or dataframe)  * remove any NaN values or drop NA values   * remove duplicate records (identical rows)  * normalise data in dataframe columns so that has a common scale [reference](https://towardsai.net/p/data-science/how-when-and-why-should-you-normalize-standardize-rescale-your-data-3f083def38ff:~:text=Similarly%2C%20the%20goal%20of%20normalization,dataset%20does%20not%20require%20normalization.&text=So%20we%20normalize%20the%20data,variables%20to%20the%20same%20range.) Sorting the data  ---Typically we want to sort data by the values in one or more columns in the dataframe  To sort the dataframe by series we use the pandas function **sort_values()**.  By default `sort_values()` sorts into ascending order.* sort by a single column e.g.  * `df.sort_values("Make") `* sort by multiple columns e.g.   * `df.sort_values(by = ["Model", "Make"]) `    * this sorts by Model, then my Make * sort in *descending* order  * `df.sort_values(by = "Make", ascending = False)`  * `df.sort_values(by = ["Make", "Model"], ascending = False])`  Dataframes are mostly **immutable**, ie changes like sort_values do not change the dataframe permanently, they just change it for the time that the instruction is being used.`df.sort_values(by='Make')` *dataframe is now in sorted order and can be copied to a new dataframe*  `df_sorted_on_make` *original dataframe, df, will be as it was - unsorted* To **split** columns away from the dataframe after sorting, do this in the same instruction, e.g.:`df.sort_values(by = ["Make", "Model"], ascending = False])[["Make", "Model"]]`This sorts on Make and then Model in descending order, then splits off the Make and Model columns.`df.sort_values(by = ["Make", "Model"], ascending = False])[["Make", "Model"]].head()`This sorts on Make and then Model, then splits off the Make and Model columns and then splits off the first 5 rows.  Exercise 1 - get data, sort by happiness score ---Read data into variable called **happiness** from the Excel file on Happiness Data at this link: https://github.com/futureCodersSE/working-with-data/blob/main/Happiness-Data/2015.xlsx?raw=trueWrite a function called **sorted_rank** to display first 5 rows of data  The data is currently sorted by Happiness Rank...*  sort the data by Happiness Score in ascending order*  display sorted table<jupyter_code>import pandas as pd
url = 'https://github.com/futureCodersSE/working-with-data/blob/main/Happiness-Data/2015.xlsx?raw=true'
happiness = pd.read_excel(url)

def sorted_rank(df):
# add code below to return a series sorted by Happiness Score in ascending order
  df_sorted_on_happiness = df.sort_values(by = 'Happiness Score')

  return df_sorted_on_happiness


# The code below will run and test your code to see if the first row of your series is correct 
actual = sorted_rank(happiness).index[0]
expected = 157

if actual == expected:
  print("Well done, test passed")
else:
  print("Test failed","Should have got", expected, "got", actual)<jupyter_output>Well done, test passed<jupyter_text>Exercise 2 - sort by multiple columns, display the first 5 rows ---Write a function called **get_gdp_health** which:1. sort the data by Economy (GDP per Capita) and Health (Life Expectancy) in ascending order 2. display the first 5 rows of sorted data<jupyter_code>def get_gdp_health():
  # add code below to return a 5 row series which sorts rows by Economy and Health in ascending order 
  sorted_on_economy_health = happiness.sort_values(by = ['Economy (GDP per Capita)', 'Health (Life Expectancy)']).head(5)
  print(sorted_on_economy_health)
  return sorted_on_economy_health


# The code below will run and test your code to see if the first row of your series has the correct happiness score 
test = get_gdp_health()

actual = test['Happiness Score'].iloc[0]
expected = 4.517

if actual == expected:
  print("Well done, test passed")
else:
  print("Test failed","Should have got", expected, "got", actual)<jupyter_output>Country              Region  ...  Generosity  Dystopia Residual
119  Congo (Kinshasa)  Sub-Saharan Africa  ...     0.24834            2.86712
156           Burundi  Sub-Saharan Africa  ...     0.19727            1.83302
130            Malawi  Sub-Saharan Africa  ...     0.33128            2.80791
143             Niger  Sub-Saharan Africa  ...     0.19387            1.87877
115           Liberia  Sub-Saharan Africa  ...     0.24362            2.77729

[5 rows x 12 columns]
Well done, test passed<jupyter_text>Exercise 3 - sorting in descending order ---Write a function called **get_descending** which will: Sort the data by Freedom and Trust (Government Corruption) in descending order and show the Country and Region only for the last five rows<jupyter_code>def get_descending(df):
  #add code below to return a series which contains the last 5 rows of Country and Region sorted by Freedom and Trust in descending order
  sorted_on_freedom_trust = df.sort_values(by = ['Freedom', 'Trust (Government Corruption)'], ascending=False)[['Country', 'Region']].tail(5)
  print(sorted_on_freedom_trust)
  return sorted_on_freedom_trust

# The code below will run and test your code to see if the length and series are correct 
actual = get_descending(happiness).index[0]
expected = 136

if actual == expected and (len(get_descending(happiness)) == 5):
  print("Test passed", actual)
else:
  print("Test failed","Should have got", expected, "got", actual, "and length of series should have been 5 but was", len(get_descending(happiness)))<jupyter_output>Country                           Region
136                  Angola               Sub-Saharan Africa
117                   Sudan               Sub-Saharan Africa
95   Bosnia and Herzegovina       Central and Eastern Europe
101                  Greece                   Western Europe
111                    Iraq  Middle East and Northern Africa
                    Country                           Region
136                  Angola               Sub-Saharan Africa
117                   Sudan               Sub-Saharan Africa
95   Bosnia and Herzegovina       Central and Eastern Europe
101                  Greece                   Western Europe
111                    Iraq  Middle East and Northern Africa
Test passed 136<jupyter_text>Cleaning the dataData comes from a range of sources:  forms, monitoring devices, etc.  There will often be missing values, duplicate records and values that are incorrectly formatted.  These can affect summary statistics and graphs plotted from the data.Techniques for data cleansing include:*  removing records with missing or null data (NaN, NA, "")*  removing duplicate rows (keeping just one, either the first or the last)Removal of rows according to criteria, or of columns are other ways that data might be cleaned up.   --- Removing NaN/Dropping NA values pandas have functions for checking a dataframe, or column, for null values, checking a column for missing values, and functions for dropping all rows that contain null values.* check for NA/NaN/missing values across dataframe (returns True if NA values exist)    `df.isnull().values.any()`  * check for NA/NaN/missing values in specific column    `df["column"].isnull().values.any()`  * filter for non-null rows     `df[~df["column"].isnull()]`    *hint: ~ means is not** drop all rows that have NA/NaN values     `df.dropna()`  * drop rows where NA/NaN values exist in specific columns    `df.dropna(subset = ["Make", "Model"])`    Exercise 4 - check for null values ---1. read data into a variable called **housing** from the file housing_in_london_yearly_variables.csv from this link: https://raw.githubusercontent.com/futureCodersSE/working-with-data/main/Data%20sets/housing_in_london_yearly_variables.csv Write a function called **check_null** which will:2. check if any NA values exist in the dataframe and print the result 3. use df.info() to see which columns have null entries (*Hint: if the non-null count is less than total entries, column contains missing/NA entries*)<jupyter_code>import pandas as pd
url = 'https://raw.githubusercontent.com/futureCodersSE/working-with-data/main/Data%20sets/housing_in_london_yearly_variables.csv'
housing = pd.read_csv(url)

def check_null(df):
  # add code below to return a bool statement for whether or not there are null values 
  return df.isnull().values.any()
 

# The code below will run and test your code to see if you are returning the correct answer
actual = check_null(housing)
expected = True

if actual == expected:
  print("Test passed", actual)
else:
  print("Test failed","Should have got", expected, "got", actual)<jupyter_output>Test passed True<jupyter_text>Exercise 5 - filter for non-null rows---Create function called **filter_null()** which filters the non-null rows by the `number_of_jobs` column.<jupyter_code>def filter_null(df):
  # add code below to filter out null values
  return df[~df['number_of_jobs'].isnull()]

# The code below will run and test your code to see if the length of your returned dataframe is correct

actual = len(filter_null(housing))
expected = 931 

if actual == expected:
  print("Test passed", actual)
else:
  print("Test failed expected", expected, "got", actual)<jupyter_output>Test passed 931<jupyter_text>Exercise 5 - remove null values ---Write a function called **drop_null** which will:1. remove rows with NA values for `life_satisfaction` (use [ ] even if only one column in list)<jupyter_code>def drop_null(df):
  # add code which returns a dataframe with rows that contain NA values in the life_satisfaction column removed
  return df.dropna(subset=['life_satisfaction'])


# The code below will run and test your code to see if you have returned a series with the correct length and first row
actual = drop_null(housing).index[0]
expected = 613 

if actual == expected and len(drop_null(housing)) == 352:
  print("Test passed", actual)
else:
  print("Test failed","Should have got", expected, "got", actual, "and length of series should have been 352 but was", len(drop_null(housing)))<jupyter_output>Test passed 613<jupyter_text>Dropping duplicates---* To remove duplicate rows based on duplication of values in all columns    `df.drop_duplicates()`  * To remove rows that have duplicate entries in a specified column    `df.drop_duplicates(subset = ['Make'])`  * To remove rows that have duplicate entries in multiple columns    `df.drop_duplicates(subset = ['Make', 'Model'])` * Remove duplicate rows keeping the last instance rather than the first (default):    `df.drop_duplicates(keep='last')`    Exercise 6 - Removing duplicate entries ---Write a function called **drop_duplicates** which will: remove duplicate `area` entries keeping first instance<jupyter_code>def drop_duplicates(df):
  # add code which returns a dataframe without duplicate area entries with first instance kept 
  return df.drop_duplicates(subset=['area'], keep='first')


# The code below will run and test your code to see if you have returned a series with the correct length and first row
actual = drop_duplicates(housing).index[0]
expected = 0

if actual == expected and len(drop_duplicates(housing)) == 51:
  print("Test passed", actual)
else:
  print("Test failed","Should have got", expected, "got", actual, "and length of series should have been 51 but was", len(drop_duplicates(housing)))<jupyter_output>Test passed 0
{'fraction_non_alphanumeric': 0.08862514077796067, 'fraction_numerical': 0.019232435242138093, 'mean_word_length': 4.019130434782609, 'pattern_counts': {'":': 0, '<': 22, '<?xml version=': 0, '>': 22, 'https://': 5, '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': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '624149', 'license': 'MIT', 'n_tokens_mistral': 3429, 'n_tokens_neox': 3186, 'n_words': 1335, 'path': 'Worksheets/3_Sorting_and_cleaning.ipynb', 'repo_name': 'Imran-Github22/Programming-For-Data', 'size': '11543'}