Data Cleaning Part 2 Notes 1

 You’ve seen most of the functions we often use to diagnose a dataset for cleaning. Some of the most useful ones are:

  • .head() — display the first 5 rows of the table
  • .info() — display a summary of the table
  • .describe() — display the summary statistics of the table
  • .columns — display the column names of the table
  • .value_counts() — display the distinct values for a column
Dealing with Multiple Files

Often, you have the same data separated out into multiple files.

Let’s say that we have a ton of files following the filename structure: 'file1.csv', 'file2.csv', 'file3.csv', and so on. The power of pandas is mainly in being able to manipulate large amounts of structured data, so we want to be able to get all of the relevant information into one table so that we can analyze the aggregate data.

We can combine the use of glob, a Python library for working with files, with pandas to organize this data better. glob can open multiple files by using regex matching to get the filenames:

import codecademylib3_seaborn
import pandas as pd
import glob
df_list = []
student_files = glob.glob("exams*.csv")

for files in student_files:
  data = pd.read_csv(files)
  df_list.append(data)


students = pd.concat(df_list)

print(students)
print(len(students))

This code goes through any file that starts with 'file' and has an extension of .csv. It opens each file, reads the data into a DataFrame, and then concatenates all of those DataFrames together.


Reshaping your Data

Since we want

  • Each variable as a separate column
  • Each row as a separate observation

We can use pd.melt() to do this transformation. .melt() takes in a DataFrame, and the columns to unpack:

pd.melt(frame=df, id_vars='name', value_vars=['Checking','Savings'], value_name="Amount", var_name="Account Type")

The parameters you provide are:

  • frame: the DataFrame you want to melt
  • id_vars: the column(s) of the old DataFrame to preserve
  • value_vars: the column(s) of the old DataFrame that you want to turn into variables
  • value_name: what to call the column of the new DataFrame that stores the values
  • var_name: what to call the column of the new DataFrame that stores the variables

The default names may work in certain situations, but it’s best to always have data that is self-explanatory. Thus, we often use .columns() to rename the columns after melting:

df.columns(["Account", "Account Type", "Amount"])

students = pd.melt(frame=students,id_vars=
['full_name','gender_age','grade'],value_vars=['fractions','probability'],value_name='score',var_name='exam')
print(students.head())
print(students.exam.value_counts())

Comments

Post a Comment

Popular posts from this blog

Binomial Test in Python

  Binomial Test Let’s imagine that we are analyzing the percentage of customers who make a purchase after visiting a website. 1000 customers visited the site this month, and 58 of them made a purchase. The marketing department reports that historical data suggests about 72 of every 1000 visitors make a purchase. Thus, they estimate that the probability of any particular customer making a purchase is 7.2%. We would like to know if this month’s number, 58 purchases, is significantly different from normal or a reasonable fluctuation due to random chance. In previous exercises, we collected samples of numerical information (eg. order price) and then used the mean and standard deviation of those samples to make comparisons. In contrast, we now have a sample where each unit (a visitor) falls into one of two discrete categories: “made a purchase” “did not make a purchase” Instead of comparing sample means, we want to compare the percent in the “made a purchase” category to some expectatio...
Read more

Slicing and Indexing in Python Pandas

 # Make a list of cities to subset on cities = ["Moscow", "Saint Petersburg"] # Subset temperatures using square brackets print(temperatures[temperatures["city"].isin(cities)]) # Subset temperatures_ind using .loc[] print(temperatures_ind.loc[cities]) temperatures_ind = temperatures.set_index(['country','city']) # List of tuples: Brazil, Rio De Janeiro & Pakistan, Lahore rows_to_keep = [('Brazil','Rio De Janeiro'),('Pakistan','Lahore')] # Subset for rows to keep print(temperatures_ind.loc[rows_to_keep]) # Sort temperatures_ind by index values print(temperatures_ind.sort_index()) # Sort temperatures_ind by index values at the city level print(temperatures_ind.sort_index(level="city")) # Sort temperatures_ind by country then descending city print(temperatures_ind.sort_index(level=["country", "city"], ascending = [True, False])) Slice rows with code like  df.loc[("a", ...
Read more

Python Syntax and Functions Part2 (Summary Statistics)

The  .agg()  method allows you to apply your own custom functions to a DataFrame, as well as apply functions to more than one column of a DataFrame at once, making your aggregations super efficient. In the custom function for this exercise, "IQR" is short for inter-quartile range, which is the 75th percentile minus the 25th percentile. It's an alternative to standard deviation that is helpful if your data contains   import numpy as np def iqr(column):     return column.quantile(0.75) - column.quantile(0.25) # Update to print IQR and median of temperature_c, fuel_price_usd_per_l, & unemployment print(sales[["temperature_c", "fuel_price_usd_per_l", "unemployment"]].agg([iqr, np.median])) Calulating Cumulative Statistics Syntax = df.drop_duplicates sales_1_1 = sales_1_1.sort_values("date") # Get the cumulative sum of weekly_sales, add as cum_weekly_sales col sales_1_1["cum_weekly_sales"] = sales_1_1["weekly_sales...
Read more