年度变化百分比

Question

两个表：财务数据（FI）和事件数据（ED）。我希望找到某些事件和财务变化之间的关系（即收入在事件发生后额外增长了2%）

例如：谷歌在2014年发生了一件事。。我想比较一下他们2012-2014年和2014-2016年的收入（以及其他指标）

我正在努力做这个比较，甚至找到一半的变化。感谢您的帮助

样本数据：

Org Metric  ID  Year    Value
4   Org1    EBITDA  Org1EBITDA2012  2012    3834
5   Org1    EBITDA  Org1EBITDA2013  2013    4141
6   Org1    EBITDA  Org1EBITDA2014  2014    7383
7   Org1    EBITDA  Org1EBITDA2015  2015    9145
8   Org1    EBITDA  Org1EBITDA2016  2016    7503
9   Org1    EBITDA  Org1EBITDA2017  2017    8182
24  Org1    Revenue Org1Revenue2012 2012    87584
25  Org1    Revenue Org1Revenue2013 2013    46794
26  Org1    Revenue Org1Revenue2014 2014    59062
27  Org1    Revenue Org1Revenue2015 2015    58378
28  Org1    Revenue Org1Revenue2016 2016    60257
29  Org1    Revenue Org1Revenue2017 2017    61158
64  Org2    EBITDA  Org2EBITDA2012  2012    20698
65  Org2    EBITDA  Org2EBITDA2013  2013    23161
66  Org2    EBITDA  Org2EBITDA2014  2014    25032
67  Org2    EBITDA  Org2EBITDA2015  2015    22116
68  Org2    EBITDA  Org2EBITDA2016  2016    24964
69  Org2    EBITDA  Org2EBITDA2017  2017    25352
84  Org2    Revenue Org2Revenue2012 2012    67224
85  Org2    Revenue Org2Revenue2013 2013    71312
86  Org2    Revenue Org2Revenue2014 2014    74331
87  Org2    Revenue Org2Revenue2015 2015    70074
88  Org2    Revenue Org2Revenue2016 2016    71890
89  Org2    Revenue Org2Revenue2017 2017    76450

当前代码： 我当前的代码显示我在每个表中添加特定的列以适应不同的年份（我的计划是合并这些表），但这不是正确的方法

import pandas as pd 
from pandas import ExcelWriter
import numpy as np
from datetime import date

# Pull in Data
path = r"C:\Users\xx.xx\desktop\Python\PyTest\PyFIvID.xlsx"
path2 = r"C:\Users\xx.xx\desktop\Python\PyTest\PyEventsvID.xlsx"
sheet = 'Adjusted Data'
fi = pd.read_excel(path,sheet_name=sheet)
tgps = pd.read_excel(path2,sheet_name=sheet)

metric = 'Revenue'
tgps.replace(['Outlier','No Data'],float('NaN'),inplace=True)
fi.replace(['Outlier','No Data'],float('NaN'),inplace=True)

tgps['ID-1'] = tgps['CapIQ'] + metric + (tgps['Year']-1).map(str)
tgps['ID-2'] = tgps['CapIQ'] + metric + (tgps['Year']-2).map(str)
tgps['ID-3'] = tgps['CapIQ'] + metric + (tgps['Year']-3).map(str)
tgps['ID+1'] = tgps['CapIQ'] + metric + (tgps['Year']+1).map(str)
tgps['ID+2'] = tgps['CapIQ'] + metric + (tgps['Year']+2).map(str)
tgps['ID+3'] = tgps['CapIQ'] + metric + (tgps['Year']+3).map(str)

fi['ID-1'] = fi['CapID'] + metric + (fi['Year']-1).map(str)
fi['ID-2'] = fi['CapID'] + metric + (fi['Year']-2).map(str)
fi['ID-3'] = fi['CapID'] + metric + (fi['Year']-3).map(str)
fi['ID+1'] = fi['CapID'] + metric + (fi['Year']+1).map(str)
fi['ID+2'] = fi['CapID'] + metric + (fi['Year']+2).map(str)
fi['ID+3'] = fi['CapID'] + metric + (fi['Year']+3).map(str)

merg = tgps.merge(fi,on='ID')
merg.set_index(['ORG'], inplace=True)

path3 = r"C:\Users\xx.xx\desktop\Python\PyTest\PyCapIQ5.xlsx"
writer = ExcelWriter(path3)
fi.to_excel(writer,'FI Data')
tgps.to_excel(writer,'TGPS Results')
merg.to_excel(writer,'Merged')
writer.save()