Как добавить строки для фрейма данных таймсерии?
Dec 31 2020
Я пишу программу, которая загружает файл timeseries excel в фрейм данных, затем я создаю несколько новых столбцов, используя некоторые базовые вычисления. Моя программа иногда будет читать файлы Excel, в которых для некоторых записей отсутствуют месяцы. Итак, в примере ниже у меня есть данные о ежемесячных продажах для двух разных магазинов. Магазины открываются в разные месяцы, поэтому дата окончания первого месяца будет отличаться. Но оба должны иметь данные на конец месяца до 30.09.2020. В моем файле Store BBB не имеет записей за 31.08.2020 и 30.09.2020, потому что в эти месяцы не было продаж.
| Магазин | Месяц открытия | Состояние | Город | Дата окончания месяца | Продажи |
|---|---|---|---|---|---|
| AAA | 31.05.2020 | Нью-Йорк | Нью-Йорк | 31.05.2020 | 1000 |
| AAA | 31.05.2020 | Нью-Йорк | Нью-Йорк | 30.06.2020 | 5000 |
| AAA | 31.05.2020 | Нью-Йорк | Нью-Йорк | 30.07.2020 | 3000 |
| AAA | 31.05.2020 | Нью-Йорк | Нью-Йорк | 31.08.2020 | 4000 |
| AAA | 31.05.2020 | Нью-Йорк | Нью-Йорк | 30.09.2020 | 2000 г. |
| BBB | 30.06.2020 | CT | Хартфорд | 30.06.2020 | 100 |
| BBB | 30.06.2020 | CT | Хартфорд | 30.07.2020 | 200 |
Поэтому для подобных случаев я хочу добавить две строки для Store BBB для 31 августа и 30 сентября. В новых строках должен использоваться тот же месяц открытия, штат и город, начиная с последней даты окончания месяца. Продажи должны быть установлены на 0 для обеих новых строк. На данный момент я делаю следующие шаги:
- Создайте фрейм данных «MaxDateData» с именем магазина и максимальной датой окончания месяца для каждого магазина, а также максимальной датой окончания месяца для всего фрейма данных временного ряда. Я называю это поле «Самая последняя дата».
| Магазин | Максимальная дата окончания месяца | Самая последняя дата |
|---|---|---|
| AAA | 30.09.2020 | 30.09.2020 |
| BBB | 30.07.2020 | 30.09.2020 |
- Создайте фрейм данных "MostRecent" с самой последней строкой из основного фрейма данных временного ряда. Для этого я выполняю внутреннее соединение между фреймом данных временного ряда и MaxDateData в названии магазина и максимальной дате окончания месяца.
| Магазин | Месяц открытия | Состояние | Город | Дата окончания месяца | Продажи | Максимальная дата окончания месяца | Самая последняя дата |
|---|---|---|---|---|---|---|---|
| AAA | 31.05.2020 | Нью-Йорк | Нью-Йорк | 30.09.2020 | 2000 г. | 30.09.2020 | 30.09.2020 |
| BBB | 30.06.2020 | CT | Хартфорд | 30.07.2020 | 200 | 30.07.2020 | 30.09.2020 |
- Создайте фрейм данных «RequireBackfill_MostRecent», используя предложение where для фильтрации хранилищ, в которых максимальная дата окончания месяца <самая последняя дата. См. Код ниже. Итак, в этом примере в таблице 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, который будет содержать только Store 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)
- Затем я добавляю NewData в свой фрейм данных таймсерии, используя concat
FullData_List = [MonthlyData,NewData]
FullData=pd.concat(FullData_List)
Весь этот процесс работает, но есть ли более эффективный способ сделать это? Это может стать дорогостоящим, когда я начну работать с большими данными.
Ответы
Ferris Dec 31 2020 at 14:08
- просто попробуйте
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()
- Обратите внимание:
7/30/2020это не конец июля.7/31/2020является. поэтому использование этого метода7/30/2020будет проблемой (преобразуйте дату окончания месяца в истинную дату окончания).
JoeFerndz Dec 31 2020 at 16:14
Вот пошаговый подход, чтобы сделать это. Если у вас есть вопросы, дайте мне знать.
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, чтобы показать вам больше вариантов.