[pandas] csv파일 입출력, 데이터값 수정, concat/merge

 

 

 

In [42]:

import numpy as np
import pandas as pd
import seaborn as sns

 

 

파일의 입출력¶

• 참고: https://pandas.pydata.org/pandas-docs/stable/user_guide/io.html
• DATA_PATH를 지정해서 파일의 위치를 효율적으로 호출할 수 있다.
• to_csv() :

 

In [43]:

DATA_PATH = 'data/'
air_quality_no2 = pd.read_csv('data/air_quality_no2_long.csv', parse_dates = True)
air_quality_pm25 = pd.read_csv(DATA_PATH + 'air_quality_pm25_long.csv', parse_dates = True)
air_quality_stations = pd.read_csv(DATA_PATH + 'air_quality_stations.csv')

 

In [44]:

air_quality_no2.loc[:, 'value'].mean()

 

Out[44]:

26.261847195357834

 

In [45]:

result = air_quality_no2.loc[(air_quality_no2['value'] >= air_quality_no2.loc[:, 'value'].mean()) &
(air_quality_no2['country'] == 'FR'),
['city', 'country']]
result.head(3)

 

Out[45]:

	city	country
2	Paris	FR
12	Paris	FR
13	Paris	FR

 

In [46]:

#디폴트 저장장소: 해당 ipyb이 있는 폴더에 저장
#index = False: index 번호를 열로 추출 안 하고 저장.
result.to_csv('result.csv', index = False)

 

In [47]:

#저장 위치 지정하기
result.to_csv(DATA_PATH + 'result_pandas_study04.csv', index = False)

 

 

데이터프레임 다루기¶

• str.lower(): 원하는 열의 문자열의 모든 문자를 소문자로 변환
• str.split(): 원하는 열의 문자열을 ()안의 기준으로 분할하고 리스트로 반환
• str.len(): 지정한 열의 문자열의 길이
• str.len().idmax: 가장 길이가 긴 문자열의 인덱스
• replace(): 지정한 열의 index를 () 안의 조건에 맞게 변경
• contains(): 지정한 열에서 ()안의 값이 들어간 열을 bool 타입으로 추출

 

In [48]:

titanic = pd.read_csv('https://raw.githubusercontent.com/pandas-dev/pandas/main/doc/data/titanic.csv')
titanic.head(2)

 

Out[48]:

	PassengerId	Survived	Pclass	Name	Sex	Age	SibSp	Parch	Ticket	Fare	Cabin	Embarked
0	1	0	3	Braund, Mr. Owen Harris	male	22.0	1	0	A/5 21171	7.2500	NaN	S
1	2	1	1	Cumings, Mrs. John Bradley (Florence Briggs Th...	female	38.0	1	0	PC 17599	71.2833	C85	C

 

In [49]:

titanic['Name'].str.lower()

 

Out[49]:

0                                braund, mr. owen harris
1      cumings, mrs. john bradley (florence briggs th...
2                                 heikkinen, miss. laina
3           futrelle, mrs. jacques heath (lily may peel)
4                               allen, mr. william henry
                             ...                        
886                                montvila, rev. juozas
887                         graham, miss. margaret edith
888             johnston, miss. catherine helen "carrie"
889                                behr, mr. karl howell
890                                  dooley, mr. patrick
Name: Name, Length: 891, dtype: object

 

In [50]:

#원본 데이터 프레임에 반영
titanic['Name'] = titanic['Name'].str.lower()
titanic.head(3)

 

Out[50]:

	PassengerId	Survived	Pclass	Name	Sex	Age	SibSp	Parch	Ticket	Fare	Cabin	Embarked
0	1	0	3	braund, mr. owen harris	male	22.0	1	0	A/5 21171	7.2500	NaN	S
1	2	1	1	cumings, mrs. john bradley (florence briggs th...	female	38.0	1	0	PC 17599	71.2833	C85	C
2	3	1	3	heikkinen, miss. laina	female	26.0	0	0	STON/O2. 3101282	7.9250	NaN	S

 

In [51]:

titanic['Name'].str.split(",") 

 

Out[51]:

0                             [braund,  mr. owen harris]
1      [cumings,  mrs. john bradley (florence briggs ...
2                              [heikkinen,  miss. laina]
3        [futrelle,  mrs. jacques heath (lily may peel)]
4                            [allen,  mr. william henry]
                             ...                        
886                             [montvila,  rev. juozas]
887                      [graham,  miss. margaret edith]
888          [johnston,  miss. catherine helen "carrie"]
889                             [behr,  mr. karl howell]
890                               [dooley,  mr. patrick]
Name: Name, Length: 891, dtype: object

 

In [52]:

#Surname이라는 열을 만드는 메서드
#Name을 split한 리스트의 0 위치의 인덱스를 가져옴

titanic['Surname'] = titanic["Name"].str.split(",").str.get(0)
titanic.head(3)

 

Out[52]:

	PassengerId	Survived	Pclass	Name	Sex	Age	SibSp	Parch	Ticket	Fare	Cabin	Embarked	Surname
0	1	0	3	braund, mr. owen harris	male	22.0	1	0	A/5 21171	7.2500	NaN	S	braund
1	2	1	1	cumings, mrs. john bradley (florence briggs th...	female	38.0	1	0	PC 17599	71.2833	C85	C	cumings
2	3	1	3	heikkinen, miss. laina	female	26.0	0	0	STON/O2. 3101282	7.9250	NaN	S	heikkinen

 

In [53]:

titanic["Name"].str.contains("countess")

 

Out[53]:

0      False
1      False
2      False
3      False
4      False
       ...  
886    False
887    False
888    False
889    False
890    False
Name: Name, Length: 891, dtype: bool

 

In [54]:

#true인 index를 전부 추출
titanic[titanic["Name"].str.contains("countess")]

 

Out[54]:

	PassengerId	Survived	Pclass	Name	Sex	Age	SibSp	Parch	Ticket	Fare	Cabin	Embarked	Surname
759	760	1	1	rothes, the countess. of (lucy noel martha dye...	female	33.0	0	0	110152	86.5	B77	S	rothes

 

In [55]:

#Name 컬럼의 문자열의 길이
titanic["Name"].str.len()

 

Out[55]:

0      23
1      51
2      22
3      44
4      24
       ..
886    21
887    28
888    40
889    21
890    19
Name: Name, Length: 891, dtype: int64

 

In [56]:

titanic["Name"].str.len().idxmax()

 

Out[56]:

307

 

In [57]:

#가장 긴 길이의 문자열의 index값을 추출
titanic.loc[titanic["Name"].str.len().idxmax(), 'Name']

 

Out[57]:

'penasco y castellana, mrs. victor de satode (maria josefa perez de soto y vallejo)'

 

In [58]:

#기존의 Sex column을 index를 replace 조건에 맞게 변경한 col

#titanic["Sex"] = titanic["Sex"].replace("male", "M")
#titanic["Sex"] = titanic["Sex"].replace("female", "F")

titanic["Sex"] = titanic["Sex"].replace({"male":"M", "female":"F"})
titanic.head(2)

 

Out[58]:

	PassengerId	Survived	Pclass	Name	Sex	Age	SibSp	Parch	Ticket	Fare	Cabin	Embarked	Surname
0	1	0	3	braund, mr. owen harris	M	22.0	1	0	A/5 21171	7.2500	NaN	S	braund
1	2	1	1	cumings, mrs. john bradley (florence briggs th...	F	38.0	1	0	PC 17599	71.2833	C85	C	cumings

 

 

데이터 프레임 합치기¶

• pd.concat(): 2개의 데이터프레임을 합친다.
  • concat을 활용한 index 구분 추가
• sort_values(): ()안을 기준으로 정리
• merge(): 2개의 데이터프레임을 공통된 항목을 기준으로 합친다.

 

In [59]:

air_quality_no2 = air_quality_no2[['date.utc', 'location', 'parameter', 'value']]
air_quality_no2.head(1)

 

Out[59]:

	date.utc	location	parameter	value
0	2019-06-21 00:00:00+00:00	FR04014	no2	20.0

 

In [60]:

air_quality_pm25 = air_quality_pm25[['date.utc', 'location', 'parameter', 'value']]
air_quality_pm25.tail(1)

 

Out[60]:

	date.utc	location	parameter	value
1109	2019-05-07 01:00:00+00:00	London Westminster	pm25	8.0

 

In [72]:

air_quality_stations.tail(3)

 

Out[72]:

	location	coordinates.latitude	coordinates.longitude
63	Thurrock	51.47707	0.31797
64	Tower Hamlets Roadside	51.52253	-0.04216
65	Groton Fort Griswold	41.35360	-72.07890

 

 

• pd.concat
  • 디폴트 값으로 axis = 0, 행을 기준으로 데이터프레임을 합친다. -> 위, 아래로 합치기
  • key를 통해 합친 데이터프레임을 구분할 수도 있다.

 

In [74]:

air_quality = pd.concat([air_quality_no2, air_quality_pm25]) #axis = 0이 디폴트임.
air_quality

#air_quality_no2.head(1)
#air_quality_pm25.tail(1)
#no2의 아래에 pm25 데이터프레임이 그대로 붙음.

 

Out[74]:

	date.utc	location	parameter	value
0	2019-06-21 00:00:00+00:00	FR04014	no2	20.0
1	2019-06-20 23:00:00+00:00	FR04014	no2	21.8
2	2019-06-20 22:00:00+00:00	FR04014	no2	26.5
3	2019-06-20 21:00:00+00:00	FR04014	no2	24.9
4	2019-06-20 20:00:00+00:00	FR04014	no2	21.4
...	...	...	...	...
1105	2019-05-07 06:00:00+00:00	London Westminster	pm25	9.0
1106	2019-05-07 04:00:00+00:00	London Westminster	pm25	8.0
1107	2019-05-07 03:00:00+00:00	London Westminster	pm25	8.0
1108	2019-05-07 02:00:00+00:00	London Westminster	pm25	8.0
1109	2019-05-07 01:00:00+00:00	London Westminster	pm25	8.0

3178 rows × 4 columns

 

In [63]:

air_quality = air_quality.sort_values("date.utc") #시간순으로 정리
air_quality.head()

 

Out[63]:

	date.utc	location	parameter	value
1109	2019-05-07 01:00:00+00:00	London Westminster	pm25	8.0
2067	2019-05-07 01:00:00+00:00	London Westminster	no2	23.0
1098	2019-05-07 01:00:00+00:00	BETR801	no2	50.5
1003	2019-05-07 01:00:00+00:00	FR04014	no2	25.0
100	2019-05-07 01:00:00+00:00	BETR801	pm25	12.5

 

In [67]:

air_quality_key = pd.concat([air_quality_no2, air_quality_pm25], keys = ["NO2", "PM25"])
air_quality_key

 

Out[67]:

		date.utc	location	parameter	value
NO2	0	2019-06-21 00:00:00+00:00	FR04014	no2	20.0
	1	2019-06-20 23:00:00+00:00	FR04014	no2	21.8
	2	2019-06-20 22:00:00+00:00	FR04014	no2	26.5
	3	2019-06-20 21:00:00+00:00	FR04014	no2	24.9
	4	2019-06-20 20:00:00+00:00	FR04014	no2	21.4
...	...	...	...	...	...
PM25	1105	2019-05-07 06:00:00+00:00	London Westminster	pm25	9.0
	1106	2019-05-07 04:00:00+00:00	London Westminster	pm25	8.0
	1107	2019-05-07 03:00:00+00:00	London Westminster	pm25	8.0
	1108	2019-05-07 02:00:00+00:00	London Westminster	pm25	8.0
	1109	2019-05-07 01:00:00+00:00	London Westminster	pm25	8.0

3178 rows × 4 columns

 

 

• pd.merge(): 두 데이터프레임을 각 데이터에 존재하는 고유값(key)를 기준으로 병합한다.

 

In [75]:

#air_quality_stations: latitude, longitude 정보

air_quality = pd.merge(air_quality, air_quality_stations, how = 'left', on = 'location')
#how = left: letf join을 한다. 왼쪽 데이터프레임(air_quality)을 모두 포함하고 오른쪽 데이터프레임은 일치하는 값만 행을 포함시킨다.
#on: join할 열의 기준

air_quality

 

Out[75]:

	date.utc	location	parameter	value	coordinates.latitude	coordinates.longitude
0	2019-06-21 00:00:00+00:00	FR04014	no2	20.0	48.83724	2.39390
1	2019-06-21 00:00:00+00:00	FR04014	no2	20.0	48.83722	2.39390
2	2019-06-20 23:00:00+00:00	FR04014	no2	21.8	48.83724	2.39390
3	2019-06-20 23:00:00+00:00	FR04014	no2	21.8	48.83722	2.39390
4	2019-06-20 22:00:00+00:00	FR04014	no2	26.5	48.83724	2.39390
...	...	...	...	...	...	...
4177	2019-05-07 06:00:00+00:00	London Westminster	pm25	9.0	51.49467	-0.13193
4178	2019-05-07 04:00:00+00:00	London Westminster	pm25	8.0	51.49467	-0.13193
4179	2019-05-07 03:00:00+00:00	London Westminster	pm25	8.0	51.49467	-0.13193
4180	2019-05-07 02:00:00+00:00	London Westminster	pm25	8.0	51.49467	-0.13193
4181	2019-05-07 01:00:00+00:00	London Westminster	pm25	8.0	51.49467	-0.13193

4182 rows × 6 columns