#!/usr/bin/env python3
# -*- coding: utf-8 -*-

import pandas as pd

data = {'Chinese': [66, 95, 93, 90, 80, 80], 'English': [65, 85, 92, 88, 90, 90],
        'Math': [None, 98, 96, 77, 90, 90]}
df = pd.DataFrame(data, index=['张飞', '关羽', '赵云', '黄忠', '典韦', '典韦'],
                  columns=['English', 'Math', 'Chinese'])
# 去除重复行
df = df.drop_duplicates()
# 列名重新排序
cols = ['Chinese', 'English', 'Math']
df = df.filter(cols, axis=1)
# 列名改为中文
df.rename(columns={'Chinese': '语文', 'English': '英语',
                   'Math': '数学'}, inplace=True)


def total_score(df):
    df['总分'] = df['语文'] + df['英语'] + df['数学']
    return df


# 求成绩的和，用老师讲的 apply 方法
df = df.apply(total_score, axis=1)
# 或者可以用这个方法求和
# df['总分'] = df['语文'] + df['英语'] + df['数学']
# 按照总分排序，从高到低，此时有缺失值
df.sort_values(['总分'], ascending=[False], inplace=True)
# 打印显示成绩单信息，张飞有空值
print(df.isnull().sum())
print(df.describe())
print(df)

# 使用数学成绩均值填充张飞同学的缺失值
df['数学'].fillna(df['数学'].mean(), inplace=True)
# 再次求成绩的和并打印显示成绩单情况
df = df.apply(total_score, axis=1)
print(df.isnull().sum())
print(df.describe())
print(df)

"""
Pandas中有Series和DataFrame两种重要的数据结构。
    Series：是一个定长的字典序列。有两个基本属性：index，values
    DataFrame：类似于数据库表的一种数据结构。我们甚至可以像操作数据库表那样对DataFrame数据进行
    连接，合并，查询等等
    常用DataFrame进行数据清晰：用到的发方法有:
        去重删除：drop()，drop_duplicates(),rename()
        去空格：strip(),lstrip(),rstrip()
        变换大小写：upper(),lower(),title()
        改变数据格式：astype()
        查找空值：lsnull
        apply


"""
from pandas import DataFrame

# Scores of students
scores = {'Chinese': [66, 95, 95, 90, 80, 80],
          'English': [65, 85, 92, 80, 90, 90],
          'Math': [None, 98, 96, 77, 90, 90],
          'Total': [None, None, None, None, None, None]}
df = DataFrame(scores, index=['Zhang Fei', 'Guan Yu', 'Zhao Yun', 'Huang Zhong', 'Dian Wei','Dian Wei'],)

# Data ckeaning.
# remove the duplicated record.
df = df.drop_duplicates()
# print(df)

# Calculate the total scores.
df['Total'] = df.sum(axis=1)
print(df.describe())

#!/usr/bin/env python
# coding: utf-8
# 使用Jupyter Notebook进行脚本编写

import pandas as pd
import numpy as np
from pandas import DataFrame
from pandasql import sqldf, load_meat, load_births

# 创建数据集并进行数据清洗：清除重复的行；
# 录入数值时，空值用None或np.nan；defaultValues为字段为空时的默认值，姓名字段默认为“无姓名”，成绩字段默认为0
# 分别使用drop_duplicates和fillna方法进行去重复值及空值填充
data = {'Name': ['张飞', '关羽', '赵云', '黄忠', '典韦', '典韦'], 'Chinese': [66, 95, 95, 90, 80, 80], 'English': [65, 85, 92, 88, 90, 90], 'Maths': [None, 98, 96, 77, 90, 90]}
df = DataFrame(data, columns=['Name', 'Chinese', 'English', 'Maths'])
defaultValues = {'Name': '无姓名', 'Chinese': 0, 'English': 0, 'Maths': 0}
df = df.drop_duplicates()
# 按照默认值进行填充
df = df.fillna(defaultValues)
# 使用其他学生的平均值对空值进行填充
# df['Maths'].fillna(df['Maths'].mean(), inplace=True)

# 使用Pandas SQL进行排序语句编写
pysqldf = lambda sql: sqldf(sql, globals())
sql = "select *, Chinese + English + Maths as Total from df order by Chinese + English + Maths desc"
print(pysqldf(sql))

Pandas最主要的两个数据结构：Series，DataFrame
Series提供：index,values,map
DataFrame常用的函数：
- describe() 统计性描述
- drop_duplicates() 删除重复行
- rename(columns=...) 更名
- dropna() 删除具有空的行
- isnull() 判断空值
- fillna() 填充空值
- apply() 应用函数
- merge() 合并df
- value_counts() 统计某列的各类型个数
- read_excel() to_excel() 读取和保存excel
- set_index() 设置索引
- cut 分组

代码，工具jupyter notebook：
import pandas as pd
from pandas import DataFrame,Series
import numpy as np

grades = {'Chinese':[66,95,95,90,80,80],'English':[65,85,92,88,90,90],'Math':[np.nan,98,96,77,90,90]} #定义成绩字典
df1 = DataFrame(grades,index=['zhangfei','guanyu','zhaoyun','huangzhong','machao','machao']) #创建DataFrame

df1.describe()
df1.isnull().any() #查看哪一列有空值

df1.dropna(inplace=True) #去除空值
df1.drop_duplicates(inplace=True) #去除重复值
df1.rename(columns={'Chinese':'YuWen','English':'YingYu','Math':'ShuXue'},inplace=True) #更名

df1['ZongFen'] = df1.YingYu+df1.YuWen+df1.ShuXue #计算总分
df1

照着老师写 df2 = df2.drop(columns=['Chinese']) 这行代码后,返回结果是:
TypeError Traceback (most recent call last)
<ipython-input-25-8116650c61ac> in <module>()
----> 1 df2 = df2.drop(columns=['Chinese'])

TypeError: drop() got an unexpected keyword argument 'columns'

这个什么意思

# -*- coding: utf-8 -*-
import pandas as pd
from pandas import Series, DataFrame

data={'语文':[66, 95, 95, 90, 80, 80],'英语':[65, 85, 92, 88, 90,90], '数学':['', 98, 96, 77, 90, 90]}
df1 = DataFrame(data, index=['张飞', '关羽', '赵云','刘备','典韦','典韦'], columns=['语文','英语','数学'])
print df1
df1 = df1.drop_duplicates()
print df1

df = df1.replace(to_replace='', value=0)

print df

df['总计']=df['语文']+df['英语']+df['数学']
df.replace(to_replace=0, value='', inplace=True)
print df

#!/usr/bin/python
# vim: set fileencoding:utf-8
'''
1.对于下表的数据，请使用Pandas中的DataFrame进行创建，并对数据进行清洗。
2.同时新增一列“总和”计算每个人的三科成绩之和。
'''
import pandas as pd
from pandas import DataFrame

# 导入成绩
data = pd.read_excel(u'成绩表.xlsx')
df = DataFrame(data)
print df


# 求和，并增加一列“总和”
def addtotal(df):
    df[u'总和'] = df[u'语文'] + df[u'英语'] + df[u'数学']
    return df


# 清洗为空的数据
df1 = df.dropna()

# 清洗重复的数据
df1 = df1.drop_duplicates()

# 生成新数据结构
df1= df1.apply(addtotal, axis=1)
print(df1)

import pandas as pd

data = {'Chinese':[66,95,95,90,80,80],'English':[65,85,92,88,90,90],'Math':[None,98,96,77,90,90]}
df = pd.DataFrame(data, index=['ZhangFei', 'GuanYu', 'ZhaoYun', 'HuangZhong', 'DianWei', 'DianWei'])
print(df)

# Delete extra lines，Replace null with 0， Add new column for total
df = df.drop_duplicates()
df = df.where(df.notnull(),0)
# None will change 'Math' column to float, so that we need to clean it back.
# [[]] is a DataFrame
df[['Math']] = df[['Math']].astype(int)

'''
Method 1: Directly
'''
# df['Total'] = df['Chinese'] + df['English'] + df['Math']
# print(df)

'''
Method 2: apply a function
'''
def add_column(df):
    df['Total'] = df['Chinese'] + df['English'] + df['Math']
    return df
df = df.apply(add_column,axis=1)
print(df)

import numpy as np
import pandas as pd

scores = pd.DataFrame(
    {'姓名': ['张飞', '关羽', '赵云', '黄忠', '典韦', '典韦'], '语文': [66, 95, 95, 90, 80, 80], '英语': [65, 85, 92, 88, 90, 90],
     '数学': [np.NaN, 98, 96, 77, 90, 90], })

print(scores)

# 查找空值所在的列
isNaN = scores.isna().any() # isnull(), isnull().any()
isNaN = isNaN[isNaN == True]
print(scores[isNaN.index])

# 列的平均值填充空值
for col in isNaN.index:
    scores[col].fillna(scores[col].mean(), inplace=True)
print(scores)

# 去除不必要的行（空值）
# scores = scores.drop(index=[0])
# scores = scores.dropna()

# 去除重复行
scores = scores.drop_duplicates()
print(scores)

# 新增一列'总和'
# scores['总和'] = scores['语文'] + scores['数学'] + scores['英语']
scores['总和'] = scores.sum(axis=1)
print(scores)

df6 = pd.DataFrame(
    {"语文": [66, 95, 95, 90, 80, 80], "数学": [65, 85, 92, 88, 90, 90], "英语": [np.nan, 98, 96, 77, 90, 90]},
    index=['张飞', '关羽', '赵云', '黄忠', '典韦', '典韦']
)
# 去重
df7 = df6.drop_duplicates()
# 替换NaN值
df8 = df7.fillna(df7['英语'].mean())
# 增加一行统计
df8['sum'] = [df8.loc[name].sum() for name in df8.index]
# 按总分倒序排列
df9 = df8.sort_values(by="sum", ascending=False)
print(df9)