06

'''
1. 創建
'''
import pandas as pd

#pd.DataFrame({'column name1':list/Series,'column name2':list/Series...})
df1 = pd.DataFrame({'cid':['c01','c02','c03','c04','c05'],'time_spent':[23,41,342,97,456]})

#可自訂index
#df1 = pd.DataFrame({'cid':['c01','c02','c03','c04','c05'],'time_spent':[23,41,342,97,456]},index=['a','b','c','d','e'])
df1

# JSON list
df3 = pd.DataFrame([{'cid':'c02','num_products':2},{'cid':'c03','num_products':3},{'cid':'c04','num_products':6}])
df3

'''
2. 取值
'''
# get one column
df1['time_spent'] #Series
# 同 df1.time_spent

# get multiple columns
df1[['cid','time_spent']] #DataFrame

# get rows : [i,j] from index i to index j
df1[0:2]

# 根據指定條件取值
df1[df1['time_spent']>100]

'''
3. 新增
'''
# 新增一欄
df1['num_products'] = [2,3,1,6,7]
df1
# 新增列：append DataFrame（若新增的欄位不存在，會自動新增欄位）
df1 = df1.append(pd.DataFrame([{'cid':'c06','time_spent':231,'num_products':3}])).reset_index(drop=True)
# append後記得要再assign回去
# reset_index(drop=True) 重置index，要不然index會維持新增DataFrame的index（0,1,2,3,4,5,0）
df1

'''
4. 修改
'''
# 修改欄位名稱 : rename(columns = {old column name mapping to new column name})
df1 = df1.rename(columns = {'cid':'pid','num_products':'num_purchase'})
df1
# 修改列內容
df1[0:1] = pd.DataFrame([{'pid':'c07','time_spent':31,'num_purchase':1}])
df1

'''
5. 刪除
'''
# 刪除欄 : del
del df1['pid']
df1
# 刪除欄 : drop（方法2），要特別標注 axis = 1 表示刪除的是欄不是列
df1 = df1.drop('time_spent',axis = 1)
df1
# 列:drop(index list)
df1 = df1.drop([1,2])
df1
# Reset Index
df1 = df1.reset_index(drop=True)
df1

'''
6.合併
pd.merge() 資料對接
pd.concat() 直接合併
'''
df2 = pd.DataFrame({'age':[20,42,25,17,13]})
df2
df3 = pd.concat([df1,df2], axis=1)
df3
df_new = pd.DataFrame({'age':[20,42,14,17,55,13],'price':[400,399,250,150,200,300]})
df_new
df4 = pd.merge(df3, df_new, how='inner', on='age')
df4