长文：一文掌握Pandas

Pandas是Python数据科学生态中重要的基础成员，功能强大，用法灵活，简单记录之。更佳阅读体验可移步Pandas核心概述。

数据结构

两种核心数据类型，Series和DataFrame。

• Series: 1D labeled homogeneously-typed array
• DataFrame: 2D labeled, size-mutable tabular structure with potentially heterogeneously-typed column

为何要用两种数据结构？

The best way to think about the pandas data structures is as flexible containers for lower dimensional data. For example, DataFrame is a container for Series, and Series is a container for scalars. We would like to be able to insert and remove objects from these containers in a dictionary-like fashion. Intro to Data Structures — pandas.

Series

Series is a one-dimensional labeled array capable of holding any data type (integers, strings, floating point numbers, Python objects, etc.).

可以看做有标签（默认是整数序列RangeIndex；可以重复）的一维数组（同类型）。是scalars的集合，同时也是DataFrame的元素。

>>> s = pd.Series(np.random.randn(3), index=['a', 'b', 'a'])
a   -0.127293
b   -0.439537
a    0.727805
dtype: float64

Series数据类型is ndarray-like and dict-like。由于是one-dimensional array，所以API可以很好地跟ndarray兼容；由于是labeled array，所以API可以很好地跟dict兼容，其label(index)可以看做dict中的key。

>>> s[0] # ndarray like
-0.1272931981576878

>>> np.negative(s) # vectorized operations
a    0.127293
b    0.439537
a   -0.727805
dtype: float64

>>> s.values # to ndarray
array([-0.1272932 , -0.43953716,  0.7278052 ])

>>> s['b'] # dict like
-0.4395371588351514

>>> s.to_dict() # to dict
{'a': 0.727805195734351, 'b': -0.4395371588351514}

DataFrame

DataFrame is a 2-dimensional labeled data structure with columns of potentially different types. You can think of it like a spreadsheet or SQL table, or a dict of Series objects. It is generally the most commonly used pandas object.

可以通过多种方式构建一个DataFrame。

• Dict of 1D ndarrays, lists, dicts, or Series
• 2-D numpy.ndarray
• Structured or record ndarray
• A Series
• Another DataFrame

# You can pass index (row labels) and columns (column labels) arguments.
pd.DataFrame(data=None, index=None, columns=None, dtype=None...)

简单的Demo

>>> d = {'one': [1., 2., 3., 4.], 'two': [4., 3., 2., 1.]}
>>> df = pd.DataFrame(d)
>>> df
   one  two
0  1.0  4.0
1  2.0  3.0
2  3.0  2.0
3  4.0  1.0
# The row and column labels can be accessed respectively by accessing the index and columns attributes
>>> df.index
RangeIndex(start=0, stop=4, step=1)
>>> df.columns
Index(['one', 'two'], dtype='object')

Index

Immutable ndarray implementing an ordered, sliceable set. The basic object storing axis labels for all pandas objects. An Index instance can only contain hashable objects.

Series和DataFrame都有对应的Index，Index本身是很有趣的数据结构。可以将其看做an immutable array or as an ordered set。其表现如下代码片段所示

>>> index = pd.Index([2, 3, 5, 7, 11])
>>> index
Int64Index([2, 3, 5, 7, 11], dtype='int64')
# operates like an array
>>> index[::2]
Int64Index([2, 5, 11], dtype='int64')
# like numpy ndarray, but immutable
>>> print(index.size, index.shape, index.dtype)
5 (5,) int64
# Designed to facilitate operations such as joins across datasets,  
# which depend on many aspects of set arithmetic. 
>>> indexA = pd.Index([1, 3, 5, 7, 9])
>>> indexB = pd.Index([2, 3, 5, 7, 11])
>>> indexA & indexB
Int64Index([3, 5, 7], dtype='int64')
>>> indexA.intersection(indexB)
Int64Index([3, 5, 7], dtype='int64')

Index有若干个子类，其中比较常用的有

• RangeIndex: Index implementing a monotonic integer range
• Int64Index
• MultiIndex: A multi-level, or hierarchical, Index
• DatetimeIndex

MultiIndex相对复杂，在GroupBy操作中比较常用。

The MultiIndex object is the hierarchical analogue of the standard Index object which typically stores the axis labels in pandas objects. You can think of MultiIndex as an array of tuples where each tuple is unique.

一个较有效的角度，是将MultiIndex看成一个多层组合key。

>>> arrays = [[0, 0, 1, 1], ['red', 'blue', 'red', 'blue']]
>>> mi = pd.MultiIndex.from_arrays(arrays, names=['gender', 'color'])
>>> mi
MultiIndex(levels=[[0, 1], ['blue', 'red']], # sequence of arrays. The unique labels for each level
           labels=[[0, 0, 1, 1], [1, 0, 1, 0]], #  Integers for each level designating which label at each location
           names=['gender', 'color'])

>>> s = pd.Series(np.random.randn(4), index=mi)
gender  color
0       red     -0.185615
        blue    -1.191781
1       red      1.054579
        blue    -0.841271
dtype: float64

>>> df = pd.DataFrame(np.random.randn(4, 2), index=mi, columns=["c1", "c2"]); df
                    c1        c2
gender color
0      red    0.587486 -0.145549
       blue   1.915447  1.066901
1      red    0.068751  1.363691
       blue   0.044886  0.096707
       
# The index can back **any axis** of a pandas object.
>>> df = pd.DataFrame(np.random.randn(3, 4), index=["A", "B", "C"], columns=mi); df
gender         0                   1
color        red      blue       red      blue
A       1.639192 -0.983447 -1.129612  0.373631
B      -0.463904  1.989585  0.667576  0.840351
C      -0.890905 -0.334301 -0.633911 -0.338430
>>> df.index
Index(['A', 'B', 'C'], dtype='object')
>>> df.columns # is also index
MultiIndex(levels=[[0, 1], ['blue', 'red']],
           labels=[[0, 0, 1, 1], [1, 0, 1, 0]],
           names=['gender', 'color'])

# indexing
>>> df = df.T
>>> df.loc[0]
              A         B         C
color
red    0.855162  1.642578 -1.918263
blue   0.492383 -0.770525  0.374322
>>> df.loc[(0, 'red')]
A    0.855162
B    1.642578
C   -1.918263
Name: (0, red), dtype: float64
>>> df.loc[(0, 'red'), 'A']
0.8551620714417688
>>> df.loc[([0, 1], ['red']), :]
                     A         B         C
gender color
0      red    0.855162  1.642578 -1.918263
1      red   -1.153564  0.328648 -0.916944

一个重点，就是当indexing的时候，tuple和list的作用是不同的。

It is important to note that tuples and lists are not treated identically in pandas when it comes to indexing. Whereas a tuple is interpreted as one multi-level key, a list is used to specify several keys. Or in other words, tuples go horizontally (traversing levels), lists go vertically (scanning levels).

对Series或DataFrame而言，有时候需要查找特定行，如果能用Index锁定，效率会比较高。

Like a dict, a DataFrame's index is backed by a hash table. Looking up rows based on index values is like looking up dict values based on a key. In contrast, the values in a column are like values in a list. Looking up rows based on index values is faster than looking up rows based on column values.

参考资料

• pandas.Index
• MultiIndex / Advanced Indexing
• Indexing

Indexing

最基本的索引操作。

Operation	Syntax	Result
Select column	df[col]	Series
Select columns	df[[col1, col2]]	DataFrame
Select row by label	df.loc[label]	Series
Select row by integer location	df.iloc[loc]	Series
Slice rows	df[5:10]	DataFrame
Select by boolean vec	df[bool_vec])	DataFrame

其中Boolean indexing、where和mask稍微复杂一点。

# boolean indexing,  boolean index | & ~ grouped by using parentheses
>>> s = pd.Series(range(-1, 3))
>>> s[s < 0]
0   -1
dtype: int64
>>> s[(s > 0) & (s < 2)]
2    1
dtype: int64

# isin. the isin() method of Series returns a boolean vector
>>> s[s.isin([1, 2])]
2    1
3    2
dtype: int64

# boolean vec返回subset，如果需要shape不变，可以用where
>>> s.where(s > 0)
0    NaN
1    NaN
2    1.0
3    2.0
dtype: float64
# You may wish to set values based on some boolean criteria. This can be done intuitively like so:
>>> s.where(s > 0, 0) # provide replacement, df[df < 0]类似，等同df.where(df < 0)
0    0
1    0
2    1
3    2
dtype: int64

# mask() is the inverse boolean operation of where.
>>> s.mask(s > 0)
0   -1.0
1    0.0
2    NaN
3    NaN
dtype: float64

参考资料

• Indexing and Selecting Data

Map and Apply

Pandas里几个概念比较容易混淆，比如map、apply、applymap等。

Summing up, apply works on a row / column basis of a DataFrame, applymap works element-wise on a DataFrame, and map works element-wise on a Series.

>>> df = pd.DataFrame(np.random.randn(4, 3), columns=list('abc'), index=['Utah', 'Ohio', 'Texas', 'Oregon']); df
               a         b         c
Utah    0.417494 -0.430255  0.320251
Ohio    0.828452 -0.823623  0.076611
Texas  -1.224572  1.584230  0.138388
Oregon -1.305397  3.315600  2.979548
# Another frequent operation is applying a function on 1D arrays to each column or row.
#  DataFrame’s apply method does exactly this:
>>> f = lambda x: x.max() - x.min()
>>> df.apply(f) # on columns
a    2.133849
b    4.139223
c    2.902937
dtype: float64
>>> df.apply(f, axis=1) # on rows
Utah      0.847749
Ohio      1.652075
Texas     2.808802
Oregon    4.620996
dtype: float64
>>> df.max()
a    0.828452
b    3.315600
c    2.979548
dtype: float64

# Element-wise Python functions can be used with applymap
>>> format = lambda x: '%.2f' % x
>>> df.applymap(format)
            a      b     c
Utah     0.42  -0.43  0.32
Ohio     0.83  -0.82  0.08
Texas   -1.22   1.58  0.14
Oregon  -1.31   3.32  2.98

# map with series
>>> df['a'].map(format)
Utah       0.42
Ohio       0.83
Texas     -1.22
Oregon    -1.31
Name: a, dtype: object

参考

• Difference between map, applymap and apply methods in Pandas

Group By

split-apply-combine范式，类似SQL中常见的Group By聚合操作。

• Splitting the data into groups based on some criteria.
• Applying a function to each group independently.
  • Aggregation: compute a summary statistic (or statistics) for each group
  • Transformation: perform some group-specific computations and return a like-indexed object
  • Filtration: discard some groups, according to a group-wise computation that evaluates True or False.
• Combining the results into a data structure.

Split这一步将数据分组。

Pandas objects can be split on any of their axes. The abstract definition of grouping is to provide a mapping of labels to group names.

# demo DataFrame
>>> arrays = [['bar', 'bar',  'foo', 'foo'], ['one', 'two', 'one', 'two']]
>>> index = pd.MultiIndex.from_arrays(arrays, names=['first', 'second'])
>>> df = pd.DataFrame({'A': [1, 1, 2, 2], 'B': np.arange(4)}, index=index); df
              A  B
first second
bar   one     1  0
      two     1  1
foo   one     2  2
      two     2  3
      
# split
# The groups attribute is a dict whose keys are the computed unique groups and corresponding values 
# being the axis labels belonging to each group.
>>> grouped = df.groupby(level=0)
>>> grouped.groups
{'bar': MultiIndex(levels=[['bar', 'foo'], ['one', 'two']],
            labels=[[0, 0], [0, 1]],
            names=['first', 'second']),
 'foo': MultiIndex(levels=[['bar', 'foo'], ['one', 'two']],
            labels=[[1, 1], [0, 1]],
            names=['first', 'second'])}    
# 遍历group
>>> for name, group in grouped:
...     print(name)
...     print(group)
bar
              A  B
first second
bar   one     1  0
      two     1  1
foo
              A  B
first second
foo   one     2  2
      two     2  3

Apply这一步，比如Aggregation、Transformation、Filtration等

# Agg
>>> grouped.aggregate(np.sum)
       A  B
first
bar    2  1
foo    4  5
>>> grouped.agg([np.sum, np.mean, np.std])
        A             B
      sum mean  std sum mean       std
first
bar     2    1  0.0   1  0.5  0.707107
foo     4    2  0.0   5  2.5  0.707107
>>> grouped.agg({'A': np.sum, 'B': np.max})
       A  B
first
bar    2  1
foo    4  3

其他几种操作

• Transformation
• Filtration
• Flexible Apply

参考

• Group By: split-apply-combine

Concat and Merge

Concat和Merge和SQL中操作比较类似，其API参数也比较清晰。

Concat操作。

>>> frames = [df1, df2, df3]
>>> result = pd.concat(frames)
>>> pd.concat(objs, 
...   axis=0, 
...   join='outer', 
...   join_axes=None, 
...   ignore_index=False,
...   keys=None,
...   levels=None, 
...   names=None, 
...   verify_integrity=False, 
...   copy=True)

Merge. SQL中Join类似操作入口。

>>> pd.merge(left, right, 
...   how='inner', 
...   on=None,
...   left_on=None,
...   right_on=None,
...   left_index=False, 
...   right_index=False, 
...   sort=True,
...   suffixes=('_x', '_y'), 
...   copy=True, 
...   indicator=False,
...   validate=None)

参考

• Merge, join, and concatenate