Import from Shelly 3EM energy readings for use in energy dashboard

Share your Projects! Scripts
,
1

Hi everyone, I’ve been looking for a way to import Shelly 3EM’s historical energy data into Home-Assistant.

As I didn’t find anything that fully suited me, so I decided to write my own script.

It generates a TXT file based on Shelly’s exported CSV.

Then, just read the TXT file inside SQLlite with the command .read FILE.TXT.

Code comments are in Brazilian Portuguese.

Batteries are not included.

I meant, I don’t intend to support it, use at your own risk.

Good luck!

P.S. It’s my first post. If this is in the wrong place, I’m sorry.

# %%
#Importa a biblioteca pandas
import pandas as pd

#Utilidades
from datetime import timedelta

# %%
#Linha com o nome do Arquivo
csv_file = "A.CSV"
txt_file = "A.TXT"

#Linha para definir o ídice para o imported e exported
meta_imported = 25
meta_exported = 26

# %%
#Faz a leitura do arquivo csv
shelly = pd.read_csv('./csv/' + csv_file)

#Converte a coluna 'Date/time UTC' para uma formato de data
shelly['Date/time UTC'] = pd.to_datetime(shelly['Date/time UTC'], format= '%Y-%m-%d %H:%M')

#define a coluna 'Date/time UTC' como indice da tabela
shelly.set_index(keys='Date/time UTC', inplace=True)

#Faz a ordenação pelo index... 
shelly.sort_index()

# %%
#cria a estrutura que vai para o Home_Assitant
ha = shelly.resample('H').sum()

#Para facilitar, renomear as colunas...
ha.rename(columns={ha.columns[0]: 'state_imported', ha.columns[1]: 'state_exported'}, inplace=True )
ha.index.rename('start', inplace=True )

#Ajusta as colunas de Wh para kWh 
ha['state_imported'] = (ha['state_imported'] / 1000)
ha['state_exported'] = (ha['state_exported'] / 1000)

#A ideia é criar uma coluna STATE que é o somatório do valor diário
ha['sum_imported'] = ha['state_imported'].cumsum()
ha['sum_exported'] = ha['state_exported'].cumsum()

#Para ficar igual ao df do home_assistant...
ha = ha.loc[:, ['state_imported', 'sum_imported', 'state_exported', 'sum_exported' ]]

# %%
#abre o arquivo de saída - em 'write mode' é sempre criado um arquivo novo...
with open('./txt/'+ txt_file, 'w') as text_file:

    #via SQL, apaga todos os registros na DB
    #ex.: DELETE FROM statistics WHERE metadata_id = '56';
    text_file.write("DELETE FROM statistics WHERE metadata_id = '{}' ;\n".format(meta_imported))
    text_file.write("DELETE FROM statistics WHERE metadata_id = '{}' ;\n".format(meta_exported))

    #via SQL, faz o comando de inser de cada registro
    #ex.: INSERT INTO statistics (created, start, state, sum, metadata_id) VALUES ('2022-08-07 19:00:10', '2022-08-07 18:00:00', 0.056900, 0.025100, '56');
    for i in range(len(ha)):
        text_file.write(
            "INSERT INTO statistics (created, start, state, sum, metadata_id) VALUES ('{}', '{}', {:.8f}, {:.8f}, '{}');\n".format(
            ha.iloc[i].name + timedelta(hours=1,seconds=10),
            ha.iloc[i].name,
            ha['state_imported'].iloc[i],
            ha['sum_imported'].iloc[i],
            meta_imported))
            
    for i in range(len(ha)):        
        text_file.write(
            "INSERT INTO statistics (created, start, state, sum, metadata_id) VALUES ('{}', '{}', {:.8f}, {:.8f}, '{}');\n".format(
            ha.iloc[i].name + timedelta(hours=1,seconds=10),
            ha.iloc[i].name,
            ha['state_exported'].iloc[i],
            ha['sum_exported'].iloc[i],
            meta_exported))
1 Like