###################extracting and transforming data######
####Positional and labeled indexing

# 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'])

####Indexing and column rearrangement
# Import pandas
import pandas as pd

# Read in filename and set the index: election
election = pd.read_csv(filename,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()
print(results.head())

####Slicing rows
# Slice the row labels 'Perry' to 'Potter': p_counties

p_counties = election.loc['Perry':'Potter',:]

# Print the p_counties DataFrame
print(p_counties)

# 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
print(p_counties_rev)

####Slicing columns
# Slice the columns from the starting column to 'Obama': left_columns
left_columns = election.loc[:,:'Obama']

# Print the output of left_columns.head()
print(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()
print(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()
print(right_columns.head())

####Subselecting DataFrames with lists
# 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
print(three_counties)

####Thresholding data
# 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[election.turnout>70]

# Print the high_turnout_results DataFrame
print(high_turnout_df)


####Filtering columns using other columns
# 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()
print(election.info())

####Filtering using NaNs
# Select the 'age' and 'cabin' columns: df
df = titanic[['age','cabin']]

# Print the shape of df
print(df.shape)

# Drop rows in df with how='any' and print the shape
print(df.dropna(how='any').shape)

# Drop rows in df with how='all' and print the shape
print(df.dropna(how='all').shape)
df3=df.dropna(how='all')

# Drop columns in titanic with less than 1000 non-missing values
print(titanic.dropna(thresh=1000, axis='columns').info())


####Using apply() to transform a column
# Write a function to convert degrees Fahrenheit to degrees Celsius: to_celsius
def to_celsius(F):
    return 5/9*(F - 32)

# Apply the function over 'Mean TemperatureF' and 'Mean Dew PointF': df_celsius
df_celsius =weather[['Mean TemperatureF','Mean Dew PointF']].apply(to_celsius) 

# Reassign the column labels of df_celsius
df_celsius.columns = ['Mean TemperatureC', 'Mean Dew PointC']

# Print the output of df_celsius.head()
print(df_celsius.head())

####Using .map() with a dictionary
# 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()
print(election.head())

####Using vectorized functions
# Import zscore from scipy.stats
from scipy.stats import zscore

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

# Print the type of turnout_zscore
print(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())


####Changing index of a DataFrame
# Create the list of new indexes: new_idx

new_idx = [month.upper() for month in sales.index]

# Assign new_idx to sales.index
sales.index = new_idx

# Print the sales DataFrame
print(sales)

####Changing index name labels
# Assign the string 'MONTHS' to sales.index.name
sales.index.name='MONTHS'

# Print the sales DataFrame
print(sales)

# Assign the string 'PRODUCTS' to sales.columns.name 
sales.columns.name='PRODUCTS'

# Print the sales dataframe again
print(sales)

####Building an index, then a DataFrame
# Generate the list of months: months
months = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun']

# Assign months to sales.index
sales.index=months

# Print the modified sales DataFrame
print(sales)

####Extracting data with a MultiIndex
# Print sales.loc[['CA', 'TX']]
print(sales.loc[['CA','TX']])

# Print sales['CA':'TX']
print(sales['CA':'TX'])

####Setting & sorting a MultiIndex
# Set the index to be the columns ['state', 'month']: sales
sales = sales.set_index(['state', 'month'])

# Sort the MultiIndex: sales
sales = sales.sort_index()

# Print the sales DataFrame
print(sales)

####Using .loc[] with nonunique indexes
# Set the index to the column 'state': sales
sales = sales.set_index(['state'])

# Print the sales DataFrame
print(sales)

# Access the data from 'NY'
print(sales.loc['NY'])

####Indexing multiple levels of a MultiIndex
# Look up data for NY in month 1: NY_month1
NY_month1 = sales.loc[('NY',1)]

# Look up data for CA and TX in month 2: CA_TX_month2
CA_TX_month2 = sales.loc[(['CA', 'TX'], 2), :]

# Access the inner month index and look up data for all states in month 2: all_month2
all_month2 = sales.loc[(slice(None), 2), :]

####Pivoting a single variable
# Pivot the users DataFrame: visitors_pivot

visitors_pivot = users.pivot(index='weekday',columns='city',values='visitors')

# Print the pivoted DataFrame
print(visitors_pivot)


####Pivoting all variables
# Pivot users with signups indexed by weekday and city: signups_pivot
signups_pivot = users.pivot(index='weekday',columns='city',values='signups')

# Print signups_pivot
print(signups_pivot)

# Pivot users pivoted by both signups and visitors: pivot
pivot = users.pivot(index='weekday',columns='city')

# Print the pivoted DataFrame
print(pivot)

####Stacking & unstacking I
# Unstack users by 'weekday': byweekday

byweekday = users.unstack(level='weekday')

# Print the byweekday DataFrame
print(byweekday)

# Stack byweekday by 'weekday' and print it
print(byweekday.stack(level='weekday'))

####Stacking & unstacking II
# Unstack users by 'city': bycity

bycity = users.unstack(level='city')

# Print the bycity DataFrame
print(bycity)

# Stack bycity by 'city' and print it
print(bycity.stack(level='city'))

####Restoring the index order
# Stack 'city' back into the index of bycity: newusers
newusers = bycity.stack(level='city')

# Swap the levels of the index of newusers: newusers
newusers = newusers.swaplevel(0,1)

# Print newusers and verify that the index is not sorted
print(newusers)

# Sort the index of newusers: newusers
newusers = newusers.sort_index()

# Print newusers and verify that the index is now sorted
print(newusers)

# Verify that the new DataFrame is equal to the original
print(newusers.equals(users))

####Adding names for readability
# Reset the index: visitors_by_city_weekday
visitors_by_city_weekday = visitors_by_city_weekday.reset_index()

# Print visitors_by_city_weekday
print(visitors_by_city_weekday)

# Melt visitors_by_city_weekday: visitors
visitors = pd.melt(visitors_by_city_weekday, id_vars=['weekday'], value_name='visitors')

# Print visitors
print(visitors)

####Going from wide to long
# Melt users: skinny

skinny = pd.melt(users,id_vars=['weekday','city'])

# Print skinny
print(skinny)

####Obtaining key-value pairs with melt()
# Set the new index: users_idx
users_idx =users.set_index(['city','weekday'])

# Print the users_idx DataFrame
print(users_idx)

# Obtain the key-value pairs: kv_pairs
kv_pairs = pd.melt(users_idx,col_level=0)

# Print the key-value pairs
print(kv_pairs)

####Setting up a pivot table
# Create the DataFrame with the appropriate pivot table: by_city_day
by_city_day = users.pivot_table(index='weekday',columns='city')

# Print by_city_day
print(by_city_day)


#####Using other aggregations in pivot tables
# Use a pivot table to display the count of each column: count_by_weekday1
count_by_weekday1 = users.pivot_table(index='weekday',aggfunc='count')

# Print count_by_weekday
print(count_by_weekday1)

# Replace 'aggfunc='count'' with 'aggfunc=len': count_by_weekday2
count_by_weekday2 =users.pivot_table(index='weekday',aggfunc=len)

# Verify that the same result is obtained
print('==========================================')
print(count_by_weekday1.equals(count_by_weekday2))

####Using margins in pivot tables
# Create the DataFrame with the appropriate pivot table: signups_and_visitors
signups_and_visitors = users.pivot_table(index='weekday',aggfunc=sum)

# Print signups_and_visitors
print(signups_and_visitors)

# Add in the margins: signups_and_visitors_total 
signups_and_visitors_total = users.pivot_table(index='weekday',aggfunc=sum,margins=True)

# Print signups_and_visitors_total
print(signups_and_visitors_total)

####Grouping by multiple columns
# Group titanic by 'pclass'
by_class = titanic.groupby('pclass')

# Aggregate 'survived' column of by_class by count
count_by_class = by_class['survived'].count()

# Print count_by_class
print(count_by_class)

# Group titanic by 'embarked' and 'pclass'
by_mult = titanic.groupby(['embarked','pclass'])

# Aggregate 'survived' column of by_mult by count
count_mult = by_mult['survived'].count()

# Print count_mult
print(count_mult)

####Grouping by another series
# Read life_fname into a DataFrame: life
life = pd.read_csv(life_fname, index_col='Country')

# Read regions_fname into a DataFrame: regions
regions = pd.read_csv(regions_fname,index_col='Country')

# Group life by regions['region']: life_by_region
life_by_region = life.groupby(regions['region'])

# Print the mean over the '2010' column of life_by_region
print(life_by_region['2010'].mean())

####Computing multiple aggregates of multiple columns
# Group titanic by 'pclass': by_class
by_class = titanic.groupby('pclass')

# Select 'age' and 'fare'
by_class_sub = by_class[['age','fare']]

# Aggregate by_class_sub by 'max' and 'median': aggregated
aggregated = by_class_sub.agg(['max','median'])

# Print the maximum age in each class
print(aggregated.loc[:, ('age','max')])

# Print the median fare in each class
print(aggregated.loc[:,('fare','median')])

####Aggregating on index levels/fields
# Read the CSV file into a DataFrame and sort the index: gapminder
gapminder = pd.read_csv('gapminder.csv',index_col=['Year','region','Country']).sort_index()

# Group gapminder by 'Year' and 'region': by_year_region
by_year_region = gapminder.groupby(level=['Year','region'])

# Define the function to compute spread: spread
def spread(series):
    return series.max() - series.min()

# Create the dictionary: aggregator
aggregator = {'population':'sum', 'child_mortality':'mean', 'gdp':spread}

# Aggregate by_year_region using the dictionary: aggregated
aggregated = by_year_region.agg(aggregator)

# Print the last 6 entries of aggregated 
print(aggregated.tail(6))

####Grouping on a function of the index
# Read file: sales
sales = pd.read_csv('sales.csv',index_col='Date',parse_dates=True)

# Create a groupby object: by_day
by_day = sales.groupby(sales.index.strftime('%a'))

# Create sum: units_sum
units_sum = by_day['Units'].sum()

# Print units_sum
print(units_sum)


####Detecting outliers with Z-Scores
# Import zscore
from scipy.stats import zscore

# Group gapminder_2010: standardized
standardized = gapminder_2010.groupby('region')[['life','fertility']].transform(zscore)

# Construct a Boolean Series to identify outliers: outliers
outliers = (standardized['life'] < -3) | (standardized['fertility'] > 3)

# Filter gapminder_2010 by the outliers: gm_outliers
gm_outliers = gapminder_2010.loc[outliers]

# Print gm_outliers
print(gm_outliers)

####Filling missing data (imputation) by group
# Create a groupby object: by_sex_class
by_sex_class = titanic.groupby(['sex','pclass'])

# Write a function that imputes median
def impute_median(series):
    return series.fillna(series.median())

# Impute age and assign to titanic['age']
titanic.age = by_sex_class['age'].transform(impute_median)

# Print the output of titanic.tail(10)
print(titanic.tail(10))

####Other transformations with .apply
# Group gapminder_2010 by 'region': regional
regional = gapminder_2010.groupby('region')

# Apply the disparity function on regional: reg_disp
reg_disp = regional.apply(disparity)

# Print the disparity of 'United States', 'United Kingdom', and 'China'
print(reg_disp.loc[['United States','United Kingdom','China']])

####Grouping and filtering with .apply()
# Create a groupby object using titanic over the 'sex' column: by_sex
by_sex = titanic.groupby('sex')

# Call by_sex.apply with the function c_deck_survival
c_surv_by_sex = by_sex.apply(c_deck_survival)

# Print the survival rates
print(c_surv_by_sex)
print(titanic.head())

####Grouping and filtering with .filter()
# Read the CSV file into a DataFrame: sales
sales = pd.read_csv('sales.csv', index_col='Date', parse_dates=True)

# Group sales by 'Company': by_company
by_company = sales.groupby('Company')

# Compute the sum of the 'Units' of by_company: by_com_sum
by_com_sum = by_company['Units'].sum()
print(by_com_sum)

# Filter 'Units' where the sum is > 35: by_com_filt
by_com_filt = by_company.filter(lambda g:g['Units'].sum() > 35)
print(by_com_filt)

####Filtering and grouping with .map()
# Create the Boolean Series: under10
under10 = (titanic['age'] < 10).map({True:'under 10', False:'over 10'})

# Group by under10 and compute the survival rate
survived_mean_1 = titanic.groupby(under10)['survived'].mean()
print(survived_mean_1)

# Group by under10 and pclass and compute the survival rate
survived_mean_2 = titanic.groupby([under10,'pclass'])['survived'].mean()
print(survived_mean_2)


####Using .value_counts() for ranking
# Select the 'NOC' column of medals: country_names
country_names = medals['NOC']

# Count the number of medals won by each country: medal_counts
medal_counts = country_names.value_counts()

# Print top 15 countries ranked by medals
print(medal_counts.head(15))

####Using .pivot_table() to count medals by type
# Construct the pivot table: counted
counted = medals.pivot_table(index='NOC', values='Athlete', columns='Medal', aggfunc='count')

# Create the new column: counted['totals']
counted['totals'] = counted.sum(axis='columns')

# Sort counted by the 'totals' column
counted = counted.sort_values('totals', ascending=False)

# Print the top 15 rows of counted
print(counted.head(15))


####Applying .drop_duplicates()
# Select columns: ev_gen
ev_gen = medals[['Event_gender','Gender']]

# Drop duplicate pairs: ev_gen_uniques
ev_gen_uniques = ev_gen.drop_duplicates()

# Print ev_gen_uniques
print(ev_gen_uniques)


####Finding possible errors with .groupby()
# Group medals by the two columns: medals_by_gender
medals_by_gender = medals.groupby(['Event_gender','Gender'])

# Create a DataFrame with a group count: medal_count_by_gender
medal_count_by_gender = medals_by_gender.count()

# Print medal_count_by_gender
print(medal_count_by_gender)

####Locating suspicious data
# Create the Boolean Series: sus
sus = (medals.Event_gender == 'W') & (medals.Gender == 'Men')

# Create a DataFrame with the suspicious row: suspect
suspect = medals[sus]

# Print suspect
print(suspect)

####Using .nunique() to rank by distinct sports
# Group medals by 'NOC': country_grouped
country_grouped = medals.groupby('NOC')

# Compute the number of distinct sports in which each country won medals: Nsports
Nsports = country_grouped['Sport'].nunique()

# Sort the values of Nsports in descending order
Nsports = Nsports.sort_values(ascending=False)

# Print the top 15 rows of Nsports
print(Nsports.head(15))

####Counting USA vs. USSR Cold War Olympic Sports
# Create a Boolean Series that is True when 'Edition' is between 1952 and 1988: during_cold_war
during_cold_war = (medals.Edition>=1952) & (medals.Edition <= 1988)

# Extract rows for which 'NOC' is either 'USA' or 'URS': is_usa_urs
is_usa_urs = medals.NOC.isin(['USA','URS'])

# Use during_cold_war and is_usa_urs to create the DataFrame: cold_war_medals
cold_war_medals = medals.loc[during_cold_war&is_usa_urs]

# Group cold_war_medals by 'NOC'
country_grouped = cold_war_medals.groupby('NOC')

# Create Nsports
Nsports = country_grouped['Sport'].nunique().sort_values(ascending=False)

# Print Nsports
print(Nsports)


####Counting USA vs. USSR Cold War Olympic Medals
# Create the pivot table: medals_won_by_country
medals_won_by_country = medals.pivot_table(index='Edition',columns='NOC',values='Athlete',aggfunc='count')

# Slice medals_won_by_country: cold_war_usa_urs_medals
cold_war_usa_urs_medals = medals_won_by_country.loc[1952:1988, ['USA','URS']]

# Create most_medals 
most_medals = cold_war_usa_urs_medals.idxmax(axis="columns")

# Print most_medals.value_counts()
print(most_medals.value_counts())


####Visualizing USA Medal Counts by Edition: Line Plot
# Create the DataFrame: usa
usa = medals[medals.NOC=='USA']

# Group usa by ['Edition', 'Medal'] and aggregate over 'Athlete'
usa_medals_by_year = usa.groupby(['Edition','Medal'])['Athlete'].count()

# Reshape usa_medals_by_year by unstacking
usa_medals_by_year = usa_medals_by_year.unstack(level='Medal')
# Plot the DataFrame usa_medals_by_year
usa_medals_by_year.plot()
plt.show()


####Visualizing USA Medal Counts by Edition: Area Plot
# Create the DataFrame: usa
usa = medals[medals.NOC == 'USA']

# Group usa by 'Edition', 'Medal', and 'Athlete'
usa_medals_by_year = usa.groupby(['Edition', 'Medal'])['Athlete'].count()

# Reshape usa_medals_by_year by unstacking
usa_medals_by_year = usa_medals_by_year.unstack(level='Medal')

# Create an area plot of usa_medals_by_year
usa_medals_by_year.plot.area()
plt.show()

####Visualizing USA Medal Counts by Edition: Area Plot with Ordered Medals
# Redefine 'Medal' as an ordered categorical
medals.Medal = pd.Categorical(values=medals.Medal, categories=['Bronze', 'Silver', 'Gold'], ordered=True)

# Create the DataFrame: usa
usa = medals[medals.NOC == 'USA']

# Group usa by 'Edition', 'Medal', and 'Athlete'
usa_medals_by_year = usa.groupby(['Edition', 'Medal'])['Athlete'].count()

# Reshape usa_medals_by_year by unstacking
usa_medals_by_year = usa_medals_by_year.unstack(level='Medal')

# Create an area plot of usa_medals_by_year
usa_medals_by_year.plot.area()
plt.show()


####
















































































62de1761f6bf113aadd4f592516b4abb8108b318
<mask token>


def to_celsius(F):
    return 5 / 9 * (F - 32)


<mask token>


def spread(series):
    return series.max() - series.min()


<mask token>


def impute_median(series):
    return series.fillna(series.median())


<mask token>

<mask token>
print(election.iloc[x, y] == election.loc['Bedford', 'winner'])
<mask token>
print(results.head())
<mask token>
print(p_counties)
<mask token>
print(p_counties_rev)
<mask token>
print(left_columns.head())
<mask token>
print(middle_columns.head())
<mask token>
print(right_columns.head())
<mask token>
print(three_counties)
<mask token>
print(high_turnout_df)
<mask token>
print(election.info())
<mask token>
print(df.shape)
print(df.dropna(how='any').shape)
print(df.dropna(how='all').shape)
<mask token>
print(titanic.dropna(thresh=1000, axis='columns').info())


def to_celsius(F):
    return 5 / 9 * (F - 32)


<mask token>
print(df_celsius.head())
<mask token>
print(election.head())
<mask token>
print(type(turnout_zscore))
<mask token>
print(election.head())
<mask token>
print(sales)
<mask token>
print(sales)
<mask token>
print(sales)
<mask token>
print(sales)
print(sales.loc[['CA', 'TX']])
print(sales['CA':'TX'])
<mask token>
print(sales)
<mask token>
print(sales)
print(sales.loc['NY'])
<mask token>
print(visitors_pivot)
<mask token>
print(signups_pivot)
<mask token>
print(pivot)
<mask token>
print(byweekday)
print(byweekday.stack(level='weekday'))
<mask token>
print(bycity)
print(bycity.stack(level='city'))
<mask token>
print(newusers)
<mask token>
print(newusers)
print(newusers.equals(users))
<mask token>
print(visitors_by_city_weekday)
<mask token>
print(visitors)
<mask token>
print(skinny)
<mask token>
print(users_idx)
<mask token>
print(kv_pairs)
<mask token>
print(by_city_day)
<mask token>
print(count_by_weekday1)
<mask token>
print('==========================================')
print(count_by_weekday1.equals(count_by_weekday2))
<mask token>
print(signups_and_visitors)
<mask token>
print(signups_and_visitors_total)
<mask token>
print(count_by_class)
<mask token>
print(count_mult)
<mask token>
print(life_by_region['2010'].mean())
<mask token>
print(aggregated.loc[:, ('age', 'max')])
print(aggregated.loc[:, ('fare', 'median')])
<mask token>


def spread(series):
    return series.max() - series.min()


<mask token>
print(aggregated.tail(6))
<mask token>
print(units_sum)
<mask token>
print(gm_outliers)
<mask token>


def impute_median(series):
    return series.fillna(series.median())


<mask token>
print(titanic.tail(10))
<mask token>
print(reg_disp.loc[['United States', 'United Kingdom', 'China']])
<mask token>
print(c_surv_by_sex)
print(titanic.head())
<mask token>
print(by_com_sum)
<mask token>
print(by_com_filt)
<mask token>
print(survived_mean_1)
<mask token>
print(survived_mean_2)
<mask token>
print(medal_counts.head(15))
<mask token>
print(counted.head(15))
<mask token>
print(ev_gen_uniques)
<mask token>
print(medal_count_by_gender)
<mask token>
print(suspect)
<mask token>
print(Nsports.head(15))
<mask token>
print(Nsports)
<mask token>
print(most_medals.value_counts())
<mask token>
usa_medals_by_year.plot()
plt.show()
<mask token>
usa_medals_by_year.plot.area()
plt.show()
<mask token>
usa_medals_by_year.plot.area()
plt.show()

x = 4
y = 4
print(election.iloc[x, y] == election.loc['Bedford', 'winner'])
<mask token>
election = pd.read_csv(filename, index_col='county')
results = election[['winner', 'total', 'voters']]
print(results.head())
p_counties = election.loc['Perry':'Potter', :]
print(p_counties)
p_counties_rev = election.loc['Potter':'Perry':-1]
print(p_counties_rev)
left_columns = election.loc[:, :'Obama']
print(left_columns.head())
middle_columns = election.loc[:, 'Obama':'winner']
print(middle_columns.head())
right_columns = election.loc[:, 'Romney':]
print(right_columns.head())
rows = ['Philadelphia', 'Centre', 'Fulton']
cols = ['winner', 'Obama', 'Romney']
three_counties = election.loc[rows, cols]
print(three_counties)
high_turnout = election.turnout > 70
high_turnout_df = election[election.turnout > 70]
print(high_turnout_df)
<mask token>
too_close = election.margin < 1
election.loc[too_close, 'winner'] = np.nan
print(election.info())
df = titanic[['age', 'cabin']]
print(df.shape)
print(df.dropna(how='any').shape)
print(df.dropna(how='all').shape)
df3 = df.dropna(how='all')
print(titanic.dropna(thresh=1000, axis='columns').info())


def to_celsius(F):
    return 5 / 9 * (F - 32)


df_celsius = weather[['Mean TemperatureF', 'Mean Dew PointF']].apply(to_celsius
    )
df_celsius.columns = ['Mean TemperatureC', 'Mean Dew PointC']
print(df_celsius.head())
red_vs_blue = {'Obama': 'blue', 'Romney': 'red'}
election['color'] = election['winner'].map(red_vs_blue)
print(election.head())
<mask token>
turnout_zscore = zscore(election['turnout'])
print(type(turnout_zscore))
election['turnout_zscore'] = turnout_zscore
print(election.head())
new_idx = [month.upper() for month in sales.index]
sales.index = new_idx
print(sales)
sales.index.name = 'MONTHS'
print(sales)
sales.columns.name = 'PRODUCTS'
print(sales)
months = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun']
sales.index = months
print(sales)
print(sales.loc[['CA', 'TX']])
print(sales['CA':'TX'])
sales = sales.set_index(['state', 'month'])
sales = sales.sort_index()
print(sales)
sales = sales.set_index(['state'])
print(sales)
print(sales.loc['NY'])
NY_month1 = sales.loc['NY', 1]
CA_TX_month2 = sales.loc[(['CA', 'TX'], 2), :]
all_month2 = sales.loc[(slice(None), 2), :]
visitors_pivot = users.pivot(index='weekday', columns='city', values='visitors'
    )
print(visitors_pivot)
signups_pivot = users.pivot(index='weekday', columns='city', values='signups')
print(signups_pivot)
pivot = users.pivot(index='weekday', columns='city')
print(pivot)
byweekday = users.unstack(level='weekday')
print(byweekday)
print(byweekday.stack(level='weekday'))
bycity = users.unstack(level='city')
print(bycity)
print(bycity.stack(level='city'))
newusers = bycity.stack(level='city')
newusers = newusers.swaplevel(0, 1)
print(newusers)
newusers = newusers.sort_index()
print(newusers)
print(newusers.equals(users))
visitors_by_city_weekday = visitors_by_city_weekday.reset_index()
print(visitors_by_city_weekday)
visitors = pd.melt(visitors_by_city_weekday, id_vars=['weekday'],
    value_name='visitors')
print(visitors)
skinny = pd.melt(users, id_vars=['weekday', 'city'])
print(skinny)
users_idx = users.set_index(['city', 'weekday'])
print(users_idx)
kv_pairs = pd.melt(users_idx, col_level=0)
print(kv_pairs)
by_city_day = users.pivot_table(index='weekday', columns='city')
print(by_city_day)
count_by_weekday1 = users.pivot_table(index='weekday', aggfunc='count')
print(count_by_weekday1)
count_by_weekday2 = users.pivot_table(index='weekday', aggfunc=len)
print('==========================================')
print(count_by_weekday1.equals(count_by_weekday2))
signups_and_visitors = users.pivot_table(index='weekday', aggfunc=sum)
print(signups_and_visitors)
signups_and_visitors_total = users.pivot_table(index='weekday', aggfunc=sum,
    margins=True)
print(signups_and_visitors_total)
by_class = titanic.groupby('pclass')
count_by_class = by_class['survived'].count()
print(count_by_class)
by_mult = titanic.groupby(['embarked', 'pclass'])
count_mult = by_mult['survived'].count()
print(count_mult)
life = pd.read_csv(life_fname, index_col='Country')
regions = pd.read_csv(regions_fname, index_col='Country')
life_by_region = life.groupby(regions['region'])
print(life_by_region['2010'].mean())
by_class = titanic.groupby('pclass')
by_class_sub = by_class[['age', 'fare']]
aggregated = by_class_sub.agg(['max', 'median'])
print(aggregated.loc[:, ('age', 'max')])
print(aggregated.loc[:, ('fare', 'median')])
gapminder = pd.read_csv('gapminder.csv', index_col=['Year', 'region',
    'Country']).sort_index()
by_year_region = gapminder.groupby(level=['Year', 'region'])


def spread(series):
    return series.max() - series.min()


aggregator = {'population': 'sum', 'child_mortality': 'mean', 'gdp': spread}
aggregated = by_year_region.agg(aggregator)
print(aggregated.tail(6))
sales = pd.read_csv('sales.csv', index_col='Date', parse_dates=True)
by_day = sales.groupby(sales.index.strftime('%a'))
units_sum = by_day['Units'].sum()
print(units_sum)
<mask token>
standardized = gapminder_2010.groupby('region')[['life', 'fertility']
    ].transform(zscore)
outliers = (standardized['life'] < -3) | (standardized['fertility'] > 3)
gm_outliers = gapminder_2010.loc[outliers]
print(gm_outliers)
by_sex_class = titanic.groupby(['sex', 'pclass'])


def impute_median(series):
    return series.fillna(series.median())


titanic.age = by_sex_class['age'].transform(impute_median)
print(titanic.tail(10))
regional = gapminder_2010.groupby('region')
reg_disp = regional.apply(disparity)
print(reg_disp.loc[['United States', 'United Kingdom', 'China']])
by_sex = titanic.groupby('sex')
c_surv_by_sex = by_sex.apply(c_deck_survival)
print(c_surv_by_sex)
print(titanic.head())
sales = pd.read_csv('sales.csv', index_col='Date', parse_dates=True)
by_company = sales.groupby('Company')
by_com_sum = by_company['Units'].sum()
print(by_com_sum)
by_com_filt = by_company.filter(lambda g: g['Units'].sum() > 35)
print(by_com_filt)
under10 = (titanic['age'] < 10).map({(True): 'under 10', (False): 'over 10'})
survived_mean_1 = titanic.groupby(under10)['survived'].mean()
print(survived_mean_1)
survived_mean_2 = titanic.groupby([under10, 'pclass'])['survived'].mean()
print(survived_mean_2)
country_names = medals['NOC']
medal_counts = country_names.value_counts()
print(medal_counts.head(15))
counted = medals.pivot_table(index='NOC', values='Athlete', columns='Medal',
    aggfunc='count')
counted['totals'] = counted.sum(axis='columns')
counted = counted.sort_values('totals', ascending=False)
print(counted.head(15))
ev_gen = medals[['Event_gender', 'Gender']]
ev_gen_uniques = ev_gen.drop_duplicates()
print(ev_gen_uniques)
medals_by_gender = medals.groupby(['Event_gender', 'Gender'])
medal_count_by_gender = medals_by_gender.count()
print(medal_count_by_gender)
sus = (medals.Event_gender == 'W') & (medals.Gender == 'Men')
suspect = medals[sus]
print(suspect)
country_grouped = medals.groupby('NOC')
Nsports = country_grouped['Sport'].nunique()
Nsports = Nsports.sort_values(ascending=False)
print(Nsports.head(15))
during_cold_war = (medals.Edition >= 1952) & (medals.Edition <= 1988)
is_usa_urs = medals.NOC.isin(['USA', 'URS'])
cold_war_medals = medals.loc[during_cold_war & is_usa_urs]
country_grouped = cold_war_medals.groupby('NOC')
Nsports = country_grouped['Sport'].nunique().sort_values(ascending=False)
print(Nsports)
medals_won_by_country = medals.pivot_table(index='Edition', columns='NOC',
    values='Athlete', aggfunc='count')
cold_war_usa_urs_medals = medals_won_by_country.loc[1952:1988, ['USA', 'URS']]
most_medals = cold_war_usa_urs_medals.idxmax(axis='columns')
print(most_medals.value_counts())
usa = medals[medals.NOC == 'USA']
usa_medals_by_year = usa.groupby(['Edition', 'Medal'])['Athlete'].count()
usa_medals_by_year = usa_medals_by_year.unstack(level='Medal')
usa_medals_by_year.plot()
plt.show()
usa = medals[medals.NOC == 'USA']
usa_medals_by_year = usa.groupby(['Edition', 'Medal'])['Athlete'].count()
usa_medals_by_year = usa_medals_by_year.unstack(level='Medal')
usa_medals_by_year.plot.area()
plt.show()
medals.Medal = pd.Categorical(values=medals.Medal, categories=['Bronze',
    'Silver', 'Gold'], ordered=True)
usa = medals[medals.NOC == 'USA']
usa_medals_by_year = usa.groupby(['Edition', 'Medal'])['Athlete'].count()
usa_medals_by_year = usa_medals_by_year.unstack(level='Medal')
usa_medals_by_year.plot.area()
plt.show()

x = 4
y = 4
print(election.iloc[x, y] == election.loc['Bedford', 'winner'])
import pandas as pd
election = pd.read_csv(filename, index_col='county')
results = election[['winner', 'total', 'voters']]
print(results.head())
p_counties = election.loc['Perry':'Potter', :]
print(p_counties)
p_counties_rev = election.loc['Potter':'Perry':-1]
print(p_counties_rev)
left_columns = election.loc[:, :'Obama']
print(left_columns.head())
middle_columns = election.loc[:, 'Obama':'winner']
print(middle_columns.head())
right_columns = election.loc[:, 'Romney':]
print(right_columns.head())
rows = ['Philadelphia', 'Centre', 'Fulton']
cols = ['winner', 'Obama', 'Romney']
three_counties = election.loc[rows, cols]
print(three_counties)
high_turnout = election.turnout > 70
high_turnout_df = election[election.turnout > 70]
print(high_turnout_df)
import numpy as np
too_close = election.margin < 1
election.loc[too_close, 'winner'] = np.nan
print(election.info())
df = titanic[['age', 'cabin']]
print(df.shape)
print(df.dropna(how='any').shape)
print(df.dropna(how='all').shape)
df3 = df.dropna(how='all')
print(titanic.dropna(thresh=1000, axis='columns').info())


def to_celsius(F):
    return 5 / 9 * (F - 32)


df_celsius = weather[['Mean TemperatureF', 'Mean Dew PointF']].apply(to_celsius
    )
df_celsius.columns = ['Mean TemperatureC', 'Mean Dew PointC']
print(df_celsius.head())
red_vs_blue = {'Obama': 'blue', 'Romney': 'red'}
election['color'] = election['winner'].map(red_vs_blue)
print(election.head())
from scipy.stats import zscore
turnout_zscore = zscore(election['turnout'])
print(type(turnout_zscore))
election['turnout_zscore'] = turnout_zscore
print(election.head())
new_idx = [month.upper() for month in sales.index]
sales.index = new_idx
print(sales)
sales.index.name = 'MONTHS'
print(sales)
sales.columns.name = 'PRODUCTS'
print(sales)
months = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun']
sales.index = months
print(sales)
print(sales.loc[['CA', 'TX']])
print(sales['CA':'TX'])
sales = sales.set_index(['state', 'month'])
sales = sales.sort_index()
print(sales)
sales = sales.set_index(['state'])
print(sales)
print(sales.loc['NY'])
NY_month1 = sales.loc['NY', 1]
CA_TX_month2 = sales.loc[(['CA', 'TX'], 2), :]
all_month2 = sales.loc[(slice(None), 2), :]
visitors_pivot = users.pivot(index='weekday', columns='city', values='visitors'
    )
print(visitors_pivot)
signups_pivot = users.pivot(index='weekday', columns='city', values='signups')
print(signups_pivot)
pivot = users.pivot(index='weekday', columns='city')
print(pivot)
byweekday = users.unstack(level='weekday')
print(byweekday)
print(byweekday.stack(level='weekday'))
bycity = users.unstack(level='city')
print(bycity)
print(bycity.stack(level='city'))
newusers = bycity.stack(level='city')
newusers = newusers.swaplevel(0, 1)
print(newusers)
newusers = newusers.sort_index()
print(newusers)
print(newusers.equals(users))
visitors_by_city_weekday = visitors_by_city_weekday.reset_index()
print(visitors_by_city_weekday)
visitors = pd.melt(visitors_by_city_weekday, id_vars=['weekday'],
    value_name='visitors')
print(visitors)
skinny = pd.melt(users, id_vars=['weekday', 'city'])
print(skinny)
users_idx = users.set_index(['city', 'weekday'])
print(users_idx)
kv_pairs = pd.melt(users_idx, col_level=0)
print(kv_pairs)
by_city_day = users.pivot_table(index='weekday', columns='city')
print(by_city_day)
count_by_weekday1 = users.pivot_table(index='weekday', aggfunc='count')
print(count_by_weekday1)
count_by_weekday2 = users.pivot_table(index='weekday', aggfunc=len)
print('==========================================')
print(count_by_weekday1.equals(count_by_weekday2))
signups_and_visitors = users.pivot_table(index='weekday', aggfunc=sum)
print(signups_and_visitors)
signups_and_visitors_total = users.pivot_table(index='weekday', aggfunc=sum,
    margins=True)
print(signups_and_visitors_total)
by_class = titanic.groupby('pclass')
count_by_class = by_class['survived'].count()
print(count_by_class)
by_mult = titanic.groupby(['embarked', 'pclass'])
count_mult = by_mult['survived'].count()
print(count_mult)
life = pd.read_csv(life_fname, index_col='Country')
regions = pd.read_csv(regions_fname, index_col='Country')
life_by_region = life.groupby(regions['region'])
print(life_by_region['2010'].mean())
by_class = titanic.groupby('pclass')
by_class_sub = by_class[['age', 'fare']]
aggregated = by_class_sub.agg(['max', 'median'])
print(aggregated.loc[:, ('age', 'max')])
print(aggregated.loc[:, ('fare', 'median')])
gapminder = pd.read_csv('gapminder.csv', index_col=['Year', 'region',
    'Country']).sort_index()
by_year_region = gapminder.groupby(level=['Year', 'region'])


def spread(series):
    return series.max() - series.min()


aggregator = {'population': 'sum', 'child_mortality': 'mean', 'gdp': spread}
aggregated = by_year_region.agg(aggregator)
print(aggregated.tail(6))
sales = pd.read_csv('sales.csv', index_col='Date', parse_dates=True)
by_day = sales.groupby(sales.index.strftime('%a'))
units_sum = by_day['Units'].sum()
print(units_sum)
from scipy.stats import zscore
standardized = gapminder_2010.groupby('region')[['life', 'fertility']
    ].transform(zscore)
outliers = (standardized['life'] < -3) | (standardized['fertility'] > 3)
gm_outliers = gapminder_2010.loc[outliers]
print(gm_outliers)
by_sex_class = titanic.groupby(['sex', 'pclass'])


def impute_median(series):
    return series.fillna(series.median())


titanic.age = by_sex_class['age'].transform(impute_median)
print(titanic.tail(10))
regional = gapminder_2010.groupby('region')
reg_disp = regional.apply(disparity)
print(reg_disp.loc[['United States', 'United Kingdom', 'China']])
by_sex = titanic.groupby('sex')
c_surv_by_sex = by_sex.apply(c_deck_survival)
print(c_surv_by_sex)
print(titanic.head())
sales = pd.read_csv('sales.csv', index_col='Date', parse_dates=True)
by_company = sales.groupby('Company')
by_com_sum = by_company['Units'].sum()
print(by_com_sum)
by_com_filt = by_company.filter(lambda g: g['Units'].sum() > 35)
print(by_com_filt)
under10 = (titanic['age'] < 10).map({(True): 'under 10', (False): 'over 10'})
survived_mean_1 = titanic.groupby(under10)['survived'].mean()
print(survived_mean_1)
survived_mean_2 = titanic.groupby([under10, 'pclass'])['survived'].mean()
print(survived_mean_2)
country_names = medals['NOC']
medal_counts = country_names.value_counts()
print(medal_counts.head(15))
counted = medals.pivot_table(index='NOC', values='Athlete', columns='Medal',
    aggfunc='count')
counted['totals'] = counted.sum(axis='columns')
counted = counted.sort_values('totals', ascending=False)
print(counted.head(15))
ev_gen = medals[['Event_gender', 'Gender']]
ev_gen_uniques = ev_gen.drop_duplicates()
print(ev_gen_uniques)
medals_by_gender = medals.groupby(['Event_gender', 'Gender'])
medal_count_by_gender = medals_by_gender.count()
print(medal_count_by_gender)
sus = (medals.Event_gender == 'W') & (medals.Gender == 'Men')
suspect = medals[sus]
print(suspect)
country_grouped = medals.groupby('NOC')
Nsports = country_grouped['Sport'].nunique()
Nsports = Nsports.sort_values(ascending=False)
print(Nsports.head(15))
during_cold_war = (medals.Edition >= 1952) & (medals.Edition <= 1988)
is_usa_urs = medals.NOC.isin(['USA', 'URS'])
cold_war_medals = medals.loc[during_cold_war & is_usa_urs]
country_grouped = cold_war_medals.groupby('NOC')
Nsports = country_grouped['Sport'].nunique().sort_values(ascending=False)
print(Nsports)
medals_won_by_country = medals.pivot_table(index='Edition', columns='NOC',
    values='Athlete', aggfunc='count')
cold_war_usa_urs_medals = medals_won_by_country.loc[1952:1988, ['USA', 'URS']]
most_medals = cold_war_usa_urs_medals.idxmax(axis='columns')
print(most_medals.value_counts())
usa = medals[medals.NOC == 'USA']
usa_medals_by_year = usa.groupby(['Edition', 'Medal'])['Athlete'].count()
usa_medals_by_year = usa_medals_by_year.unstack(level='Medal')
usa_medals_by_year.plot()
plt.show()
usa = medals[medals.NOC == 'USA']
usa_medals_by_year = usa.groupby(['Edition', 'Medal'])['Athlete'].count()
usa_medals_by_year = usa_medals_by_year.unstack(level='Medal')
usa_medals_by_year.plot.area()
plt.show()
medals.Medal = pd.Categorical(values=medals.Medal, categories=['Bronze',
    'Silver', 'Gold'], ordered=True)
usa = medals[medals.NOC == 'USA']
usa_medals_by_year = usa.groupby(['Edition', 'Medal'])['Athlete'].count()
usa_medals_by_year = usa_medals_by_year.unstack(level='Medal')
usa_medals_by_year.plot.area()
plt.show()

###################extracting and transforming data######
####Positional and labeled indexing

# 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'])

####Indexing and column rearrangement
# Import pandas
import pandas as pd

# Read in filename and set the index: election
election = pd.read_csv(filename,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()
print(results.head())

####Slicing rows
# Slice the row labels 'Perry' to 'Potter': p_counties

p_counties = election.loc['Perry':'Potter',:]

# Print the p_counties DataFrame
print(p_counties)

# 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
print(p_counties_rev)

####Slicing columns
# Slice the columns from the starting column to 'Obama': left_columns
left_columns = election.loc[:,:'Obama']

# Print the output of left_columns.head()
print(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()
print(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()
print(right_columns.head())

####Subselecting DataFrames with lists
# 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
print(three_counties)

####Thresholding data
# 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[election.turnout>70]

# Print the high_turnout_results DataFrame
print(high_turnout_df)


####Filtering columns using other columns
# 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()
print(election.info())

####Filtering using NaNs
# Select the 'age' and 'cabin' columns: df
df = titanic[['age','cabin']]

# Print the shape of df
print(df.shape)

# Drop rows in df with how='any' and print the shape
print(df.dropna(how='any').shape)

# Drop rows in df with how='all' and print the shape
print(df.dropna(how='all').shape)
df3=df.dropna(how='all')

# Drop columns in titanic with less than 1000 non-missing values
print(titanic.dropna(thresh=1000, axis='columns').info())


####Using apply() to transform a column
# Write a function to convert degrees Fahrenheit to degrees Celsius: to_celsius
def to_celsius(F):
    return 5/9*(F - 32)

# Apply the function over 'Mean TemperatureF' and 'Mean Dew PointF': df_celsius
df_celsius =weather[['Mean TemperatureF','Mean Dew PointF']].apply(to_celsius) 

# Reassign the column labels of df_celsius
df_celsius.columns = ['Mean TemperatureC', 'Mean Dew PointC']

# Print the output of df_celsius.head()
print(df_celsius.head())

####Using .map() with a dictionary
# 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()
print(election.head())

####Using vectorized functions
# Import zscore from scipy.stats
from scipy.stats import zscore

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

# Print the type of turnout_zscore
print(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())


####Changing index of a DataFrame
# Create the list of new indexes: new_idx

new_idx = [month.upper() for month in sales.index]

# Assign new_idx to sales.index
sales.index = new_idx

# Print the sales DataFrame
print(sales)

####Changing index name labels
# Assign the string 'MONTHS' to sales.index.name
sales.index.name='MONTHS'

# Print the sales DataFrame
print(sales)

# Assign the string 'PRODUCTS' to sales.columns.name 
sales.columns.name='PRODUCTS'

# Print the sales dataframe again
print(sales)

####Building an index, then a DataFrame
# Generate the list of months: months
months = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun']

# Assign months to sales.index
sales.index=months

# Print the modified sales DataFrame
print(sales)

####Extracting data with a MultiIndex
# Print sales.loc[['CA', 'TX']]
print(sales.loc[['CA','TX']])

# Print sales['CA':'TX']
print(sales['CA':'TX'])

####Setting & sorting a MultiIndex
# Set the index to be the columns ['state', 'month']: sales
sales = sales.set_index(['state', 'month'])

# Sort the MultiIndex: sales
sales = sales.sort_index()

# Print the sales DataFrame
print(sales)

####Using .loc[] with nonunique indexes
# Set the index to the column 'state': sales
sales = sales.set_index(['state'])

# Print the sales DataFrame
print(sales)

# Access the data from 'NY'
print(sales.loc['NY'])

####Indexing multiple levels of a MultiIndex
# Look up data for NY in month 1: NY_month1
NY_month1 = sales.loc[('NY',1)]

# Look up data for CA and TX in month 2: CA_TX_month2
CA_TX_month2 = sales.loc[(['CA', 'TX'], 2), :]

# Access the inner month index and look up data for all states in month 2: all_month2
all_month2 = sales.loc[(slice(None), 2), :]

####Pivoting a single variable
# Pivot the users DataFrame: visitors_pivot

visitors_pivot = users.pivot(index='weekday',columns='city',values='visitors')

# Print the pivoted DataFrame
print(visitors_pivot)


####Pivoting all variables
# Pivot users with signups indexed by weekday and city: signups_pivot
signups_pivot = users.pivot(index='weekday',columns='city',values='signups')

# Print signups_pivot
print(signups_pivot)

# Pivot users pivoted by both signups and visitors: pivot
pivot = users.pivot(index='weekday',columns='city')

# Print the pivoted DataFrame
print(pivot)

####Stacking & unstacking I
# Unstack users by 'weekday': byweekday

byweekday = users.unstack(level='weekday')

# Print the byweekday DataFrame
print(byweekday)

# Stack byweekday by 'weekday' and print it
print(byweekday.stack(level='weekday'))

####Stacking & unstacking II
# Unstack users by 'city': bycity

bycity = users.unstack(level='city')

# Print the bycity DataFrame
print(bycity)

# Stack bycity by 'city' and print it
print(bycity.stack(level='city'))

####Restoring the index order
# Stack 'city' back into the index of bycity: newusers
newusers = bycity.stack(level='city')

# Swap the levels of the index of newusers: newusers
newusers = newusers.swaplevel(0,1)

# Print newusers and verify that the index is not sorted
print(newusers)

# Sort the index of newusers: newusers
newusers = newusers.sort_index()

# Print newusers and verify that the index is now sorted
print(newusers)

# Verify that the new DataFrame is equal to the original
print(newusers.equals(users))

####Adding names for readability
# Reset the index: visitors_by_city_weekday
visitors_by_city_weekday = visitors_by_city_weekday.reset_index()

# Print visitors_by_city_weekday
print(visitors_by_city_weekday)

# Melt visitors_by_city_weekday: visitors
visitors = pd.melt(visitors_by_city_weekday, id_vars=['weekday'], value_name='visitors')

# Print visitors
print(visitors)

####Going from wide to long
# Melt users: skinny

skinny = pd.melt(users,id_vars=['weekday','city'])

# Print skinny
print(skinny)

####Obtaining key-value pairs with melt()
# Set the new index: users_idx
users_idx =users.set_index(['city','weekday'])

# Print the users_idx DataFrame
print(users_idx)

# Obtain the key-value pairs: kv_pairs
kv_pairs = pd.melt(users_idx,col_level=0)

# Print the key-value pairs
print(kv_pairs)

####Setting up a pivot table
# Create the DataFrame with the appropriate pivot table: by_city_day
by_city_day = users.pivot_table(index='weekday',columns='city')

# Print by_city_day
print(by_city_day)


#####Using other aggregations in pivot tables
# Use a pivot table to display the count of each column: count_by_weekday1
count_by_weekday1 = users.pivot_table(index='weekday',aggfunc='count')

# Print count_by_weekday
print(count_by_weekday1)

# Replace 'aggfunc='count'' with 'aggfunc=len': count_by_weekday2
count_by_weekday2 =users.pivot_table(index='weekday',aggfunc=len)

# Verify that the same result is obtained
print('==========================================')
print(count_by_weekday1.equals(count_by_weekday2))

####Using margins in pivot tables
# Create the DataFrame with the appropriate pivot table: signups_and_visitors
signups_and_visitors = users.pivot_table(index='weekday',aggfunc=sum)

# Print signups_and_visitors
print(signups_and_visitors)

# Add in the margins: signups_and_visitors_total 
signups_and_visitors_total = users.pivot_table(index='weekday',aggfunc=sum,margins=True)

# Print signups_and_visitors_total
print(signups_and_visitors_total)

####Grouping by multiple columns
# Group titanic by 'pclass'
by_class = titanic.groupby('pclass')

# Aggregate 'survived' column of by_class by count
count_by_class = by_class['survived'].count()

# Print count_by_class
print(count_by_class)

# Group titanic by 'embarked' and 'pclass'
by_mult = titanic.groupby(['embarked','pclass'])

# Aggregate 'survived' column of by_mult by count
count_mult = by_mult['survived'].count()

# Print count_mult
print(count_mult)

####Grouping by another series
# Read life_fname into a DataFrame: life
life = pd.read_csv(life_fname, index_col='Country')

# Read regions_fname into a DataFrame: regions
regions = pd.read_csv(regions_fname,index_col='Country')

# Group life by regions['region']: life_by_region
life_by_region = life.groupby(regions['region'])

# Print the mean over the '2010' column of life_by_region
print(life_by_region['2010'].mean())

####Computing multiple aggregates of multiple columns
# Group titanic by 'pclass': by_class
by_class = titanic.groupby('pclass')

# Select 'age' and 'fare'
by_class_sub = by_class[['age','fare']]

# Aggregate by_class_sub by 'max' and 'median': aggregated
aggregated = by_class_sub.agg(['max','median'])

# Print the maximum age in each class
print(aggregated.loc[:, ('age','max')])

# Print the median fare in each class
print(aggregated.loc[:,('fare','median')])

####Aggregating on index levels/fields
# Read the CSV file into a DataFrame and sort the index: gapminder
gapminder = pd.read_csv('gapminder.csv',index_col=['Year','region','Country']).sort_index()

# Group gapminder by 'Year' and 'region': by_year_region
by_year_region = gapminder.groupby(level=['Year','region'])

# Define the function to compute spread: spread
def spread(series):
    return series.max() - series.min()

# Create the dictionary: aggregator
aggregator = {'population':'sum', 'child_mortality':'mean', 'gdp':spread}

# Aggregate by_year_region using the dictionary: aggregated
aggregated = by_year_region.agg(aggregator)

# Print the last 6 entries of aggregated 
print(aggregated.tail(6))

####Grouping on a function of the index
# Read file: sales
sales = pd.read_csv('sales.csv',index_col='Date',parse_dates=True)

# Create a groupby object: by_day
by_day = sales.groupby(sales.index.strftime('%a'))

# Create sum: units_sum
units_sum = by_day['Units'].sum()

# Print units_sum
print(units_sum)


####Detecting outliers with Z-Scores
# Import zscore
from scipy.stats import zscore

# Group gapminder_2010: standardized
standardized = gapminder_2010.groupby('region')[['life','fertility']].transform(zscore)

# Construct a Boolean Series to identify outliers: outliers
outliers = (standardized['life'] < -3) | (standardized['fertility'] > 3)

# Filter gapminder_2010 by the outliers: gm_outliers
gm_outliers = gapminder_2010.loc[outliers]

# Print gm_outliers
print(gm_outliers)

####Filling missing data (imputation) by group
# Create a groupby object: by_sex_class
by_sex_class = titanic.groupby(['sex','pclass'])

# Write a function that imputes median
def impute_median(series):
    return series.fillna(series.median())

# Impute age and assign to titanic['age']
titanic.age = by_sex_class['age'].transform(impute_median)

# Print the output of titanic.tail(10)
print(titanic.tail(10))

####Other transformations with .apply
# Group gapminder_2010 by 'region': regional
regional = gapminder_2010.groupby('region')

# Apply the disparity function on regional: reg_disp
reg_disp = regional.apply(disparity)

# Print the disparity of 'United States', 'United Kingdom', and 'China'
print(reg_disp.loc[['United States','United Kingdom','China']])

####Grouping and filtering with .apply()
# Create a groupby object using titanic over the 'sex' column: by_sex
by_sex = titanic.groupby('sex')

# Call by_sex.apply with the function c_deck_survival
c_surv_by_sex = by_sex.apply(c_deck_survival)

# Print the survival rates
print(c_surv_by_sex)
print(titanic.head())

####Grouping and filtering with .filter()
# Read the CSV file into a DataFrame: sales
sales = pd.read_csv('sales.csv', index_col='Date', parse_dates=True)

# Group sales by 'Company': by_company
by_company = sales.groupby('Company')

# Compute the sum of the 'Units' of by_company: by_com_sum
by_com_sum = by_company['Units'].sum()
print(by_com_sum)

# Filter 'Units' where the sum is > 35: by_com_filt
by_com_filt = by_company.filter(lambda g:g['Units'].sum() > 35)
print(by_com_filt)

####Filtering and grouping with .map()
# Create the Boolean Series: under10
under10 = (titanic['age'] < 10).map({True:'under 10', False:'over 10'})

# Group by under10 and compute the survival rate
survived_mean_1 = titanic.groupby(under10)['survived'].mean()
print(survived_mean_1)

# Group by under10 and pclass and compute the survival rate
survived_mean_2 = titanic.groupby([under10,'pclass'])['survived'].mean()
print(survived_mean_2)


####Using .value_counts() for ranking
# Select the 'NOC' column of medals: country_names
country_names = medals['NOC']

# Count the number of medals won by each country: medal_counts
medal_counts = country_names.value_counts()

# Print top 15 countries ranked by medals
print(medal_counts.head(15))

####Using .pivot_table() to count medals by type
# Construct the pivot table: counted
counted = medals.pivot_table(index='NOC', values='Athlete', columns='Medal', aggfunc='count')

# Create the new column: counted['totals']
counted['totals'] = counted.sum(axis='columns')

# Sort counted by the 'totals' column
counted = counted.sort_values('totals', ascending=False)

# Print the top 15 rows of counted
print(counted.head(15))


####Applying .drop_duplicates()
# Select columns: ev_gen
ev_gen = medals[['Event_gender','Gender']]

# Drop duplicate pairs: ev_gen_uniques
ev_gen_uniques = ev_gen.drop_duplicates()

# Print ev_gen_uniques
print(ev_gen_uniques)


####Finding possible errors with .groupby()
# Group medals by the two columns: medals_by_gender
medals_by_gender = medals.groupby(['Event_gender','Gender'])

# Create a DataFrame with a group count: medal_count_by_gender
medal_count_by_gender = medals_by_gender.count()

# Print medal_count_by_gender
print(medal_count_by_gender)

####Locating suspicious data
# Create the Boolean Series: sus
sus = (medals.Event_gender == 'W') & (medals.Gender == 'Men')

# Create a DataFrame with the suspicious row: suspect
suspect = medals[sus]

# Print suspect
print(suspect)

####Using .nunique() to rank by distinct sports
# Group medals by 'NOC': country_grouped
country_grouped = medals.groupby('NOC')

# Compute the number of distinct sports in which each country won medals: Nsports
Nsports = country_grouped['Sport'].nunique()

# Sort the values of Nsports in descending order
Nsports = Nsports.sort_values(ascending=False)

# Print the top 15 rows of Nsports
print(Nsports.head(15))

####Counting USA vs. USSR Cold War Olympic Sports
# Create a Boolean Series that is True when 'Edition' is between 1952 and 1988: during_cold_war
during_cold_war = (medals.Edition>=1952) & (medals.Edition <= 1988)

# Extract rows for which 'NOC' is either 'USA' or 'URS': is_usa_urs
is_usa_urs = medals.NOC.isin(['USA','URS'])

# Use during_cold_war and is_usa_urs to create the DataFrame: cold_war_medals
cold_war_medals = medals.loc[during_cold_war&is_usa_urs]

# Group cold_war_medals by 'NOC'
country_grouped = cold_war_medals.groupby('NOC')

# Create Nsports
Nsports = country_grouped['Sport'].nunique().sort_values(ascending=False)

# Print Nsports
print(Nsports)


####Counting USA vs. USSR Cold War Olympic Medals
# Create the pivot table: medals_won_by_country
medals_won_by_country = medals.pivot_table(index='Edition',columns='NOC',values='Athlete',aggfunc='count')

# Slice medals_won_by_country: cold_war_usa_urs_medals
cold_war_usa_urs_medals = medals_won_by_country.loc[1952:1988, ['USA','URS']]

# Create most_medals 
most_medals = cold_war_usa_urs_medals.idxmax(axis="columns")

# Print most_medals.value_counts()
print(most_medals.value_counts())


####Visualizing USA Medal Counts by Edition: Line Plot
# Create the DataFrame: usa
usa = medals[medals.NOC=='USA']

# Group usa by ['Edition', 'Medal'] and aggregate over 'Athlete'
usa_medals_by_year = usa.groupby(['Edition','Medal'])['Athlete'].count()

# Reshape usa_medals_by_year by unstacking
usa_medals_by_year = usa_medals_by_year.unstack(level='Medal')
# Plot the DataFrame usa_medals_by_year
usa_medals_by_year.plot()
plt.show()


####Visualizing USA Medal Counts by Edition: Area Plot
# Create the DataFrame: usa
usa = medals[medals.NOC == 'USA']

# Group usa by 'Edition', 'Medal', and 'Athlete'
usa_medals_by_year = usa.groupby(['Edition', 'Medal'])['Athlete'].count()

# Reshape usa_medals_by_year by unstacking
usa_medals_by_year = usa_medals_by_year.unstack(level='Medal')

# Create an area plot of usa_medals_by_year
usa_medals_by_year.plot.area()
plt.show()

####Visualizing USA Medal Counts by Edition: Area Plot with Ordered Medals
# Redefine 'Medal' as an ordered categorical
medals.Medal = pd.Categorical(values=medals.Medal, categories=['Bronze', 'Silver', 'Gold'], ordered=True)

# Create the DataFrame: usa
usa = medals[medals.NOC == 'USA']

# Group usa by 'Edition', 'Medal', and 'Athlete'
usa_medals_by_year = usa.groupby(['Edition', 'Medal'])['Athlete'].count()

# Reshape usa_medals_by_year by unstacking
usa_medals_by_year = usa_medals_by_year.unstack(level='Medal')

# Create an area plot of usa_medals_by_year
usa_medals_by_year.plot.area()
plt.show()


####















































































