如何为时间序列数据框添加行？

Question

如何为时间序列数据框添加行？

4

我正在编写一个程序，将时间序列的Excel文件加载到数据框中，然后使用一些基本计算创建几个新列。我的程序有时会读取缺失某些记录月份的Excel文件。例如下面的例子，我有两个不同店铺的月销售数据。这些商店开业的月份不同，因此它们的第一个月末日期也不同。但是两者都应该具有截至2020年9月30日的月末数据。在我的文件中，由于这些月份没有销售额，因此商店BBB在2020年8月31日和9月30日没有记录。

商店名称	开业日期	所在州	所在城市	月末日期	销售额
AAA	2020年5月31日	NY	纽约市	2020年5月31日	1000
AAA	2020年5月31日	NY	纽约市	2020年6月30日	5000
AAA	2020年5月31日	NY	纽约市	2020年7月30日	3000
AAA	2020年5月31日	NY	纽约市	2020年8月31日	4000
AAA	2020年5月31日	NY	纽约市	2020年9月30日	2000
BBB	2020年6月30日	CT	哈特福德	2020年6月30日	100
BBB	2020年6月30日	CT	哈特福德	2020年7月30日	200

因此，对于任何类似的情况，我希望能够为商店BBB添加2020年8月31日和9月30日两个新行。新行应使用最近月末日期的相同开业日期、州和城市。这两个新行的销售额应设置为0。目前，我按照以下步骤进行操作：

创建名为"MaxDateData"的数据帧，其中包含每个商店的店名和最大月末日期，以及整个时间序列数据帧的最大月末日期，我将这个字段命名为“Most Recent Date”。

商店	最大月末日期	最近日期
AAA	2020年9月30日	2020年9月30日
BBB	2020年7月30日	2020年9月30日

创建名为"MostRecent"的数据帧，其中包含主时间序列数据帧中的最新行。为此，我在商店名称和最大月末日期上使用内部连接(time series dataframe and the MaxDateData)。

商店	开业月份	州	城市	月末日期	销售额	最大月末日期	最近日期
AAA	2020年5月31日	纽约	纽约市	2020年9月30日	2000	2020年9月30日	2020年9月30日
BBB	2020年6月30日	康涅狄格	哈特福德	2020年7月30日	200	2020年7月30日	2020年9月30日

使用一个where子句创建名为"RequireBackfill_MostRecent"的数据帧，以过滤最大月末日期<最近日期的商店。在本例中，“RequireBackfill_MostRecent”表将只有BBB商店的一行。

RequireBackfill_Stores_MostRecent = MaxDateData.where(MaxDateData['Max Month End Date'] <MaxDateData['Most Recent Date'])
RequireBackfill_MostRecent = MostRecent.merge(RequireBackfill_Stores_MostRecent,how='inner')

我接着使用两个嵌套的for循环来循环遍历需要填充的日期。这是利用了RequireBackfill_MostRecent数据帧，该数据帧仅包含商店BBB。

X=[]
end = MaxDateData['Most Recent Date'][0]
for i in MonthlyData['Month End Date'].unique():
    per1 = pd.date_range(start = i,  end = end, freq ='M') 
    for val in per1: 
        Data=[]
        Data = RequireBackfill_MostRecent[["Store"
                                           ,"Month Opened"
                                           ,"City"
                                           ,"State"
                                           ]].where(RequireBackfill_MostRecent['Max Month End date']==i).dropna()   

        Data["Month End Date"]= val                
        Data["Sales"]= 0
        X.append(Data)
NewData = pd.concat(X)

我接着使用concat将NewData添加到我的时间序列数据框中。

FullData_List = [MonthlyData,NewData]
FullData=pd.concat(FullData_List)

整个过程是有效的，但有没有更加高效的方法来完成这个任务？当我开始处理更大量的数据时，这可能会变得昂贵。

- user14838970

2个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Ferris · Answer 1

尝试使用 upsample 方法对 DateTime 索引进行重采样。参考链接：pandas-resample-upsample-last-date-edge-of-data

# group by `Store`
# with `Month End Date` column show be converted to DateTime

group.set_index(['Month End Date']).resample('M').asfreq()

请注意：2020年7月30日不是七月的最后一天，而是2020年7月31日。因此，使用这种方法将会有问题（将月末日期转换为真正的结束日期）。

- Joe Ferndz · Answer 2

以下是逐步操作的方法。如果您有任何问题，请告诉我。

import pandas as pd
pd.set_option('display.max_columns', None)
c = ['Store','Month Opened','State','City','Month End Date','Sales']
d = [['AAA','5/31/2020','NY','New York','5/31/2020',1000],
['AAA','5/31/2020','NY','New York','6/30/2020',5000],
['AAA','5/31/2020','NY','New York','7/30/2020',3000],
['AAA','5/31/2020','NY','New York','8/31/2020',4000],
['AAA','5/31/2020','NY','New York','9/30/2020',2000],
['BBB','6/30/2020','CT','Hartford','6/30/2020',100],
['BBB','6/30/2020','CT','Hartford','7/30/2020',200],
['CCC','3/31/2020','NJ','Cranbury','3/31/2020',1500]]

df = pd.DataFrame(d,columns = c)
df['Month Opened'] = pd.to_datetime(df['Month Opened'])
df['Month End Date'] = pd.to_datetime(df['Month End Date'])

#select last entry for each Store
df1 = df.sort_values('Month End Date').drop_duplicates('Store', keep='last').copy()

#delete all rows that have 2020-09-30. We want only ones that are less than 2020-09-30
df1 = df1[df1['Month End Date'] != '2020-09-30']

#set target end date to 2020-09-30
df1['Target_End_Date'] = pd.to_datetime ('2020-09-30')

#calculate how many rows to repeat
df1['repeats'] = df1['Target_End_Date'].dt.to_period('M').astype(int) - df1['Month End Date'].dt.to_period('M').astype(int)

#add 1 month to month end so we can start repeating from here
df1['Month End Date'] = df1['Month End Date'] + pd.DateOffset(months =1)

#set sales value as 0 per requirement
df1['Sales'] = 0

#repeat each row by the value in column repeats
df1 = df1.loc[df1.index.repeat(df1.repeats)].reset_index(drop=True)

#reset repeats to start from 0 thru n using groupby cumcouunt
#this will be used to calculate months to increment from month end date
df1['repeats'] = df1.groupby('Store').cumcount()

#update month end date based on value in repeats
df1['Month End Date'] = df1.apply(lambda x: x['Month End Date'] + pd.DateOffset(months = x['repeats']), axis=1)

#set end date to last day of the month
df1['Month End Date'] = pd.to_datetime(df1['Month End Date']) + pd.offsets.MonthEnd(0)

#drop columns that we don't need anymore. required before we concat dfs
df1.drop(columns=['Target_End_Date','repeats'],inplace=True)

#concat df and df1 to get the final dataframe
df = pd.concat([df, df1], ignore_index=True)

#sort values by Store and Month End Date
df = df.sort_values(by=['Store','Month End Date'],ignore_index=True)

print (df)

它的输出是：

   Store Month Opened State      City Month End Date  Sales
0    AAA   2020-05-31    NY  New York     2020-05-31   1000
1    AAA   2020-05-31    NY  New York     2020-06-30   5000
2    AAA   2020-05-31    NY  New York     2020-07-30   3000
3    AAA   2020-05-31    NY  New York     2020-08-31   4000
4    AAA   2020-05-31    NY  New York     2020-09-30   2000
5    BBB   2020-06-30    CT  Hartford     2020-06-30    100
6    BBB   2020-06-30    CT  Hartford     2020-07-30    200
7    BBB   2020-06-30    CT  Hartford     2020-08-30      0
8    BBB   2020-06-30    CT  Hartford     2020-09-30      0
9    CCC   2020-03-31    NJ  Cranbury     2020-03-31   1500
10   CCC   2020-03-31    NJ  Cranbury     2020-04-30      0
11   CCC   2020-03-31    NJ  Cranbury     2020-05-31      0
12   CCC   2020-03-31    NJ  Cranbury     2020-06-30      0
13   CCC   2020-03-31    NJ  Cranbury     2020-07-31      0
14   CCC   2020-03-31    NJ  Cranbury     2020-08-31      0
15   CCC   2020-03-31    NJ  Cranbury     2020-09-30      0

请注意，我添加了一个带有CCC的条目，以展示更多的变化。