I need to find the variability from the long-term mean for monthly data from 1991 to 2021. I have data that looks like this that is a 204,3 size:
dfavgs =
plant_name month power_kwh
0 ARIZONA I 1 10655.989885
1 ARIZONA I 2 9789.542672
2 ARIZONA I 3 7889.403154
3 ARIZONA I 4 7965.595843
4 ARIZONA I 5 9299.316756
.. ... ... ...
199 SANTANA II 8 16753.999870
200 SANTANA II 9 17767.383616
201 SANTANA II 10 17430.005363
202 SANTANA II 11 16628.784139
203 SANTANA II 12 15167.085560
My large monthly by year df looks like this with size 6137,4:
dfmonthlys:
plant_name year month power_kwh
0 ARIZONA I 1991 1 9256.304704
1 ARIZONA I 1991 2 8851.689732
2 ARIZONA I 1991 3 7649.949328
3 ARIZONA I 1991 4 6728.544028
4 ARIZONA I 1991 5 8601.165457
... ... ... ...
6132 SANTANA II 2020 9 16481.202361
6133 SANTANA II 2020 10 15644.358737
6134 SANTANA II 2020 11 14368.804306
6135 SANTANA II 2020 12 15473.958468
6136 SANTANA II 2021 1 13161.219086
My new df "dfvar" should look like this showing the monthly deviation from long-term mean by year - i don't think these values are correct below:
plant_name year month Var
0 ARIZONA I 1991 1 -0.250259
1 ARIZONA I 1991 2 -0.283032
2 ARIZONA I 1991 3 -0.380370
3 ARIZONA I 1991 4 -0.455002
4 ARIZONA I 1991 5 -0.303324
I could do this easily in MATLAB but i'm not sure how to do this using pandas which i need to learn. Thank you very much. I've tried this below which gives me a series but there seem to be unexpected NaN's at the end rows:
t = dfmonthlys['power_kwh']/dfavgs.loc[:,'power_kwh'] - 1
the output from above looks like this:
t
Out[159]:
0 -0.131352
1 -0.095802
2 -0.030351
3 -0.155299
4 -0.075076
6132 NaN
6133 NaN
6134 NaN
6135 NaN
6136 NaN
Name: power_kwh, Length: 6137, dtype: float64
This is an example code of how you could do it. merge the dfavgs to the monthly data by month and plant name and then assign the calculation to a new column.
import numpy as np
import pandas as pd
dfavgs = {'plant_name':np.append(np.repeat(["ARIZONA I"], 12) , np.repeat("SANTANA II", 12)),
'month': np.tile(range(1, 13), 2),
'mnth_power_kwh': np.concatenate(([10655, 9789, 7889, 7965, 9299],
range(8000, 1500, -1000), range(12000, 500, -1000)))}
dfavgs=pd.DataFrame(dfavgs)
dfmonthlys = {'plant_name':np.append(np.repeat("ARIZONA I", 24), np.repeat("SANTANA II", 24)),
'year': np.tile(np.repeat([1991, 1992], 12), 2),
'month': np.tile(np.tile(range(1, 13), 2), 2),
'power_kwh': np.concatenate(([9256, 8851, 7649, 6728, 8601],
range(7000, 500, -1000),
range(13000, 1500, -1000),
range(25000, 1500, -1000)))}
dfmonthlys=pd.DataFrame(dfmonthlys)
merg=pd.merge(dfmonthlys, dfavgs, how="left", on=["month", "plant_name"])\
.assign(diff = lambda x: x["power_kwh"]/x["mnth_power_kwh"]-1)
print merg