2. Pandas介绍

Introduction to Pandas

You can think of pandas as an extremely powerful version of Excel, with a lot more features

Series

A Series is very similar to a Numpy array. What differentiate the Numpy array from a Series, is that a Series can have axis labels, meaning it can be indexed by a label, instead of just a number location. It also does’nt need to hold numeric data, it can hold any arbitrary Python Object.

import numpy as np
import pandas as pd

Creating a Series

You can convert a list, numpy array, or dictionary to a Series

labels = ['a','b','c']
my_list = [10,20,30]
arr = np.array([10,20,30])
d = {'a':10,'b':20,'c':30}
pd.Series(data=my_list)
#0 10
#1 20
#2 30
#dtype: int64
pd.Series(data = my_list, index = labels)
pd.Series(my_list, labels)
#a 10
#b 20
#c 30
#dtype: int64
pd.Series(arr)
#0 10
#1 20
#2 30
#dtype: int64
pd.Series(arr,labels)
#a 10
#b 20
#c 30
#dtype: int64
pd.Series(d)
#a 10
#b 20
#c 30
#dtype: int64

Data in a Series

A pandas Series can hold a variety of object types

pd.Series(data = labels)
#0 a
#1 b
#2 c
#dtype: object
#even functions(although unlikely that you will use this)
pd.Series([sum,print,len])
#0 <built-in function sum>
#1 <built-in function print>
#2 <built-in function len>
dtype: object

Using an Index

ser1 = pd.Series([1,2,3,4], index = ['USA','Germany','USSR','Japan'])
#USA     1
#Germany 2
#USSR    3
#Japan   4
#dtype: int64
ser2 = pd.Series([1,2,3,4], index = ['USA', 'Germany','Italy', 'Japan'])
#USA     1
#Germany 2
#Italy   5
#Japan   4
#dtype: int64
ser1['USA'] #1
ser1+ser2
#Germany 4.0
#Italy   NaN
#Japan   8.0
#USA     2.0
#USSR    NaN
#dtype: float64

DataFrames

DataFrames are the workhorse of pandas and directly inspired by the R programming language. We can think of a DataFrame as a buch of Series objects put together to share the same index.

import pandas as pd
import numpy as np
from numpy.random import randn
np.random.seed(101)
df = pd.DataFrame(randn(5,4), index = 'A B C D E'.split(), columns = 'W X Y Z'.split())

Selection and Indexing

df['W']
#A 2.706850
#B 0.651118
#C -2.018168
#D 0.188695
#E 0.190794
#Name: W, dtype: float64

# Pass a list of column names
df[['W','Z']]

#SQL Syntax (not recommended)
df.W
#A 2.706850
#B 0.651118
#C -2.018168
#D 0.188695
#E 0.190794
#Name: W, dtype: float64

DataFrame Columns are just Series

type(df['W']) #pandas.core.series.Series

Creating a new column

df['new'] = df['W'] + df['Y']

Removing Columns

df.drop('new',axis=1)

Note inplace unless specified

df

df.drop('new',axis=1,inplace=True)
# Can also drop rows in this way:
df.drop('E', axis=0)

Selecting Rows

df.loc['A']
#W 2.706850
#X 0.628133
#Y 0.907969
#Z 0.503826
#Name: A, dtype: float64

#Or select based off of position instead of label
df.iloc[2]
#W   -2.018168
#X 0.740122
#Y 0.528813
#Z -0.589001
#Name: C, dtype: float64

Selecting subset of rows and columns

df.loc['B','Y']
#-0.84807698340363147
df.loc[['A','B'], ['W','Y']]

Conditional Selection

An important feature of pandas is conditional seletion using bracket notation, very similar to numpy

df

df>0

df[df>0]

df[df['W']>0]

df[df['W']>0]['Y']
#A    0.907969
#B -0.848077
#D -0.933237
#E 2.605967
#Name: Y, dtype: float64
df[df['W']>0][['Y','X']]

for two conditions you can use & and | with parenthesis

df[(df['W']>0)&(df['Y']>1)]

Mode Index Details

df

# reset to default 0,1,... n index
df.reset_index()

newind = 'CA NY WY OR CO'.split()
df['States'] = newind

df.set_index('States')

Note inplace unless specified

df

df.set_index('States', inplace = True)
df

Multi-Index and Index Hierarchy

outside = ['G1','G1','G1','G2','G2','G2']
inside = [1,2,3,1,2,3]
hier_index = list(zip(outside,inside))
hier_index = pd.MultiIndex.from_tuples(hier_index)
hier_index
#MultiIndex(levels=[['G1', 'G2'], [1, 2, 3]],
labels=[[0, 0, 0, 1, 1, 1], [0, 1, 2, 0, 1, 2]])
df = pd.DataFrame(np.random.randn(6,2), index = hier_index, columns = ['A','B'])
df

For index hierarchy we use df.loc[], if this was the column axis, you would just use normal bracket notation df[]. Calling one level of the index returns the sub-dataframe

df.loc['G1']

df.loc['G1'].loc[1]
#A    0.153661
#B    0.167638
#Name: 1, dtype: float64

#Cross Section fuction df.xs()
df.index,names
#FrozenList([None, None])
df.index.names = ['Group', 'Num']
df

df.xs('G1')

df.xs['G1',1]
#A    0.153661
#B    0.167638
#Name: (G1, 1), dtype: float64
df.xs(1,level = 'Num')

---

补充： Numpy—np.random.seed()函数的应用

np.random.seed()函数可以保证生成的随机数具有可预测性。

这里的可预测性是指相同的种子（seed值）所产生的随机数是相同的。如果不设置seed值，则系统根据时间来自己选择这个值，此时每次生成的随机数因时间差异而不同。

语法：

numpy.random.seed(seed=None)


输入：

—-seed参数默认为空，可选择整数或者数组，可选。

但是，需要注意的是，seed值的有效次数仅为一次，因此，若要保证每次产生的随机数相同，则需要在调用随机数函数之前再次使用相同的seed值。下面给出相应的案例，即



在机器学习和深度学习中，如果要保证部分参数（比如W权重参数）的随机初始化值相同，可以采用这种方式来实现。

---

Missing Data

import numpy as np
import pandas as pd
df = pd.DataFrame({'A':[1,2,np.nan],
                'B':[5,np.nan,np.nan],
                'C':[1,2,3]})

df.dropna()

not inplace by default, drop rows by default

df.dropna(axis=1)

df.dropna(thresh=2)

df.fillna(value='FILL VALUE')

df['A'].fillna(value=df['A'].mean())
#0 1.0
#1 2.0
#2 1.5
#Name: A, dtype: float64

Groupby

The groupby method allows you to group rows of data together and call aggregate functions

import pandas as pd
data = {'Company':['GOOG','GOOG','MSFT','MSFT','FB','FB'],
       'Person':['Sam','Charlie','Amy','Vanessa','Carl','Sarah'],
       'Sales':[200,120,340,124,243,350]}
df = pd.DataFrame(data)

Now you can use the .groupby() method to group rows together based off a column name.

by_comp = df.groupby("Company")
by_comp.mean()

# same as df.groupby('Company').mean()
by_comp.std()
by_comp.min()
by_comp,.max()
by_comp.count()
by_comp.describe()

by_comp.describe().transpose()

by_comp.describe().transpose()['GOOG']

#####Merging Joining and Concatenating
Concatenation

Concatenation basically glues together DataFrames. Keep in mind that dimensions should match along the axis you are concatenating on. You can use pd.concat and pass in a list of DataFrames to concatenate together.

import pandas as pd
df1 = pd.DataFrame({'A': ['A0', 'A1', 'A2', 'A3'],
                    'B': ['B0', 'B1', 'B2', 'B3'],
                    'C': ['C0', 'C1', 'C2', 'C3'],
                    'D': ['D0', 'D1', 'D2', 'D3']},
                    index=[0, 1, 2, 3])

df2 = pd.DataFrame({'A': ['A4', 'A5', 'A6', 'A7'],
                    'B': ['B4', 'B5', 'B6', 'B7'],
                    'C': ['C4', 'C5', 'C6', 'C7'],
                    'D': ['D4', 'D5', 'D6', 'D7']},
                     index=[4, 5, 6, 7])

df3 = pd.DataFrame({'A': ['A8', 'A9', 'A10', 'A11'],
                    'B': ['B8', 'B9', 'B10', 'B11'],
                    'C': ['C8', 'C9', 'C10', 'C11'],
                    'D': ['D8', 'D9', 'D10', 'D11']},
                    index=[8, 9, 10, 11])

pd.concat([df1,df2,df3])

pd.concat([df1,df2,df3],axis=1)

Merging

The merge function allows you to merge DataFrames together using a siilar logic as merging SQL tables together.

left = pd.DataFrame({'key': ['K0', 'K1', 'K2', 'K3'],
                     'A': ['A0', 'A1', 'A2', 'A3'],
                     'B': ['B0', 'B1', 'B2', 'B3']})

right = pd.DataFrame({'key': ['K0', 'K1', 'K2', 'K3'],
                       'C': ['C0', 'C1', 'C2', 'C3'],
                       'D': ['D0', 'D1', 'D2', 'D3']})    

pd.merge(left,right,how='inner', on='key')

left = pd.DataFrame({'key1': ['K0', 'K0', 'K1', 'K2'],
                     'key2': ['K0', 'K1', 'K0', 'K1'],
                        'A': ['A0', 'A1', 'A2', 'A3'],
                        'B': ['B0', 'B1', 'B2', 'B3']})

right = pd.DataFrame({'key1': ['K0', 'K1', 'K1', 'K2'],
                               'key2': ['K0', 'K0', 'K0', 'K0'],
                                  'C': ['C0', 'C1', 'C2', 'C3'],
                                  'D': ['D0', 'D1', 'D2', 'D3']})

pd.merge(left, right, on=['key1', 'key2'])

pd.merge(left, right, how='outer', on=['key1', 'key2'])

pd.merge(left, right, how='right', on=['key1', 'key2'])

pd.merge(left, right, how='left', on=['key1', 'key2'])

Joining

Joining is a convenient method for combing the columns of two potentially differently-indexed DataFrames into a single result DataFrame

left = pd.DataFrame({'A': ['A0', 'A1', 'A2'],
                     'B': ['B0', 'B1', 'B2']},
                      index=['K0', 'K1', 'K2'])

right = pd.DataFrame({'C': ['C0', 'C2', 'C3'],
                    'D': ['D0', 'D2', 'D3']},
                      index=['K0', 'K2', 'K3'])

left.join(right)

left.join(right, how='outer')

Operations

import pandas as pd
df = pd.DataFrame({'col1':[1,2,3,4],'col2':[444,555,666,444],'col3':['abc','def','ghi','xyz']})
df.head() #return the first n rows

info on unique values

df['col2'].unique()
#array([444, 555, 666])
df['col2'].nunique()
#3
df['col2'].value_counts()
#444 2
#555 1
#666 1
#Name: col2, dtype: int64

Selecting Data

newdf = df[(df['col1']>2) & (df['col2']==444)]

Applyin Functions

def times2(x):
    return x*2
df['col1'].apply(times2)
#0 2
#1 4
#2 6
#3 8
#Name: col1, dtype: int64
#same as: df['col1'].apply(lambda x:x*2)
def['col3'].apply(len)
#0 3
#1 3
#2 3
#3 3
#Name: col3, dtype: int64
df['col1'].sum()
#10

Use .apply to send a column of every row to a function

Use .apply with axis=1 to send every single row to a function

Permanently Removing a Column

del df['col1']

Get column and index names

df.columns
#index(['col2', 'col3'], dtype='object')
df.index
#RangeIndex(start=0, stop=4, step=1)

Sorting and Ordering a DataFrame
df.sort_values(by=‘col2’) #inplace=false by default

![result](https://github.com/Yinstinctive/Python_for_Data_Science/blob/master/images/56.png)<br>
**Pivot Table**
```Python
data = {'A':['foo','foo','foo','bar','bar','bar'],
     'B':['one','one','two','two','one','one'],
       'C':['x','y','x','y','x','y'],
       'D':[1,3,2,5,4,1]}
df = pd.DataFrame(data)

df.pivot_table(values='D',index=['A', 'B'],columns=['C'])

Data Input and Output

import numpy as np
import pandas as pd

CSV

df = pd.read_csv('example')
#

df.to_csv('example',index=False)
# write index into row names, index=True by default

Excel

pd.read_excel('Excel_Sample.xlsx',sheetname='Sheet1')
df.to_excel('Excel_Sample.xlsx',sheet_name='Sheet1')

HTML

#Pandas read_html function will read tables off of a webpage and return a list of DataFrame objects
df = pd.read_html('http://www.fdic.gov/bank/individual/failed/banklist.html')
df[0]

SQL

The pandas.io.sql module provide a collection of query wrappers to both facilitate data retrieval and to reduce dependency on DB-specific API. Database abstraction is provided by SQLAlchemy if installed. In addition you will need a driver library for your database. Examples of such drivers are psycopg2 for PostgreSQL or pymysql for MySQL. For SQLite this is included in Python’s standard library by default. You can find an overview of supported drivers for each SQL dialect in the SQLAlchemy docs.
If SQLAlchemy is not installed, a fallback is only provided for sqlite (and for mysql for backwards compatibility, but this is deprecated and will be removed in a future version). This mode requires a Python database adapter which respect the Python DB-API.

See also some cookbook examples for some advanced strategies.

The key functions are:

• read_sql_table(table_name, con[, schema, …])
  • Read SQL database table into a DataFrame.
• read_sql_query(sql, con[, index_col, …])
  • Read SQL query into a DataFrame.
• read_sql(sql, con[, index_col, …])
  • Read SQL query or database table into a DataFrame.
• DataFrame.to_sql(name, con[, flavor, …])
  • Write records stored in a DataFrame to a SQL database.

from sqlalchemy import create_engine
engine = create_engine('sqlite:///:memory:')
df.to_sql('data',engine)
sql_df = pd.read_sql('data', con=engine)
sql_df