第十四章 pandas官方文档0.22中文教程---Tutorials（lessons 3 ），个人渣翻译

#####Lesson 3 创建函数，从EXCEL读取，导出到EXCEL - 异常值 - Lambda函数-切片和切块数据。

import pandas as pd
import numpy.random as np
import matplotlib.pyplot as plt

我们将创建自己的测试数据进行分析

# set seed
np.seed(111)

# Function to generate test data
def CreateDataSet(Number=1):
    
    Output = []
    
    for i in range(Number):
        
        # Create a weekly (mondays) date range
        rng = pd.date_range(start='1/1/2009', end='12/31/2012', freq='W-MON')
        
        # Create random data
        data = np.randint(low=25,high=1000,size=len(rng))
        
        # Status pool
        status = [1,2,3]
        
        # Make a random list of statuses
        random_status = [status[np.randint(low=0,high=len(status))] for i in range(len(rng))]
        
        # State pool
        states = ['GA','FL','fl','NY','NJ','TX']
        
        # Make a random list of states 
        random_states = [states[np.randint(low=0,high=len(states))] for i in range(len(rng))]
    
        Output.extend(zip(random_states, random_status, data, rng))
        
    return Output

现在我们有了生成测试数据的函数，我们可以创建一些数据并将其插入到dataframe中。

dataset = CreateDataSet(4)
df = pd.DataFrame(data=dataset, columns=['State','Status','CustomerCount','StatusDate'])
df.info()

我们现在将这个dataframe保存到一个Excel文件中，然后将它返回到dataframe。我们这样做只是为了向您展示如何读写Excel文件。
我们不会将dataframe的索引值写入Excel文件，因为它们不是我们初始测试数据集的一部分。

# Save results to excel
df.to_excel('Lesson3.xlsx', index=False)
print('Done')

我们将使用read_excel函数从Excel文件中读取数据。该函数允许您按名称或位置读取特定的选项卡。

# Parse a specific sheet
df = pd.read_excel('Lesson3.xlsx', 0, index_col='StatusDate')
df.dtypes

df.index

df.head()

本节试图清理数据以进行分析
①确保state列都是大写的。
②只选择帐户状态为“1”的记录
③将(NJ和NY)合并到state列中的NY。
④删除任何异常值(数据集中的任何奇怪结果)
让我们快速看一下一些状态值是大写的，有些是小写的。

df['State'].unique()

要将所有状态值转换为大写，我们将使用upper()函数和dataframe的apply属性。lambda函数简单地将上部函数应用于状态列中的每个值。

# Clean State Column, convert to upper case
df['State'] = df.State.apply(lambda x: x.upper())
df['State'].unique()

# Only grab where Status == 1
mask = df['Status'] == 1
df = df[mask]

简单得把NJ变成NY…
[df.State == ‘NJ’] --在状态列中找到它们等于NJ的所有记录
df.State[df.State == ‘NJ’] = ‘NY’ --在状态列中它们等于NJ的所有记录中，将它们替换为NY

# Convert NJ to NY
mask = df.State == 'NJ'
df['State'][mask] = 'NY'

现在我们可以看到我们有一个更干净的数据集

df['State'].unique()

在这一点上，我们可能想要绘制数据以检查数据中的异常值或不一致。我们将使用dataframe的plot()属性。
正如你从下图所看到的，它不是非常确定的，可能是我们需要执行更多的数据准备的标志。

df['CustomerCount'].plot(figsize=(15,5));

如果我们看一下数据，我们就会开始意识到同一个状态、状态和状态组合有多个值。这可能意味着您正在处理的数据是脏的/坏的/不准确的，但是我们将不这么认为。我们可以假设这个数据集是更大数据集的子集，如果我们只是将CustomerCount列中的值添加到每个状态、StatusDate和Status中，我们将得到每天的客户总数。

sortdf = df[df['State']=='NY'].sort_index(axis=0)
sortdf.head(10)

现在我们的任务是创建一个新的dataframe，以压缩数据，这样我们就有了每个状态和StatusDate的每日客户计数。我们可以忽略状态列，因为这个列中的所有值都是值1。为此，我们将使用dataframe的函数groupby和sum()。
注意，我们必须使用reset_index。如果我们不这样做，我们就不能同时使用状态和状态，因为groupby函数只期望列作为输入。reset_index函数将索引StatusDate返回到dataframe中的列。

# Group by State and StatusDate
Daily = df.reset_index().groupby(['State','StatusDate']).sum()
Daily.head()

（我不知道为什么要必须使用reset_index，不用它结果也一样）

state和StatusDate列将自动被放置在每日dataframe的索引中。您可以将索引看作是数据库表的主键，但不考虑具有惟一值的约束。您将看到的索引中的列允许我们轻松地选择、绘制和执行数据的计算。
下面我们删除状态栏，因为它都等于1，不再需要。

del Daily['Status']
Daily.head()

# What is the index of the dataframe
Daily.index

# Select the State index
Daily.index.levels[0]

# Select the StatusDate index
Daily.index.levels[1]

现在让我们绘制每个状态的数据。
通过打破状态栏的图表你可以看到，我们对数据的样子有了更清晰的了解。你能发现任何异常值吗?

Daily.loc['FL'].plot()
Daily.loc['GA'].plot()
Daily.loc['NY'].plot()
Daily.loc['TX'].plot()

（多重索引画图还是用了.loc定位）

我们还可以在特定的日期(比如2012年)绘制数据。我们现在可以清楚地看到，这些州的数据到处都是。由于数据由每周客户数组成，因此数据的可变性似乎是可疑的。在本教程中，我们将假设糟糕的数据并继续进行。

Daily.loc['FL']['2012':].plot()
Daily.loc['GA']['2012':].plot()
Daily.loc['NY']['2012':].plot()
Daily.loc['TX']['2012':].plot();

我们假设每个月客户数量应该保持相对稳定。在那个月的特定范围之外的任何数据都将从数据集中删除。最终的结果应该是没有峰值的平滑的图形。
StateYearMonth–这里我们按state、年和月的statusdate分组
Daily[‘Outlier’]–一个布尔值(True或False)值，让我们知道CustomerCount列中的值是否超出可接受范围。
我们将使用属性transform而不是apply。其原因是，transform将保持dataframe的形状(行号和列)不变，而apply不会。通过查看前面的图，我们可以发现它们不像高斯分布，这意味着我们不能使用像平均值和stDev这样的汇总统计信息。我们用百分位数。注意，我们有消除良好数据的风险。

# Calculate Outliers
StateYearMonth = Daily.groupby([Daily.index.get_level_values(0), Daily.index.get_level_values(1).year, Daily.index.get_level_values(1).month])
Daily['Lower'] = StateYearMonth['CustomerCount'].transform( lambda x: x.quantile(q=.25) - (1.5*x.quantile(q=.75)-x.quantile(q=.25)) )
Daily['Upper'] = StateYearMonth['CustomerCount'].transform( lambda x: x.quantile(q=.75) + (1.5*x.quantile(q=.75)-x.quantile(q=.25)) )
Daily['Outlier'] = (Daily['CustomerCount'] < Daily['Lower']) | (Daily['CustomerCount'] > Daily['Upper']) 

# Remove Outliers
Daily = Daily[Daily['Outlier'] == False]

dataframe名为Daily，它将保存每日汇总的客户数。原始数据(df)每天有多个记录。我们剩下的数据集是由状态和状态数据进行索引的。Outlier列应该等于False，表示记录不是异常值。

Daily.head()

我们创建了一个单独的dataframe，它以StatusDate的形式将每天的dataframe命名为ALL。本质上，我们去掉了状态列。Max列表示每个月的最大客户数。最大列用来平滑图形。

# Combine all markets

# Get the max customer count by Date
ALL = pd.DataFrame(Daily['CustomerCount'].groupby(Daily.index.get_level_values(1)).sum())
ALL.columns = ['CustomerCount'] # rename column

# Group by Year and Month
YearMonth = ALL.groupby([lambda x: x.year, lambda x: x.month])

# What is the max customer count per Year and Month
ALL['Max'] = YearMonth['CustomerCount'].transform(lambda x: x.max())
ALL.head()

正如您从上面的所有dataframe中看到的，在2009年1月，客户的最大数量是901。如果我们使用了apply，那么我们就会得到一个dataframe(年份和月份)作为索引，并且只使用值为901的Max列。

---

此外，衡量目前的客户数量是否达到了公司设定的目标也有好处。这里的任务是直观地显示当前的客户数量是否达到了下面列出的目标。我们将把目标称为BHAG(庞大的年度目标)。
12/31/2011 - 1,000 customers
12/31/2012 - 2,000 customers
12/31/2013 - 3,000 customers
我们将使用date_range函数来创建日期
通过选择一个或一年的频率，我们将能够从上面得到三个目标日期

# Create the BHAG dataframe
data = [1000,2000,3000]
idx = pd.date_range(start='12/31/2011', end='12/31/2013', freq='A')
BHAG = pd.DataFrame(data, index=idx, columns=['BHAG'])
BHAG

将dataframes与我们在上一课中所学到的结合起来，使用concat函数进行简单操作。记住，当我们选择轴= 0时，我们会追加行。

# Combine the BHAG and the ALL data set 
combined = pd.concat([ALL,BHAG], axis=0)
combined = combined.sort_index(axis=0)
combined.tail()

fig, axes = plt.subplots(figsize=(12, 7))

combined['BHAG'].fillna(method='pad').plot(color='green', label='BHAG')
combined['Max'].plot(color='blue', label='All Markets')
plt.legend(loc='best');

还需要预测明年的客户数量，我们可以在几个简单的步骤中做到这一点。我们将首先将合并的dataframe按年分组，并将该年的最大客户数放在一起。这将使我们每年有一行。

# Group by Year and then get the max value per year
Year = combined.groupby(lambda x: x.year).max()
Year

# Add a column representing the percent change per year
Year['YR_PCT_Change'] = Year['Max'].pct_change(periods=1)
Year

为了获得明年的最终客户数量，我们将假定我们目前的增长率保持不变。然后，我们将增加这几年的客户数量，这将是我们明年的预测。
创建每个状态的单个图

# First Graph
ALL['Max'].plot(figsize=(10, 5));plt.title('ALL Markets')

# Last four Graphs
fig, axes = plt.subplots(nrows=2, ncols=2, figsize=(20, 10))
fig.subplots_adjust(hspace=1.0) ## Create space between plots

Daily.loc['FL']['CustomerCount']['2012':].fillna(method='pad').plot(ax=axes[0,0])
Daily.loc['GA']['CustomerCount']['2012':].fillna(method='pad').plot(ax=axes[0,1]) 
Daily.loc['TX']['CustomerCount']['2012':].fillna(method='pad').plot(ax=axes[1,0]) 
Daily.loc['NY']['CustomerCount']['2012':].fillna(method='pad').plot(ax=axes[1,1]) 

# Add titles
axes[0,0].set_title('Florida')
axes[0,1].set_title('Georgia')
axes[1,0].set_title('Texas')
axes[1,1].set_title('North East');