2. Filtering & Sorting

In [11]:

import pandas as pd

In [12]:

url='https://raw.githubusercontent.com/Datamanim/pandas/main/chipo.csv'

In [13]:

df=pd.read_csv(url)

In [14]:

df.head()

Out[14]:

	order_id	quantity	item_name	choice_description	item_price
0	1	1	Chips and Fresh Tomato Salsa	NaN	$2.39
1	1	1	Izze	[Clementine]	$3.39
2	1	1	Nantucket Nectar	[Apple]	$3.39
3	1	1	Chips and Tomatillo-Green Chili Salsa	NaN	$2.39
4	2	2	Chicken Bowl	[Tomatillo-Red Chili Salsa (Hot), [Black Beans...	$16.98

quantity컬럼 값이 3인 데이터를 추출하여 첫 5행을 출력하라¶

In [17]:

df.loc[df["quantity"]==3].head()

Out[17]:

	order_id	quantity	item_name	choice_description	item_price
409	178	3	Chicken Bowl	[[Fresh Tomato Salsa (Mild), Tomatillo-Green C...	$32.94
445	193	3	Bowl	[Braised Carnitas, Pinto Beans, [Sour Cream, C...	$22.20
689	284	3	Canned Soft Drink	[Diet Coke]	$3.75
818	338	3	Bottled Water	NaN	$3.27
850	350	3	Canned Soft Drink	[Sprite]	$3.75

quantity컬럼 값이 3인 데이터를 추출하여 index를 0부터 정렬하고 첫 5행을 출력하라¶

In [19]:

df.loc[df["quantity"]==3].head().reset_index(drop=True)

Out[19]:

	order_id	quantity	item_name	choice_description	item_price
0	178	3	Chicken Bowl	[[Fresh Tomato Salsa (Mild), Tomatillo-Green C...	$32.94
1	193	3	Bowl	[Braised Carnitas, Pinto Beans, [Sour Cream, C...	$22.20
2	284	3	Canned Soft Drink	[Diet Coke]	$3.75
3	338	3	Bottled Water	NaN	$3.27
4	350	3	Canned Soft Drink	[Sprite]	$3.75

quantity , item_price 두개의 컬럼으로 구성된 새로운 데이터 프레임을 정의하라¶

In [22]:

new_df=df[["quantity","item_price"]]

In [23]:

print(new_df)

      quantity item_price
0            1     $2.39 
1            1     $3.39 
2            1     $3.39 
3            1     $2.39 
4            2    $16.98 
...        ...        ...
4617         1    $11.75 
4618         1    $11.75 
4619         1    $11.25 
4620         1     $8.75 
4621         1     $8.75 

[4622 rows x 2 columns]

item_price 컬럼의 달러표시 문자를 제거하고 float 타입으로 저장하여 new_price 컬럼에 저장하라¶

In [35]:

df["new_price"]=new_df["item_price"].str[1:].astype('float')

In [37]:

new_df.head()
print(df.head())

   order_id  quantity                              item_name  \
0         1         1           Chips and Fresh Tomato Salsa   
1         1         1                                   Izze   
2         1         1                       Nantucket Nectar   
3         1         1  Chips and Tomatillo-Green Chili Salsa   
4         2         2                           Chicken Bowl   

                                  choice_description item_price  new_price  
0                                                NaN     $2.39        2.39  
1                                       [Clementine]     $3.39        3.39  
2                                            [Apple]     $3.39        3.39  
3                                                NaN     $2.39        2.39  
4  [Tomatillo-Red Chili Salsa (Hot), [Black Beans...    $16.98       16.98  

new_price 컬럼이 5이하의 값을 가지는 데이터프레임을 추출하고, 전체 갯수를 구하여라¶

In [29]:

len(new_df.loc[new_df["new_price"]<=5])

Out[29]:

1652

item_name명이 Chicken Salad Bowl 인 데이터 프레임을 추출하라고 index 값을 초기화 하여라¶

In [33]:

df.loc[df["item_name"]=="Chicken Salad Bowl"].reset_index(drop=True)

Out[33]:

	order_id	quantity	item_name	choice_description	item_price
0	20	1	Chicken Salad Bowl	[Fresh Tomato Salsa, [Fajita Vegetables, Pinto...	$8.75
1	60	2	Chicken Salad Bowl	[Tomatillo Green Chili Salsa, [Sour Cream, Che...	$22.50
2	94	2	Chicken Salad Bowl	[Fresh Tomato Salsa, [Fajita Vegetables, Pinto...	$22.50
3	111	1	Chicken Salad Bowl	[Fresh Tomato Salsa, [Fajita Vegetables, Rice,...	$8.75
4	137	2	Chicken Salad Bowl	[Fresh Tomato Salsa, Fajita Vegetables]	$17.50
...	...	...	...	...	...
105	1813	2	Chicken Salad Bowl	[Fresh Tomato Salsa, [Fajita Vegetables, Pinto...	$17.50
106	1822	1	Chicken Salad Bowl	[Fresh Tomato Salsa, [Black Beans, Cheese, Gua...	$11.25
107	1834	1	Chicken Salad Bowl	[Fresh Tomato Salsa, [Fajita Vegetables, Pinto...	$11.25
108	1834	1	Chicken Salad Bowl	[Fresh Tomato Salsa, [Fajita Vegetables, Lettu...	$8.75
109	1834	1	Chicken Salad Bowl	[Fresh Tomato Salsa, [Fajita Vegetables, Pinto...	$8.75

110 rows × 5 columns

new_price값이 9 이하이고 item_name 값이 Chicken Salad Bowl 인 데이터 프레임을 추출하라¶

In [41]:

df.loc[(df["new_price"]<=9)&(df["item_name"]=="Chicken Salad Bowl")].head()

Out[41]:

	order_id	quantity	item_name	choice_description	item_price	new_price
44	20	1	Chicken Salad Bowl	[Fresh Tomato Salsa, [Fajita Vegetables, Pinto...	$8.75	8.75
256	111	1	Chicken Salad Bowl	[Fresh Tomato Salsa, [Fajita Vegetables, Rice,...	$8.75	8.75
526	220	1	Chicken Salad Bowl	[Roasted Chili Corn Salsa, [Black Beans, Sour ...	$8.75	8.75
528	221	1	Chicken Salad Bowl	[Tomatillo Green Chili Salsa, [Fajita Vegetabl...	$8.75	8.75
529	221	1	Chicken Salad Bowl	[Tomatillo Green Chili Salsa, [Fajita Vegetabl...	$8.75	8.75

In [43]:

df.loc[(df.new_price<=9)&(df.item_name=="Chicken Salad Bowl")].head()

Out[43]:

	order_id	quantity	item_name	choice_description	item_price	new_price
44	20	1	Chicken Salad Bowl	[Fresh Tomato Salsa, [Fajita Vegetables, Pinto...	$8.75	8.75
256	111	1	Chicken Salad Bowl	[Fresh Tomato Salsa, [Fajita Vegetables, Rice,...	$8.75	8.75
526	220	1	Chicken Salad Bowl	[Roasted Chili Corn Salsa, [Black Beans, Sour ...	$8.75	8.75
528	221	1	Chicken Salad Bowl	[Tomatillo Green Chili Salsa, [Fajita Vegetabl...	$8.75	8.75
529	221	1	Chicken Salad Bowl	[Tomatillo Green Chili Salsa, [Fajita Vegetabl...	$8.75	8.75

df의 new_price 컬럼 값에 따라 오름차순으로 정리하고 index를 초기화 하여라¶

In [47]:

df.sort_values(by='new_price').reset_index(drop=True)

Out[47]:

	order_id	quantity	item_name	choice_description	item_price	new_price
0	471	1	Bottled Water	NaN	$1.09	1.09
1	338	1	Canned Soda	[Coca Cola]	$1.09	1.09
2	1575	1	Canned Soda	[Dr. Pepper]	$1.09	1.09
3	47	1	Canned Soda	[Dr. Pepper]	$1.09	1.09
4	1014	1	Canned Soda	[Coca Cola]	$1.09	1.09
...	...	...	...	...	...	...
4617	1443	3	Veggie Burrito	[Fresh Tomato Salsa, [Fajita Vegetables, Rice,...	$33.75	33.75
4618	1443	4	Chicken Burrito	[Fresh Tomato Salsa, [Rice, Black Beans, Chees...	$35.00	35.00
4619	511	4	Chicken Burrito	[Fresh Tomato Salsa, [Fajita Vegetables, Rice,...	$35.00	35.00
4620	1398	3	Carnitas Bowl	[Roasted Chili Corn Salsa, [Fajita Vegetables,...	$35.25	35.25
4621	1443	15	Chips and Fresh Tomato Salsa	NaN	$44.25	44.25

4622 rows × 6 columns

df의 item_name 컬럼 값중 Chips 포함하는 경우의 데이터를 출력하라¶

In [50]:

df.loc[df.item_name.str.contains('Chips')]

Out[50]:

	order_id	quantity	item_name	choice_description	item_price	new_price
0	1	1	Chips and Fresh Tomato Salsa	NaN	$2.39	2.39
3	1	1	Chips and Tomatillo-Green Chili Salsa	NaN	$2.39	2.39
6	3	1	Side of Chips	NaN	$1.69	1.69
10	5	1	Chips and Guacamole	NaN	$4.45	4.45
14	7	1	Chips and Guacamole	NaN	$4.45	4.45
...	...	...	...	...	...	...
4596	1826	1	Chips and Guacamole	NaN	$4.45	4.45
4600	1827	1	Chips and Guacamole	NaN	$4.45	4.45
4605	1828	1	Chips and Guacamole	NaN	$4.45	4.45
4613	1831	1	Chips	NaN	$2.15	2.15
4616	1832	1	Chips and Guacamole	NaN	$4.45	4.45

1084 rows × 6 columns

df의 짝수번째 컬럼만을 포함하는 데이터프레임을 출력하라¶

In [52]:

df.iloc[:,::2]

Out[52]:

	order_id	item_name	item_price
0	1	Chips and Fresh Tomato Salsa	$2.39
1	1	Izze	$3.39
2	1	Nantucket Nectar	$3.39
3	1	Chips and Tomatillo-Green Chili Salsa	$2.39
4	2	Chicken Bowl	$16.98
...	...	...	...
4617	1833	Steak Burrito	$11.75
4618	1833	Steak Burrito	$11.75
4619	1834	Chicken Salad Bowl	$11.25
4620	1834	Chicken Salad Bowl	$8.75
4621	1834	Chicken Salad Bowl	$8.75

4622 rows × 3 columns

df의 new_price 컬럼 값에 따라 내림차순으로 정리하고 index를 초기화 하여라¶

In [56]:

df.sort_values(by='new_price',ascending=False).reset_index(drop=True).head()

Out[56]:

	order_id	quantity	item_name	choice_description	item_price	new_price
0	1443	15	Chips and Fresh Tomato Salsa	NaN	$44.25	44.25
1	1398	3	Carnitas Bowl	[Roasted Chili Corn Salsa, [Fajita Vegetables,...	$35.25	35.25
2	511	4	Chicken Burrito	[Fresh Tomato Salsa, [Fajita Vegetables, Rice,...	$35.00	35.00
3	1443	4	Chicken Burrito	[Fresh Tomato Salsa, [Rice, Black Beans, Chees...	$35.00	35.00
4	1443	3	Veggie Burrito	[Fresh Tomato Salsa, [Fajita Vegetables, Rice,...	$33.75	33.75

df의 item_name 컬럼 값이 Steak Salad 또는 Bowl 인 데이터를 인덱싱하라¶

In [59]:

df.loc[(df['item_name']=='Steak Salad')|(df['item_name']=='Bowl')]

Out[59]:

	order_id	quantity	item_name	choice_description	item_price	new_price
445	193	3	Bowl	[Braised Carnitas, Pinto Beans, [Sour Cream, C...	$22.20	22.20
664	276	1	Steak Salad	[Tomatillo-Red Chili Salsa (Hot), [Black Beans...	$8.99	8.99
673	279	1	Bowl	[Adobo-Marinated and Grilled Steak, [Sour Crea...	$7.40	7.40
752	311	1	Steak Salad	[Tomatillo-Red Chili Salsa (Hot), [Black Beans...	$8.99	8.99
893	369	1	Steak Salad	[Fresh Tomato Salsa (Mild), [Rice, Cheese, Sou...	$8.99	8.99
3502	1406	1	Steak Salad	[[Lettuce, Fajita Veggies]]	$8.69	8.69

df의 item_name 컬럼 값이 Steak Salad 또는 Bowl 인 데이터를 데이터 프레임화 한 후, item_name를 기준으로 중복행이 있으면 제거하되 첫번째 케이스만 남겨라¶

In [61]:

cut=df.loc[(df['item_name']=='Steak Salad')|(df['item_name']=='Bowl')]
cut.drop_duplicates('item_name')

Out[61]:

	order_id	quantity	item_name	choice_description	item_price	new_price
445	193	3	Bowl	[Braised Carnitas, Pinto Beans, [Sour Cream, C...	$22.20	22.20
664	276	1	Steak Salad	[Tomatillo-Red Chili Salsa (Hot), [Black Beans...	$8.99	8.99

In [63]:

cut.drop_duplicates('item_name',keep='last')

Out[63]:

	order_id	quantity	item_name	choice_description	item_price	new_price
673	279	1	Bowl	[Adobo-Marinated and Grilled Steak, [Sour Crea...	$7.40	7.40
3502	1406	1	Steak Salad	[[Lettuce, Fajita Veggies]]	$8.69	8.69

df의 데이터 중 new_price값이 new_price값의 평균값 이상을 가지는 데이터들을 인덱싱하라¶

In [66]:

mean=df.new_price.mean()
print(mean)

7.464335785374397

In [67]:

df.loc[df.new_price>=mean]

Out[67]:

	order_id	quantity	item_name	choice_description	item_price	new_price
4	2	2	Chicken Bowl	[Tomatillo-Red Chili Salsa (Hot), [Black Beans...	$16.98	16.98
5	3	1	Chicken Bowl	[Fresh Tomato Salsa (Mild), [Rice, Cheese, Sou...	$10.98	10.98
7	4	1	Steak Burrito	[Tomatillo Red Chili Salsa, [Fajita Vegetables...	$11.75	11.75
8	4	1	Steak Soft Tacos	[Tomatillo Green Chili Salsa, [Pinto Beans, Ch...	$9.25	9.25
9	5	1	Steak Burrito	[Fresh Tomato Salsa, [Rice, Black Beans, Pinto...	$9.25	9.25
...	...	...	...	...	...	...
4617	1833	1	Steak Burrito	[Fresh Tomato Salsa, [Rice, Black Beans, Sour ...	$11.75	11.75
4618	1833	1	Steak Burrito	[Fresh Tomato Salsa, [Rice, Sour Cream, Cheese...	$11.75	11.75
4619	1834	1	Chicken Salad Bowl	[Fresh Tomato Salsa, [Fajita Vegetables, Pinto...	$11.25	11.25
4620	1834	1	Chicken Salad Bowl	[Fresh Tomato Salsa, [Fajita Vegetables, Lettu...	$8.75	8.75
4621	1834	1	Chicken Salad Bowl	[Fresh Tomato Salsa, [Fajita Vegetables, Pinto...	$8.75	8.75

2890 rows × 6 columns

df의 데이터 중 item_name의 값이 Izze 데이터를 Fizzy Lizzy로 수정하라¶

In [74]:

df.loc[df['item_name']=='Izze']='Fizzy Lizzy'
df

Out[74]:

	order_id	quantity	item_name	choice_description	item_price	new_price
0	1	1	Chips and Fresh Tomato Salsa	NaN	$2.39	2.39
1	Fizzy Lizzy	Fizzy Lizzy	Fizzy Lizzy	Fizzy Lizzy	Fizzy Lizzy	Fizzy Lizzy
2	1	1	Nantucket Nectar	[Apple]	$3.39	3.39
3	1	1	Chips and Tomatillo-Green Chili Salsa	NaN	$2.39	2.39
4	2	2	Chicken Bowl	[Tomatillo-Red Chili Salsa (Hot), [Black Beans...	$16.98	16.98
...	...	...	...	...	...	...
4617	1833	1	Steak Burrito	[Fresh Tomato Salsa, [Rice, Black Beans, Sour ...	$11.75	11.75
4618	1833	1	Steak Burrito	[Fresh Tomato Salsa, [Rice, Sour Cream, Cheese...	$11.75	11.75
4619	1834	1	Chicken Salad Bowl	[Fresh Tomato Salsa, [Fajita Vegetables, Pinto...	$11.25	11.25
4620	1834	1	Chicken Salad Bowl	[Fresh Tomato Salsa, [Fajita Vegetables, Lettu...	$8.75	8.75
4621	1834	1	Chicken Salad Bowl	[Fresh Tomato Salsa, [Fajita Vegetables, Pinto...	$8.75	8.75

4622 rows × 6 columns

In [75]:

df.loc[df.item_name=='Izze','item_name']='Fizzy Lizzy'

df의 데이터 중 choice_description 값이 NaN 인 데이터의 갯수를 구하여라¶

In [77]:

df.choice_description.isnull().sum()

Out[77]:

1246

df의 데이터 중 choice_description 값이 NaN 인 데이터를 NoData 값으로 대체하라(loc 이용)¶

In [80]:

df.head()

Out[80]:

	order_id	quantity	item_name	choice_description	item_price	new_price
0	1	1	Chips and Fresh Tomato Salsa	NaN	$2.39	2.39
1	Fizzy Lizzy	Fizzy Lizzy	Fizzy Lizzy	Fizzy Lizzy	Fizzy Lizzy	Fizzy Lizzy
2	1	1	Nantucket Nectar	[Apple]	$3.39	3.39
3	1	1	Chips and Tomatillo-Green Chili Salsa	NaN	$2.39	2.39
4	2	2	Chicken Bowl	[Tomatillo-Red Chili Salsa (Hot), [Black Beans...	$16.98	16.98

In [81]:

df.loc[df.choice_description.isnull(),'choice_description']='NoData'

In [82]:

df.head()

Out[82]:

	order_id	quantity	item_name	choice_description	item_price	new_price
0	1	1	Chips and Fresh Tomato Salsa	NoData	$2.39	2.39
1	Fizzy Lizzy	Fizzy Lizzy	Fizzy Lizzy	Fizzy Lizzy	Fizzy Lizzy	Fizzy Lizzy
2	1	1	Nantucket Nectar	[Apple]	$3.39	3.39
3	1	1	Chips and Tomatillo-Green Chili Salsa	NoData	$2.39	2.39
4	2	2	Chicken Bowl	[Tomatillo-Red Chili Salsa (Hot), [Black Beans...	$16.98	16.98

df의 데이터 중 choice_description 값에 Black이 들어가는 경우를 인덱싱하라¶

In [84]:

df[df.choice_description.str.contains('Black')]

Out[84]:

	order_id	quantity	item_name	choice_description	item_price	new_price
4	2	2	Chicken Bowl	[Tomatillo-Red Chili Salsa (Hot), [Black Beans...	$16.98	16.98
7	4	1	Steak Burrito	[Tomatillo Red Chili Salsa, [Fajita Vegetables...	$11.75	11.75
9	5	1	Steak Burrito	[Fresh Tomato Salsa, [Rice, Black Beans, Pinto...	$9.25	9.25
11	6	1	Chicken Crispy Tacos	[Roasted Chili Corn Salsa, [Fajita Vegetables,...	$8.75	8.75
12	6	1	Chicken Soft Tacos	[Roasted Chili Corn Salsa, [Rice, Black Beans,...	$8.75	8.75
...	...	...	...	...	...	...
4604	1828	1	Chicken Bowl	[Fresh Tomato Salsa, [Rice, Black Beans, Chees...	$8.75	8.75
4608	1829	1	Veggie Burrito	[Tomatillo Red Chili Salsa, [Fajita Vegetables...	$11.25	11.25
4611	1830	1	Veggie Burrito	[Tomatillo Green Chili Salsa, [Rice, Fajita Ve...	$11.25	11.25
4612	1831	1	Carnitas Bowl	[Fresh Tomato Salsa, [Fajita Vegetables, Rice,...	$9.25	9.25
4617	1833	1	Steak Burrito	[Fresh Tomato Salsa, [Rice, Black Beans, Sour ...	$11.75	11.75

1345 rows × 6 columns

df의 데이터 중 choice_description 값에 Vegetables 들어가지 않는 경우의 갯수를 출력하라¶

In [86]:

len(df.loc[~df.choice_description.str.contains('Vegetables')])

Out[86]:

3900

df의 데이터 중 item_name 값이 N으로 시작하는 데이터를 모두 추출하라¶

In [90]:

df.loc[df.item_name.str.startswith('N')].head()

Out[90]:

	order_id	quantity	item_name	choice_description	item_price	new_price
2	1	1	Nantucket Nectar	[Apple]	$3.39	3.39
22	11	1	Nantucket Nectar	[Pomegranate Cherry]	$3.39	3.39
105	46	1	Nantucket Nectar	[Pineapple Orange Banana]	$3.39	3.39
173	77	1	Nantucket Nectar	[Apple]	$3.39	3.39
205	91	1	Nantucket Nectar	[Peach Orange]	$3.39	3.39

df의 데이터 중 item_name 값의 단어갯수가 15개 이상인 데이터를 인덱싱하라¶

In [93]:

df.loc[df.item_name.str.len()>=15].head(3)

Out[93]:

	order_id	quantity	item_name	choice_description	item_price	new_price
0	1	1	Chips and Fresh Tomato Salsa	NoData	$2.39	2.39
2	1	1	Nantucket Nectar	[Apple]	$3.39	3.39
3	1	1	Chips and Tomatillo-Green Chili Salsa	NoData	$2.39	2.39

df의 데이터 중 new_price값이 lst에 해당하는 경우의 데이터 프레임을 구하고 그 갯수를 출력하라 lst =[1.69, 2.39, 3.39, 4.45, 9.25, 10.98, 11.75, 16.98]¶

In [98]:

lst =[1.69, 2.39, 3.39, 4.45, 9.25, 10.98, 11.75, 16.98]
len(df.loc[df.new_price.isin(lst)])

Out[98]:

1373

In [ ]: