英文:
Compute difference based on matching observations over time
问题
假设我们有以下数据框：
Date Type Country Value
0 2016-04-30 A NL 1
1 2016-04-30 A BE 2
2 2016-04-30 B NL 3
3 2016-04-30 B BE 4
4 2016-04-30 C NL 5
5 2016-04-30 C BE 6
6 2016-04-30 C FR 7
7 2016-04-30 C UK 8
8 2016-05-31 A NL 9
9 2016-05-31 A BE 10
10 2016-05-31 A FR 11
11 2016-05-31 B NL 12
12 2016-05-31 B BE 13
13 2016-05-31 B FR 14
14 2016-05-31 C NL 15
15 2016-05-31 C BE 16
16 2016-05-31 C UK 17
17 2016-05-31 C SL 18
18 2016-06-30 A NL 19
19 2016-06-30 B FR 20
20 2016-06-30 B UK 21
21 2016-06-30 B SL 22
22 2016-06-30 C NL 23
23 2016-06-30 C BE 24
以下是要添加的代码段：
import pandas as pd
# 创建数据框
df = pd.DataFrame([['2016-04-30','A','NL',1], ['2016-04-30','A','BE',2], ['2016-04-30','B','NL',3], ['2016-04-30','B','BE',4], ['2016-04-30','C','NL',5], ['2016-04-30','C','BE',6],['2016-04-30','C','FR',7], ['2016-04-30','C','UK',8], ['2016-05-31','A','NL',9], ['2016-05-31','A','BE',10], ['2016-05-31','A','FR',11], ['2016-05-31','B','NL',12], ['2016-05-31','B','BE',13], ['2016-05-31','B','FR',14], ['2016-05-31','C','NL',15], ['2016-05-31','C','BE',16], ['2016-05-31','C','UK',17], ['2016-05-31','C','SL',18], ['2016-06-30','A','NL',19], ['2016-06-30','B','FR',20], ['2016-06-30','B','UK',21], ['2016-06-30','B','SL',22], ['2016-06-30','C','NL',23], ['2016-06-30','C','BE',24]], columns=['Date','Type','Country','Value'])
# 将日期列转换为日期时间对象
df['Date'] = pd.to_datetime(df['Date'])
# 按日期和类型进行排序
df.sort_values(['Date', 'Type'], inplace=True)
# 计算ValueDiff列，即每个观察值与前一期的差异
df['ValueDiff'] = df.groupby(['Type', 'Country'])['Value'].diff()
# 对于第一次出现的观察值，将ValueDiff设置为NaN
df.loc[df.groupby(['Type', 'Country'])['Value'].head(1).index, 'ValueDiff'] = None
# 打印结果
print(df)
期望的数据框如下：
Date Type Country Value ValueDiff
0 2016-04-30 A NL 1 NaN
1 2016-04-30 A BE 2 NaN
2 2016-04-30 B NL 3 NaN
3 2016-04-30 B BE 4 NaN
4 2016-04-30 C NL 5 NaN
5 2016-04-30 C BE 6 NaN
6 2016-04-30 C FR 7 NaN
7 2016-04-30 C UK 8 NaN
8 2016-05-31 A NL 9 8.0
9 2016-05-31 A BE 10 8.0
10 2016-05-31 A FR 11 NaN
11 2016-05-31 B NL 12 9.0
12 2016-05-31 B BE 13 9.0
13 2016-05-31 B FR 14 NaN
14 2016-05-31 C NL 15 10.0
15 2016-05-31 C BE 16 10.0
16 2016-05-31 C UK 17 9.0
17 2016-05-31 C SL 18 NaN
18 2016-06-30 A NL 19 10.0
19 2016-06-30 B FR 20 6.0
20 2016-06-30 B UK 21 NaN
21 2016-06-30 B SL 22 NaN
22 2016-06-30 C NL 23 8.0
23 2016-06-30 C BE 24 8.0
英文:
Suppose we have the following dataframe:
Date Type Country Value
0 2016-04-30 A NL 1
1 2016-04-30 A BE 2
2 2016-04-30 B NL 3
3 2016-04-30 B BE 4
4 2016-04-30 C NL 5
5 2016-04-30 C BE 6
6 2016-04-30 C FR 7
7 2016-04-30 C UK 8
8 2016-05-31 A NL 9
9 2016-05-31 A BE 10
10 2016-05-31 A FR 11
11 2016-05-31 B NL 12
12 2016-05-31 B BE 13
13 2016-05-31 B FR 14
14 2016-05-31 C NL 15
15 2016-05-31 C BE 16
16 2016-05-31 C UK 17
17 2016-05-31 C SL 18
18 2016-06-30 A NL 19
19 2016-06-30 B FR 20
20 2016-06-30 B UK 21
21 2016-06-30 B SL 22
22 2016-06-30 C NL 23
23 2016-06-30 C BE 24
Which can be computed with the following code:
df = pd.DataFrame([['2016-04-30','A','NL',1], ['2016-04-30','A', "BE" ,2], ['2016-04-30', 'B', 'NL',3], ['2016-04-30','B','BE',4], ['2016-04-30','C','NL',5], ['2016-04-30','C','BE',6],['2016-04-30','C','FR', 7], ['2016-04-30','C','UK',8], ['2016-05-31','A','NL',9], ['2016-05-31','A','BE',10], ['2016-05-31','A','FR',11], ['2016-05-31','B','NL',12], ['2016-05-31','B','BE',13], ['2016-05-31','B','FR',14], ['2016-05-31','C','NL',15], ['2016-05-31','C','BE',16], ['2016-05-31','C','UK',17], ['2016-05-31','C','SL',18], ['2016-06-30','A','NL',19], ['2016-06-30','B','FR',20], ['2016-06-30','B','UK',21], ['2016-06-30','B','SL',22], ['2016-06-30','C','NL',23], ['2016-06-30','C','BE',24]], columns=['Date','Type' ,'Country' ,'Value'])
I want to add an extra column 'ValueDiff', that basically computes the difference compared to the observation in the previous period. Hence, for instance for the observation 'Date: 2016-05-31, Type: B, Country: BE', I would want to set 'ValueDiff' to 13-4 = 9. In case the observation is not available in the previous period, I would want to set it to NaN.
Expected df:
Date Type Country Value ValueDiff
0 2016-04-30 A NL 1 nan
1 2016-04-30 A BE 2 nan
2 2016-04-30 B NL 3 nan
3 2016-04-30 B BE 4 nan
4 2016-04-30 C NL 5 nan
5 2016-04-30 C BE 6 nan
6 2016-04-30 C FR 7 nan
7 2016-04-30 C UK 8 nan
8 2016-05-31 A NL 9 8
9 2016-05-31 A BE 10 8
10 2016-05-31 A FR 11 nan
11 2016-05-31 B NL 12 9
12 2016-05-31 B BE 13 9
13 2016-05-31 B FR 14 nan
14 2016-05-31 C NL 15 10
15 2016-05-31 C BE 16 10
16 2016-05-31 C UK 17 9
17 2016-05-31 C SL 18 nan
18 2016-06-30 A NL 19 10
19 2016-06-30 B FR 20 6
20 2016-06-30 B UK 21 nan
21 2016-06-30 B SL 22 nan
22 2016-06-30 C NL 23 8
23 2016-06-30 C BE 24 8
Is there an efficient way to do this?
答案1
得分: 2
以下是翻译好的部分：
如果每个Date组中都有唯一的Type和Country对，那么可以使用DataFrameGroupBy.diff：
df['ValueDiff'] = df.groupby(['Type', 'Country'])['Value'].diff()
print(df)
Date Type Country Value ValueDiff
0 2016-04-30 A NL 1 NaN
1 2016-04-30 A BE 2 NaN
2 2016-04-30 B NL 3 NaN
3 2016-04-30 B BE 4 NaN
4 2016-04-30 C NL 5 NaN
5 2016-04-30 C BE 6 NaN
6 2016-04-30 C FR 7 NaN
7 2016-04-30 C UK 8 NaN
8 2016-05-31 A NL 9 8.0
9 2016-05-31 A BE 10 8.0
10 2016-05-31 A FR 11 NaN
11 2016-05-31 B NL 12 9.0
12 2016-05-31 B BE 13 9.0
13 2016-05-31 B FR 14 NaN
14 2016-05-31 C NL 15 10.0
15 2016-05-31 C BE 16 10.0
16 2016-05-31 C UK 17 9.0
17 2016-05-31 C SL 18 NaN
18 2016-06-30 A NL 19 10.0
19 2016-06-30 B FR 20 6.0
20 2016-06-30 B UK 21 NaN
21 2016-06-30 B SL 22 NaN
22 2016-06-30 C NL 23 8.0
23 2016-06-30 C BE 24 8.0
英文:
If there are all unique pairs Type and Country per Date groups then is possible use DataFrameGroupBy.diff:
df['ValueDiff'] = df.groupby(['Type','Country'])['Value'].diff()
print (df)
Date Type Country Value ValueDiff
0 2016-04-30 A NL 1 NaN
1 2016-04-30 A BE 2 NaN
2 2016-04-30 B NL 3 NaN
3 2016-04-30 B BE 4 NaN
4 2016-04-30 C NL 5 NaN
5 2016-04-30 C BE 6 NaN
6 2016-04-30 C FR 7 NaN
7 2016-04-30 C UK 8 NaN
8 2016-05-31 A NL 9 8.0
9 2016-05-31 A BE 10 8.0
10 2016-05-31 A FR 11 NaN
11 2016-05-31 B NL 12 9.0
12 2016-05-31 B BE 13 9.0
13 2016-05-31 B FR 14 NaN
14 2016-05-31 C NL 15 10.0
15 2016-05-31 C BE 16 10.0
16 2016-05-31 C UK 17 9.0
17 2016-05-31 C SL 18 NaN
18 2016-06-30 A NL 19 10.0
19 2016-06-30 B FR 20 6.0
20 2016-06-30 B UK 21 NaN
21 2016-06-30 B SL 22 NaN
22 2016-06-30 C NL 23 8.0
23 2016-06-30 C BE 24 8.0
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论