英文:
Compute difference based on matching observations over time
问题
假设我们有以下数据框：
Date Type Country Value0 2016-04-30 A NL 11 2016-04-30 A BE 22 2016-04-30 B NL 33 2016-04-30 B BE 44 2016-04-30 C NL 55 2016-04-30 C BE 66 2016-04-30 C FR 77 2016-04-30 C UK 88 2016-05-31 A NL 99 2016-05-31 A BE 1010 2016-05-31 A FR 1111 2016-05-31 B NL 1212 2016-05-31 B BE 1313 2016-05-31 B FR 1414 2016-05-31 C NL 1515 2016-05-31 C BE 1616 2016-05-31 C UK 1717 2016-05-31 C SL 1818 2016-06-30 A NL 1919 2016-06-30 B FR 2020 2016-06-30 B UK 2121 2016-06-30 B SL 2222 2016-06-30 C NL 2323 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设置为NaNdf.loc[df.groupby(['Type', 'Country'])['Value'].head(1).index, 'ValueDiff'] = None# 打印结果print(df)
期望的数据框如下：
Date Type Country Value ValueDiff0 2016-04-30 A NL 1 NaN1 2016-04-30 A BE 2 NaN2 2016-04-30 B NL 3 NaN3 2016-04-30 B BE 4 NaN4 2016-04-30 C NL 5 NaN5 2016-04-30 C BE 6 NaN6 2016-04-30 C FR 7 NaN7 2016-04-30 C UK 8 NaN8 2016-05-31 A NL 9 8.09 2016-05-31 A BE 10 8.010 2016-05-31 A FR 11 NaN11 2016-05-31 B NL 12 9.012 2016-05-31 B BE 13 9.013 2016-05-31 B FR 14 NaN14 2016-05-31 C NL 15 10.015 2016-05-31 C BE 16 10.016 2016-05-31 C UK 17 9.017 2016-05-31 C SL 18 NaN18 2016-06-30 A NL 19 10.019 2016-06-30 B FR 20 6.020 2016-06-30 B UK 21 NaN21 2016-06-30 B SL 22 NaN22 2016-06-30 C NL 23 8.023 2016-06-30 C BE 24 8.0
英文:
Suppose we have the following dataframe:
Date Type Country Value0 2016-04-30 A NL 11 2016-04-30 A BE 22 2016-04-30 B NL 33 2016-04-30 B BE 44 2016-04-30 C NL 55 2016-04-30 C BE 66 2016-04-30 C FR 77 2016-04-30 C UK 88 2016-05-31 A NL 99 2016-05-31 A BE 1010 2016-05-31 A FR 1111 2016-05-31 B NL 1212 2016-05-31 B BE 1313 2016-05-31 B FR 1414 2016-05-31 C NL 1515 2016-05-31 C BE 1616 2016-05-31 C UK 1717 2016-05-31 C SL 1818 2016-06-30 A NL 1919 2016-06-30 B FR 2020 2016-06-30 B UK 2121 2016-06-30 B SL 2222 2016-06-30 C NL 2323 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 ValueDiff0 2016-04-30 A NL 1 nan1 2016-04-30 A BE 2 nan2 2016-04-30 B NL 3 nan3 2016-04-30 B BE 4 nan4 2016-04-30 C NL 5 nan5 2016-04-30 C BE 6 nan6 2016-04-30 C FR 7 nan7 2016-04-30 C UK 8 nan8 2016-05-31 A NL 9 89 2016-05-31 A BE 10 810 2016-05-31 A FR 11 nan11 2016-05-31 B NL 12 912 2016-05-31 B BE 13 913 2016-05-31 B FR 14 nan14 2016-05-31 C NL 15 1015 2016-05-31 C BE 16 1016 2016-05-31 C UK 17 917 2016-05-31 C SL 18 nan18 2016-06-30 A NL 19 1019 2016-06-30 B FR 20 620 2016-06-30 B UK 21 nan21 2016-06-30 B SL 22 nan22 2016-06-30 C NL 23 823 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 ValueDiff0 2016-04-30 A NL 1 NaN1 2016-04-30 A BE 2 NaN2 2016-04-30 B NL 3 NaN3 2016-04-30 B BE 4 NaN4 2016-04-30 C NL 5 NaN5 2016-04-30 C BE 6 NaN6 2016-04-30 C FR 7 NaN7 2016-04-30 C UK 8 NaN8 2016-05-31 A NL 9 8.09 2016-05-31 A BE 10 8.010 2016-05-31 A FR 11 NaN11 2016-05-31 B NL 12 9.012 2016-05-31 B BE 13 9.013 2016-05-31 B FR 14 NaN14 2016-05-31 C NL 15 10.015 2016-05-31 C BE 16 10.016 2016-05-31 C UK 17 9.017 2016-05-31 C SL 18 NaN18 2016-06-30 A NL 19 10.019 2016-06-30 B FR 20 6.020 2016-06-30 B UK 21 NaN21 2016-06-30 B SL 22 NaN22 2016-06-30 C NL 23 8.023 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 ValueDiff0 2016-04-30 A NL 1 NaN1 2016-04-30 A BE 2 NaN2 2016-04-30 B NL 3 NaN3 2016-04-30 B BE 4 NaN4 2016-04-30 C NL 5 NaN5 2016-04-30 C BE 6 NaN6 2016-04-30 C FR 7 NaN7 2016-04-30 C UK 8 NaN8 2016-05-31 A NL 9 8.09 2016-05-31 A BE 10 8.010 2016-05-31 A FR 11 NaN11 2016-05-31 B NL 12 9.012 2016-05-31 B BE 13 9.013 2016-05-31 B FR 14 NaN14 2016-05-31 C NL 15 10.015 2016-05-31 C BE 16 10.016 2016-05-31 C UK 17 9.017 2016-05-31 C SL 18 NaN18 2016-06-30 A NL 19 10.019 2016-06-30 B FR 20 6.020 2016-06-30 B UK 21 NaN21 2016-06-30 B SL 22 NaN22 2016-06-30 C NL 23 8.023 2016-06-30 C BE 24 8.0
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论