我有以下數據框：這里Hotel_id和Chef_Id都是唯一的（都可以作為主鍵）。我需要填充 Month_Year 列中缺失的行（應該在每個 id 的第一個日期和最后一個日期之間添加連續的month_year，然后向前填充其他相應的列值，我需要這樣的內容：在這里我解釋了一些 id，但我需要將這個概念應用于數據框中的每個 id。請讓我知道解決方案。@r-beginners，請找到以下數據供您參考：Hotel_id? ? Month_Year? ? ? last_review_date2400614? ? ?May-2015? ? ? ? March-20162400614? ? ?June-2015? ? ? ?March-20162400614? ? ?December-2015? ?March-20162400614? ? ?January-2016? ? March-20162400614? ? ?March-2016? ? ? March-20162400133? ? ?April-2016? ? ? May-20172400133? ? ?June-2016? ? ? ?May-20172400133? ? ?August-2016? ? ?May-20172400133? ? ?January-2017? ? May-20172400133? ? ?April-2017? ? ? May-20172400133? ? ?May-2017? ? ? ? May-20172400178? ? ?June-2015? ? ? ?April-20182400178? ? ?July-2016? ? ? ?April-20182400178? ? ?August-2016? ? ?April-20182400178? ? ?January-2017? ? April-20182400178? ? ?March-2017? ? ? April-20182400178? ? ?April-2018? ? ? April-2018
2 回答
一只萌萌小番薯
TA貢獻1795條經驗 獲得超7個贊
GroupBy.transform與以下一起使用GroupBy.last：
df['last_review_date'] = df.groupby('Hotel_id')['Month_Year'].transform('last')
print (df)
? ? Hotel_id? ? ?Month_Year last_review_date
0? ? 2400614? ? ? ?May-2015? ? ? ?March-2016
1? ? 2400614? ? ? June-2015? ? ? ?March-2016
2? ? 2400614? December-2015? ? ? ?March-2016
3? ? 2400614? ?January-2016? ? ? ?March-2016
4? ? 2400614? ? ?March-2016? ? ? ?March-2016
5? ? 2400133? ? ?April-2016? ? ? ? ?May-2017
6? ? 2400133? ? ? June-2016? ? ? ? ?May-2017
7? ? 2400133? ? August-2016? ? ? ? ?May-2017
8? ? 2400133? ?January-2017? ? ? ? ?May-2017
9? ? 2400133? ? ?April-2017? ? ? ? ?May-2017
10? ?2400133? ? ? ?May-2017? ? ? ? ?May-2017
11? ?2400178? ? ? June-2015? ? ? ?April-2018
12? ?2400178? ? ? July-2016? ? ? ?April-2018
13? ?2400178? ? August-2016? ? ? ?April-2018
14? ?2400178? ?January-2017? ? ? ?April-2018
15? ?2400178? ? ?March-2017? ? ? ?April-2018
16? ?2400178? ? ?April-2018? ? ? ?April-2018
另一個想法是將值轉換為日期時間并返回每組的最大值：
df['Month_Year'] = pd.to_datetime(df['Month_Year'], format='%B-%Y')
df['last_review_date'] = df.groupby('Hotel_id')['Month_Year'].transform('max')
print (df)
? ? Hotel_id Month_Year last_review_date
0? ? 2400614 2015-05-01? ? ? ?2016-03-01
1? ? 2400614 2015-06-01? ? ? ?2016-03-01
2? ? 2400614 2015-12-01? ? ? ?2016-03-01
3? ? 2400614 2016-01-01? ? ? ?2016-03-01
4? ? 2400614 2016-03-01? ? ? ?2016-03-01
5? ? 2400133 2016-04-01? ? ? ?2017-05-01
6? ? 2400133 2016-06-01? ? ? ?2017-05-01
7? ? 2400133 2016-08-01? ? ? ?2017-05-01
8? ? 2400133 2017-01-01? ? ? ?2017-05-01
9? ? 2400133 2017-04-01? ? ? ?2017-05-01
10? ?2400133 2017-05-01? ? ? ?2017-05-01
11? ?2400178 2015-06-01? ? ? ?2018-04-01
12? ?2400178 2016-07-01? ? ? ?2018-04-01
13? ?2400178 2016-08-01? ? ? ?2018-04-01
14? ?2400178 2017-01-01? ? ? ?2018-04-01
15? ?2400178 2017-03-01? ? ? ?2018-04-01
16? ?2400178 2018-04-01? ? ? ?2018-04-01
如果需要日期時間的原始格式：
dates = pd.to_datetime(df['Month_Year'], format='%B-%Y')
df['last_review_date'] = dates.groupby(df['Hotel_id']).transform('max').dt.strftime('%B-%Y')
print (df)
? ? Hotel_id? ? ?Month_Year last_review_date
0? ? 2400614? ? ? ?May-2015? ? ? ?March-2016
1? ? 2400614? ? ? June-2015? ? ? ?March-2016
2? ? 2400614? December-2015? ? ? ?March-2016
3? ? 2400614? ?January-2016? ? ? ?March-2016
4? ? 2400614? ? ?March-2016? ? ? ?March-2016
5? ? 2400133? ? ?April-2016? ? ? ? ?May-2017
6? ? 2400133? ? ? June-2016? ? ? ? ?May-2017
7? ? 2400133? ? August-2016? ? ? ? ?May-2017
8? ? 2400133? ?January-2017? ? ? ? ?May-2017
9? ? 2400133? ? ?April-2017? ? ? ? ?May-2017
10? ?2400133? ? ? ?May-2017? ? ? ? ?May-2017
11? ?2400178? ? ? June-2015? ? ? ?April-2018
12? ?2400178? ? ? July-2016? ? ? ?April-2018
13? ?2400178? ? August-2016? ? ? ?April-2018
14? ?2400178? ?January-2017? ? ? ?April-2018
15? ?2400178? ? ?March-2017? ? ? ?April-2018
16? ?2400178? ? ?April-2018? ? ? ?April-2018
編輯：
如果需要添加每個組的所有現有月份日期時間，請使用：
df['Month_Year'] = pd.to_datetime(df['Month_Year'], format='%B-%Y')
df1 = (df.set_index('Month_Year')
? ? ? ? ?.groupby('Hotel_id')
? ? ? ? ?.resample('1M')
? ? ? ? ?.ffill()
? ? ? ? ?.reset_index(level=0, drop=True)
? ? ? ? ?.reset_index())
print (df1)
? ?Month_Year? Hotel_id
0? 2016-04-30? ?2400133
1? 2016-05-31? ?2400133
2? 2016-06-30? ?2400133
3? 2016-07-31? ?2400133
4? 2016-08-31? ?2400133
5? 2016-09-30? ?2400133
6? 2016-10-31? ?2400133
7? 2016-11-30? ?2400133
8? 2016-12-31? ?2400133
9? 2017-01-31? ?2400133
10 2017-02-28? ?2400133
11 2017-03-31? ?2400133
12 2017-04-30? ?2400133
13 2017-05-31? ?2400133
14 2015-06-30? ?2400178
15 2015-07-31? ?2400178
16 2015-08-31? ?2400178
17 2015-09-30? ?2400178
18 2015-10-31? ?2400178
19 2015-11-30? ?2400178
20 2015-12-31? ?2400178
21 2016-01-31? ?2400178
22 2016-02-29? ?2400178
23 2016-03-31? ?2400178
24 2016-04-30? ?2400178
25 2016-05-31? ?2400178
26 2016-06-30? ?2400178
27 2016-07-31? ?2400178
28 2016-08-31? ?2400178
29 2016-09-30? ?2400178
30 2016-10-31? ?2400178
31 2016-11-30? ?2400178
32 2016-12-31? ?2400178
33 2017-01-31? ?2400178
34 2017-02-28? ?2400178
35 2017-03-31? ?2400178
36 2017-04-30? ?2400178
37 2017-05-31? ?2400178
38 2017-06-30? ?2400178
39 2017-07-31? ?2400178
40 2017-08-31? ?2400178
41 2017-09-30? ?2400178
42 2017-10-31? ?2400178
43 2017-11-30? ?2400178
44 2017-12-31? ?2400178
45 2018-01-31? ?2400178
46 2018-02-28? ?2400178
47 2018-03-31? ?2400178
48 2018-04-30? ?2400178
49 2015-05-31? ?2400614
50 2015-06-30? ?2400614
51 2015-07-31? ?2400614
52 2015-08-31? ?2400614
53 2015-09-30? ?2400614
54 2015-10-31? ?2400614
55 2015-11-30? ?2400614
56 2015-12-31? ?2400614
57 2016-01-31? ?2400614
58 2016-02-29? ?2400614
59 2016-03-31? ?2400614
桃花長相依
TA貢獻1860條經驗 獲得超8個贊
我使用您作為示例數據提供的一些數據編寫了代碼。用于as.freq()填充缺失數據并method='ffill'確定如何填充缺失數據。如果你的完整數據有錯誤，你可以將這樣得到的數據與原始數據結合起來。
import pandas as pd
import numpy as np
import io
data = '''
Hotel_id Month_Year last_review_date
2400614 May-2015 March-2016
2400614 June-2015 March-2016
2400614 December-2015 March-2016
2400614 January-2016 March-2016
2400614 March-2016 March-2016
2400133 April-2016 May-2017
2400133 June-2016 May-2017
2400133 August-2016 May-2017
2400133 January-2017 May-2017
2400133 April-2017 May-2017
2400133 May-2017 May-2017
2400178 June-2015 April-2018
2400178 July-2016 April-2018
2400178 August-2016 April-2018
2400178 January-2017 April-2018
2400178 March-2017 April-2018
2400178 April-2018 April-2018
'''
df = pd.read_csv(io.StringIO(data), sep='\s+')
df['Month_Year'] = pd.to_datetime(df['Month_Year'], format='%B-%Y')
hid = df['Hotel_id'].unique().tolist()
new = pd.DataFrame()
for h in hid:
tmp = df[df['Hotel_id'] == h].set_index('Month_Year').asfreq('1M', method='ffill')
new = pd.concat([new, tmp], axis=0)
new
Hotel_id last_review_date
Month_Year
2015-05-31 2400614 March-2016
2015-06-30 2400614 March-2016
2015-07-31 2400614 March-2016
2015-08-31 2400614 March-2016
2015-09-30 2400614 March-2016
2015-10-31 2400614 March-2016
2015-11-30 2400614 March-2016
2015-12-31 2400614 March-2016
2016-01-31 2400614 March-2016
2016-02-29 2400614 March-2016
2016-04-30 2400133 May-2017
2016-05-31 2400133 May-2017
2016-06-30 2400133 May-2017
2016-07-31 2400133 May-2017
2016-08-31 2400133 May-2017
2016-09-30 2400133 May-2017
2016-10-31 2400133 May-2017
2016-11-30 2400133 May-2017
2016-12-31 2400133 May-2017
2017-01-31 2400133 May-2017
2017-02-28 2400133 May-2017
2017-03-31 2400133 May-2017
2017-04-30 2400133 May-2017
2015-06-30 2400178 April-2018
2015-07-31 2400178 April-2018
2015-08-31 2400178 April-2018
2015-09-30 2400178 April-2018
2015-10-31 2400178 April-2018
2015-11-30 2400178 April-2018
2015-12-31 2400178 April-2018
2016-01-31 2400178 April-2018
2016-02-29 2400178 April-2018
2016-03-31 2400178 April-2018
2016-04-30 2400178 April-2018
2016-05-31 2400178 April-2018
2016-06-30 2400178 April-2018
2016-07-31 2400178 April-2018
2016-08-31 2400178 April-2018
2016-09-30 2400178 April-2018
2016-10-31 2400178 April-2018
2016-11-30 2400178 April-2018
2016-12-31 2400178 April-2018
2017-01-31 2400178 April-2018
2017-02-28 2400178 April-2018
2017-03-31 2400178 April-2018
2017-04-30 2400178 April-2018
2017-05-31 2400178 April-2018
2017-06-30 2400178 April-2018
2017-07-31 2400178 April-2018
2017-08-31 2400178 April-2018
2017-09-30 2400178 April-2018
2017-10-31 2400178 April-2018
2017-11-30 2400178 April-2018
2017-12-31 2400178 April-2018
2018-01-31 2400178 April-2018
2018-02-28 2400178 April-2018
2018-03-31 2400178 April-2018
添加回答
舉報
0/150
提交
取消