DATA QUALITY AND ANALYTICAL DATAMART CREATION

SET UP

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
%matplotlib inline
import seaborn as sns

#Automcompletar rápido
%config IPCompleter.greedy=True

import fiser_tools as fs
fs.misc.dark_theme()

DATA UPLOAD

This case consists of 4 files:

• Plant 1, generation data
• plant 1, environmental sensor data
• Plant 2, generation data
• Plant 2, environmental sensor data

Load data plant 1 - generation data

p1g = pd.read_csv('Plant_1_Generation_Data.csv')
p1g

Load data plant 1 - environmental sensor data

p1w = pd.read_csv('Plant_1_Weather_Sensor_Data.csv')
p1w

Load data plant 2 - generation data

p2g = pd.read_csv('Plant_2_Generation_Data.csv')
p2g

Data upload plant 2 - environmental sensor data

p2w = pd.read_csv('Plant_2_Weather_Sensor_Data.csv')
p2w

DATA QUALITY

Plant quality 1 - generation data

We start with the overview.

p1g.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 68778 entries, 0 to 68777
Data columns (total 7 columns):
 #   Column       Non-Null Count  Dtype  
---  ------       --------------  -----  
 0   DATE_TIME    68778 non-null  object 
 1   PLANT_ID     68778 non-null  int64  
 2   SOURCE_KEY   68778 non-null  object 
 3   DC_POWER     68778 non-null  float64
 4   AC_POWER     68778 non-null  float64
 5   DAILY_YIELD  68778 non-null  float64
 6   TOTAL_YIELD  68778 non-null  float64
dtypes: float64(4), int64(1), object(2)
memory usage: 3.7+ MB

We see that there are no nulls.

We see that DATE_TIME is as object.

We convert DATE_TIME to type datetime.

p1g['DATE_TIME'] = pd.to_datetime(p1g.DATE_TIME,dayfirst=True)

We reviewed a sample of data.

p1g.head()

We check that the plant identifier is unique.

p1g.PLANT_ID.unique()

array([4135001], dtype=int64)

Let's replace it with a more readable literal.

p1g['PLANT_ID'] = p1g.PLANT_ID.replace(4135001, 'p1')

We review the descriptions.

p1g.describe().T

Let's remove the scientific notation display.

pd.options.display.float_format = '{:15.2f}'.format

p1g.describe().T

The difference in means between DC and AC is strange.

Let's visualize it.

p1g[['DC_POWER','AC_POWER']].plot(figsize = (16,12));

The diference is huge.

First we are going to check if they go in the same direction even if it is on a different scale (with a correlation), and then we are going to check what is the average ratio between both measures.

p1g.DC_POWER.corr(p1g.AC_POWER)

0.9999962553331414

(p1g.DC_POWER / p1g.AC_POWER).describe()

count          36827.00
mean              10.23
std                0.05
min                9.38
25%               10.20
50%               10.22
75%               10.25
max               10.47
dtype: float64

It seems that the Inverters are transforming only 10% from DC to AC, which a priori is very low.

In any case, from quality we got here and we will continue exploring this in the analysis part and comparing it with Plant 2 to see if the same thing happens.

We analyze the categorical variable, which is the identifier of the inverters.

p1g.SOURCE_KEY.nunique()

22

p1g.SOURCE_KEY.value_counts()

bvBOhCH3iADSZry    3155
1BY6WEcLGh8j5v7    3154
7JYdWkrLSPkdwr4    3133
VHMLBKoKgIrUVDU    3133
ZnxXDlPa8U1GXgE    3130
ih0vzX44oOqAx2f    3130
z9Y9gH1T5YWrNuG    3126
wCURE6d3bPkepu2    3126
uHbuxQJl8lW7ozc    3125
pkci93gMrogZuBj    3125
iCRJl6heRkivqQ3    3125
rGa61gmuvPhdLxV    3124
sjndEbLyjtCKgGv    3124
McdE0feGgRqW7Ca    3124
zVJPv84UY57bAof    3124
ZoEaEvLYb1n2sOq    3123
1IF53ai7Xc0U56Y    3119
adLQvlD726eNBSB    3119
zBIq5rxdHJRwDNY    3119
WRmjgnKYAwPKWDb    3118
3PZuoBAID5Wc2HD    3118
YxYtjZvoooNbGkE    3104
Name: SOURCE_KEY, dtype: int64

Conclusions:

 * plant 1 has 22 inverters
 * All have a similar number of measures although not exactly the same
 * They could be stops due to maintenance, or simple data loss, but we write it down for the analysis phase

We are going to analyze the DAILY_YIELD variables, since the metadata tells us that the TOTAL_YIELD variable is the accumulated total per inverter, but in DAILY_YIELD it does not specify it, so we do not know if it is accumulated per inverter or per plant.

The hypothesis is the following: if it is per plant, there should be no differences between the data from the different inverters at the same specific moment.

And therefore if we see that there are differences then it is that the data is due to the inverter.

To verify it, it is useful to take a sample of inverters.

seleccion = list(p1g.SOURCE_KEY.unique()[:5])

temp = p1g[p1g.SOURCE_KEY.isin(seleccion)].set_index('DATE_TIME')
temp

In the data we already see that it is different, but we are going to check on more data so that it is not an effect of those specific records.

We are going to see it graphically, and for simplicity we are going to take only a sample of days.

Since we have the date as index we remember that we can use partial and slice indexing.

temp = temp.loc['2020-06-01':'2020-06-05']
temp

plt.figure(figsize = (16,12))
sns.lineplot(data = temp.reset_index(), x = temp.reset_index().DATE_TIME, y = 'DAILY_YIELD', hue = 'SOURCE_KEY');

Definitely different inverters have different data at the same time, so we conclude that this variable is per inverter

Finally we are going to analyze the period in which we have data and if the number of daily measurements is constant.

p1g.DATE_TIME.dt.date.value_counts().sort_index().plot.bar(figsize = (12,8));

Conclusions:

 * The data period is between May 15, 2020 and June 17, 2020
 * We have data for every day, there are no intermediate days missing
 * But some days like 05/21 or 05/29 have fewer measurements
 * So it doesn't look 100% regular

Data quality plant 1 - environmental sensor data

p1w.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 3182 entries, 0 to 3181
Data columns (total 6 columns):
 #   Column               Non-Null Count  Dtype  
---  ------               --------------  -----  
 0   DATE_TIME            3182 non-null   object 
 1   PLANT_ID             3182 non-null   int64  
 2   SOURCE_KEY           3182 non-null   object 
 3   AMBIENT_TEMPERATURE  3182 non-null   float64
 4   MODULE_TEMPERATURE   3182 non-null   float64
 5   IRRADIATION          3182 non-null   float64
dtypes: float64(3), int64(1), object(2)
memory usage: 149.3+ KB

We correct the type of DATE_TIME

p1w.DATE_TIME = pd.to_datetime(p1w.DATE_TIME)
p1w.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 3182 entries, 0 to 3181
Data columns (total 6 columns):
 #   Column               Non-Null Count  Dtype         
---  ------               --------------  -----         
 0   DATE_TIME            3182 non-null   datetime64[ns]
 1   PLANT_ID             3182 non-null   int64         
 2   SOURCE_KEY           3182 non-null   object        
 3   AMBIENT_TEMPERATURE  3182 non-null   float64       
 4   MODULE_TEMPERATURE   3182 non-null   float64       
 5   IRRADIATION          3182 non-null   float64       
dtypes: datetime64[ns](1), float64(3), int64(1), object(1)
memory usage: 149.3+ KB

p1w.head()

We replaced the name of the plant.

p1w['PLANT_ID'] = p1w.PLANT_ID.replace(4135001,'p1')
p1w

We review the statistics.

p1w.describe().T

We check the categorical variable, which is the sensor identifier.

p1w.SOURCE_KEY.nunique()

1

There is only one sensor for environmental variables in the plant.

We revise the date.

p1w.DATE_TIME.dt.date.value_counts().sort_index().plot.bar(figsize = (12,8));

Conclusions:

 * The data period is between May 15, 2020 and June 17, 2020
 * We have data for every day, there are no intermediate days missing
 * But some days like 05/21 or 05/29 have fewer measurements
 * So it doesn't look 100% regular

Plant quality 2 - generation data

p2g.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 67698 entries, 0 to 67697
Data columns (total 7 columns):
 #   Column       Non-Null Count  Dtype  
---  ------       --------------  -----  
 0   DATE_TIME    67698 non-null  object 
 1   PLANT_ID     67698 non-null  int64  
 2   SOURCE_KEY   67698 non-null  object 
 3   DC_POWER     67698 non-null  float64
 4   AC_POWER     67698 non-null  float64
 5   DAILY_YIELD  67698 non-null  float64
 6   TOTAL_YIELD  67698 non-null  float64
dtypes: float64(4), int64(1), object(2)
memory usage: 3.6+ MB

p2g['DATE_TIME'] = pd.to_datetime(p2g.DATE_TIME)

p2g['PLANT_ID'] = p2g.PLANT_ID.replace(4136001, 'p2')

p2g.head()

p2g.describe().T

In this case the values of DC and AC are much closer to each other.

Let's calculate the ratio.

(p2g.DC_POWER / p2g.AC_POWER).describe()

count          32036.00
mean               1.02
std                0.01
min                0.99
25%                1.02
50%                1.02
75%                1.03
max                1.10
dtype: float64

Now the values of the ratio are very close to one.

We analyze the categorical variable, which is the identifier of the inverters.

p2g.SOURCE_KEY.nunique()

22

p2g.SOURCE_KEY.value_counts()

xoJJ8DcxJEcupym    3259
WcxssY2VbP4hApt    3259
9kRcWv60rDACzjR    3259
vOuJvMaM2sgwLmb    3259
rrq4fwE8jgrTyWY    3259
LYwnQax7tkwH5Cb    3259
LlT2YUhhzqhg5Sw    3259
q49J1IKaHRwDQnt    3259
oZZkBaNadn6DNKz    3259
PeE6FRyGXUgsRhN    3259
81aHJ1q11NBPMrL    3259
V94E5Ben1TlhnDV    3259
oZ35aAeoifZaQzV    3195
4UPUqMRk7TRMgml    3195
Qf4GUc1pJu5T6c6    3195
Mx2yZCDsyf6DPfv    3195
Et9kgGMDl729KT4    3195
Quc1TzYxW2pYoWX    3195
mqwcsP2rE7J0TFp    2355
NgDl19wMapZy17u    2355
IQ2d7wF4YD8zU1Q    2355
xMbIugepa2P7lBB    2355
Name: SOURCE_KEY, dtype: int64

Conclusiones:

* La planta 2 tiene 22 inverters
* Todos tienen un número similar de medidas aunque no exactamente igual
* A excepción de 4 que tienen unas 800 medidas menos
* Lo apuntamos para la fase de análisis

Por último vamos a analizar la fecha.

p2g.DATE_TIME.dt.date.value_counts().sort_index().plot.bar(figsize = (12,8));

Conclusiones:

* El período de datos es entre el 15 de Mayo del 2020 y el 17 de Junio de 2020
* Tenemos datos para todos los días, no falta ninguno intermedio
* Pero algunos días como el 20/05 y varios más tienen menos mediciones
* Por lo que no parece 100% regular

Calidad de datos planta 2 - datos de sensor ambiental

p2w.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 3259 entries, 0 to 3258
Data columns (total 6 columns):
 #   Column               Non-Null Count  Dtype  
---  ------               --------------  -----  
 0   DATE_TIME            3259 non-null   object 
 1   PLANT_ID             3259 non-null   int64  
 2   SOURCE_KEY           3259 non-null   object 
 3   AMBIENT_TEMPERATURE  3259 non-null   float64
 4   MODULE_TEMPERATURE   3259 non-null   float64
 5   IRRADIATION          3259 non-null   float64
dtypes: float64(3), int64(1), object(2)
memory usage: 152.9+ KB

We correct the type of DATE_TIME

p2w.DATE_TIME = pd.to_datetime(p2w.DATE_TIME)
p2w.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 3259 entries, 0 to 3258
Data columns (total 6 columns):
 #   Column               Non-Null Count  Dtype         
---  ------               --------------  -----         
 0   DATE_TIME            3259 non-null   datetime64[ns]
 1   PLANT_ID             3259 non-null   int64         
 2   SOURCE_KEY           3259 non-null   object        
 3   AMBIENT_TEMPERATURE  3259 non-null   float64       
 4   MODULE_TEMPERATURE   3259 non-null   float64       
 5   IRRADIATION          3259 non-null   float64       
dtypes: datetime64[ns](1), float64(3), int64(1), object(1)
memory usage: 152.9+ KB

p2w.head()

We replaced the name of the plant.

p2w['PLANT_ID'] = p2w.PLANT_ID.replace(4136001,'p2')
p2w

We review the statistics.

p2w.describe().T

We analyze the categorical variable, which is the sensor identifier.

p2w.SOURCE_KEY.nunique()

1

There is only one sensor for environmental variables in the plant.

We revise the date.

p2w.DATE_TIME.dt.date.value_counts().sort_index().plot.bar(figsize = (12,8));

Conclusions:

 * The data period is between May 15, 2020 and June 17, 2020
 * We have data for every day, there are no intermediate days missing
 * But some days like 05/15 or others have fewer measurements, although they are much less missing than in the other datasets
 * But it doesn't look 100% regular

Pending data quality issues for further analysis

• On plant 1 it seems that the inverters are transforming only 10% from DC to AC, which a priori is very low.
• On plant 2 the ratio is much closer to 1.
• The measurement intervals are not 100% regular. There are days with fewer measurements, and there are also differences due to inverters.

CREATION OF THE ANALYTICAL DATAMART

We are going to do a union by parts.

First the two generation datasets. Which will be a stack of records since the fields are the same.

After the two of environmental measures. Which will be a stack of records since the fields are the same.

And finally we will cross both partials through integration by key fields.

Union of generation datasets

gener = pd.concat([p1g,p2g],axis = 'index')
gener

Let's rename the variables now to make them more descriptive and usable.

gener.columns = ['fecha','planta','inverter_id','kw_dc','kw_ac','kw_dia','kw_total']
gener

Now that we have the 2 plants together we are going to do what is called a consistency analysis, since according to the documentation kw_dia and kw_total are directly related to kw_dc and kw_ac.

We are going to try to replicate the data of kw_dia and kw_total.

gener2 = gener.copy()

Creamos una variable date para poder agregar por ella.

gener2['date'] = gener2.fecha.dt.date
gener2

The sum per plant, date and inverter of kw_dc or kw_ac should match the maximum of kw_dia.

gener2 = gener2.groupby(['planta','date','inverter_id']).agg({'kw_dc':sum,
                                                              'kw_ac':sum,
                                                              'kw_dia':max,
                                                              'kw_total':max}).reset_index()
gener2

We order to be able to analyze.

gener2 = gener2.sort_values(['planta','inverter_id','date'])
gener2

Kw_dia does not match either kw_dc or kw_ac at all.

We are going to see if it agrees with kw_total, for this we calculate the daily increase of kw_total that should coincide with the maximum of kw_day of the previous day.

gener2['lag1'] = gener2.groupby(['planta','inverter_id']).kw_total.shift(1)
gener2['incremento'] = gener2.kw_total - gener2.lag1
gener2

We check on plant 1.

gener2[gener2.planta == 'p1'].head(50)

We check on plant 2.

gener2[gener2.planta == 'p2'].head(50)

Conclusions: * kw_dia is consistent with kw_total * but these are not consistent with kw_dc or kw_ac * it's as if they are in different units or there is some calculation that we are not aware of * therefore we will have 2 blocks to be able to use: either kw_dc with kw_ac, or kw_dia with kw_total, but we cannot mix them together

Union of environmental measurement datasets

temper = pd.concat([p1w,p2w], axis = 'index')
temper

Let's rename the variables now to make them more descriptive and usable.

temper.columns = ['fecha','planta','sensor_id','t_ambiente','t_modulo','irradiacion']
temper

Creation of the analytical datamart

In this case, the key field is made up of date and plant and commands the generation dataset, since the temperature field only provides us with additional variables.

df = pd.merge(left = gener, right = temper, how = 'left', on = ['fecha','planta'])
df

After an integration it is always convenient to check if nulls have been generated.

df.isna().sum()

fecha          0
planta         0
inverter_id    0
kw_dc          0
kw_ac          0
kw_dia         0
kw_total       0
sensor_id      4
t_ambiente     4
t_modulo       4
irradiacion    4
dtype: int64

We search if the nulls fulfill some pattern.

nulos = df[df.sensor_id.isna()]
nulos

It is about June 3 at 2:00 p.m., which for some reason does not have temperature data but only for 4 inverters on floor 1.

We are going to search the temperature dataset if that datetime exists.

temper[temper.fecha.between('2020-06-03 13:30:00', '2020-06-03 14:30:00')]

Indeed we see that this section is missing on both floors. However, there are only measurements at that time on floor 1, and only on 4 inverters.

So there would be two solutions:

• impute these data for these investments
• delete those 4 records

Since it seems like a measurement strip of its own, only 4 inverters on floor 1, we are going to choose to eliminate them.

df.dropna(inplace = True)
df

Finally we are going to pass the date to the index to be able to use all the power of Pandas.

df.set_index('fecha', inplace = True)
df

GUARDAMOS EL DATAMART

df.to_pickle('df.pickle')

DATA TRANSFORMATION

SET UP

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
%matplotlib inline
import seaborn as sns

import fiser_tools as fs
fs.misc.dark_theme()


#Automcompletar rápido
%config IPCompleter.greedy=True

#Formato de display
pd.options.display.float_format = '{:15.2f}'.format

DATA UPLOAD

df = pd.read_pickle('df.pickle')
df

df.info()

<class 'pandas.core.frame.DataFrame'>
DatetimeIndex: 136472 entries, 2020-05-15 00:00:00 to 2020-06-17 23:45:00
Data columns (total 10 columns):
 #   Column       Non-Null Count   Dtype  
---  ------       --------------   -----  
 0   planta       136472 non-null  object 
 1   inverter_id  136472 non-null  object 
 2   kw_dc        136472 non-null  float64
 3   kw_ac        136472 non-null  float64
 4   kw_dia       136472 non-null  float64
 5   kw_total     136472 non-null  float64
 6   sensor_id    136472 non-null  object 
 7   t_ambiente   136472 non-null  float64
 8   t_modulo     136472 non-null  float64
 9   irradiacion  136472 non-null  float64
dtypes: float64(7), object(3)
memory usage: 11.5+ MB

CREATION OF VARIABLES

We start by extracting the date components and adding them as new variables.

def componentes_fecha(dataframe):
    mes = dataframe.index.month
    dia = dataframe.index.day
    hora = dataframe.index.hour
    minuto = dataframe.index.minute


    return(pd.DataFrame({'mes':mes, 'dia':dia, 'hora':hora, 'minuto':minuto}))

df = pd.concat([df.reset_index(),componentes_fecha(df)], axis = 1).set_index('fecha')
df

We are going to create the inverter efficiency variable, which consists of the percentage of DC that successfully transforms to AC.

But we are presented with a very common difficulty in ratios, that the denominator can be zero.

If that were the case, when doing the ratio it would return a null.

In our case the denominator is DC, so if the DC generation were zero, the AC generation should also be zero.

We can correct that simply by imputing the nulls that come out with zeros.

def eficiencia_inverter(AC,DC):
    temp = AC / DC * 100
    return(temp.fillna(0))

df['eficiencia'] = eficiencia_inverter(df.kw_ac, df.kw_dc)

We check that it has not generated nulls.

df.eficiencia.isna().sum()

0

We visualize efficiency globally.

df.eficiencia.plot.kde();

Here is something important.

There are two clearly differentiated groups and one of them is clearly inefficient.

But for now we'll leave it written down and later we'll review which entity is having problems: plant, inverter, etc.

DATAFRAME REORDERING

In this case it is very important not to start analyzing by analyzing, but to follow the plan defined in the project design, since there is a very clear order in the process: environmental factors --> kw_dc --> kw ac.

So let's rearrange the columns of the df to help us interpret in this order.

df

orden = ['planta','mes','dia','hora','minuto','sensor_id','irradiacion','t_ambiente','t_modulo','inverter_id','kw_dc','kw_ac','eficiencia','kw_dia','kw_total']

df = df[orden]
df

DAILY DATAFRAME

The level of analysis at which we have the data is every 15 minutes, which may be too disaggregated for certain analyses.

We are going to build a version of the dataframe added to the day level.

For this we use resample to do downgrading.

We must add by plant and inverter, which are the key fields of our dataset.

Since we have variables to which different aggregation functions apply we can use the dictionary format of agg()

df.head()

df_dia = df.groupby(['planta', 'inverter_id']).resample('D') \
    .agg({'irradiacion': [min,np.mean,max],
          't_ambiente': [min,np.mean,max],
          't_modulo': [min,np.mean,max],
          'kw_dc': [min,np.mean,max,sum],
          'kw_ac': [min,np.mean,max,sum],
          'eficiencia': [min,np.mean,max],
          'kw_dia': max,
          'kw_total': max})

df_dia

It has been generated for us with a multi-index, both in rows and in columns.

To remove the from the columns we can flatten the names with .to_flat_index().

This returns the levels in tuples, which we can then join with a list comprehension.

Let's review how to_flat_index() returns the column names

tuplas = df_dia.columns.to_flat_index()
tuplas

Index([ ('irradiacion', 'min'), ('irradiacion', 'mean'),
        ('irradiacion', 'max'),   ('t_ambiente', 'min'),
        ('t_ambiente', 'mean'),   ('t_ambiente', 'max'),
           ('t_modulo', 'min'),    ('t_modulo', 'mean'),
           ('t_modulo', 'max'),        ('kw_dc', 'min'),
             ('kw_dc', 'mean'),        ('kw_dc', 'max'),
              ('kw_dc', 'sum'),        ('kw_ac', 'min'),
             ('kw_ac', 'mean'),        ('kw_ac', 'max'),
              ('kw_ac', 'sum'),   ('eficiencia', 'min'),
        ('eficiencia', 'mean'),   ('eficiencia', 'max'),
             ('kw_dia', 'max'),     ('kw_total', 'max')],
      dtype='object')

And we join both parts of the pair with an underscore using .join

df_dia.columns = ["_".join(par) for par in tuplas]
df_dia

Now we need to pass plant and inverter_id to columns, and leave the date as the index.

df_dia = df_dia.reset_index().set_index('fecha')
df_dia

We already have our hourly and daily datasets ready.

We keep them.

df.to_pickle('df.pickle')
df_dia.to_pickle('df_dia.pickle')

ANALYSIS AND INSIGHTS

SET UP

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
%matplotlib inline
import seaborn as sns


#Automcompletar rápido
%config IPCompleter.greedy=True

#Formato de display
pd.options.display.float_format = '{:15.2f}'.format

#Formato de graficos
import fiser_tools as fs 
fs.misc.dark_theme()

CARGA DE DATOS

df = pd.read_pickle('df.pickle')
df.head()

df_dia = pd.read_pickle('df_dia.pickle')
df_dia.head()

ANALYSIS AND INSIGHTS

The first lever is the reception of solar energy.

We have 3 kpis with which to measure this lever: incoming irradiation, ambient temperature, and module temperature.

These kpis are measured with a single sensor per plant, so the data is the same for all inverters.

We need to understand how these variables work with each other before moving on to see how they interact with the next level.

Since the inverter does not matter and we only need those 3 variables, we are going to create a smaller dataset with only one inverter from each plant to work on.

df

recepcion = df.loc[(df.inverter_id == '1BY6WEcLGh8j5v7') | (df.inverter_id == 'q49J1IKaHRwDQnt'), 'planta':'t_modulo']
recepcion

Do the two plants receive the same amount of solar energy?

temp = recepcion.groupby('planta').agg({'irradiacion':sum,'t_ambiente':np.mean,'t_modulo':np.mean})
temp

f, ax = plt.subplots(nrows=1, ncols=3, figsize = (18,5))

ax[0].bar(temp.index, temp.irradiacion, color = ['red','blue'], alpha = 0.3)
ax[1].bar(temp.index, temp.t_ambiente, color = ['red','blue'], alpha = 0.3)
ax[2].bar(temp.index, temp.t_modulo, color = ['red','blue'], alpha = 0.3)
ax[0].set_title('Irradiación por planta')
ax[1].set_title('Temperatura ambiente por planta')
ax[2].set_title('Temperatura módulo por planta');

Conclusions:

• In general, plant 2 receives more solar energy than plant 1
• But this difference cannot imply the performance problem that supposedly exists

How are these three variables related?

temp = recepcion.loc[:,['planta','irradiacion','t_ambiente','t_modulo']]
temp

sns.heatmap(temp.corr(), annot=True);

sns.pairplot(temp.reset_index(), hue = 'planta', height=3, plot_kws={'alpha': 0.1});

Conclusions:

• Irradiance highly correlates with module temperature
• But not so much with room temperature
• Therefore, a first way to identify defective or dirty modules is to locate those that produce little when the irradiation is high

How is the irradiation and temperature distributed throughout the day?

temp = pd.crosstab(recepcion.hora,recepcion.planta,values = recepcion.irradiacion,aggfunc='mean')
temp

plt.figure(figsize=(10,10))
sns.heatmap(temp, annot=True, fmt=".2f");

temp = pd.crosstab(recepcion.hora,recepcion.planta,values = recepcion.t_ambiente,aggfunc='mean')
temp

plt.figure(figsize=(10,10))
sns.heatmap(temp, annot=True, fmt=".1f");

Conclusions:

• Both plants have similar patterns. We might think that they are in geographical areas not far away
• There is irradiation (and therefore a priori the plants should produce) between 7 a.m. and 5 p.m.
• The maximum irradiation occurs between 11 and 12
• The maximum room temperature occurs between 14 and 16

Are both plants equally capable of generating DC from irradiation?

plt.figure(figsize = (12,8))
sns.scatterplot(data = df, x = df.irradiacion, y = df.kw_dc);

There are 2 clearly different patterns. Could it be the plants?

plt.figure(figsize = (12,8))
sns.scatterplot(data = df, x = df.irradiacion, y = df.kw_dc, hue = 'planta');

Plant number 2 produces much less kW at the same irradiation levels.

But before we had seen that the relationship between dc and ac in plant 1 was strange.

And also that the data for dc and ac did not match those of kw_dia.

There is something strange in the data.

Let's see the relationship between irradiation and kw_dia to see if it gives us light.

plt.figure(figsize = (12,10))
sns.scatterplot(data = df, x = df.irradiacion, y = df.kw_dia, hue = 'planta');

It's very strange. It seems that the relationship is that the more irradiation, the less kW generated. Which doesn't make sense.

It even seems that the kw maximums occur in hours of zero irradiation.

Can you imagine what could be happening?

BEWARE: the variable kw_dia is a CUMULATIVE. That means that it should reach its maximum when the last hour of the day arrives, for example 23:45, where obviously the irradiation is zero.

And not have data until after 7, which is when we see that there is irradiation.

Let's check it out.

df.groupby('hora')[['kw_dia']].mean().plot.bar();

Again something doesn't add up. There is generation between 00 and 06.

And also after 6:00 p.m. it begins to decline, which should not happen if it is accumulated.

Conclusion:

We don't trust these cumulative variables like kw_day and kw_total.

But the truth is that we don't trust the others much either.

In a real situation I would stop the project until I was able to see what happens with the data.

But in order to continue we are going to assume that the dc and ac data are correct.

And under this assumption we will obtain our conclusions.

INSIGHT #1

Plant 2 generates much lower levels of DC even at similar levels of irradiation

Is the generation constant throughout the days?

We can use the df_dia to plot the global vision of DC generation during the analysis period.

plt.figure(figsize = (10,8))
sns.lineplot(data = df_dia.reset_index(), x = df_dia.reset_index().fecha, y = 'kw_dc_sum', hue = 'planta');

We see that plant 1 has much more variability while plant 2 is much more constant.

But above all we are surprised by the low levels of DC generation on plant 2 compared to 1.

Let's examine the generation of each day to see if we see something strange.

We generate a date variable to be able to add by it.

df['date'] = df.index.date
df

We create a temporal dataframe to analyze the hourly DC generation on each day at plant 1.

dc_constante_p1 = df[df.planta == 'p1'].groupby(['planta','date','hora']).kw_dc.sum()
dc_constante_p1

planta  date        hora
p1      2020-05-15  0                 0.00
                    1                 0.00
                    2                 0.00
                    3                 0.00
                    4                 0.00
                                 ...      
        2020-06-17  19                0.00
                    20                0.00
                    21                0.00
                    22                0.00
                    23                0.00
Name: kw_dc, Length: 796, dtype: float64

We create a temporal dataframe to analyze the hourly DC generation on each day at plant 1.

dc_constante_p1.unstack(level = 1).plot(subplots = True, layout = (17,2), sharex=True, figsize=(20,30));

Conclusions:

• On floor 1, similar patterns are maintained every day
• Except for a break on May 20 and a strange crash on June 5
• But none appear to be structural
• Therefore, although each day may have different production totals, the intraday patterns are similar and seem correct.

We repeated the analysis on plant 2

dc_constante_p2 = df[df.planta == 'p2'].groupby(['planta','date','hora']).kw_dc.sum()
dc_constante_p2

planta  date        hora
p2      2020-05-15  0                 0.00
                    1                 0.00
                    2                 0.00
                    3                 0.00
                    4                 0.00
                                 ...      
        2020-06-17  19                0.00
                    20                0.00
                    21                0.00
                    22                0.00
                    23                0.00
Name: kw_dc, Length: 816, dtype: float64

We are going to pass date to columns, to be able to represent each column (which are the dates) as a variable and therefore as an independent graph.

dc_constante_p2.unstack(level = 1).plot(subplots = True, layout = (17,2), sharex=True, figsize=(20,30));

Conclusions:

• Again on May 20 he appears with a strange behavior
• Production levels are constant over the days, but always about 10 times below plant 1 levels

INSIGHT #2: The low levels of plant 2 are constant and have daily curves that seem normal.

Is the DC to AC conversion generated correctly?

sns.scatterplot(data = df, x = df.kw_dc, y = df.kw_ac, hue = df.planta);

Again the patterns are very clear: plant 2 transforms the current much more efficiently.

We are going to expand by analyzing the efficiency variable that we had created.

temp = df.groupby(['planta','hora'],as_index = False).eficiencia.mean()
temp

sns.lineplot(data = temp, x = 'hora', y = 'eficiencia', hue = 'planta');

INSIGHT #3

Plant 1 has a very low capacity to transform DC to AC, which suggests problems with the inverters

Other conclusions:

• Go into the details of the inverters on floor 1, to see if they are all or there are some that bias the average
• Review why plant 2 loses efficiency during the hours of most irradiation

We are going to start with the second, comparing the production of DC with that of AC in plant 2.

temp = df[['planta','hora','kw_dc','kw_ac']].melt(id_vars= ['planta','hora'])
temp

plt.figure(figsize = (12,8))
sns.lineplot(data = temp[temp.planta == 'p2'], x = 'hora', y = 'value', hue = 'variable', ci = False);

We see that indeed in the central hours there is a loss of efficiency. But nowhere near the level of loss that we had seen in the previous analysis.

We are going to analyze the distribution of efficiency in those hours.

temp = df.between_time('08:00:00','15:00:00')
temp = temp[temp.planta == 'p2']

temp.eficiencia.plot.density();

There is a data set with zero efficiency, and that is what causes the problem. But what is the cause of that zero efficiency?

We are going to select those cases and review them.

temp[temp.kw_dc == 0]

Parece que no es problema del inverter, si no de que en esos momentos no se ha generado DC.

Vamos a poner la condición de que DC > 0 y ver ahí cual es la eficiencia.

temp[temp.kw_dc > 0].eficiencia.plot.density();

Indeed when there is DC the efficiency is higher than 96%.

The question then is why is there no DC? Is there a pattern?

Let's create a DC = 0 flag so we can analyze it.

temp['kw_dc_cero'] = np.where(temp['kw_dc'] == 0, 1, 0)
temp

We will start with the numerical variables.

temp.groupby('kw_dc_cero')[['irradiacion','t_ambiente','t_modulo']].mean()

At room temperature there is not much difference, but at module temperature and irradiation yes.

Could it be that if it gets too hot the module stops generating DC?

Let's see it by comparing the module temperature with the DC generation.

sns.scatterplot(data = temp, x = 't_modulo', y = 'kw_dc',hue = 'kw_dc_cero');

The previous hypothesis is not confirmed, since there are many cases of high temperatures where DC is generated, and also of kw_dc equal to zero in almost all temperature ranges.

We are now going to analyze the categorical ones, starting with the inverter.

temp.groupby('inverter_id').kw_dc_cero.mean().sort_values(ascending = False).plot.bar();

There is a big difference in the percentage of zero DC production per inverter.

From some that have less than 5% to some that exceed 30%.

INSIGHT #4:: On plant 2 there are several inverters that are not getting enough DC production, and therefore whose modules need revision.

We are going to analyze the inverters from the point of view of the average efficiency to see if there are "good and bad".

temp[temp.kw_dc > 0].groupby(['inverter_id','date'],as_index = False).eficiencia.mean().boxplot(column = 'eficiencia', by = 'inverter_id', figsize = (14,10))
plt.xticks(rotation = 90);

INSIGHT #5:: Once discounting the problem of not generating DC, the inverters of plant 2 do work well and do the job of transformation to AC well.

To finish analyzing the efficiency of the inverters, we can see their performance on each of the days to see if there may have been specific problems.

temp[temp.kw_dc > 0].groupby(['inverter_id','date']).eficiencia.mean().unstack(level = 0).plot(subplots = True, sharex=True, figsize=(20,40))
plt.xticks(rotation = 90);

To have a term of comparison we are going to repeat the analyzes with plant 1.

temp = df.between_time('08:00:00','15:00:00')
temp = temp[temp.planta == 'p1']
temp['kw_dc_cero'] = np.where(temp['kw_dc'] == 0, 1, 0)
temp

temp.eficiencia.plot.density();

We see that no, here all the inverters have a constant efficiency (although very low)

temp.groupby(['inverter_id','date'],as_index = False).eficiencia.mean().boxplot(column = 'eficiencia', by = 'inverter_id', figsize = (14,10))
plt.xticks(rotation = 90);

We see that except for specific days in some inverters, in the rest the efficiency is constant.

We are going to review the average daily efficiency for each inverter.

temp.groupby(['inverter_id','date']).eficiencia.mean().unstack(level = 0).plot(subplots = True, sharex=True, figsize=(20,40))
plt.xticks(rotation = 90);

In the inverter analysis we see again that all the data are constant.

We are going to verify that then there are no failures in the generation of DC.

temp.groupby('inverter_id').kw_dc_cero.mean().sort_values(ascending = False).plot.bar();

We see that although there are some inverters that have had failures, their magnitude is less than 2% of the measurements.

Therefore the generation of DC in plant 1 is correct, and the failure is in the transformation from DC to AC.

CONCLUSIONS

After an analysis of the data we can conclude that:

• There are serious data quality problems. It should be reviewed in which part of the chain these problems are generated, including the meters of the plants.
• The fact that the DC generation is about 10 times higher in plant 1 than in plant 2, added to the fact that the efficiency in plant 1 is over 10% leads us to think that the DC generation data on floor 1 it may be artificially scaled for some reason.
• But for now, in the absence of verification, we will assume that the data is correct.
• The two plants have received high amounts of irradiation, we have not located any problem at this stage
• Although the ambient temperature is higher on floor 2 and its modules get hotter than those on floor 1, this does not seem to have a significant impact
• Plant 1 DC generation works fine, the modules seem to bring DC to the inverters.
• The DC generation of plant 2 does NOT work well, some modules carry very little DC to the inverters even in the hours of greatest irradiation.
• The transformation from DC to AC of floor 1 does NOT work well, it only transforms around 10%, yes, constantly. And this low efficiency is not due to moments of non-reception of DC nor is it concentrated in specific inverters, but rather it seems more structural (again keep in mind that it could be due to a data quality problem in kw_dc of plant 1
• The transformation from DC to AC of plant 2 works well, since once the periods of zero DC generation are eliminated, the rest have an efficiency greater than 97%

Recommendations:

• Review the data collection and its reliability
• Maintenance check on the inverter modules of plant 2 in which there are many moments of zero DC generation
• Maintenance review of the inverters of Plant 1