如何将数据帧连接到具有不同索引的序列

Question

我有一个熊猫数据框，看起来像：

              High      Low      ...       Volume  OpenInterest
2018-01-02   983.25   975.50      ...         8387         67556
2018-01-03   986.75   981.00      ...         7447         67525
2018-01-04   985.25   977.00      ...         8725         67687
2018-01-05   990.75   984.00      ...         7948         67975

我计算Average True Range并将其保存为一个系列：

    i = 0
    TR_l = [0]
    while i < (df.shape[0]-1):
        #TR = max(df.loc[i + 1, 'High'], df.loc[i, 'Close']) - min(df.loc[i + 1, 'Low'], df.loc[i, 'Close'])
        TR = max(df['High'][i+1], df['Close'][i]) - min(df['Low'][i+1], df['Close'][i])
        TR_l.append(TR)
        i = i + 1
    TR_s = pd.Series(TR_l)
    ATR = pd.Series(TR_s.ewm(span=n, min_periods=n).mean(), name='ATR_' + str(n))

对于14周期滚动窗口，ATR看起来像：

0            NaN
1            NaN
2            NaN
3            NaN
4            NaN
5            NaN
6            NaN
7            NaN
8            NaN
9            NaN
10           NaN
11           NaN
12           NaN
13      8.096064
14      7.968324
15      8.455205
16      9.046418
17      8.895405
18      9.088769
19      9.641879
20      9.516764

但当我这么做的时候：

df = df.join(ATR)

df中的ATR列都是NaN。这是因为数据帧和ATR之间的索引不同。有没有办法将ATR列添加到数据帧中？你知道吗

Answer 1

考虑shift以避免跨行和列表构建的while循环。下面使用联合太平洋铁路公司（UNP）的铁路库存数据来证明：

import pandas as pd
import pandas_datareader as pdr

stock_df = pdr.get_data_yahoo('UNP').loc['2019-01-01':'2019-03-29']

# SHIFT DATA ONE DAY BACK AND JOIN TO ORIGINAL DATA
stock_df = stock_df.join(stock_df.shift(-1), rsuffix='_future')

# CALCULATE TR DIFFERENCE BY ROW
stock_df['TR'] = stock_df.apply(lambda x: max(x['High_future'], x['Close']) - min(x['Low_future'], x['Close']), axis=1)

# CALCULATE EWM MEAN
n = 14
stock_df['ATR'] = stock_df['TR'].ewm(span=n, min_periods=n).mean()

输出

print(stock_df.head(20))
#                  High         Low        Open       Close      Volume   Adj Close  High_future  Low_future  Open_future  Close_future  Volume_future  Adj Close_future         TR       ATR
# Date                                                                                                                                                                                        
# 2019-01-02  138.320007  134.770004  135.649994  137.779999   3606300.0  137.067413   136.750000  132.169998   136.039993    132.679993      5684500.0        131.993790   5.610001       NaN
# 2019-01-03  136.750000  132.169998  136.039993  132.679993   5684500.0  131.993790   138.580002  134.520004   134.820007    137.789993      5649900.0        137.077362   5.900009       NaN
# 2019-01-04  138.580002  134.520004  134.820007  137.789993   5649900.0  137.077362   139.229996  136.259995   137.330002    138.649994      4034200.0        137.932907   2.970001       NaN
# 2019-01-07  139.229996  136.259995  137.330002  138.649994   4034200.0  137.932907   152.889999  149.039993   151.059998    150.750000     10558800.0        149.970337  14.240005       NaN
# 2019-01-08  152.889999  149.039993  151.059998  150.750000  10558800.0  149.970337   151.059998  148.610001   150.289993    150.360001      4284600.0        149.582352   2.449997       NaN
# 2019-01-09  151.059998  148.610001  150.289993  150.360001   4284600.0  149.582352   155.289993  149.009995   149.899994    154.660004      6444600.0        153.860123   6.279999       NaN
# 2019-01-10  155.289993  149.009995  149.899994  154.660004   6444600.0  153.860123   155.029999  153.089996   153.639999    153.210007      3845200.0        152.417618   1.940002       NaN
# 2019-01-11  155.029999  153.089996  153.639999  153.210007   3845200.0  152.417618   154.240005  151.649994   152.229996    153.889999      3507100.0        153.094101   2.590012       NaN
# 2019-01-14  154.240005  151.649994  152.229996  153.889999   3507100.0  153.094101   154.360001  151.740005   153.789993    152.479996      4685100.0        151.691391   2.619995       NaN
# 2019-01-15  154.360001  151.740005  153.789993  152.479996   4685100.0  151.691391   153.729996  150.910004   152.910004    151.970001      4053200.0        151.184021   2.819992       NaN
# 2019-01-16  153.729996  150.910004  152.910004  151.970001   4053200.0  151.184021   154.919998  150.929993   151.110001    154.639999      4075400.0        153.840210   3.990005       NaN
# 2019-01-17  154.919998  150.929993  151.110001  154.639999   4075400.0  153.840210   158.800003  155.009995   155.539993    158.339996      5003900.0        157.521072   4.160004       NaN
# 2019-01-18  158.800003  155.009995  155.539993  158.339996   5003900.0  157.521072   157.199997  154.410004   156.929993    155.020004      6052900.0        154.218262   3.929993       NaN
# 2019-01-22  157.199997  154.410004  156.929993  155.020004   6052900.0  154.218262   156.020004  152.429993   155.449997    154.330002      4858000.0        153.531830   3.590012  4.011254
# 2019-01-23  156.020004  152.429993  155.449997  154.330002   4858000.0  153.531830   160.759995  156.009995   160.039993    160.339996      9222400.0        159.510742   6.429993  4.376440
# 2019-01-24  160.759995  156.009995  160.039993  160.339996   9222400.0  159.510742   162.000000  160.220001   161.460007    160.949997      7770700.0        160.117584   1.779999  3.991223
# 2019-01-25  162.000000  160.220001  161.460007  160.949997   7770700.0  160.117584   160.789993  159.339996   160.000000    159.899994      3733800.0        159.073013   1.610001  3.643168
# 2019-01-28  160.789993  159.339996  160.000000  159.899994   3733800.0  159.073013   160.929993  158.750000   160.039993    160.169998      3436900.0        159.341614   2.179993  3.432011
# 2019-01-29  160.929993  158.750000  160.039993  160.169998   3436900.0  159.341614   161.889999  159.440002   161.089996    160.820007      4112200.0        159.988266   2.449997  3.291831
# 2019-01-30  161.889999  159.440002  161.089996  160.820007   4112200.0  159.988266   160.990005  157.020004   160.750000    159.070007      7438600.0        158.247314   3.970001  3.387735