Pandas

"""Pandas
Series 一维序列DataFrame  二维表结构

"""

import pandas as pdfrom pandas import Series,DataFrame
print('-------------------1-Series index，values ------------------------------------------------')x1 = Series([1,2,3,5])  # index默认为0,1,2,3print(x1)
x2 = Series(data=[1,3,5,7],index=['a','b','c','d'])  # index 手动指定print(x2)
print('-------------------2-DataFrame  ------------------------------------------------')
data = {'Chinese':[66,67,85,39,30],'English':[64,56,89,76,18],'Math':[40,35,77,66,56]}df1 =DataFrame(data)print(df1)
df2 = DataFrame(data,index=['liubei','guanyu','zhangfei','caocao','zhugeliang'])print(df2)
print('-----------------3 数据导入和输出   ----------------------------------------')
ss = DataFrame(pd.read_excel(r'E:\08_启迪城服2021\2021-07\3天进阶课_菜场年销售情况.xlsx'))  # 读取excel文件print(ss)# ss.to_excel('3天菜场销售python输出.xlsx')  # 写入excel文件 # 屏蔽掉，避免重复导出
# 依赖包： openpyxl
print('----------------------4 数据清洗   ----------------------------------------')
print(df2) # 现数据
print('------------------4-1 删除行列 columns -----------------------------------')
df2 = df2.drop(columns=['Chinese'])   # 删除列   # 注意中括号print(df2)
df2 = df2.drop(index=['caocao']) # 删除行      # 注意中括号print(df2)

print('------------------4-2 重命名列名 columns -----------------------------------')
data = {'Chinese':[66,67,85,39,30],'English':[64,56,89,76,18],'Math':[40,35,77,66,56]}
df3 = DataFrame(data,index=['liubei','guanyu','zhangfei','caocao','zhugeliang'])print(df3)
df3.rename(columns={'Chinese':'语文','English':'英语'},inplace=True)print(df3)
print('------------------4-3 去除重复行 columns -----------------------------------')
data = {'Chinese':[66,66,85,39,30],'English':[64,64,89,76,18],'Math':[40,40,47,66,56]}
df4 = DataFrame(data,index=['liubei8','liubei45','zhangfei','caocao','zhugeliang'])print(df4)
print('--------去除重复后---------')
df4 = df4.drop_duplicates() # 去重复行
print(df4)
#结论：行索引名是否重复无关系，行值重复就会去重，自动保留第一行。
print('------------------4-4 更改数据格式 columns --------------这个没搞懂---------------------')
print(type(df4['Chinese']))
df4['Chinese'].astype('str')
print(type(df4['Chinese']))import numpy as npdf4 ['English'].astype(np.int64)print(type(df4['English']))
# 结论 修改格式，似乎无效，目前暂不清楚是修改方式不对，还是查看方式不对。
print('------------------4-5 删除空格  -----------未测试出效果，读取excel可能更好点------------------------')
data = {'Chinese':[66,66,85,39,30],'English':[64,64,89,76,18],'Math':[40,40,47,66,56]}
df5 = DataFrame(data,index=['liubei8','liubei45','zhangfei','caocao','zhugeliang'])print(df5)
# #删除左右两边空格# df5['Chinese']=df2['Chinese'].map(str.strip)## #删除左边空格# df5['Chinese']=df2['Chinese'].map(str.lstrip)## #删除右边空格# df5['Chinese']=df2['Chinese'].map(str.rstrip)
## df2['Chinese']=df2['Chinese'].str.strip('$')  #删除特殊字符# print(df5)

print('------------------4-6 大小写转换------------------------')

#全部大写df2.columns = df2.columns.str.upper()#全部小写df2.columns = df2.columns.str.lower()#首字母大写df2.columns = df2.columns.str.title()

print('------------------4-7 查找空值------------------------')
data = {'Chinese':[66,66,85,39,30],'English':[64,64,89,76,18],'Math':[40,40,47,66,56]}
df6 = DataFrame(data,index=['liubei8','liubei45','zhangfei','caocao','zhugeliang'])print(df6)
print(df6.isnull())print(df6.isnull().any())

import pandas as pd
data ={'name':['zhangsan','lisi','wangwu'],'age':[34,53,22]}df1 = pd.DataFrame(data)print(df1)
print('-------name列大写---------')
print(df1['name'].apply(str.upper))
print('-------年龄翻倍---------')
def double_age(x):        return 2*x

print(df1['age'].apply(double_age))

from pandas import  Series,DataFrameimport pandas as pd
print('---------数据统计-数据描述------------------')
df1 = DataFrame({'name':['ZhangFei', 'GuanYu', 'a', 'b', 'c'], 'data1':range(5)})print(df1)print(df1.describe())
print('---------数据统计-数据合并------------------')
df1 = DataFrame({'name':['ZhangFei', 'GuanYu', 'a', 'b', 'c'], 'data1':range(5)})df2 = DataFrame({'name':['ZhangFei', 'GuanYu', 'A', 'B', 'C'], 'data2':range(5)})print(df1)print(df2)
print('---------数据统计-数据合并 1 基于指定列进行连接------------------')
df3 = pd.merge(df1,df2,on='name')print(df3)
print('---------数据统计-数据合并 2 inner 内连接------------------')
df4 = pd.merge(df1,df2,how='inner')print(df4)
print('---------数据统计-数据合并 3 left 内连接------------------')
df5 = pd.merge(df1,df2,how='left')print(df5)
print('---------数据统计-数据合并 4 right 内连接------------------')
df6 = pd.merge(df1,df2,how='right')print(df6)

print('---------数据统计-数据合并 5 outer 内连接------------------')
df7 = pd.merge(df1,df2,how='outer')print(df7)

import pandas as pdfrom pandas import DataFramefrom pandasql import sqldf, load_meat, load_birthsdf1 = DataFrame({'name':['ZhangFei', 'GuanYu', 'a', 'b', 'c'], 'data1':range(5)})pysqldf = lambda sql: sqldf(sql, globals())sql = "select * from df1 where name ='ZhangFei'"print(pysqldf(sql))