import pandas as pd
#python3.6  pycharm 编辑器
#andaconda 可以用 prompt 命令行：jupyter notebook 启动idle 交互界面
#学习视频 Python数据分析 - Pandas玩转Excel-Timothy 为基础
#围绕四个模块1、数据读写，2、数据查看定位，3、数据操作（EXCEL），4、数据统计
#通用思路通过拿到index或者columns进行数据修改

#要理解 Serise、DateFrame、axis轴概念（上下下0轴、左右右1轴根据index和columns检索；
#0 行操作、1列操作）
#https://www.jianshu.com/p/4f18e8327872
#核心是 遍历顺序0、1轴；每次对一个Serise记录操作
df.values #就是去掉 index 与 column 的二维ndarray、一维就是 series ndarray 


#一、数据读写

#读取文件
df = pd.read_excel(r'C:\Temp\Books.xlsx',skiprows=3,header=2,usecols='C:F',dtype={'ID':object,'InStore':str,'Date':str},index_col='ID')
#skiprows 跳过前3行
#header 从第2行开始读
#usecols 列选择C到F列数据读取
#dtype 重点 pandas把NaN默认flode 如果想下面迭代表达先把空列设置成str类型、或者object
#index_col 把ID列作为DateFrame的index列
#sheet_name=‘sheet1’ 把Excel表中读取sheet1 或者sheet2
df = pd.read_csv(path,encoding='gb18030',header=1,engine='python') #读取文件endcoding注意
# names=list 如果读取的没有columns 可以用list添加 columns
#保存文件
df.to_excel(path,index=False)
df.to_csv('5.csv',encoding='gb18030')
#如果未设置index列系统自动保存，excel打开后 多出一列index，解决方案指定index列


#二、数据查看定位
df.query()  # 优雅的查找方法 等与pandas原始 df[(df["a"]>1) | (df["b"]<100)] 布尔索引 ~ 否定运算 &、|
df.info()#查看每列数据类型 str time object等等
df.head(3)#查看头部前三行
df.tail()#查看尾部
df.shape#查看行列
df.index#查看行 Serise
df.columns#查看列 Serise
df.describe()  # 查看基础统计值 计数值、均值、标准差、最下最大值、1/4值
df['close'].values_counts(dropna = False)  # 查看Series对象的唯一值和计数
df["Names"].unique  # 查看Series列唯一值
df.query("p_change > 2 | d_change <10")#逻辑运算 返回在这个范围的 data
df['销售额'].isin([55,33,11])#销售额列中是否有55、33、11 返回 布尔索引
df.count()# 查看每列是否有空值
df.isnull().sum() #查看 每列空数值
df.创建时间.unique()#查看 创建时间 列 不重复值有多少
df['业务员']#查看业务员列从DataFrame变成Series
df.loc[:,['A','C']]#按照index与columns选择 标签选择
df.iloc[[1,5,6]:[0,3]#按照标签的顺序数字选择从0开始
df.loc[::2]  # 选择切片偶数行
# 选择 单据类型列 含有 销售发票 字符的行
df=df[df.单据类型.str.contains('销售发票')]
# 选择 分钟列 大于0 且 小于等于 15。然后给 分钟这一列负值15 int
df.loc[(df.分钟>0)&(df.分钟<=15),'分钟']=15
df.loc[(df.分钟>0)&(df.小时<=15),['分钟','小时']=[15,30]
#数据效验
def score_valication(row):
    try:
        assert 0 <= row.Score <= 100
    except:
        print(f'#{row.ID}\tstudent {row.Name} has an invalid score {row.Score}')
students = pd.read_excel('C:/Temp/Students.xlsx')
students.apply(score_valication, axis=1)


#三、数据操作

#1、设置index,修改index索引，重设index,修改
df=df.set_index('ID')
df.set_index(['ID','日期']，inplace=True,drop=True)#原来index扔掉,设置两列都为index 具有Mulitindex index三维数组储存
#两个一样操作
#修改indexs与columns索引需要重新构建list
df.index.tolist #获得索引list 修改
df.index=new_listindex #修改后的list赋值index
#重新设置index
df.reset_index(drop=True)#重新设置index并且放弃老的index 默认不放弃老index
#修改第二列变成size
df.rename(columns = {df.columns[1]:'size'},inplace=True)

#2、设置数据类型
df['创建时间'] = pd.to_datetime(df.创建时间)
df.数量 = df.数量.astype('int')  # 需要赋值修改

#3、Serise转换list，然后更具loc定位操作list选择需要的columns
list=df.columns.tolist()

#4、元格填空
df['ID'].at[0] = 100  #Serise 后 at
df.at[0,'ID'] = 100 #DateFrame 后 at
df.loc[0,'ID'] = 100
#ID 列 index 0行 赋值 100 
for i in books.index:
    books['ID'].at[i]=i+1 #i从0开始 单元格赋值从1、2、3开始，注意index
    books['InStore'].at[i]='Yes'if i%2==0 else 'No'

#5、排序多重排序
df.sort_values(by=['Worthy', 'Price'], ascending=[True, False], inplace=True)
#by 排序的columns
#ascending True 从小到大
#inplace True 直接在df数据上保留修改

#6、数据筛选、过滤 apply
def age_range(x):
    return 20<=x<=36
def score_range(y):
    return 80<=y<=100
df1=df.loc[df['Age'].apply(age_range)].loc[df['Score'].apply(score_range)]
df = df['数量'].apply(lambda x : -x)  #数量列全部负值
#7、多表联合VLOOKUP合并
table = df1.merge(df2,how='left',on='ID').fillna('没找到')
table.Score = table.Score.astype(int)#Score列变成整数
#how=’left‘ 表示依 df1 基础 保留所有df1列信息。默认inner参数
#on=’ID‘ df1与df2都有ID列 前提两张表都有ID列，没有用 left_on与right_on
#.fillna() 表示 在df1中df2没有的数据填下’没找到‘
#merge 不能默认指定index列 必须 on指定
students = pd.read_excel('C:/Temp/Student_score.xlsx', sheet_name='Students', index_col='ID')
scores = pd.read_excel('C:/Temp/Student_score.xlsx', sheet_name='Scores', index_col='ID')
table = students.join(scores, how='left').fillna(0)
table.Score = table.Score.astype(int)
#join 必须指定index_col一样，也有on参数

#8、str方法 有空看一下
#合并两列用 - 区分
df['小时分钟']=df['小时'].str.cat(df.['分钟']，sep='-')
#分列修改每列替换
time=df.创建时间.str.split(expand=True)
#str.split()空的表示默认空格分
#expand list分为全咧
#columns 0 1

#9、定位、消除重复数据
students = pd.read_excel('C:/Temp/Students_Duplicates.xlsx')
#找出重复数据行index定位
dupe = students.duplicated(subset='Name')#dupe是一个serises index与students 一样是布尔True与Flase
dupe = dupe[dupe == True]  # dupe = dupe[dupe]，拿到dupe 重复数据的index True
print(students.iloc[dupe.index])#用iloc 定位 index

#删除重复数据
students.drop_duplicates(subset='Name', inplace=True, keep='last')
#subset 需要列重复数据 如果多列 ['Name','ID']
#keep last 意思保留后面的数据，默认first 保留前面数据

#10、分类汇总
#方法1：
import numpy as np
from datetime import date
orders = pd.read_excel('C:/Temp/Orders.xlsx', dtype={'Date': date})#让Date列变成日期类型？始终有问题
orders['Year'] = pd.DatetimeIndex(orders.Date).year
#新增Year列 让Date 2019/5-31 在Year列中显示年份year
pt1 = orders.pivot_table(index='Category', columns='Year', values='Total', aggfunc=np.sum)
#生成数据透视表 row是Category，columns是Year的种类，求值是Total列的数值，目的是aggfunc 求和
#方法2：
groups = orders.groupby(['Category', 'Year'])
#根据Category、Year列分组
s = groups['Total'].sum()
#Total列求和
c = groups['ID'].count()
#ID列求数量
pt2 = pd.DataFrame({'Sum': s, 'Count': c})
#s、c 两个DataFrame 按照列Sum 与 Count 再合并成一个新DateFrame

#11、缺失值处理
#判断是否有空值nan
pd.isnull(df)#布尔索引 nan返回 True
pd.isnull(df).any() #每个字段是否有缺失值
np.any(pd.isnull(df)#返回True 就存在空值

pd.notnull(df)
np.all(pd.notnull(df)#返回False 就是存在空值
pd.notnull(df).all()
#处理两种方法：删除与替换
df.dropna()#删除存在缺失值，不会修改原来数据 返回 按照row删除
df.fillna(value,inplace=True)#value修改原来数据

#判断有特殊符号缺失值 比如 ？
df.replace(to_replace='?',value=np.nan)#to_replace替换前的值，value替换的值
#替换成空值nan后就可以删除替换了

#12、实现表合并
pd.concat((data1,data2),axis=1)#水平合并


#四、数据统计

#行列求和、求平均
students = pd.read_excel('C:/Temp/Students.xlsx', index_col='ID')

row_sum = students[['Test_1', 'Test_2', 'Test_3']].sum(axis=1)
row_mean = students[['Test_1', 'Test_2', 'Test_3']].mean(axis=1)

students['Total'] = row_sum
students['Average'] = row_mean

col_mean = students[['Test_1', 'Test_2', 'Test_3', 'Total', 'Average']].mean()
col_mean['Name'] = 'Summary'
students = students.append(col_mean, ignore_index=True)




#行操作集锦
page_001 = pd.read_excel('C:/Temp/Students.xlsx', sheet_name='Page_001')
page_002 = pd.read_excel('C:/Temp/Students.xlsx', sheet_name='Page_002')
# 追加已有：append row 添加002、重新排列index并且放弃原来的两个index
students = page_001.append(page_002).reset_index(drop=True)
# 追加新建：stu新建 row 放到students后面并且忽略index不然报错
stu = pd.Series({'ID': 41, 'Name': 'Abel', 'Score': 90})
students = students.append(stu, ignore_index=True)
# 删除（可切片）：students放弃index39到40 row 并且赋值给 students
students = students.drop(index=[39, 40])
# 插入（切片操作）
stu = pd.Series({'ID': 100, 'Name': 'Bailey', 'Score': 100})
part1 = students[:21]  # .iloc[] is the same
part2 = students[21:]
students = part1.append(stu, ignore_index=True).append(part2).reset_index(drop=True)
# 更改覆盖
stu = pd.Series({'ID': 101, 'Name': 'Danni', 'Score': 101})
students.iloc[39] = stu
# 设置空值
for i in range(5, 15):
    students['Name'].at[i] = ''
# 去掉空值
missing = students.loc[students['Name'] == '']
students.drop(missing.index, inplace=True)


#列操作集锦
students = pd.read_excel('C:/Temp/Students.xlsx')
# 追加列
students['Age'] = 25
# 删除列
students.drop(columns=['Score', 'Age'], inplace=True)
# 插入列
students.insert(1, column='Foo', value=np.repeat('foo', len(students)))
# 改列名
students.rename(columns={'Foo': 'FOO', 'Name': 'NAME'}, inplace=True)
# 设置空值
students['ID'] = students['ID'].astype(float)
for i in range(5, 15):
    students['ID'].at[i] = np.nan
# 去掉空值
students.dropna(inplace=True)





#时间轴处理集锦 dt类（有空看一下）
#设置 创建时间 列 数据类型为 datetime64[ns]
df['创建时间'] = pd.to_datetime(df.创建时间)
#查看datetime类型
df['创建时间'].dt.year.unique()
df['创建时间']].dt.month.unique()
df['创建时间']].dt.day.unique()
#查看day=26天的数据有多少
df[df['创建时间'].dt.day == 26]
#查看第6行 创建时间列 与 第200行 创建时间列时间差距
df.loc[6,'创建时间'] - df.loc[200,'创建时间']
#创建新 开票星期几 列 然后 把创建时间列算下星期几或者时间
df['开票星期几'] = pd.DatetimeIndex(df.创建时间).dayofweek



#特殊
100-df.isnull().sum() #每列df.isnull每列False与True 求True的sum，100对每个序列减

#数据行列转换
df.shape
#从numpy角度 (3,10).几位置就是几维素组，位置本身就是信息。axis=1 就是 10位置 与axis=-1 一致
#22-26课看下

#小知识：python3.5 pandas与python3.6 pandas转换 list语法不同
df['a列'].values.tolist()  # python3.5的 先转换为nparry在用np转换list
df['a列'].to_list()   # python3. 6的非常优雅

list1 = df_hospital['客户编码'].values.tolist()
df = df[~df['客户编码'].isin(list1)]  # ~ 选择不再list1列表里面的数据 经典       

# 小知识 删除 所在列有null的行
df.info() # 观察到
df['金额'].isnull().value_counts()  #  发现金额列有几个null值
df['金额'] = df['金额'].fillna("这是空值")  # 先空值填空
list_index = df[df['金额'] == "这是空值"].index.tolist() # 拿到index
df = df.drop(list_index)  # 删除空值行

# 或者一行代码
df = df.dropna(subset=["金额"])  # axis默认0

#数据筛选
df.query
https://blog.csdn.net/AlanGuoo/article/details/88874742

# 重新归纳
https://blog.csdn.net/liufang0001/article/details/77856255

np.where #函数
s = pd.Series(range(5))
s = np.where(s>2,'高',s)
>> array(['0', '1', '2', '高', '高'], dtype='<U21')
# 可以且套使用
s = pd.Series(range(5))
s = np.where(s==2,'高',np.where(s==1,'zhes',s))
>> array(['0', 'zhes', '高', '3', '4'], dtype='<U21')
#微信收藏
pub = df['Place of Publication']  # 拿到 这一列 Series
london = pub.str.contains("London") # 变量london 拿到含有 London字段
oxford = pub.str.contains("Oxford") 
df['Place of Publication'] = np.where(london,"London",np.where(oxford,"Oxford",pub.str.replace("_","")))

# 开启正则re模式 apply应用
import pandas as pd
import re
gl_path = r"C:\Users\yanglichao\Desktop\药事服务项目管理\月_品种客户销售统计\201901.csv"
df = pd.read_csv(gl_path,encoding = "gb18030",engine = "python",header=1)
# 选择有用数据列
df = df.loc[:,["客户","商品编码","商品","单位","数量","金额","考核毛利"]]
# 观察数据是否有null
df.isnull().any()
# drop 有空值得行
df.dropna(inplace = True)
df.reset_index(drop = True,inplace = True)  #重新设置索引
df["数量"] = df["数量"].astype("int32")

import re 
def search_word(row):
    pattern = re.compile(r"仪征*")
    if pattern.search(row):
        return row
    # 错误则返回None
new_df = df["客户"].apply(search_word)  # 里面天函数名称
df[~new_df.isnull()]  # 这个完美 新的new_de Serise index一样 isnull()布尔索引的去，反值~

new_df = pd.DataFrame(new_df)  # 返回结果 pd下
new_df[new_df["客户"].notnull()] # 看下notnull()结果
new_df[new_df.notnull()].index  # 或者直接拿到索引