pandas学习-基础用法

 

 

导入数据、数据的基本操作¶

 

 

导入libraries¶

 

In [1]:

import pandas as pd
import numpy as np

import os

 

 

导入数据¶

 

In [2]:

file_path = os.path.abspath('data/chipotle.tsv')
file_path

 

Out[2]:

'D:\\python_jupyter\\mine_python_notes\\07_Pandas\\data\\chipotle.tsv'

 

In [3]:

chipo = pd.read_csv(file_path, sep = '\t')

 

In [4]:

chipo

 

Out[4]:

 

	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
...	...	...	...	...	...
4617	1833	1	Steak Burrito	[Fresh Tomato Salsa, [Rice, Black Beans, Sour ...	$11.75
4618	1833	1	Steak Burrito	[Fresh Tomato Salsa, [Rice, Sour Cream, Cheese...	$11.75
4619	1834	1	Chicken Salad Bowl	[Fresh Tomato Salsa, [Fajita Vegetables, Pinto...	$11.25
4620	1834	1	Chicken Salad Bowl	[Fresh Tomato Salsa, [Fajita Vegetables, Lettu...	$8.75
4621	1834	1	Chicken Salad Bowl	[Fresh Tomato Salsa, [Fajita Vegetables, Pinto...	$8.75

4622 rows × 5 columns

 

 

查看前10行数据¶

 

In [5]:

chipo.head(10)

 

Out[5]:

 

	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
5	3	1	Chicken Bowl	[Fresh Tomato Salsa (Mild), [Rice, Cheese, Sou...	$10.98
6	3	1	Side of Chips	NaN	$1.69
7	4	1	Steak Burrito	[Tomatillo Red Chili Salsa, [Fajita Vegetables...	$11.75
8	4	1	Steak Soft Tacos	[Tomatillo Green Chili Salsa, [Pinto Beans, Ch...	$9.25
9	5	1	Steak Burrito	[Fresh Tomato Salsa, [Rice, Black Beans, Pinto...	$9.25

 

 

查看数据集中的数据量¶

 

In [6]:

chipo.shape

 

Out[6]:

(4622, 5)

 

In [7]:

# 数据行数
chipo.shape[0]

 

Out[7]:

4622

 

In [8]:

chipo.info()

 

 

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 4622 entries, 0 to 4621
Data columns (total 5 columns):
 #   Column              Non-Null Count  Dtype 
---  ------              --------------  ----- 
 0   order_id            4622 non-null   int64 
 1   quantity            4622 non-null   int64 
 2   item_name           4622 non-null   object
 3   choice_description  3376 non-null   object
 4   item_price          4622 non-null   object
dtypes: int64(2), object(3)
memory usage: 180.7+ KB

 

 

查看数据集的列数¶

 

In [9]:

chipo.shape[1]

 

Out[9]:

5

 

 

打印出列名称¶

 

In [10]:

chipo.columns

 

Out[10]:

Index(['order_id', 'quantity', 'item_name', 'choice_description',
       'item_price'],
      dtype='object')

 

 

数据集的索引¶

 

In [11]:

chipo.index

 

Out[11]:

RangeIndex(start=0, stop=4622, step=1)

 

 

汇总排序¶

 

In [12]:

c = chipo.groupby('item_name')
c = c.sum('order_id')
c = c.sort_values(['quantity'], ascending=False)
c.head(10)

 

Out[12]:

 

	order_id	quantity
item_name
Chicken Bowl	713926	761
Chicken Burrito	497303	591
Chips and Guacamole	449959	506
Steak Burrito	328437	386
Canned Soft Drink	304753	351
Chips	208004	230
Steak Bowl	193752	221
Bottled Water	175944	211
Chips and Fresh Tomato Salsa	100419	130
Canned Soda	76396	126

 

In [13]:

c = chipo.groupby('choice_description').sum('order_id')
c = c.sort_values(['quantity'], ascending=False)
c.head(10)

 

Out[13]:

 

	order_id	quantity
choice_description
[Diet Coke]	123455	159
[Coke]	122752	143
[Sprite]	80426	89
[Fresh Tomato Salsa, [Rice, Black Beans, Cheese, Sour Cream, Lettuce]]	43088	49
[Fresh Tomato Salsa, [Rice, Black Beans, Cheese, Sour Cream]]	36041	42
[Fresh Tomato Salsa, [Rice, Black Beans, Cheese, Sour Cream, Guacamole, Lettuce]]	37550	40
[Lemonade]	31892	36
[Fresh Tomato Salsa (Mild), [Pinto Beans, Rice, Cheese, Sour Cream]]	24432	36
[Coca Cola]	19282	32
[Fresh Tomato Salsa, [Rice, Cheese, Sour Cream, Lettuce]]	29614	30

 

 

列quantity数量统计¶

 

In [14]:

chipo.quantity.sum()

 

Out[14]:

4972

 

 

查看列类型¶

 

In [15]:

chipo.item_price.dtype

 

Out[15]:

dtype('O')

 

 

使用lambda函数转化数据类型¶

apply函数的作用是将一个函数应用到 DataFrame 或 Series 的每一行或每一列上，然后返回一个新的DataFrame或Series。这个函数可以是内置的函数，也可以是用户自定义的函数。

df.apply(func, axis=0)

df 是一个 DataFrame，func 是要应用的函数，axis 是指定函数应用的方向，取值为 0 或 1。当 axis=0 时，表示对每一列应用函数；当 axis=1 时，表示对每一行应用函数。

 

In [16]:

func = lambda x : float(x[1:-1])
chipo.item_price = chipo.item_price.apply(func)
# 或者使用 列表推导式
# chipo.item_price = [float(val[1:]) for val in chipo.item_price]

 

In [17]:

chipo.item_price.dtype

 

Out[17]:

dtype('float64')

 

In [18]:

chipo.item_price

 

Out[18]:

0        2.39
1        3.39
2        3.39
3        2.39
4       16.98
        ...  
4617    11.75
4618    11.75
4619    11.25
4620     8.75
4621     8.75
Name: item_price, Length: 4622, dtype: float64

 

 

统计总价¶

 

In [19]:

(chipo.quantity * chipo.item_price).sum()

 

Out[19]:

39237.02

 

In [20]:

c = chipo.order_id.value_counts()
c

 

Out[20]:

926     23
1483    14
205     12
759     11
1786    11
        ..
768      1
341      1
1048     1
94       1
1199     1
Name: order_id, Length: 1834, dtype: int64

 

In [21]:

c.count()

 

Out[21]:

1834

 

In [22]:

chipo.item_name.value_counts().count()

 

Out[22]:

50

 

 

过滤和排序数据¶

 

 

drop_duplicates函数用于去除DataFrame中的重复行。

DataFrame.drop_duplicates(subset=None, keep='first', inplace=False)

• subset：指定要考虑的列名或列名的列表。默认值为None，表示考虑所有列。
• keep：指定保留哪个重复的行。可选值为'first'（保留第一个出现的重复行）、'last'（保留最后一个出现的重复行）或False（删除所有重复行）。默认值为'first'。
• inplace：指定是否在原始DataFrame上进行修改。如果为True，则在原始DataFrame上删除重复行并返回None。如果为False（默认值），则返回一个新的DataFrame，其中删除了重复行。

 

In [23]:

data = {'name': ['John', 'Mary', 'John', 'Peter'],
        'city': ['London', 'Paris', 'London', 'Berlin'],
         'age':[10, 20, 10 ,40]}
df = pd.DataFrame(data)

# 删除所有重复行
df.drop_duplicates(inplace=True)
df

 

Out[23]:

 

	name	city	age
0	John	London	10
1	Mary	Paris	20
3	Peter	Berlin	40

 

 

统计超过$10的产品¶

 

In [24]:

chipo_filtered = chipo.drop_duplicates(['item_name','quantity','choice_description'])
# chipo_filtered = chipo_filtered[chipo_filtered.quantity==1]
chipo_filtered

 

Out[24]:

 

	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
...	...	...	...	...	...
4602	1827	1	Barbacoa Burrito	[Tomatillo Green Chili Salsa]	9.25
4607	1829	1	Steak Burrito	[Tomatillo Green Chili Salsa, [Rice, Cheese, S...	11.75
4610	1830	1	Steak Burrito	[Fresh Tomato Salsa, [Rice, Sour Cream, Cheese...	11.75
4611	1830	1	Veggie Burrito	[Tomatillo Green Chili Salsa, [Rice, Fajita Ve...	11.25
4612	1831	1	Carnitas Bowl	[Fresh Tomato Salsa, [Fajita Vegetables, Rice,...	9.25

1949 rows × 5 columns

 

In [25]:

# 查找产品数量等于1
chipo_one_prd = chipo_filtered[chipo_filtered.quantity==1]
chipo_one_prd

 

Out[25]:

 

	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
5	3	1	Chicken Bowl	[Fresh Tomato Salsa (Mild), [Rice, Cheese, Sou...	10.98
...	...	...	...	...	...
4602	1827	1	Barbacoa Burrito	[Tomatillo Green Chili Salsa]	9.25
4607	1829	1	Steak Burrito	[Tomatillo Green Chili Salsa, [Rice, Cheese, S...	11.75
4610	1830	1	Steak Burrito	[Fresh Tomato Salsa, [Rice, Sour Cream, Cheese...	11.75
4611	1830	1	Veggie Burrito	[Tomatillo Green Chili Salsa, [Rice, Fajita Ve...	11.25
4612	1831	1	Carnitas Bowl	[Fresh Tomato Salsa, [Fajita Vegetables, Rice,...	9.25

1806 rows × 5 columns

 

In [26]:

chipo_one_prd[chipo_one_prd['item_price']>10].item_name

 

Out[26]:

5          Chicken Bowl
7         Steak Burrito
13         Chicken Bowl
23      Chicken Burrito
39        Barbacoa Bowl
             ...       
4593      Carnitas Bowl
4594      Barbacoa Bowl
4607      Steak Burrito
4610      Steak Burrito
4611     Veggie Burrito
Name: item_name, Length: 685, dtype: object

 

In [27]:

chipo_one_prd.query('item_price>10').item_name.nunique()

 

Out[27]:

25

 

 

多个条件查找¶

 

In [28]:

chipo[(chipo['item_name'] == 'Chicken Bowl') & (chipo['quantity'] == 1)]

 

Out[28]:

 

	order_id	quantity	item_name	choice_description	item_price
5	3	1	Chicken Bowl	[Fresh Tomato Salsa (Mild), [Rice, Cheese, Sou...	10.98
13	7	1	Chicken Bowl	[Fresh Tomato Salsa, [Fajita Vegetables, Rice,...	11.25
19	10	1	Chicken Bowl	[Tomatillo Red Chili Salsa, [Fajita Vegetables...	8.75
26	13	1	Chicken Bowl	[Roasted Chili Corn Salsa (Medium), [Pinto Bea...	8.49
42	20	1	Chicken Bowl	[Roasted Chili Corn Salsa, [Rice, Black Beans,...	11.25
...	...	...	...	...	...
4590	1825	1	Chicken Bowl	[Roasted Chili Corn Salsa, [Rice, Black Beans,...	11.25
4591	1825	1	Chicken Bowl	[Tomatillo Red Chili Salsa, [Rice, Black Beans...	8.75
4595	1826	1	Chicken Bowl	[Tomatillo Green Chili Salsa, [Rice, Black Bea...	8.75
4599	1827	1	Chicken Bowl	[Roasted Chili Corn Salsa, [Cheese, Lettuce]]	8.75
4604	1828	1	Chicken Bowl	[Fresh Tomato Salsa, [Rice, Black Beans, Chees...	8.75

693 rows × 5 columns

 

 

根据name排序¶

 

In [29]:

chipo.item_name.sort_values()
# 或者
chipo.sort_values(by='item_name')

 

Out[29]:

 

	order_id	quantity	item_name	choice_description	item_price
3389	1360	2	6 Pack Soft Drink	[Diet Coke]	12.98
341	148	1	6 Pack Soft Drink	[Diet Coke]	6.49
1849	749	1	6 Pack Soft Drink	[Coke]	6.49
1860	754	1	6 Pack Soft Drink	[Diet Coke]	6.49
2713	1076	1	6 Pack Soft Drink	[Coke]	6.49
...	...	...	...	...	...
2384	948	1	Veggie Soft Tacos	[Roasted Chili Corn Salsa, [Fajita Vegetables,...	8.75
781	322	1	Veggie Soft Tacos	[Fresh Tomato Salsa, [Black Beans, Cheese, Sou...	8.75
2851	1132	1	Veggie Soft Tacos	[Roasted Chili Corn Salsa (Medium), [Black Bea...	8.49
1699	688	1	Veggie Soft Tacos	[Fresh Tomato Salsa, [Fajita Vegetables, Rice,...	11.25
1395	567	1	Veggie Soft Tacos	[Fresh Tomato Salsa (Mild), [Pinto Beans, Rice...	8.49

4622 rows × 5 columns

 

 

查询最贵的商品数量是多少¶

 

In [30]:

chipo.sort_values(by='item_price', ascending=False).head(1)

 

Out[30]:

 

	order_id	quantity	item_name	choice_description	item_price
3598	1443	15	Chips and Fresh Tomato Salsa	NaN	44.25

 

 

其他条件检索¶

 

In [31]:

chipo_salad = chipo[chipo.item_name == "Veggie Salad Bowl"]
len(chipo_salad)

 

Out[31]:

18

 

In [32]:

chipo_drink_steak_bowl = chipo[(chipo.item_name == "Canned Soda") & (chipo.quantity > 1)]
len(chipo_drink_steak_bowl)

 

Out[32]:

20

 

In [33]:

chipo[chipo.item_name.str.startswith('C')]

 

Out[33]:

 

	order_id	quantity	item_name	choice_description	item_price
0	1	1	Chips and Fresh Tomato Salsa	NaN	2.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
5	3	1	Chicken Bowl	[Fresh Tomato Salsa (Mild), [Rice, Cheese, Sou...	10.98
10	5	1	Chips and Guacamole	NaN	4.45
...	...	...	...	...	...
4615	1832	1	Chicken Soft Tacos	[Fresh Tomato Salsa, [Rice, Cheese, Sour Cream]]	8.75
4616	1832	1	Chips and Guacamole	NaN	4.45
4619	1834	1	Chicken Salad Bowl	[Fresh Tomato Salsa, [Fajita Vegetables, Pinto...	11.25
4620	1834	1	Chicken Salad Bowl	[Fresh Tomato Salsa, [Fajita Vegetables, Lettu...	8.75
4621	1834	1	Chicken Salad Bowl	[Fresh Tomato Salsa, [Fajita Vegetables, Pinto...	8.75

3131 rows × 5 columns

 

In [34]:

# 根据列明来筛选
chipo[['item_name','item_price']]

 

Out[34]:

 

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

4622 rows × 2 columns

 

In [59]:

chipo[(chipo['item_name'] != 'Steak Burrito') & (chipo.item_name != 'Chicken Salad Bowl')]

 

Out[59]:

 

	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
...	...	...	...	...	...
4612	1831	1	Carnitas Bowl	[Fresh Tomato Salsa, [Fajita Vegetables, Rice,...	9.25
4613	1831	1	Chips	NaN	2.15
4614	1831	1	Bottled Water	NaN	1.50
4615	1832	1	Chicken Soft Tacos	[Fresh Tomato Salsa, [Rice, Cheese, Sour Cream]]	8.75
4616	1832	1	Chips and Guacamole	NaN	4.45

4144 rows × 5 columns

 

 

使用.iloc通过传递的整数的位置进行切片¶

在 pandas 中，iloc 是用于基于位置进行索引和选择数据的方法。它的基本语法是df.iloc[row_index, column_index]，其中 df 是一个 DataFrame 对象。

iloc 方法的主要用途有以下几个：

• 选择行和列：可以使用 iloc 方法选择指定的行和列。例如，df.iloc[3, 0] 选择第 3 行、第 0 列的值。

• 切片：可以使用 iloc 方法进行切片操作，选择指定范围内的行和列。例如，df.iloc[1:3, 0:2] 选择第 1 到 2 行和第 0 到 1 列。

• 布尔索引：可以使用布尔条件进行索引，选择满足条件的行和列。例如，df.iloc[df['A'] > 0, [0, 1]] 选择满足 'A' 列大于 0 的行，并选择第 0 和 1 列。

• 设置值：可以使用 iloc 方法设置指定位置的值。例如，df.iloc[2, 0] = 10 将第 2 行、第 0 列的值设置为 10。

需要注意的是，iloc 方法使用的是位置索引，即指定的行和列的位置必须存在于 DataFrame 中。如果要使用标签索引，可以使用 loc 方法。另外，iloc 方法也支持多个位置索引的列表或数组，以选择多个行或列。

 

In [35]:

# :表示所有，0:3表示从0列到第3列
chipo.iloc[:, 0:3]

 

Out[35]:

 

	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
...	...	...	...
4617	1833	1	Steak Burrito
4618	1833	1	Steak Burrito
4619	1834	1	Chicken Salad Bowl
4620	1834	1	Chicken Salad Bowl
4621	1834	1	Chicken Salad Bowl

4622 rows × 3 columns

 

In [36]:

chipo.iloc[0:10, 0:3]

 

Out[36]:

 

	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
5	3	1	Chicken Bowl
6	3	1	Side of Chips
7	4	1	Steak Burrito
8	4	1	Steak Soft Tacos
9	5	1	Steak Burrito

 

 

在 pandas 中，loc 是用于基于标签进行索引和选择数据的方法。它的基本语法是df.loc[row_label, column_label]，其中 df 是一个 DataFrame 对象。

loc 方法的主要用途有以下几个：

• 选择行和列：可以使用 loc 方法选择指定的行和列。例如，df.loc[3, 'A'] 选择第 3 行、名为 'A' 的列的值。

• 切片：可以使用 loc 方法进行切片操作，选择指定范围内的行和列。例如，df.loc[1:3, 'A':'C'] 选择第 1 到 3 行和名为 'A' 到 'C' 的列。

• 布尔索引：可以使用布尔条件进行索引，选择满足条件的行和列。例如，df.loc[df['A'] > 0, ['A', 'B']] 选择满足 'A' 列大于 0 的行，并选择名为 'A' 和 'B' 的列。

• 设置值：可以使用 loc 方法设置指定位置的值。例如，df.loc[2, 'A'] = 10 将第 2 行、名为 'A' 的列的值设置为 10。

需要注意的是，loc 方法使用的是标签索引，即指定的行和列的标签必须存在于 DataFrame 中。另外，loc 方法也支持多个标签的列表或数组，以选择多个行或列。

 

In [38]:

chipo.loc[chipo.item_name.isin(['Chicken Bowl','Steak Burrito']), ['item_name','item_price']]

 

Out[38]:

 

	item_name	item_price
4	Chicken Bowl	16.98
5	Chicken Bowl	10.98
7	Steak Burrito	11.75
9	Steak Burrito	9.25
13	Chicken Bowl	11.25
...	...	...
4604	Chicken Bowl	8.75
4607	Steak Burrito	11.75
4610	Steak Burrito	11.75
4617	Steak Burrito	11.75
4618	Steak Burrito	11.75

1094 rows × 2 columns

 

In [41]:

chipo

 

Out[41]:

 

	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
...	...	...	...	...	...
4617	1833	1	Steak Burrito	[Fresh Tomato Salsa, [Rice, Black Beans, Sour ...	11.75
4618	1833	1	Steak Burrito	[Fresh Tomato Salsa, [Rice, Sour Cream, Cheese...	11.75
4619	1834	1	Chicken Salad Bowl	[Fresh Tomato Salsa, [Fajita Vegetables, Pinto...	11.25
4620	1834	1	Chicken Salad Bowl	[Fresh Tomato Salsa, [Fajita Vegetables, Lettu...	8.75
4621	1834	1	Chicken Salad Bowl	[Fresh Tomato Salsa, [Fajita Vegetables, Pinto...	8.75

4622 rows × 5 columns

 

In [43]:

chipo.loc[chipo.item_name == 'Chips and Fresh Tomato Salsa',]

 

Out[43]:

 

	order_id	quantity	item_name	choice_description	item_price
0	1	1	Chips and Fresh Tomato Salsa	NaN	2.39
25	13	1	Chips and Fresh Tomato Salsa	NaN	2.39
55	25	1	Chips and Fresh Tomato Salsa	NaN	2.39
89	39	1	Chips and Fresh Tomato Salsa	NaN	2.95
183	82	1	Chips and Fresh Tomato Salsa	NaN	2.95
...	...	...	...	...	...
4231	1689	1	Chips and Fresh Tomato Salsa	NaN	2.95
4318	1722	1	Chips and Fresh Tomato Salsa	NaN	2.95
4324	1725	1	Chips and Fresh Tomato Salsa	NaN	2.95
4425	1764	1	Chips and Fresh Tomato Salsa	NaN	2.95
4503	1790	1	Chips and Fresh Tomato Salsa	NaN	2.95

110 rows × 5 columns

 

In [ ]: