<jupyter_start><jupyter_text>Extracting and transforming data  1. Positional and labeled indexingGiven a pair of label-based indices, sometimes it's necessary to find the corresponding positions. In this exercise, you will use the Pennsylvania election results.Find x and y such that election.iloc[x, y] == election.loc['Bedford', 'winner']. That is, what is the row position of 'Bedford', and the column position of 'winner'? Remember that the first position in Python is 0, not 1!<jupyter_code>import pandas as pd
election = pd.read_csv("data/pennsylvania2012_turnout.csv", index_col = "county")
election.head()
# Assign the row position of election.loc['Bedford']: x
x = 4

# Assign the column position of election['winner']: y
y = 4

# Print the boolean equivalence
print(election.iloc[x, y] == election.loc['Bedford', 'winner'])<jupyter_output>True<jupyter_text>Great work! Depending on the situation, you may wish to use .iloc[] over .loc[], and vice versa. The important thing to realize is you can achieve the exact same results using either approach.  2. Indexing and column rearrangementThere are circumstances in which it's useful to modify the order of your DataFrame columns. We do that now by extracting just two columns from the Pennsylvania election results DataFrame.Your job is to read the CSV file and set the index to 'county'. You'll then assign a new DataFrame by selecting the list of columns ['winner', 'total', 'voters']. The CSV file is provided to you in the variable filename.<jupyter_code># Import pandas
import pandas as pd

# Read in filename and set the index: election
election = pd.read_csv("data/pennsylvania2012_turnout.csv", index_col='county')

# Create a separate dataframe with the columns ['winner', 'total', 'voters']: results
results = election[["winner", "total", "voters"]]

# Print the output of results.head()
results.head()<jupyter_output><empty_output><jupyter_text>Wonderful work! The original election DataFrame had 6 columns, but as you can see, your results DataFrame now has just the 3 columns: 'winner', 'total', and 'voters'.  3. Slicing rowsThe Pennsylvania US election results data set that you have been using so far is ordered by county name. This means that county names can be sliced alphabetically. In this exercise, you're going to perform slicing on the county names of the election DataFrame from the previous exercises, which has been pre-loaded for you.<jupyter_code># Slice the row labels 'Perry' to 'Potter': p_counties
p_counties = election.loc["Perry" : "Potter"]

# Print the p_counties DataFrame
p_counties
# Slice the row labels 'Potter' to 'Perry' in reverse order: p_counties_rev
p_counties_rev = election.loc["Potter" : "Perry"]

# Print the p_counties_rev DataFrame
p_counties_rev
# Slice the row labels 'Potter' to 'Perry' in reverse order: p_counties_rev
p_counties_rev = election.loc["Potter" : "Perry":-1]

# Print the p_counties_rev DataFrame
p_counties_rev<jupyter_output><empty_output><jupyter_text>Well done! It looks like Obama did particularly well in Philadelphia.  4. Slicing columnsSimilar to row slicing, columns can be sliced by value. In this exercise, your job is to slice column names from the Pennsylvania election results DataFrame using .loc[].<jupyter_code># Slice the columns from the starting column to 'Obama': left_columns
left_columns = election.loc[:,:"Obama"]

# Print the output of left_columns.head()
left_columns.head()
# Slice the columns from 'Obama' to 'winner': middle_columns
middle_columns = election.loc[:, 'Obama': 'winner']

# Print the output of middle_columns.head()
middle_columns.head()
# Slice the columns from 'Romney' to the end: 'right_columns'
right_columns = election.loc[:, "Romney":]

# Print the output of right_columns.head()
right_columns.head()<jupyter_output><empty_output><jupyter_text>5. Subselecting DataFrames with listsYou can use lists to select specific row and column labels with the .loc[] accessor. In this exercise, your job is to select the counties ['Philadelphia', 'Centre', 'Fulton'] and the columns ['winner','Obama','Romney'] from the election DataFrame..<jupyter_code># Create the list of row labels: rows
rows = ['Philadelphia', 'Centre', 'Fulton']

# Create the list of column labels: cols
cols = ['winner', 'Obama', 'Romney']

# Create the new DataFrame: three_counties
three_counties = election.loc[rows, cols]

# Print the three_counties DataFrame
three_counties<jupyter_output><empty_output><jupyter_text>Excellent work! If you know exactly which rows and columns are of interest to you, this is a useful approach for subselecting DataFrames.  6. Thresholding dataIn this exercise, we have provided the Pennsylvania election results and included a column called 'turnout' that contains the percentage of voter turnout per county. Your job is to prepare a boolean array to select all of the rows and columns where voter turnout exceeded 70%.<jupyter_code># Create the boolean array: high_turnout
high_turnout = election.turnout > 70

# Filter the election DataFrame with the high_turnout array: high_turnout_df
high_turnout_df = election[high_turnout]

# Print the high_turnout_results DataFrame
high_turnout_df<jupyter_output><empty_output><jupyter_text>7. Filtering columns using other columnsThe election results DataFrame has a column labeled 'margin' which expresses the number of extra votes the winner received over the losing candidate. This number is given as a percentage of the total votes cast. It is reasonable to assume that in counties where this margin was less than 1%, the results would be too-close-to-call.Your job is to use boolean selection to filter the rows where the margin was less than 1. You'll then convert these rows of the 'winner' column to np.nan to indicate that these results are too close to declare a winner.<jupyter_code># Import numpy
import numpy as np

# Create the boolean array: too_close
too_close = election.margin < 1

# Assign np.nan to the 'winner' column where the results were too close to call
election.loc[too_close, "winner"] = np.nan

# Print the output of election.info()
election.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Index: 67 entries, Adams to York
Data columns (total 8 columns):
state      67 non-null object
total      67 non-null int64
Obama      67 non-null float64
Romney     67 non-null float64
winner     64 non-null object
voters     67 non-null int64
turnout    67 non-null float64
margin     67 non-null float64
dtypes: float64(4), int64(2), object(2)
memory usage: 7.2+ KB<jupyter_text>8. Filtering using NaNsIn certain scenarios, it may be necessary to remove rows and columns with missing data from a DataFrame. The .dropna() method is used to perform this action. You'll now practice using this method on a dataset obtained from [Vanderbilt University](http://biostat.mc.vanderbilt.edu/wiki/pub/Main/DataSets/titanic.html), which consists of data from passengers on the Titanic.The DataFrame has been pre-loaded for you as titanic. Explore it in the IPython Shell and you will note that there are many NaNs. You will focus specifically on the 'age' and 'cabin' columns in this exercise. Your job is to use .dropna() to remove rows where any of these two columns contains missing data and rows where all of these two columns contain missing data.You'll also use the .shape attribute, which returns the number of rows and columns in a tuple from a DataFrame, or the number of rows from a Series, to see the effect of dropping missing values from a DataFrame.Finally, you'll use the thresh= keyword argument to drop columns from the full dataset that have less than 1000 non-missing values.<jupyter_code>titanic = pd.read_csv("data/titanic.csv")
titanic.tail()
titanic.info()
# Select the 'age' and 'cabin' columns: df
df = titanic[["age", "cabin"]]

# Print the shape of df
df.shape<jupyter_output><empty_output><jupyter_text>- 'any' : If any NA values are present, drop that row or column.<jupyter_code># Drop rows in df with how='any' and print the shape
df.dropna(how="any").shape<jupyter_output><empty_output><jupyter_text>- 'all' : If all values are NA, drop that row or column.<jupyter_code># Drop rows in df with how='all' and print the shape
df.dropna(how="all").shape
# Drop columns in titanic with less than 1000 non-missing values
titanic.dropna(thresh=1000, axis='columns').info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 1309 entries, 0 to 1308
Data columns (total 10 columns):
pclass      1309 non-null int64
survived    1309 non-null int64
name        1309 non-null object
sex         1309 non-null object
age         1046 non-null float64
sibsp       1309 non-null int64
parch       1309 non-null int64
ticket      1309 non-null object
fare        1308 non-null float64
embarked    1307 non-null object
dtypes: float64(2), int64(4), object(4)
memory usage: 102.3+ KB<jupyter_text>Usually you would want to avoid dropping too much of your data, but in some situations, it may be necessary.  9. Using apply() to transform a columnThe .apply() method can be used on a pandas DataFrame to apply an arbitrary Python function to every element. In this exercise you'll take daily weather data in Pittsburgh in 2013 obtained from Weather Underground.A function to convert degrees Fahrenheit to degrees Celsius has been written. Your job is to use the .apply() method to perform this conversion on the 'Mean TemperatureF' and 'Mean Dew PointF' columns of the weather DataFrame.<jupyter_code>weather = pd.read_csv("data/pittsburgh2013.csv")
weather.info()
# Write a function to convert degrees Fahrenheit to degrees Celsius: to_celsius
def to_celsius(F):
    return 5/9*(F - 32)
# Print the tail of "Mean TemperatureF" and  "Mean Dew PointF" columns
weather[["Mean TemperatureF", "Mean Dew PointF"]].tail()
# Apply the function over 'Mean TemperatureF' and 'Mean Dew PointF': df_celsius
df_celsius = weather[["Mean TemperatureF", "Mean Dew PointF"]].apply(to_celsius)
df_celsius.tail()
# Reassign the column labels of df_celsius
df_celsius.columns = ['Mean TemperatureC', 'Mean Dew PointC']

# Print the output of df_celsius.tail()
df_celsius.tail()<jupyter_output><empty_output><jupyter_text>Great work - take a look at the renamed columns and converted temperatures!  10. Using .map() with a dictionaryThe .map() method is used to transform values according to a Python dictionary look-up. In this exercise you'll practice this method while returning to working with the election DataFrame, which has been pre-loaded for you.Your job is to use a dictionary to map the values 'Obama' and 'Romney' in the 'winner' column to the values 'blue' and 'red', and assign the output to the new column 'color'.<jupyter_code># Create the dictionary: red_vs_blue
red_vs_blue = {'Obama': 'blue', 'Romney': 'red'}

# Use the dictionary to map the 'winner' column to the new column: election['color']
election['color'] = election.winner.map(red_vs_blue)

# Print the output of election.head()
election.head()<jupyter_output><empty_output><jupyter_text>Notice the new 'color' column!  11. Using vectorized functionsWhen performance is paramount, you should avoid using .apply() and .map() because those constructs perform Python for-loops over the data stored in a pandas Series or DataFrame. By using vectorized functions instead, you can loop over the data at the same speed as compiled code (C, Fortran, etc.)! NumPy, SciPy and pandas come with a variety of vectorized functions (called Universal Functions or UFuncs in NumPy).You can even write your own vectorized functions, but for now we will focus on the ones distributed by NumPy and pandas.In this exercise you're going to import the zscore function from scipy.stats and use it to compute the deviation in voter turnout in Pennsylvania from the mean in fractions of the standard deviation. In statistics, the z-score is the number of standard deviations by which an observation is above the mean - so if it is negative, it means the observation is below the mean.Instead of using .apply() as you did in the earlier exercises, the zscore UFunc will take a pandas Series as input and return a NumPy array. You will then assign the values of the NumPy array to a new column in the DataFrame. You will be working with the election DataFrame.<jupyter_code># Import zscore from scipy.stats
from scipy.stats import zscore

# Call zscore with election['turnout'] as input: turnout_zscore
turnout_zscore = zscore(election.turnout)

turnout_zscore
# Print the type of turnout_zscore
type(turnout_zscore)
# Assign turnout_zscore to a new column: election['turnout_zscore']
election["turnout_zscore"] = turnout_zscore

# Print the output of election.head()
print(election.head())<jupyter_output>state   total      Obama     Romney  winner  voters    turnout  \
county                                                                     
Adams        PA   41973  35.482334  63.112001  Romney   61156  68.632677   
Allegheny    PA  614671  56.640219  42.185820   Obama  924351  66.497575   
Armstrong    PA   28322  30.696985  67.901278  Romney   42147  67.198140   
Beaver       PA   80015  46.032619  52.637630  Romney  115157  69.483401   
Bedford      PA   21444  22.057452  76.986570  Romney   32189  66.619031   

              margin color  turnout_zscore  
county                                      
Adams      27.629667   red        0.853734  
Allegheny  14.454399  blue        0.439846  
Armstrong  37.204293   red        0.575650  
Beaver      6.605012   red        1.018647  
Bedford    54.929118   red        0.463391
{'fraction_non_alphanumeric': 0.0730843053299867, 'fraction_numerical': 0.03100546286726709, 'mean_word_length': 4.502437043054427, 'pattern_counts': {'":': 2, '<': 52, '<?xml version=': 0, '>': 52, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 3, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '13', 'dir': 'jupyter-structured-clean-dedup', 'id': '424671', 'license': 'MIT', 'n_tokens_mistral': 4226, 'n_tokens_neox': 3844, 'n_words': 1736, 'path': 'Datacamp Assignments/Data Science Track/Manipulating DataFrames with pandas/1. Extracting and transforming data.ipynb', 'repo_name': 'Ali-Parandeh/Data_Science_Playground', 'size': '13546'}