logo标签列表

Pandas：计算每半年度的月度房屋销售平均值

pythonpandas
7

使用来自Zillow研究数据网站的数据，主要是城市级别的数据。数据结构包含6列城市相关信息和245列月销售价格。我使用以下代码显示了数据的样本：

import pandas as pd
from tabulate import tabulate 

df = pd.read_csv("City_Zhvi_AllHomes.csv")
c = df.columns.tolist()
cols = c[:7] 
cols.append(c[-1]) 
print (tabulate(df[cols].iloc[23:29], headers = 'keys', tablefmt = 'orgtbl'))

以上代码将打印出如下所示的示例：
|    |   RegionID | RegionName    | State   | Metro         | CountyName   |   SizeRank |   1996-04 |   2016-08 |
|----+------------+---------------+---------+---------------+--------------+------------+-----------+-----------|
| 23 |       5976 | Milwaukee     | WI      | Milwaukee     | Milwaukee    |         24 |     68100 |     99500 |
| 24 |       7481 | Tucson        | AZ      | Tucson        | Pima         |         25 |     91500 |    153000 |
| 25 |      13373 | Portland      | OR      | Portland      | Multnomah    |         26 |    121100 |    390500 |
| 26 |      33225 | Oklahoma City | OK      | Oklahoma City | Oklahoma     |         27 |     64900 |    130500 |
| 27 |      40152 | Omaha         | NE      | Omaha         | Douglas      |         28 |     88900 |    143800 |
| 28 |      23429 | Albuquerque   | NM      | Albuquerque   | Bernalillo   |         29 |    115400 |    172000 |


df的一部分是时间序列，关键在于将时间相关的列与其他列分开，使用pandas中的resample和to_datetime方法。

假设我们只对1998-2000年的销售额进行总结， 这将使我们能够选择列。

# seperate time columns and convert their names to datetime
tdf = df[df.columns[6:]].rename(columns=pd.to_datetime)

# find the columns in the period 1998-2000
cols = tdf.columns
sel_cols = cols[(cols > '1997-12-31') & (cols < '2000')]

# select the columns, resample on columns
# calculate the mean 
# rename the columns the way we like

mdf = tdf[sel_cols].resample('6M',axis=1).mean().rename(
    columns=lambda x: '{:}${:}'.format(x.year, [1, 2][x.quarter > 2]))

# reattach non-time columns
mdf[df.columns[:6]] = df[df.columns[:6]]

print (tabulate(mdf[mdf.columns[0:9]].iloc[
       23:29], headers='keys', tablefmt='orgtbl'))

上面的代码将会打印如下所示的一个样例：
|    |   1998$1 |   1998$2 |   1999$1 |   1999$2 |   2000$1 |   RegionID | RegionName    | State   | Metro         |
|----+----------+----------+----------+----------+----------+------------+---------------+---------+---------------|
| 23 |    71900 |  72483.3 |  72616.7 |  74266.7 |    75920 |       5976 | Milwaukee     | WI      | Milwaukee     |
| 24 |    94200 |  95133.3 |  96533.3 |  99100   |   100600 |       7481 | Tucson        | AZ      | Tucson        |
| 25 |   139000 | 141900   | 145233   | 148900   |   151980 |      13373 | Portland      | OR      | Portland      |
| 26 |    68500 |  69616.7 |  72016.7 |  73616.7 |    74900 |      33225 | Oklahoma City | OK      | Oklahoma City |
| 27 |    98200 |  99250   | 103367   | 109083   |   112160 |      40152 | Omaha         | NE      | Omaha         |
| 28 |   121000 | 122050   | 122833   | 123633   |   124420 |      23429 | Albuquerque   | NM      | Albuquerque   |

问题是：
重新取样结果的最后一列显示了年份“2000”，尽管选择的是<'2000'，为什么会这样呢？
编辑： 只是为了好玩，我包括了一种更“pandorable”的方法来完成上述操作。
import pandas as pd

housing = pd.read_csv('City_Zhvi_AllHomes.csv',
    index_col=list(range(6))).filter(
    regex='199[8-9]-[0-1][0-9]').rename(
    columns=pd.to_datetime).resample('2Q',
                    closed='left',axis=1).mean().rename(
    columns=lambda x: str(x.to_period('2Q')).replace(
        'Q','$').replace('2','1').replace('4','2')).reset_index()

这将会得到我们想要的结果，housing.iloc[23:27,4:] 的输出如下所示。
|    | CountyName   |   SizeRank |   1998$1 |   1998$2 |   1999$1 |   1999$2 |
|----+--------------+------------+----------+----------+----------+----------|
| 23 | Milwaukee    |         24 |  72366.7 |  72583.3 |  73916.7 |  75750   |
| 24 | Pima         |         25 |  94883.3 |  96183.3 |  98783.3 | 100450   |
| 25 | Multnomah    |         26 | 141167   | 144733   | 148183   | 151767   |
| 26 | Oklahoma     |         27 |  69300   |  71550   |  73466.7 |  74766.7 |
1
你可以使用 tdf[sel_cols].resample('6M', axis=1).mean().rename 替换 tdf[sel_cols].T.resample('6M').mean().T.rename 中的 axis=1 参数。 - jezrael
那么问题是什么？改进你的代码？还是其他什么？ - jezrael
知道这个很好，谢谢！ - sgDysregulation
好的！抱歉，我忘记了问题，我已经编辑过了！ - sgDysregulation
1
提供此转换的最简单答案在这里：https://dev59.com/pFkS5IYBdhLWcg3wAiN8。还可以在这里查找将“Q”转换为“q”：https://stackoverflow.com/questions/42330848/convert-pandas-period-period-type-column-names-to-lowercase。 - pushya
2个回答
6
考虑使用 pandas 的 resample 的 closed 参数来决定:

区间端点哪一侧是闭合的

下方使用 left，其中 6 个月的截止日期为 6/30 和 12/31，而不是 1/1 和 7/1，这样可以得到 2000 的值:
mdf = tdf[sel_cols].T.resample('6M', closed='left').mean().T.rename(
    columns=lambda x: '{:}${:}'.format(x.year, [1, 2][x.quarter > 2]))

mdf[df.columns[:6]] = df[df.columns[:6]]
print(mdf.head())

#           1998$1         1998$2         1999$1         1999$2  RegionID    RegionName State                           Metro    CountyName  SizeRank
# 0            NaN            NaN            NaN            NaN      6181      New York    NY                        New York        Queens         1
# 1  169183.333333  179166.666667  189116.666667  198466.666667     12447   Los Angeles    CA  Los Angeles-Long Beach-Anaheim   Los Angeles         2
# 2  117700.000000  121666.666667  125550.000000  133000.000000     17426       Chicago    IL                         Chicago          Cook         3
# 3   50550.000000   50650.000000   51150.000000   51866.666667     13271  Philadelphia    PA                    Philadelphia  Philadelphia         4
# 4   97583.333333  101083.333333  104816.666667  108566.666667     40326       Phoenix    AZ                         Phoenix      Maricopa         5

print(mdf[mdf['Metro'].isin(['Milwaukee', 'Tucson', 'Portland', 'Oklahoma City', 'Omaha', 'Albuquerque'])].head())

#            1998$1         1998$2         1999$1         1999$2  RegionID   RegionName State        Metro  CountyName  SizeRank
# 23   72366.666667   72583.333333   73916.666667   75750.000000      5976    Milwaukee    WI    Milwaukee   Milwaukee        24
# 24   94883.333333   96183.333333   98783.333333  100450.000000      7481       Tucson    AZ       Tucson        Pima        25
# 25  141166.666667  144733.333333  148183.333333  151766.666667     13373     Portland    OR     Portland   Multnomah        26
# 26   98950.000000  102450.000000  108016.666667  112116.666667     40152        Omaha    NE        Omaha     Douglas        27
# 27  121816.666667  122666.666667  123550.000000  124333.333333     23429  Albuquerque    NM  Albuquerque  Bernalillo        28
顺便考虑使用 melt 将数据转换为长格式，按半年聚合，然后再使用 pivot_table 转换回宽格式。诚然，在这里性能有所下降，但可读性更高（主要是 Half_Year 字符串拼接是瓶颈）。您可以获得其他聚合和/或建模的长格式数据集：
import pandas as pd
import datetime as dt
import numpy as np

# MELT (WIDE --> LONG)
idcols = ['RegionID', 'RegionName', 'State', 'Metro']
mdf = pd.melt(df, id_vars=idcols + ['CountyName', 'SizeRank'], var_name='Year_Month', value_name='Sale_Amt').reset_index()

# CALCULATE HALF_YEAR STRING
mdf['Year_Month'] = pd.to_datetime(mdf['Year_Month'])
mdf['Half_Year'] = mdf['Year_Month'].dt.year.astype(str) + '$' + np.where(mdf['Year_Month'].dt.month <= 6, 1, 2).astype(str)

# FILTER DATASET BY DATE INTERVAL
mdf = mdf[mdf['Year_Month'].between('1998-01-01', '1999-12-31')]

#  GROUP BY AGGREGATION OF HOUSE SALES
mdf = mdf.groupby(idcols + ['Half_Year'])['Sale_Amt'].mean().reset_index()

# PIVOT (LONG --> WIDE)
pvtdf = mdf.pivot_table(index=idcols, columns='Half_Year', values='Sale_Amt', aggfunc=sum).reset_index()

输出

metros = ['Milwaukee', 'Tucson', 'Portland', 'Oklahoma City', 'Omaha', 'Albuquerque']
print(pvtdf[(pvtdf['RegionName'].isin(metros)) &  (pvtdf['Metro'].isin(metros))])

# Half_Year  RegionID   RegionName State        Metro         1998$1         1998$2         1999$1         1999$2
# 430            5976    Milwaukee    WI    Milwaukee   72366.666667   72583.333333   73916.666667   75750.000000
# 680            7481       Tucson    AZ       Tucson   94883.333333   96183.333333   98783.333333  100450.000000
# 1584          13373     Portland    OR     Portland  141166.666667  144733.333333  148183.333333  151766.666667
# 2923          23429  Albuquerque    NM  Albuquerque  121816.666667  122666.666667  123550.000000  124333.333333
# 5473          40152        Omaha    NE        Omaha   98950.000000  102450.000000  108016.666667  112116.666667
关于melt函数，性能通常比可读性更重要，特别是涉及大数据集时。这里的数据集超过了10,000条，所以melt可能还可以使用。但如果大小超过那个范围，那么使用它就不切实际了。 - sgDysregulation
我发现这非常有用，直到我添加了closed='left'参数之前，我也得到了一些奇怪的日期。然而我不明白它是如何工作的，你能再详细解释一下吗？“截止日期为6/30和12/31而不是1/1和7/1”，这些分数的意义是什么？ - Abimael Domínguez
1
@AbimaelDomínguez，这些分数是以美国月份为先的日期格式：MM/YY。在resample中使用6M周期，这会根据closed参数略微变化。 - Parfait
2

您可以使用新的列名称和自定义格式来使用groupby，因为如果使用resample按6M返回2000（看起来像是bug）：

c = df.columns.tolist()
cols = c[:7] 
cols.append(c[-1]) 
print (cols)
['RegionID', 'RegionName', 'State', 'Metro', 'CountyName', 'SizeRank', '1996-04', '2016-11']

#set to index all NOT date columns
tdf = df.set_index(df.columns.tolist()[:6])
tdf.columns = pd.to_datetime(tdf.columns).to_period('M')

# find the columns in the period 1998-2000
cols = tdf.columns
sel_cols = cols[(cols > '1997-12') & (cols < '2000')]
print (sel_cols)
PeriodIndex(['1998-01', '1998-02', '1998-03', '1998-04', '1998-05', '1998-06',
             '1998-07', '1998-08', '1998-09', '1998-10', '1998-11', '1998-12',
             '1999-01', '1999-02', '1999-03', '1999-04', '1999-05', '1999-06',
             '1999-07', '1999-08', '1999-09', '1999-10', '1999-11', '1999-12'],
            dtype='period[M]', freq='M')

#change format columns
a = tdf[sel_cols].rename(columns=lambda x: '{:}${:}'.format(x.year, [1, 2][x.quarter > 2]))
print (a.columns)
Index(['1998$1', '1998$1', '1998$1', '1998$1', '1998$1', '1998$1', '1998$2',
       '1998$2', '1998$2', '1998$2', '1998$2', '1998$2', '1999$1', '1999$1',
       '1999$1', '1999$1', '1999$1', '1999$1', '1999$2', '1999$2', '1999$2',
       '1999$2', '1999$2', '1999$2'],
      dtype='object')

#groupby by a with new format
mdf = a.groupby(a.columns, axis=1).mean().reset_index()


print (mdf.head())
   RegionID    RegionName State                           Metro    CountyName  \
0      6181      New York    NY                        New York        Queens   
1     12447   Los Angeles    CA  Los Angeles-Long Beach-Anaheim   Los Angeles   
2     17426       Chicago    IL                         Chicago          Cook   
3     13271  Philadelphia    PA                    Philadelphia  Philadelphia   
4     40326       Phoenix    AZ                         Phoenix      Maricopa   

   SizeRank         1998$1         1998$2         1999$1         1999$2  
0         1            NaN            NaN            NaN            NaN  
1         2  169183.333333  179166.666667  189116.666667  198466.666667  
2         3  117700.000000  121666.666667  125550.000000  133000.000000  
3         4   50550.000000   50650.000000   51150.000000   51866.666667  
4         5   97583.333333  101083.333333  104816.666667  108566.666667
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接