Manipulating DataFrames

import pandas as pd
import numpy as np
%matplotlib inline
import matplotlib.pyplot as plt

# setting plot defatult size
%pylab inline
pylab.rcParams['figure.figsize'] = (12, 6)

df = pd.read_csv('data/election2012.csv')
df = df.loc[df.state == 'PA']
df.head()

election = df.set_index('county')
election.head()

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

p_counties = election.loc['Potter':'Perry':-1]   # reverse
p_counties

turnout_df = election[election['turnout'] < 1]
turnout_df

low_margin = election[(election.margin < 3) & (election.turnout < 3)]   # or = |
low_margin

# 득표율 차이가 3% 이내이면 winner 를 NULL 처리.
too_close_row = election['margin'] < 3
election.loc[too_close_row, 'winner'] = np.nan
election.info()

election['winner'].value_counts()

# test data
sales = pd.DataFrame({'month':['Jan','Feb','Mar','Apr','May','Jun'],
                  'eggs':[47,110,226,82,132,210],
                  'salt':[12,50,89,87,np.nan,60],
                  'spam':[17,31,72,20,52,55]})
sales = sales.set_index('month')
sales

# convert to dozon unit function
def dozens(n):
    return n//12

sales.apply(dozens)

sales.apply(lambda n: n//12)

sales['dozen_spam'] = sales.spam.apply(dozens)
sales['salty_spam'] = sales.salt + sales.spam
sales

red_vs_blue = {'Obama':'blue', 'Romney':'red'}
election['color'] = election.winner.map(red_vs_blue)

election.head()

from scipy.stats import zscore

turnout_zscore = zscore(election['turnout'])    # z-score
election['turnout_zscore'] = turnout_zscore
election.tail()

sales.index = [idx.upper() for idx in sales.index]
sales.head(3)

sales.index.name = 'MONTHS'
sales.head(3)

# make test data
sales = pd.DataFrame({'state':['CA','CA','TX','TX','NY','NY'], 'month':[1,2,1,2,1,2],
                      'eggs':[47,110,221,77,69,88],
                      'salt':[12,50,89,87,73,49], 'spam':[17,31,72,20,37,56]})
sales

sales2 = sales.set_index(['state','month'])   # multi index
sales2

sales2.index      # 인덱스 정보 조회

sales2.loc[('NY', 1)]

sales2.loc['CA']

sales = pd.DataFrame({'weekday':['Sun','Sun','Mon','Mon'],
                  'item':['egg','spam','egg','spam'],
                  'sold':[120,250,189,187],
                  'retail':[17,31,72,20]})
sales

pivot_sold = sales.pivot(index='weekday', columns='item', values='sold')
pivot_sold

pivot_sales = sales.pivot(index='weekday', columns='item')
pivot_sales

sales = sales.set_index(['weekday','item'])
sales

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

byweekday.stack(level='weekday')

byitem = sales.unstack(level='item')
byitem

byitem.stack(level='item')

sales = pd.DataFrame({'weekday':['Sun','Sun','Mon','Mon'],
                  'item':['egg','spam','egg','spam'],
                  'sold':[120,250,189,187],
                  'retail':[17,31,72,20]})
sales

sales2 = pd.melt(sales, id_vars=['weekday'], value_name='value')
sales2

sales3 = pd.melt(sales, id_vars=['weekday','item'])
sales3

kv_pairs = pd.melt(sales, col_level=0)
kv_pairs.tail()

sales

by_item_day = sales.pivot_table(index='weekday', columns='item')
by_item_day

sales.pivot_table(index='weekday', aggfunc=sum)

sales.pivot_table(index='weekday', aggfunc=sum, margins=True)   # All 포함.

titanic = pd.read_csv('data/titanic.csv')
titanic.head()

by_class = titanic.groupby('pclass')
by_class

by_class['survived'].count()

by_class_sub = by_class[['age','fare']]
aggregated = by_class_sub.agg(['max','median','mean'])
aggregated

aggregated.loc[:, ('age','max')]

titanic.groupby(['embarked','pclass'])['survived'].count()

titanic_idx = pd.read_csv('data/titanic.csv', index_col=['pclass','sex']).sort_index()
titanic_idx

by_grp1 = titanic_idx.groupby(level=['pclass','sex'])
by_grp1

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

# Aggregate by using the dictionary
aggregator = {'survived':'sum', 'age':'mean', 'fare':spread}
aggr = by_grp1.agg(aggregator)
aggr

# make test data
dates = pd.date_range('20161001', periods=30)
df = pd.DataFrame(np.random.rand(30,3).round(2), index=dates, columns=list('ABC'))
df.index.name = 'Date'
df.head()

by_day = df.groupby(df.index.strftime('%a'))
by_day['C'].sum()

from scipy.stats import zscore

import warnings
warnings.simplefilter(action = "ignore", category = RuntimeWarning)

cars = pd.read_csv('data/automobile.csv')
cars.head()

np.seterr(divide='ignore')
standardized = cars.groupby('cylinders')['city_mpg','price'].transform(zscore)
# identify outliers
outliers = (standardized['city_mpg'] < -3) | (standardized['city_mpg'] > 3)

cars.loc[outliers]

titanic.tail()

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

# grouping by sex & pclass
by_sex_class = titanic.groupby(['sex','pclass'])

# sex & pclass 그룹별로 median 값을 missing data에 할당
titanic['age'] = by_sex_class['age'].transform(impute_median)
titanic.tail()

def disparity(gr):
    # spread
    s = gr['fare'].max() - gr['fare'].min()
    # z-score
    z = (gr['fare'] - gr['fare'].mean())/gr['fare'].std()
    return pd.DataFrame({'zscore':z , 'spread':s})

by_class = titanic.groupby('pclass')
by_class['fare'].mean()

pgroup_disp = by_class.apply(disparity)  # 모든 row에 대해 각각 적용됨.
pgroup_disp.head()

# C deck 의 생존율

def c_deck_survival(gr):
    c_passengers = gr['cabin'].str.startswith('C').fillna(False)
    return gr.loc[c_passengers, 'survived'].mean()

titanic.groupby('sex').apply(c_deck_survival)

# 10살 기준 생존율

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

survived_mean_1 = titanic.groupby(under10)['survived'].mean()
survived_mean_1

survived_mean_2 = titanic.groupby([under10, 'pclass'])['survived'].mean()
survived_mean_2

medals = pd.read_csv('data/olympic_medals.csv')
medals.head()

# 국가별 메달 합계
medals['NOC'].value_counts()[0:10]

# count medals by type

counted = medals.pivot_table(index='NOC', values='Athlete', columns='Medal', aggfunc='count')
counted['totals'] = counted.sum(axis='columns')   # Create the new column
counted = counted.sort_values('totals', ascending=False)
counted.head()

# drop_duplicates

ev_gen = medals[['Event_gender', 'Gender']]
ev_gen_uniques = ev_gen.drop_duplicates()
ev_gen_uniques

medals_by_gender = medals.groupby(['Event_gender', 'Gender'])
medal_count_by_gender = medals_by_gender.count()
medal_count_by_gender

medals[(medals.Event_gender == 'W') & (medals.Gender == 'Men')]

# distinct : nunique()

# 국가별로 메달을 획득한 종목 수
country_grouped = medals.groupby('NOC')
Nsports = country_grouped['Sport'].nunique()
Nsports = Nsports.sort_values(ascending=False)
Nsports.head()

# USA vs. USSR

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)
Nsports

medals_won_by_country = medals.pivot_table(index='Edition', columns='NOC', values='Athlete', aggfunc='count')
cold_war_usa_usr_medals = medals_won_by_country.loc[1952:1988, ['USA','URS']]   # Slice
most_medals = cold_war_usa_usr_medals.idxmax(axis='columns')
most_medals.value_counts()

# USA Medal Counts by Edition

usa = medals[medals.NOC == 'USA']
usa_medals_by_year = usa.groupby(['Edition', 'Medal'])['Athlete'].count()

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

# Line Plot
usa_medals_by_year.plot()

# Area Plot
usa_medals_by_year.plot.area()

# 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 = 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()