반응형
/*******************************************************************************************************************
-- Title : [Py3.5] Pandas.DataFrame을 이용한 Join 및 Groupby 처리 - ver.dBRang
-- Key word : pandas dataframe data frame 데이터프레임 데이터 프레임 list 리스트 tolist() tolist loc() iloc()
loc iloc as_index apply groupby merge lambda join 조인 axis
*******************************************************************************************************************/
■ Script
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
|
# -*- coding: utf-8 -*-
import pandas as pd
from pandas import Series, DataFrame
# ******************************
# -- Set Dataframe Option
# ******************************
pd.set_option('display.height', 1000)
pd.set_option('display.max_rows', 500)
pd.set_option('display.max_columns', 500)
pd.set_option('display.width', 1000)
# ******************************
# -- Create Dataframe
# ******************************
# -- group df 생성
dict_ti = \
{'akey': [2112041012232,1503203013332,1686004005182,1794048000012,1685003006210,1503202023281,2015411202753,4585020000937,2794048000795,
7495920000509,2985004001629,6586002001041,6791009001054,2796036001894,4586044000763,2986004002019,3090007000569,3090006000292],
'grp' : [11,11,11,11,11,22,22,22,33,44,44,44,44,55,55,55,66,66]}
df_ti = DataFrame(dict_ti);
print(df_ti)
print("... df_ti", "." * 100, "\n")
# -- inventor df 생성
dict_inv = \
{'akey': [2112041012232,1503203013332,1686004005182,1794048000012,1685003006210,1503202023281,2015411202753,4585020000937,2794048000795,
7495920000509,2985004001629,6586002001041,6791009001054,2796036001894,4586044000763,2986004002019,3090007000569,3090006000292],
'evan' : [1,1,0,1,0,0,1,0,0,0,0,0,0,0,0,0,0,0],
'harold' : [1,1,1,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0],
'hopkins' : [0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0],
'james' : [0,0,0,0,0,0,0,0,0,1,1,1,0,0,0,0,0,0],
'john' : [0,0,0,0,0,0,0,0,0,0,1,1,0,1,1,0,0,0],
'kieta' : [0,0,0,0,0,0,0,0,0,0,0,1,1,1,1,0,0,0],
'thomas' : [0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1],
'hayden' : [0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0]}
df_inv = DataFrame(dict_inv);
print(df_inv)
print("... df_inv", "." * 100, "\n")
# ******************************
# -- Merge Dataframe
# ******************************
# -- group-inventor를 조인한 df 생성
df_merge = pd.merge(df_ti, df_inv, how="inner", left_on="akey", right_on="akey")
print (df_merge)
print("--- df_merge", "-" * 50, "\n")
# -- 값이 1인 것만 추출
print (df_merge[["grp","evan"]].loc[df_merge["evan"] == 1])
print (df_merge[[1,2]].loc[df_merge.iloc[0:,2] == 1])
print("--- df_merge.value=1", "-" * 50, "\n")
# -- 값이 1인 것만 추출하여 리스트로 전환
print (df_merge[["grp","evan"]].loc[df_merge["evan"] == 1].values.tolist())
print (df_merge[[1,2]].loc[df_merge.iloc[0:,2] == 1].values.tolist())
print("--- df_merge.list from dataframe", "-" * 50, "\n")
# -- 컬럼 개수 추출
print (len(df_merge.columns))
print("--- df_merge.column-count", "-" * 50, "\n")
# -- 행 개수 추출
print (df_merge["grp"].loc[df_merge.iloc[0:, 7] == 1].count())
print("--- df_merge.row-count", "-" * 50, "\n")
# ******************************
# -- Calculate Values using min()
# ******************************
# -- group열만 존재하는 df 생성
df_result = df_merge[['grp']].groupby(['grp'], as_index=False).min()
print (df_result)
print("*** df_result", "*" * 100, "\n")
# -- 컬럼 개수 추출
col_cnt = len(df_merge.columns)
print ("rowcnt: ", col_cnt)
print("*** df_merge.row_count", "*" * 100, "\n")
# -- 컬럼별로 최소 group값 추출하기
for i in range(2, col_cnt):
# 처리할 컬럼명 가져오기
col_nm = df_merge.columns[i]
# 해당 컬럼에서 value=1인것만 추출
df_tmp = df_merge[["grp"]].loc[df_merge.iloc[0:, i] == 1]
# 'grp'으로 그룹핑
df_tmp = df_tmp.groupby(['grp'], as_index=False).min()
# 해당 컬럼에 최소 grp값으로 추가
df_tmp[col_nm] = df_tmp['grp'].min()
# df_tmp 출력
print(df_tmp)
print("." * 15)
df_result = pd.merge(df_result, df_tmp, how="left", left_on="grp", right_on="grp")
# -- df_result 출력
print(df_result)
print("*** df_result.added all cols", "*" * 100, "\n")
# ******************************
# -- Recreate df_result
# ******************************
# -- 컬럼간에 min() 값 추출하여 신규 필드 생성
df_result["new_grp"] = df_result.apply(lambda x: x.min(), axis=1)
# -- 필요 컬럼만 재조합
df_result = df_result[["grp", "new_grp"]]
print (df_result)
print("$$$ df_result.recreated", "$" * 100, "\n")
# ******************************
# -- Last Result
# ******************************
print(pd.merge(df_ti, df_result, how="left", left_on="grp", right_on="grp"))
|
■ Files
반응형