1. 基本数据结构

1.1 Series

Series是个定长的字典序列。
存储时相当于两个ndarray。

1.1.1 初始化的3种方式

  1. Parameters
  2. ----------
  3. data : array-like, Iterable, dict, or scalar value
  4.     Contains data stored in Series.
  6.     .. versionchanged :: 0.23.0
  7.        If data is a dict, argument order is maintained for Python 3.6
  8.        and later.
  10. index : array-like or Index (1d)
  11.     Values must be hashable and have the same length as `data`.
  12.     Non-unique index values are allowed. Will default to
  13.     RangeIndex (0, 1, 2, ..., n) if not provided. If both a dict and index
  14.     sequence are used, the index will override the keys found in the
  15.     dict.
  16. dtype : str, numpy.dtype, or ExtensionDtype, optional
  17.     dtype for the output Series. If not specified, this will be
  18.     inferred from `data`.
  19.     See the :ref:`user guide <basics.dtypes>` for more usages.
  20. copy : bool, default False
  21.     Copy input data.
  1. from pandas import Series
  2. import numpy as np
  4. # 方式1：以列表进行初始化，列表值为列值
  5. li = [1,2,3,4,5]
  6. x1 = Series(li)   # index 默认从0起始
  7. # ---OUTPUT---
  8. 0   1
  9. 1   2
  10. 2   3
  11. 3   4
  12. 4   5
  13. dtype: int64
  15. # 方式2：以 numpy.ndarray 进行初始化
  16. n = np.random.random(5)
  17. x1 = Series(n, index=['a', 'b', 'c', 'd'], dtype=str)
  18. # ---OUTPUT---
  19. a    0.377831
  20. b    0.629109
  21. c    0.841532
  22. d   -0.429905
  23. dtype: float64
  25. # 方式3：以字典方式创建Series
  26. d = {'a':5, 'b':6, 'c':7, 'd':8}
  27. x2 = Series(d)
  28. # ---OUTPUT---
  29. a    5
  30. b    6
  31. c    7
  32. d    8
  33. dtype: int64

1.1.2 修改Index

  1. # 修改index
  2. s1 = pd.Series([1,2,3])
  3. # ---OUTPUT---
  4. 0   1
  5. 1   2
  6. 2   3
  7. dtype: int64
  9. s1.index = ['a', 'b', 'c']
  10. # ---OUTPUT---
  11. a   1
  12. b   2
  13. c   3
  14. dtype: int64

1.1.3 纵向拼接

  1. s1.append(s2)

1.1.4 按指定索引删除元素

  1. si.drop('e')  # 删除index为'e'的元素信息

1.1.5 按索引值索引元素

  1. # 修改索引值为'A'的值为6
  2. si['A'] = 6

1.1.6 Series的切片操作

  1. si[:3]  # si的前三个索引元素的切片，类型仍然为Series

1.1.7 Series的运算符

  1. # S3
  2. a    1
  3. b    2
  4. c    3
  5. d    4
  6. e    5
  7. dtype: int64
  9. # S4
  10. a    1
  11. b    2
  12. c    3
  13. d    4
  14. A    6
  15. B    1
  16. C    2
  17. D    3
  18. E    4
  19. dtype: int64
  • 加法：加法/减法/乘法/除法运算是按照索引计算，如果索引不同则填充为 NaN（空值）.
  1. S4.add(S3)
  2. # --OUTPUT---
  3. A    NaN
  4. B    NaN
  5. C    NaN
  6. D    NaN
  7. E    NaN
  8. a    2.0
  9. b    4.0
  10. c    6.0
  11. d    8.0
  12. e    NaN
  13. dtype: float64
  1. S4.sub(S3)
  2. # --OUTPUT---
  3. A    NaN
  4. B    NaN
  5. C    NaN
  6. D    NaN
  7. E    NaN
  8. a    0.0
  9. b    0.0
  10. c    0.0
  11. d    0.0
  12. e    NaN
  13. dtype: float64

1.1.8 ufunc

  1. # 中位数
  2. si.median()
  4. # 求和
  5. si.sum()
  7. # 最大值
  8. si.max()
  10. # 最小值
  11. si.min()

1.2 DataFrame — 二维表结构

DataFrame数据结构类似于数据库表，包含了行索引和列索引，可以将其视为由相同索引的Series组成的字典类型。

1.2.1 初始化的2种方式

  1. Parameters
  2. ----------
  3. data : ndarray (structured or homogeneous), Iterable, dict, or DataFrame
  4.     Dict can contain Series, arrays, constants, or list-like objects
  6.     .. versionchanged :: 0.23.0
  7.        If data is a dict, argument order is maintained for Python 3.6
  8.        and later.
  10. index : Index or array-like
  11.     Index to use for resulting frame. Will default to RangeIndex if
  12.     no indexing information part of input data and no index provided
  13. columns : Index or array-like
  14.     Column labels to use for resulting frame. Will default to
  15.     RangeIndex (0, 1, 2, ..., n) if no column labels are provided
  16. dtype : dtype, default None
  17.     Data type to force. Only a single dtype is allowed. If None, infer
  18. copy : boolean, default False
  19.     Copy data from inputs. Only affects DataFrame / 2d ndarray input
  1. >>> d = {'col1': [1, 2], 'col2': [3, 4]}
  3. # 方式1：由字典创造DataFrame, 字典的每个key-value对，为一列数据, 列头值已定
  4. >>> df = pd.DataFrame(data=d)
  5. >>> df
  6.    col1  col2
  7. 0     1     3
  8. 1     2     4
  10. # 由numpy.ndarray创造DataFrame, ndarray仅为值, columns实为列头值, index为索引值
  11. df2 = pd.DataFrame(data=np.array([[1, 2, 3], [4, 5, 6], [7, 8, 9]]),
  12.                    columns=['a', 'b', 'c'], index=['1st', '2nd', '3rd'])
  13. # ---OUTPUT---
  14.      a  b  c
  15. 1st  1  2  3
  16. 2nd  4  5  6
  17. 3rd  7  8  9

1.2.2 DataFrame的类型

  1. df.dtypes
  2. # ---OUTPUT---
  3. animal       object
  4. age         float64
  5. visits        int64
  6. priority     object
  7. dtype: object

1.2.3 DataFrame的基本操作

  1. df
  2. # ---OUTPUT---
  4.     animal    age        visits    priority
  5. a    cat      2.5        1        yes
  6. b    cat        3.0        3        yes
  7. c    snake    0.5        2        no
  8. d    dog        NaN        3        yes
  9. e    dog        5.0        2        no
  10. f    cat        2.0        3        no
  11. g    snake    4.5        1        no
  12. h    cat        NaN        1        yes
  13. i    dog        7.0        2        no
  14. j    dog        3.0        1        no

1.2.3.1 预览头N（默认5）行数据

  1. df.head(N)

1.2.3.2 预览尾N（默认5）行数据

  1. df.tail(N)

1.2.3.3 查看DataFrame的索引

  1. df.index
  2. # ---OUTPUT---
  3. Index(['a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j'], dtype='object')

1.2.3.4 查看DataFrame的列名

  1. df.columns
  2. # ---OUTPUT---
  3. Index(['animal', 'age', 'visits', 'priority'], dtype='object')

1.2.3.5 查看DataFrame的数值

  1. df.values
  2. # ---OUTPUT---
  3. array([['cat', 2.5, 1, 'yes'],
  4.        ['cat', 3.0, 3, 'yes'],
  5.        ['snake', 0.5, 2, 'no'],
  6.        ['dog', nan, 3, 'yes'],
  7.        ['dog', 5.0, 2, 'no'],
  8.        ['cat', 2.0, 3, 'no'],
  9.        ['snake', 4.5, 1, 'no'],
  10.        ['cat', nan, 1, 'yes'],
  11.        ['dog', 7.0, 2, 'no'],
  12.        ['dog', 3.0, 1, 'no']], dtype=object)

1.2.3.6 查看DataFrame的统计数据

  1. df.describe()
  2. # ---OUTPUT---
  4.         age            visits
  5. count    8.000000    10.000000
  6. mean    3.437500    1.900000
  7. std        2.007797    0.875595
  8. min        0.500000    1.000000
  9. 25%        2.375000    1.000000
  10. 50%        3.000000    2.000000
  11. 75%        4.625000    2.750000
  12. max        7.000000    3.000000

1.2.3.7 DataFrame的转置操作（横列互换）

  1. df.T  # 类型仍为 pandas.core.frame.DataFrame

1.2.3.8 对DataFrame进行排序

  1. df.sort_values(by='age')

sort_values(by, axis=0, ascending=True, inplace=False, kind=’quicksort’, na_position=’last’) method of pandas.core.frame.DataFrame instance.
Parameters:
———————
by : str or list of str
Name or list of names to sort by.
- if axis is 0 or 'index' then by may contain index
levels and/or column labels
- if axis is 1 or 'columns' then by may contain column
levels and/or index labels
axis : {0 or ‘index’, 1 or ‘columns’}, default 0
Axis to be sorted
ascending : bool or list of bool, default True
Sort ascending vs. descending. Specify list for multiple sort
orders. If this is a list of bools, must match the length of
the by.
inplace : bool, default False
if True, perform operation in-place
kind : {‘quicksort’, ‘mergesort’, ‘heapsort’}, default ‘quicksort’
Choice of sorting algorithm. See also ndarray.np.sort for more
information. mergesort is the only stable algorithm. For
DataFrames, this option is only applied when sorting on a single
column or label.
na_position : {‘first’, ‘last’}, default ‘last’
first puts NaNs at the beginning, last puts NaNs at the end

TIPS:
若参数axis为0，则是依据纵列值进行排序，by取值应为column的label；
若参数axis为1，则是依据横排值进行排序，by取值应为index的label。

1.2.3.9 DataFrame的切片 — N排

  1. df[1:3]

1.2.3.10 DataFrame 获取rows of columns — DataFrame.loc[input, col]

Access a group of rows and columns by label(s) or a boolean array.

input：

  • single label : ‘a’ …
  • A list or array of labels : [‘a’, ‘b’, ‘c’]…
  • A slice object with labels : ‘a’ : ‘f’ (including)
  • A boolean array of the same length as the axis being sliced : [True, True, False]
  • A callable function with one argument (the calling Series, DataFrame or Panel) and that returns valid output for indexing (one of the above)

col : 可选

  • single col name — Series type
  • a list of col names — DataFrame
  1. data = {'animal': ['cat', 'cat', 'snake', 'dog', 'dog', 'cat', 'snake', 'cat', 'dog', 'dog'],
  2.         'age': [2.5, 3, 0.5, np.nan, 5, 2, 4.5, np.nan, 7, 3],
  3.         'visits': [1, 3, 2, 3, 2, 3, 1, 1, 2, 1],
  4.         'priority': ['yes', 'yes', 'no', 'yes', 'no', 'no', 'no', 'yes', 'no', 'no']}
  6. labels = ['a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j']
  7. df = pd.DataFrame(data, index=labels)
  8. # ---OUTPUT---
  10.     animal    age    visits    priority
  11. a    cat        2.5        1        yes
  12. b    cat        3.0        3        yes
  13. c    snake    0.5        2        no
  14. d    dog        NaN        3        yes
  15. e    dog        5.0        2        no
  16. f    cat        2.0        3        no
  17. g    snake    4.5        1        no
  18. h    cat        NaN        1        yes
  19. i    dog        7.0        2        no
  20. j    dog        3.0        1        no
  22. # input : single label
  23. df.loc['a']
  24. # ---OUTPUT---
  25. animal      cat
  26. age         2.5
  27. visits        1
  28. priority    yes
  29. Name: a, dtype: object
  31. # input : a list of labels
  32. df.loc[['a', 'b']]
  33. # ---OUTPUT---
  34.     animal    age    visits    priority
  35. a    cat        2.5        1        yes
  36. b    cat        3.0        3        yes
  38. # input : a slice object with labels
  39. df.loc['b' : 'c']
  40. # ---OUTPUT---
  41.     animal    age    visits    priority
  42. b    cat        3.0        3        yes
  43. c    snake    0.5        2        no
  45. # A boolean array
  46. df.loc[df['age'] > 4]
  47. # ---OUTPUT---
  48.     animal    age    visits    priority
  49. e    dog        5.0        2        no
  50. g    snake    4.5        1        no
  51. i    dog        7.0        2        no
  53. # input : A callable function
  54. def func(DataFrame):
  55.     return 'a'
  56. df.loc[func]
  57. # ---OUTPUT---
  58. animal      cat
  59. age         2.5
  60. visits        1
  61. priority    yes
  62. Name: a, dtype: object
  64. # col : single col name
  65. df.loc['a':'c', 'visits']
  66. # ---OUTPUT---
  67. a    1
  68. b    3
  69. c    2
  70. Name: visits, dtype: int64
  72. # col : a list of col names
  73. df.loc['a':'c', ['visits', 'age']]
  74.     visits    age
  75. a        1    2.5
  76. b        3    3.0
  77. c        2    0.5

1.2.3.11 DataFrame 获取rows of columns — DataFrame.iloc[input, col]

Purely integer-location based indexing for selection by position from 0 to length-1 of the axis.

input :

  • An integer : 3
  • A list or array of integers : [2,4,7]
  • A slice object with ints : 2 : 5 (excluding)
  • a boolean array
  • A callable function with one argument (the calling Series, DataFrame or Panel) and that returns valid output for indexing (one of the above)

col :

  • An integer
  • a list of array of integers
  • a slice object with ints
  1. # 沿用上例的数据
  3. # input : An integer
  4. df.iloc[2]
  5. # ---OUTPUT---
  6. animal      snake
  7. age           0.5
  8. visits          2
  9. priority       no
  10. Name: c, dtype: object
  12. # input : A list of array of integers
  13. df.iloc[[2, 4]]
  14. # ---OUTPUT---
  15.     animal    age    visits    priority
  16. c    snake    0.5        2        no
  17. e    dog        5.0        2        no
  19. # input : A slice object with ints
  20. df.iloc[2:4]
  21. # ---OUTPUT---
  22.     animal    age    visits    priority
  23. c    snake    0.5        2        no
  24. d    dog        NaN        3        yes
  26. # intput : a boolean array -- 不能使用 df['age']>6这种方式
  27. df.iloc[[False, True]]
  28. # ---OUTPUT---
  29.     animal    age    visits    priority
  30. b    cat        3.0        3        yes
  32. # input : func
  33. df.iloc[lambda df : [4, 5]]
  34. # ---OUTPUT---
  35.     animal    age    visits    priority
  36. e    dog        5.0        2        no
  37. f    cat        2.0        3        no
  39. # loc : An integer
  40. df.iloc[0:3, 1]
  41. # ---OUTPUT---
  42. a    2.5
  43. b    3.0
  44. c    0.5
  45. Name: age, dtype: float64
  47.         a    2.5
  48. b    3.0
  49. c    0.5
  50. Name: age, dtype: float64
  52. # loc : A list of integers
  53. df.iloc[0:3, [1,2]]
  54. # ---OUTPUT---
  55.     age    visits
  56. a    2.5        1
  57. b    3.0        3
  58. c    0.5        2
  60. # loc : a slice object with ints
  61. df.iloc[0:3, 1:3]
  62. # ---OUTPUT---
  63.     age    visits
  64. a    2.5        1
  65. b    3.0        3
  66. c    0.5        2

1.2.3.12 生成DataFrame副本

  1. df_bak = df.copy()

1.2.3.13 判别DataFrame元素为空

  1. df.isnull()
  2. 返回一个与df相同size的DataFrame，若元素之为None或numpy.NaN，则返回True，否则返回Flase

1.2.3.14 添加列数据

  1. col_add = pd.Series([0,1,2,3,4,5,6,7,8,9], index=df.index)
  2. df['No.'] = col_add

1.2.3.15 修改value

修改DataFrame的value的方法：

  1. 通过绝对坐标进行修改 df.iat(x,y)
  2. 通过索引 index,col 进行修改 df.loc(index, col) 
  1. # a. 通过绝对坐标进行修改
  2. df.iat[0, 1] = 3
  4. # b. 通过索引 index,col 进行修改
  5. df['f', 'age'] = 15

DataFrame.iat : Access a single value for a row/column pair by integer position.
DataFrame.at : Access a single value for a row/column label pair

DataFrame.iloc : Access a group of rows and columns by integer position(s)
DataFrame.loc : Access a group of rows and columns by label(s)

1.2.3.16 DataFrame合并

  1. merge(left, right, how='inner', on=None, left_on=None, right_on=None, left_index=False, 
  2.       right_index=False, sort=False, suffixes=('_x', '_y'), copy=True, 
  3.       indicator=False, validate=None)
  4.     Merge DataFrame or named Series objects with a database-style join.
  6.     The join is done on columns or indexes. If joining columns on
  7.     columns, the DataFrame indexes *will be ignored*. Otherwise if joining indexes
  8.     on indexes or indexes on a column or columns, the index will be passed on.
  10.     Parameters
  11.     ----------
  12.     left : DataFrame
  13.     right : DataFrame or named Series
  14.         Object to merge with.
  16.     how : {'left', 'right', 'outer', 'inner'}, default 'inner'
  17.         Type of merge to be performed.
  19.         * left: use only keys from left frame, similar to a SQL left outer join;
  20.           preserve key order.
  21.         * right: use only keys from right frame, similar to a SQL right outer join;
  22.           preserve key order.
  23.         * outer: use union of keys from both frames, similar to a SQL full outer
  24.           join; sort keys lexicographically.
  25.         * inner: use intersection of keys from both frames, similar to a SQL inner
  26.           join; preserve the order of the left keys.
  27.     on : label or list
  28.         Column or index level names to join on. These must be found in both
  29.         DataFrames. If `on` is None and not merging on indexes then this defaults
  30.         to the intersection of the columns in both DataFrames.
  31.     left_on : label or list, or array-like
  32.         Column or index level names to join on in the left DataFrame. Can also
  33.         be an array or list of arrays of the length of the left DataFrame.
  34.         These arrays are treated as if they are columns.
  35.     right_on : label or list, or array-like
  36.         Column or index level names to join on in the right DataFrame. Can also
  37.         be an array or list of arrays of the length of the right DataFrame.
  38.         These arrays are treated as if they are columns.
  39.     left_index : bool, default False
  40.         Use the index from the left DataFrame as the join key(s). If it is a
  41.         MultiIndex, the number of keys in the other DataFrame (either the index
  42.         or a number of columns) must match the number of levels.
  43.     right_index : bool, default False
  44.         Use the index from the right DataFrame as the join key. Same caveats as
  45.         left_index.
  46.     sort : bool, default False
  47.         Sort the join keys lexicographically in the result DataFrame. If False,
  48.         the order of the join keys depends on the join type (how keyword).
  49.     suffixes : tuple of (str, str), default ('_x', '_y')
  50.         Suffix to apply to overlapping column names in the left and right
  51.         side, respectively. To raise an exception on overlapping columns use
  52.         (False, False).
  53.     copy : bool, default True
  54.         If False, avoid copy if possible.
  55.     indicator : bool or str, default False
  56.         If True, adds a column to output DataFrame called "_merge" with
  57.         information on the source of each row.
  58.         If string, column with information on source of each row will be added to
  59.         output DataFrame, and column will be named value of string.
  60.         Information column is Categorical-type and takes on a value of "left_only"
  61.         for observations whose merge key only appears in 'left' DataFrame,
  62.         "right_only" for observations whose merge key only appears in 'right'
  63.         DataFrame, and "both" if the observation's merge key is found in both.
  65.     validate : str, optional
  66.         If specified, checks if merge is of specified type.
  68.         * "one_to_one" or "1:1": check if merge keys are unique in both
  69.           left and right datasets.
  70.         * "one_to_many" or "1:m": check if merge keys are unique in left
  71.           dataset.
  72.         * "many_to_one" or "m:1": check if merge keys are unique in right
  73.           dataset.
  74.         * "many_to_many" or "m:m": allowed, but does not result in checks.
  76.     Returns
  77.     -------
  78.     DataFrame
  79.         A DataFrame of the two merged objects.
  81.     See Also
  82.     --------
  83.     merge_ordered : Merge with optional filling/interpolation.
  84.     merge_asof : Merge on nearest keys.
  85.     DataFrame.join : Similar method using indices.
  1. left = pd.DataFrame({'key': ['foo1', 'foo2'], 'one': [1, 2]})
  2. right = pd.DataFrame({'key': ['foo2', 'foo3'], 'two': [4, 5]})
  4. print(left)
  5. print(right)
  7. # ---OUTPUT---
  8.     key  one
  9. 0  foo1    1
  10. 1  foo2    2
  11.     key  two
  12. 0  foo2    4
  13. 1  foo3    5
  15. pd.merge(left, right, on='key')
  16.     key        one        two
  17. 0    foo2    2        4
  19. pd.merge(left, right, on='key', how='outer')
  20.     key        one        two
  21. 0    foo1    1.0        NaN
  22. 1    foo2    2.0        4.0
  23. 2    foo3    NaN        5.0
  25. pd.merge(left, right, on='key', how='left')
  26.     key        one        two
  27. 0    foo1    1        NaN
  28. 1    foo2    2        4.0
  30. pd.merge(left, right, on='key', how='right')
  31.     key        one        two
  32. 0    foo2    2.0        4
  33. 1    foo3    NaN        5

1.2.3.17 分组groupby

  • 单一维度，列有重复值，按列分类 ```python

    数据

    df = pd.DataFrame({‘Animal’ : [‘Falcon’, ‘Falcon’,
    1.                           'Parrot', 'Parrot'],
    2.              'Max Speed' : [380., 370., 24., 26.]})

    —-OUTPUT—-

    Animal Max Speed 0 Falcon 380.0 1 Falcon 370.0 2 Parrot 24.0 3 Parrot 26.0

df.groupby(‘Animal’).size()

—-OUTPUT—-

Animal Falcon 2 Parrot 2 dtype: int64

  2. - 多维度Index的分组
  3. ```python
  4. # 数据
  5. arrays = [['Falcon', 'Falcon', 'Parrot', 'Parrot'],
  6.           ['Capitve', 'Wild', 'Capitve', 'Wild']]
  7. index = pd.MultiIndex.from_arrays(arrays, names=('Animal', 'Type'))
  8. df = pd.DataFrame({'Max Speed' : [390., 350., 30., 20.]}, index=index)
  9. # ---OUTPUT---
  10.                 Max Speed
  11. Animal Type
  12. Falcon Capitve      390.0
  13.        Wild         350.0 
  14. Parrot Capitve       30.0
  15.        Wild          20.0
  17. df.groupby(level=0).size()
  18. # ---OUTPUT---
  19. Animal
  20. Falcon    2
  21. Parrot    2
  22. dtype: int64
  24. df.groupby(level=1).size()
  25. # ---OUTPUT---
  26. Type
  27. Capitve    2
  28. Wild       2
  29. dtype: int64

1.2.4 DataFrame对缺失值的操作

  • 对缺失值填充

fillna(value=None, method=None, axis=None, inplace=False, limit=None, downcast=None, **kwargs) method of pandas.core.series.Series instance

Parameters:
——————
inplace : boolean, default False

  1. # 将age列NaN填充为3
  2. df['age'].fillna(3)
  4. # 将整个DataFrame的NaN填充为0
  5. df.fillna(0)
  7. # inplace=True 执行原地替换
  9. # 各列替换为不同的值
  10. df.fillna({'A':0, 'B':1, 'C':2, 'D':3})
  • 删除存在缺失的行

dropna(axis=0, how=’any’, thresh=None, subset=None, inplace=False) method of pandas.core.frame.DataFrame instance

  1. Remove missing values.<br />Parameters:<br />------------<br />**axis** : {0 or 'index', 1 or 'columns'}, default 0<br />        Determine if rows or columns which contain missing values are<br />        removed.<br />    <br />        * 0, or 'index' : Drop rows which contain missing values.<br />        * 1, or 'columns' : Drop columns which contain missing value.<br />.. deprecated:: 0.23.0: Pass tuple or list to drop on multiple<br />        axes.

how : {‘any’, ‘all’}, default ‘any’
Determine if row or column is removed from DataFrame, when we have
at least one NA or all NA.

‘any’ : If any NA values are present, drop that row or column.
 ‘all’ : If all values are NA, drop that row or column.
subset : array-like, optional
Labels along other axis to consider, e.g. if you are dropping rows
these would be a list of columns to include.

inplace : bool, default False
If True, do operation inplace and return None.

  1. # 删除任意 NaN 所在的行
  2. df.dropna(how='any')
  4. # 删除任意 NaN 所在的列
  5. df.dropna(axis=1)
  7. # 删除整行皆为 NaN 的行
  8. df.dropna(how='all')
  10. # 删除 NAN 所在行，范围锁定在 name 和 bore 列
  11. df.dropna(axis=0, subset=['name', 'born'])
  13. # 删除 NAN 所在列，范围锁定在 第0和第1行
  14. df.dropna(axis=1, subset=[0, 1])
  16. # 原地删除
  17. df.dropna(inplace=True)
  1. DataFrame.isna: Indicate missing values.<br />    DataFrame.notna : Indicate existing (non-missing) values.<br />    DataFrame.fillna : Replace missing values.<br />    Series.dropna : Drop missing values.<br />    Index.dropna : Drop missing indices.

1.2.5 DataFrame文件操作

Pandas可以将DataFrame写入Excel/CSV文件中，同样也可以从Excel/CSV文件中读取。

  1. # 写入csv文件
  2. df.to_csv('animal.csv')
  4. # 读取csv文件
  5. df.read_csv('animal.csv')
  7. # 写入excel
  8. writer = pd.ExcelWriter('animal.xlsx')
  9. df3.to_excel(writer, sheet_name='Sheet1')
  10. df3.to_excel(writer, sheet_name='Sheet2')
  11. df3.to_excel(writer, sheet_name='Sheet3')
  12. writer.save()
  14. # 读取excel
  15. pd.read_excel('animal.xlsx', 2, index_col=None, na_values=['NA'])

具体的参数值函数，请参考doc。

2. 实用进阶

2.1 pandas.core.indexes.datetimes.DatetimeIndex

建立一个以 2018 年每一天为索引，值为随机数的 Series

  1. dti = pd.date_range(start = '2018-01-01', end='2018-12-31', freq='D')
  2. s = pd.Series(np.random.rand(len(dti)), index=dti)

统计每个周三的值的和

  1. s[s.index.weekday == 2].sum()

统计每个月的平均值

  1. s.resample('M').mean()

统计每分钟的平均值

  1. s = pd.date_range(start='2018/1/1', end='2018/1/2', closed='left', freq='S')
  2. ts = pd.Series(np.random.randint(0, 500, len(s)), index=s)
  3. ts.resample('Min').mean()

resample 适合小粒度向大粒度转换， 否则大粒度向小粒度的转换会产生NaN

转换 tz

  1. s = pd.date_range('today', periods=1, freq='D')  # 获取当前时间
  2. ts = pd.Series(np.random.randn(len(s)), index=s)  # 随机数值
  3. ts.tz_localize('UTC')  # 转换为 UTC 时间
  4. 2019-05-28 09:17:29.204571+00:00    1.583188
  5. Freq: D, dtype: float64
  7. ts.tz_localize('Asia/Shanghai')
  8. # ---OUTPUT---
  9. 2019-05-28 17:08:55.604510+08:00    0.245186
  10. Freq: D, dtype: float64

2.2 Series 多重索引

  1. # 创建多重索引Series
  3. letters = ['A', 'B', 'C']
  4. numbers = list(range(3))
  6. mi = pd.MultiIndex.from_product([letters, numbers])  # 设置多重索引
  7. s = pd.Series(np.random.rand(len(letters) * len(numbers)), 
  8.               index=mi,
  9.               names=['ca', 'idx'])  # 随机数
  11. ca  idx
  12. A    0    0.430402
  13.      1    0.990126
  14.      2    0.084091
  15. B    0    0.878942
  16.      1    0.209060
  17.      2    0.717289
  18. C    0    0.143833
  19.      1    0.780968
  20.      2    0.297559
  21. dtype: float64

索引查询

  1. # 一级索引全部，二级索引的0和2
  2. s.loc[:, [0,2]]
  3. # ---OUTPUT---
  4. A  0    0.470544
  5.    2    0.907349
  6. B  0    0.153506
  7.    2    0.875122
  8. C  0    0.680005
  9.    2    0.169241
  10. dtype: float64
  12. # 一级索引的'A', 二级索引的0和2
  13. s.loc['A', [0,2]]
  14. # ---OUTPUT---
  15. A  0    0.470544
  16.    2    0.907349
  17. dtype: float64
  19. # 一级索引的'A'和'C', 二级索引的0和2
  20. s.loc[['A', 'C'], [0, 2]]
  21. # ---OUTPUT---
  22. A  0    0.470544
  23.    2    0.907349
  24. C  0    0.680005
  25.    2    0.169241
  26. dtype: float64

多重索引的切片

  1. s.loc[pd.IndexSlice[:'B', 1:]]
  2. # ---OUTPUT---
  3. ca  idx
  4. A   1      0.945881
  5.     2      0.119563
  6. B   1      0.979727
  7.     2      0.531048
  8. dtype: float64

2.3 DataFrame多重索引

  1. # 创建多重索引 DataFrame
  2. frame = pd.DataFrame(np.arange(18).reshape(6, 3),
  3.                      index=[['A','A', 'A', 'B', 'B', 'B'], list('123123')],
  4.                      columns=['hello', 'Leo', 'Pansy'])
  5. # ---OUTPUT---
  6.         hello    Leo        Pansy
  7. A    1    0        1        2
  8.     2    3        4        5
  9.     3    6        7        8
  10. B    1    9        10        11
  11.     2    12        13        14
  12.     3    15        16        17

多重索引分组求和

  1. frame.index.names = ['first', 'second']
  2. # ---OUTPUT---
  3.                 hello    Leo        Pansy
  4. first   second
  5. A        1        0        1        2
  6.         2        3        4        5
  7.         3        6        7        8
  8. B        1        9        10        11
  9.         2        12        13        14
  10.         3        15        16        17
  12. frame.groupby('first').sum()
  13. # ---OUTPUT---
  14.         hello    Leo        Pansy
  15. first            
  16. A        9        12        15
  17. B        36        39        42

2.4 DataFrame行列互换

  1. frame.stack()           # 一行三列变三行一列
  2. # ---OUTPUT---
  3. first  second       
  4. A      1       hello     0
  5.                Leo       1
  6.                Pansy     2
  7.        2       hello     3
  8.                Leo       4
  9.                Pansy     5
  10.        3       hello     6
  11.                Leo       7
  12.                Pansy     8
  13. B      1       hello     9
  14.                Leo      10
  15.                Pansy    11
  16.        2       hello    12
  17.                Leo      13
  18.                Pansy    14
  19.        3       hello    15
  20.                Leo      16
  21.                Pansy    17
  22. dtype: int64

2.5 DataFrame条件查找

  1. data = {'animal': ['cat', 'cat', 'snake', 'dog', 'dog', 'cat', 'snake', 'cat', 'dog', 'dog'],
  2.         'age': [2.5, 3, 0.5, np.nan, 5, 2, 4.5, np.nan, 7, 3],
  3.         'visits': [1, 3, 2, 3, 2, 3, 1, 1, 2, 1],
  4.         'priority': ['yes', 'yes', 'no', 'yes', 'no', 'no', 'no', 'yes', 'no', 'no']}
  6. labels = ['a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j']
  7. df = pd.DataFrame(data, index=labels)
  9. #---OUTPUT---
  10.     animal    age    visits    priority
  11. a    cat        2.5        1        yes
  12. b    cat        3.0        3        yes
  13. c    snake    0.5        2        no
  14. d    dog        NaN        3        yes
  15. e    dog        5.0        2        no
  16. f    cat        2.0        3        no
  17. g    snake    4.5        1        no
  18. h    cat        NaN        1        yes
  19. i    dog        7.0        2        no
  20. j    dog        3.0        1        no
  22. df['age']
  23. # ---OUTPUT
  24. a    2.5
  25. b    3.0
  26. c    0.5
  27. d    NaN
  28. e    5.0
  29. f    2.0
  30. g    4.5
  31. h    NaN
  32. i    7.0
  33. j    3.0
  34. Name: age, dtype: float64
  36. df['age'] > 3
  37. # ---OUTPUT---
  38. a    False
  39. b    False
  40. c    False
  41. d    False
  42. e     True
  43. f    False
  44. g     True
  45. h    False
  46. i     True
  47. j    False
  48. Name: age, dtype: bool
  50. df[df['age'] > 3]
  51. # ---OUTPUT---
  52.     animal    age    visits    priority
  53. e    dog        5.0        2        no
  54. g    snake    4.5        1        no
  55. i    dog        7.0        2        no