import numpy as np
import pandas as pd

labels = ['a','b','c']   #list
my_list = [10,20,30]    #list
arr = np.array([10,20,30])   # numpy array
d = {'a':10,'b':20,'c':30}   # dictionary

pd.Series(data=my_list)

pd.Series(data=my_list,index=labels)   # Series主要的两个参数data在前，index在后

pd.Series(my_list,labels)  # 在正式项目中最好指明哪个变量指向哪个参数

pd.Series(arr)  # axis labels 默认为有理实数

pd.Series(arr,labels)

pd.Series(d)

pd.Series(data=labels)

# 甚至函数
pd.Series([sum,print,len])

ser1 = pd.Series([1,2,3,4],index = ['USA', 'Germany','USSR', 'Japan'])                                   

ser1

ser2 = pd.Series([1,2,5,4],index = ['USA', 'Germany','Italy', 'Japan'])                                   

ser2

ser1['USA']

ser1 + ser2

ser2.set_value('Canada',7)

ser1 + ser2

import pandas as pd
import numpy as np

from numpy.random import randn
np.random.seed(101)  # seed 相当于reset。 因为随机数每次会取一个数作为seed，然后对这个数进行运算，通过算法得到一系列随机数，如果seed定了，那么得到的随机数是可以预测的。所以我们每次可以重置seed。

df = pd.DataFrame(randn(5,4),index='A B C D E'.split(),columns='W X Y Z'.split())

df

df['W']

# Pass a list of column names 注意是双括号
df[['W','Z']]

df.W

type(df['W'])

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

df

df.drop('new',axis=1) #axis=1指的是列，axis=0指行，axis=2指二维表，以此类推

df

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

df

df.drop('E',axis=0)

df.loc['A']  # 选择行必须要用loc或者iloc函数

df.iloc[2]

df.loc['B','Y']

df.loc[['A','B'],['W','Y']]

df.loc[['B','X','A','Y']]

df

df>0

df[df>0]

# 注意，在上一个情况中为false的值会用NaN占位（当然以后我们在机器学习中可以用其他数值替换NaN），但是在这个情况中，为false的值直接不显示
df[df['W']>0]

df[df['W']>0]['Y']

df[df['W']>0][['Y','X']]
# 注意啦！注意啦！此处是两个双中括号的并列关系，前面一个设定条件，后面一个设置需要选择的范围

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

df

# 将index重置为 0,1...n
df.reset_index()
# 会有inplace参数 和fillin参数可以重置index

newind = 'CA NY WY OR CO'.split()

df['States'] = newind

df # 增加新的一列

df.set_index('States')

df

df.set_index('States',inplace=True)
# 由此可见inplace参数将影响到原数据
df

# Index Levels
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

df = pd.DataFrame(np.random.randn(6,2),index=hier_index,columns=['A','B'])
df

df.xs(['G1',1])

df.xs(1,level='Num')

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

df.dropna()  # 将含有nan值的rows一同删除

df.dropna(thresh=2) # 这个很有用， drop掉所有含有有效数据（除NaN外）小于thresh的Series
# 还有个how参数，需要指定是drop掉全为NaN的行还是drop掉出现NaN的行，这我就不给出例子了

df.fillna(value='FILL VALUE') # 这个不用介绍了吧

df['A'].fillna(value=df['A'].mean())

df.fillna(value=df['B'].mean())

import pandas as pd
# Create dataframe
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)

df

by_comp.describe().transpose() # 翻转

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

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])

df1

df2

df3

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

pd.concat([df1,df2,df3],axis=1) # 通过column相连

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']})    

left

right

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(right,left,  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'])

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')

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

df['col2'].unique()  # 找出唯一的值并返回array

df['col2'].nunique()  # 返回唯一值的个数

df['col2'].value_counts()  # 计数

# 从复合条件中选择数据
newdf = df[(df['col1']>2) & (df['col2']==444)]

newdf

def times2(x):
    return x*2

df['col1'].apply(times2)  # 通过apply对DF使用自定义函数

df['col3'].apply(len)

df['col1'].sum()

del df['col1']

df

df.columns

df.index

df

df.sort_values(by='col2') #inplace=False 默认

df.isnull()

# Drop rows with NaN Values
df.dropna()

import numpy as np

df = pd.DataFrame({'col1':[1,2,3,np.nan],
                   'col2':[np.nan,555,666,444],
                   'col3':['abc','def','ghi','xyz']})
df.head()

df.fillna('FILL')

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

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

import numpy as np
import pandas as pd

df = pd.read_csv('example') # 文件最好在相同目录下，不在的话需要指定文件路径
df

df.to_csv('example',index=False)

pd.read_excel('Excel_Sample.xlsx',sheetname='Sheet1') # 注意需要指明哪个sheet

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

df = pd.read_html('http://www.fdic.gov/bank/individual/failed/banklist.html')

df[0]

from sqlalchemy import create_engine

engine = create_engine('sqlite:///:memory:') # Python默认使用sqlite数据库
df.to_sql('data', engine)
sql_df = pd.read_sql('data',con=engine)

sql_df