<jupyter_start><jupyter_text>Chapter 5===============================[[실행 코드]](https://github.com/alstn2468/Python_For_Machine_Learning/blob/master/Chapter.5/3.ipynb) Groupby- SQL groupby 명령어와 같음- split -> apply -> combine과정을 거쳐 연산<jupyter_code>import pandas as pd

ipl_data = {'Team': ['Riders', 'Riders', 'Devils', 'Devils', 'Kings',
                     'kings', 'Kings', 'Kings', 'Riders', 'Royals', 'Royals', 'Riders'],
            'Rank': [1, 2, 2, 3, 3, 4 ,1 ,1, 2 , 4, 1, 2],
            'Year': [2014, 2015, 2014, 2015, 2014, 2015, 2016, 2017, 2016, 2014, 2015, 2017],
            'Points':[876, 789, 863, 673, 741, 812, 756, 788, 694, 701, 804, 690]}

df = pd.DataFrame(ipl_data)
df<jupyter_output><empty_output><jupyter_text>- 묶음의 기준이 되는 컬럼 : Team- 적용받는 컬럼 : Points- 적용받는 연산 : sum()- 결과 : Team을 기준으로 Points를 Sum<jupyter_code>df.groupby("Team")["Points"].sum()<jupyter_output><empty_output><jupyter_text>- 1개 이상의 column을 묶을 수 있다.<jupyter_code>df.groupby(['Team', 'Year'])['Points'].sum()<jupyter_output><empty_output><jupyter_text>Hierarchical index- Groupby 명령의 결과물은 DataFrame- 2개의 column으로 groupby를 할 경우, index가 2개 생성<jupyter_code>h_index = df.groupby(["Team", "Year"])["Points"].sum()
h_index
h_index.index
h_index['Devils' : 'Kings']<jupyter_output><empty_output><jupyter_text>Hierarchical index - unstack()- Group으로 묶여진 데이터를 matrix형태로 전환<jupyter_code>h_index.unstack()<jupyter_output><empty_output><jupyter_text>Hierarchical index  - swaplevel- index level을 변경할 수 있다.<jupyter_code>h_index.swaplevel()
h_index.swaplevel().sortlevel(0)<jupyter_output>/anaconda3/lib/python3.6/site-packages/ipykernel_launcher.py:1: FutureWarning: sortlevel is deprecated, use sort_index(level=...)
  """Entry point for launching an IPython kernel.<jupyter_text>Hierarchical index - operations- index level을 기준으로 기본 연산 수행 가능<jupyter_code>h_index.sum(level = 0)
h_index.sum(level = 1)<jupyter_output><empty_output><jupyter_text>Groupby - grouped- Groupby에 의해 Split된 상태를 추출 가능- Tuple 형태로 그룹의 key값, value값이 추출<jupyter_code>grouped = df.groupby('Team')

for name, group in grouped :
    print(name)
    print(type(name))
    print(group)
    print(type(group))<jupyter_output>Devils
<class 'str'>
   Points  Rank    Team  Year
2     863     2  Devils  2014
3     673     3  Devils  2015
<class 'pandas.core.frame.DataFrame'>
Kings
<class 'str'>
   Points  Rank   Team  Year
4     741     3  Kings  2014
6     756     1  Kings  2016
7     788     1  Kings  2017
<class 'pandas.core.frame.DataFrame'>
Riders
<class 'str'>
    Points  Rank    Team  Year
0      876     1  Riders  2014
1      789     2  Riders  2015
8      694     2  Riders  2016
11     690     2  Riders  2017
<class 'pandas.core.frame.DataFrame'>
Royals
<class 'str'>
    Points  Rank    Team  Year
9      701     4  Royals  2014
10     804     1  Royals  2015
<class 'pandas.core.frame.DataFrame'>
kings
<class 'str'>
   Points  Rank   Team  Year
5     812     4  kings  2015
<class 'pandas.core.frame.DataFrame'><jupyter_text>- 특정 key값을 가진 그룹의 정보만 추출 가능<jupyter_code>grouped.get_group('Devils')<jupyter_output><empty_output><jupyter_text>- 추출된 group 정보에는 세 가지 유형의 apply가 가능- Aggregation : 요약된 통계정보 추출- Transformation : 해당 정보를 변환- Filtration : 특정 정보를 제거하여 보여주는 필터링 기능  Groupby - aggregation<jupyter_code>grouped.agg(sum)
import numpy as np
grouped.agg(np.mean)<jupyter_output><empty_output><jupyter_text>- 특정 column에 여러개의 function을 적용 가능<jupyter_code>grouped['Points'].agg([np.sum, np.mean, np.std])<jupyter_output><empty_output><jupyter_text>Groupby - transformation- Aggregation과 달리 key값 별로 요약된 정보가 아니다.- 개별 데이터의 변환을 지원<jupyter_code>df
score = lambda x : (x)
grouped.transform(score)<jupyter_output><empty_output><jupyter_text>- 단 max나 min처럼 Series 데이터에 적용되는데이터들은 Key값을 기준으로 Grouped된 데이터 기준<jupyter_code>score = lambda x : (x.max())
grouped.transform(score)
score = lambda x : (x - x.mean()) / x.std()
grouped.transform(score)<jupyter_output><empty_output><jupyter_text>Groupby - filter- 특정 조건으로 데이터를 검색할 때 사용- filter안에는 boolean 조건이 존재해야한다.- len(x)는 grouped된 DataFrame 개수<jupyter_code>df.groupby('Team').filter(lambda x : len(x) >= 3)<jupyter_output><empty_output><jupyter_text>Data- 시간과 데이터 종류가 정리된 통화량 데이터<jupyter_code>import dateutil

df_phone = pd.read_csv('./data/phone_data.csv')
df_phone['date'] = df_phone['date'].apply(dateutil.parser.parse, dayfirst = True)
df_phone.head()
df_phone.groupby('month')['duration'].sum()
df_phone[df_phone['item'] == 'call'].groupby('network')['duration'].sum()
df_phone.groupby(['month', 'item'])['date'].count()
df_phone.groupby(['month', 'item'])['date'].count().unstack()
df_phone.groupby('month', as_index = False).agg({'duration' : 'sum'})
df_phone.groupby(['month', 'item']).agg({'duration' : sum, 
                                         'network_type' : 'count',
                                         'date' : 'first'})
df_phone.groupby(['month', 'item']).agg({'duration' : [min, max, sum],
                                     'network_type' : "count",
                                     'date' : [min, 'first', 'nunique']})
grouped = df_phone.groupby('month').agg( {"duration" : [min, max, np.mean]})

grouped.columns = grouped.columns.droplevel(level = 0)

grouped.rename(columns = {"min" : "min_duration",
                          "max" : "max_duration", 
                          "mean" : "mean_duration"})<jupyter_output><empty_output><jupyter_text>Pivot Table- 우리가 Excel에서 보던 그것- index축은 groupby와 동일- column에 추가로 labelling 값을 추가하여,value에 numeric type값을 aggregation하는 형태<jupyter_code>df_phone = pd.read_csv("./data/phone_data.csv")
df_phone['date'] = df_phone['date'].apply(dateutil.parser.parse, dayfirst = True)
df_phone.head()
df_phone.pivot_table(["duration"],
                     index = [df_phone.month,df_phone.item], 
                     columns = df_phone.network, aggfunc = "sum", fill_value = 0)<jupyter_output><empty_output><jupyter_text>Crosstab- 두 칼럼에 교차 빈도, 비율, 덧셈등을 구할 때 사용- Pivot table의 특수한 형태- User-Item Rating Matix등을 만들 때 사용 가능<jupyter_code>df_movie = pd.read_csv('./data/movie_rating.csv')
df_movie.head()
pd.crosstab(index = df_movie.critic, columns = df_movie.title,
            values = df_movie.rating, aggfunc = 'first').fillna(0)
df_movie.pivot_table(['rating'], index = df_movie.critic, columns = df_movie.title,
                     aggfunc = 'sum', fill_value = 0)<jupyter_output><empty_output><jupyter_text>Merge- SQL에서 많이 사용하는 Merge와 같은 기능- 두 개의 데이터를 하나로 합침<jupyter_code>raw_data = {'subject_id' : ['1', '2', '3', '4', '5', '7', '8', '9', '10', '11'],
            'test_score': [51, 15, 15, 61, 16, 14, 15, 1, 61, 16]}
df_a = pd.DataFrame(raw_data, columns = ['subject_id', 'test_score'])
df_a
raw_data = {'subject_id': ['4', '5', '6', '7', '8'],
            'first_name': ['Billy', 'Brian', 'Bran', 'Bryce', 'Betty'], 
            'last_name': ['Bonder', 'Black', 'Balwner', 'Brice', 'Btisan']}
df_b = pd.DataFrame(raw_data, columns = ['subject_id', 'first_name', 'last_name'])
df_b<jupyter_output><empty_output><jupyter_text>- subject_id기준으로 Merge<jupyter_code>pd.merge(df_a, df_b, on = 'subject_id')<jupyter_output><empty_output><jupyter_text>- 두 DataFrame column이름이 다를 때<jupyter_code>pd.merge(df_a, df_b, left_on = 'subject_id', right_on = 'subject_id')<jupyter_output><empty_output><jupyter_text>Left Join<jupyter_code>pd.merge(df_a, df_b, on='subject_id', how='left')<jupyter_output><empty_output><jupyter_text>Right Join<jupyter_code>pd.merge(df_a, df_b, on='subject_id', how='right')<jupyter_output><empty_output><jupyter_text>Full(Outer) Join<jupyter_code>pd.merge(df_a, df_b, on = 'subject_id', how = 'outer')<jupyter_output><empty_output><jupyter_text>Inner Join<jupyter_code>pd.merge(df_a, df_b, on = 'subject_id', how = 'inner')<jupyter_output><empty_output><jupyter_text>Index based Join<jupyter_code>pd.merge(df_a, df_b, right_index = True, left_index = True)<jupyter_output><empty_output><jupyter_text>Concat- 같은 형태의 데이터를 붙이는 연산작업<jupyter_code>raw_data = {'subject_id' : ['1', '2', '3', '4', '5'],
            'first_name' : ['Alex', 'Amy', 'Allen', 'Alice', 'Ayoung'], 
            'last_name' : ['Anderson', 'Ackerman', 'Ali', 'Aoni', 'Atiches']}
df_a = pd.DataFrame(raw_data, columns = ['subject_id', 'first_name', 'last_name'])
df_a
raw_data = {'subject_id' : ['4', '5', '6', '7', '8'],
            'first_name' : ['Billy', 'Brian', 'Bran', 'Bryce', 'Betty'],
            'last_name' : ['Bonder', 'Black', 'Balwner', 'Brice', 'Btisan']}
df_b = pd.DataFrame(raw_data, columns = ['subject_id', 'first_name', 'last_name'])
df_b
df_new = pd.concat([df_a, df_b])
df_new.reset_index()
df_a.append(df_b)
df_new = pd.concat([df_a, df_b], axis = 1)
df_new.reset_index()<jupyter_output><empty_output><jupyter_text>Database connection- Data loading시 db connection 기능을 제공<jupyter_code>import sqlite3

conn = sqlite3.connect('./data/flights.db')
cur = conn.cursor()
cur.execute('SELECT * FROM airlines LIMIT 5;')
results = cur.fetchall()
results
df_airplines = pd.read_sql_query("SELECT * FROM airlines;", conn)
df_airports = pd.read_sql_query("SELECT * FROM airports;", conn)
df_routes = pd.read_sql_query("SELECT * FROM routes;", conn)
df_airplines
df_airports
df_routes<jupyter_output><empty_output><jupyter_text>XLS persistence- DataFrame의 엑셀 추출 코드- XLS 엔진으로 openpyxls 또는 XlsxWrite 사용<jupyter_code>writer = pd.ExcelWriter('.data/df_routes.xlsx', engine = 'xlsxwriter')
df_routes.to_excel(writer, sheet_name = 'Sheet1')<jupyter_output><empty_output><jupyter_text>Pickle persistence- 가장 일반적인 Python 파일 persistence- to_pickle, read_pickle 함수 사용<jupyter_code>df_routes.to_pickle('./data/df_routes.pickle')
df_routes_pickle = pd.read_pickle("./data/df_routes.pickle")
df_routes_pickle.head()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.15819909128459314, 'fraction_numerical': 0.028810408921933085, 'mean_word_length': 4.634089586969168, 'pattern_counts': {'":': 0, '<': 125, '<?xml version=': 0, '>': 128, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 8, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '29', 'dir': 'jupyter-structured-clean-dedup', 'id': '479800', 'license': 'MIT', 'n_tokens_mistral': 4301, 'n_tokens_neox': 4378, 'n_words': 754, 'path': 'Chapter.5/4/3.ipynb', 'repo_name': 'alstn2468/Python_For_Machine_Learning', 'size': '9684'}