# 常用方法
# 获取某处索引值：s[index](假如index不存在会报KeyError) s.get(index) s.get(index,default)
# s.values 返回list结构的所有值
# 过滤查询 判断是否存在index index in s 查询符合某个条件的集合返回Series s[s > 2]
# 直接参与计算 s[ s+1 ] 每个value都进行+1操作
# ==============================
# 这两个是常用的单独导出
from pandas import Series
from utils import util
dateList = [1, 3, 5, 6, 8]
db = {"No.1": "Wo", "No.2": "Shi", "No.3": "Ni", "No.4": "Da", "No.5": "Ye"}
# Creating a Series by passing a list of values, letting pandas create a default integer index
s = Series(dateList, index=["A", "B", "C", "D", "E"]) # 索引的长度必须和list的长度一样，否则为[0, ..., len(data) - 1]
util.report_tag("Series 处理list结构数据")
print "Series data structures is \n", s
print "index is ", s.index
print "values is ", s.values
print "the fist element is ", s[0]
print "0~3 element is \n", s[:3]
print ">3 element is \n", s[s > 3]
# print "通过 s[6] 会报错", s[6]
print "通过 s.get(6) return None", s.get(6)
print "查看前2行\n", s.head(2)
print "查看最后2行\n", s.tail(2)
util.report_tag("Series 处理dict数据")
s = Series(db)
print "Series data structures is \n", s
# Series更像是一个dict ，可以直接通过 s[index] 取值 ，判断是否存在index,假如通过s[index]取一个不存在的索引，将会报 KeyError,而用
import MySQLdb
from utils import util
conn = MySQLdb.Connect(
host='db.***.cn',
port=3306,
user='******',
passwd='write',
db='db_crm',
)
df = pd.read_sql("select phone , order_count from t_user_basic_info limit 1", conn)
# df = pd.read_sql("select * from t_user_basic_info limit 1", conn, index_col="phone")
print df
util.report_tag("保存数据到文件：支持csv,xls,xlsx,hdf,html,txt")
df.to_csv("aa.csv")
print("已保存到aa.csv")
# 需要下载xlwt 模块
df.to_excel("bb.xls", sheet_name='Sheet1')
print("已保存到bb.xls")
# 需要下载openpyxl 摸块
# df.to_excel("bb.xlsx", index_label='label', merge_cells=False)
util.report_tag("保存数据到json串中")
json = df.to_json()
print(json)
util.report_tag("保存数据到数据库")
# 保存数据到表里，aa是表名 ，需要安装 sqlalchemy 模块
from sqlalchemy import create_engine
# s.values 返回list结构的所有值
# 过滤查询 判断是否存在index index in s 查询符合某个条件的集合返回Series s[s > 2]
# 直接参与计算 s[ s+1 ] 每个value都进行+1操作
# ==============================
# 这两个是常用的单独导出
from pandas import Series
from utils import util
dateList = [1, 3, 5, 6, 8]
db = {"No.1": "Wo", "No.2": "Shi", "No.3": "Ni", "No.4": "Da", "No.5": "Ye"}
# Creating a Series by passing a list of values, letting pandas create a default integer index
s = Series(dateList,
index=["A", "B", "C", "D",
"E"]) # 索引的长度必须和list的长度一样，否则为[0, ..., len(data) - 1]
util.report_tag("Series 处理list结构数据")
print "Series data structures is \n", s
print "index is ", s.index
print "values is ", s.values
print "the fist element is ", s[0]
print "0~3 element is \n", s[:3]
print ">3 element is \n", s[s > 3]
# print "通过 s[6] 会报错", s[6]
print "通过 s.get(6) return None", s.get(6)
print "查看前2行\n", s.head(2)
print "查看最后2行\n", s.tail(2)
util.report_tag("Series 处理dict数据")
s = Series(db)
print "Series data structures is \n", s
# Series更像是一个dict ，可以直接通过 s[index] 取值 ，判断是否存在index,假如通过s[index]取一个不存在的索引，将会报 KeyError,而用
s = Series(dateList, index=["A", "B", "C", "D", "E"]) # 索引的长度必须和list的长度一样，否则为[0, ..., len(data) - 1]
print "Series data structures is \n", s
print "index is ", s.index
print "values is ", s.values
print "the fist element is ", s[0]
print "0~3 element is \n", s[:3]
print ">3 element is \n", s[s > 3]
s = Series(db)
print "No.1 is ", s['No.1']
print "Wo is in db", 'No.1' in s
# Creating a DataFrame by passing a numpy array, with a datetime index and labeled columns:
dates = pd.date_range('20130101', periods=6)
df = pd.DataFrame(np.random.randn(6, 4), index=dates, columns=list('ABCD'))
print dates
print df
print df.T
s = Series(db)
util.report_tag("dict to Series:")
print(s)
util.report_tag("dict to DataFrame")
df = DataFrame(db, index=[1, 2, 3, 4, 5])
print df
db = {"No.1": "Wo", "No.2": "Shi", "No.3": "Ni", "No.4": "Da", "No.5": "Ye"}
# Creating a Series by passing a list of values, letting pandas create a default integer index
s = Series(dateList,
index=["A", "B", "C", "D",
"E"]) # 索引的长度必须和list的长度一样，否则为[0, ..., len(data) - 1]
print "Series data structures is \n", s
print "index is ", s.index
print "values is ", s.values
print "the fist element is ", s[0]
print "0~3 element is \n", s[:3]
print ">3 element is \n", s[s > 3]
s = Series(db)
print "No.1 is ", s['No.1']
print "Wo is in db", 'No.1' in s
# Creating a DataFrame by passing a numpy array, with a datetime index and labeled columns:
dates = pd.date_range('20130101', periods=6)
df = pd.DataFrame(np.random.randn(6, 4), index=dates, columns=list('ABCD'))
print dates
print df
print df.T
s = Series(db)
util.report_tag("dict to Series:")
print(s)
util.report_tag("dict to DataFrame")
df = DataFrame(db, index=[1, 2, 3, 4, 5])
print df
