Update worksheet by another worksheet

Asked

Viewed 49 times

0

I have two dataframes with identical columns, one being the database and another monthly updates. I need to update the database with these monthly updates. I thought of the following code: 

if(A$Empreendimento == B$Empreendimento)

  {

  A$`POT [MW]`<-B$`POT [MW]`
  A$STATUS<-B$STATUS
  A$`VALIDADE ATUAL`<-B$Validade
  A$RESOLUÇÃO<-B$Resolução

}

But I get if following error message

Error in A$Empreendimento : $ operator is invalid for atomic vectors

The idea would be to update by the Projects field, if you have the same enterprise in the two spreadsheets, you update the fields of spreadsheet A based on spreadsheet B

Edit1: dput(head(A, 10))

structure(list(Empreendimento = c("J1", "AMB 108", "Água Branca", 
"Foz do Apiacás", "Prata", "Araguanã", "Couto Magalhães", "Mortes 2", 
"Santa Isabel", "Torixoréu"), Tipo = c("UHE", "UHE", "UHE", "UHE", 
"UHE", "UHE", "UHE", "UHE", "UHE", "UHE"), Processo = c(NA, NA, 
NA, "48500.004786/2006-80", "48500.001486/2009-35", NA, NA, NA, 
NA, "48500.000218/2000-22"), `POT [MW]` = c(140.5, 11.7, 73, 
275, 12.4, 960, 150, 310.4, 1087, 408), `Curso D'Água` = c("Acari", 
"Amambaí", "Amapari", "Apiacás", "Aporé", "Araguaia", "Araguaia", 
"Araguaia", "Araguaia", "Araguaia"), UF = c("AM", "MS", "AM", 
"MT", "GO/MS", "MA/PA/TO", "GO/MT", "PA", "PA/TO", "GO/MT"), 
    Empreendedor = c(NA, NA, NA, "Empresa de Pesquisa Energética - EPE", 
    NA, NA, NA, NA, NA, NA), STATUS = c("Eixo Disponível", "Eixo Disponível", 
    "Eixo Disponível", "Aceito", "Eixo Disponível", "Eixo Disponível", 
    "Eixo Disponível", "Eixo Disponível", "Eixo Disponível", 
    "Eixo Disponível"), Ato = structure(c(NA, NA, NA, 1242, 3246, 
    NA, NA, NA, NA, NA), class = "Date"), Data = structure(c(NA, 
    NA, NA, 1273104000, 1481846400, NA, NA, NA, NA, NA), class = c("POSIXct", 
    "POSIXt"), tzone = "UTC"), Resolução = c(NA, NA, NA, "395/1998", 
    NA, NA, NA, NA, NA, "395/1998"), Validade = structure(c(NA_real_, 
    NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
    NA_real_, NA_real_, NA_real_), class = c("POSIXct", "POSIXt"
    ), tzone = "UTC")), row.names = c(NA, -10L), class = c("tbl_df", 
"tbl", "data.frame"))

dput(head(B, 10)

 structure(list(Num = c("5", "8", "9", "26", "27", "30", "29", 
"39", "54", NA), Processo = c(NA, NA, NA, NA, NA, "48500.006323/2014-14", 
"48500.005280/2014-41", NA, NA, NA), Tipo = c("UHE", "UHE", "UHE", 
"UHE", "UHE", "UHE", "UHE", "UHE", "UHE", "UHE"), Empreendimento = c("A17PA118 (Bacuri)", 
"A26PA184 (Miriti)", "A29PA208 (Touré)", "A34PA250 (Samuã)", 
"A38PA100", "A41PA008 Panamã", "A41PA008 Panamã", "Açaipé B", 
"Água Branca I", "Água Clara"), `POT [MW]` = c("225.7", "140.5", 
"186.3", "104.1", "177.8", NA, "870.4", "831.1", "73", "32.799999999999997"
), `Curso D'Água` = c("Paru", "Paru", "Paru", "Paru", "Paru", 
"Paru", "Paru", "Jari", "Amapari", "Verde"), `Bacia do rio` = c("PARU", 
"PARU", "PARU", "PARU", "PARU", "PARU", "PARU", "JARI", "ARAGUARI", 
"PARANÁ"), `Região Hidrográfica` = c("AMAZÔNICA", "AMAZÔNICA", 
"AMAZÔNICA", "AMAZÔNICA", "AMAZÔNICA", "AMAZÔNICA", "AMAZÔNICA", 
"AMAZÔNICA", "AMAZÔNICA", "PARANÁ"), UF = c("PA", NA, "PA", NA, 
"PA", "PA", "48500.005280/2014-42", NA, NA, "MS"), Empreendedor = c("Omega Energia Renovável", 
"Omega Energia Renovável", "Omega Energia Renovável", "Omega Energia Renovável", 
"Omega Energia Renovável", "Enel Brasil S.A.", "Zeta Energia S.A.", 
NA, "Hydros Engenharia", "Minas PCH S.A."), STATUS = c("EIXO DISPONÍVEL", 
"EIXO DISPONÍVEL", "EIXO DISPONÍVEL", "EIXO DISPONÍVEL", "EIXO DISPONÍVEL", 
"REGISTRO ATIVO", "REGISTRO ATIVO", "EIXO DISPONÍVEL", "EIXO DISPONÍVEL", 
"ACEITO"), DESPACHO...12 = c(NA, NA, NA, NA, NA, "4744", "4104", 
NA, NA, "539"), `DATA DE 
PUBLICAÇÃO...13` = structure(c(NA, 
NA, NA, NA, NA, 1418169600, 1413158400, NA, NA, 1425427200), class = c("POSIXct", 
"POSIXt"), tzone = "UTC"), RESOLUÇÃO = c(NA, NA, NA, NA, NA, 
"395/1998", "395/1998", NA, NA, "412/2010"), `VALIDADE ATUAL` = structure(c(NA, 
NA, NA, NA, NA, 1481500800, 1476403200, NA, NA, NA), class = c("POSIXct", 
"POSIXt"), tzone = "UTC"), DESPACHO...16 = c("1272 - Aprov", 
"1272 - Aprov", "1272 - Aprov", "1272 - Aprov", "1272 - Aprov", 
NA, "1272 - Aprov", "4757 - Aprov", "157 - Aprov", NA), `DATA DE 
PUBLICAÇÃO...17` = structure(c(1398211200, 
1398211200, 1398211200, 1398211200, 1398211200, 590803200, 1398211200, 
1323302400, 955929600, 1425427200), class = c("POSIXct", "POSIXt"
), tzone = "UTC"), `SITUAÇÃO ESTUDO/ANEEL` = c(NA, NA, NA, NA, 
NA, NA, NA, "Inventário:Empresa de Pesquisa Energética - EPE", 
NA, NA), CODNW = c(NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_), E = c(NA, 
NA, NA, NA, NA, 259490.614, 259490.614, NA, NA, NA), N = c(NA, 
NA, NA, NA, NA, 9884644.072, 9884644.072, NA, NA, NA), FUSO = c(NA, 
NA, NA, NA, NA, 22, 22, NA, NA, NA), `Energia Firme MWmed` = c(124.3, 
77.3, 102.5, 57.3, 97.8, 478.7, 478.7, 457.11, 22, 24.9), `VAZÃO MLT

m³/s
(a)` = c(NA, 
NA, NA, NA, NA, NA, "677.77", NA, NA, NA), `Tempo Enchimento
(meses)
(a)` = c(NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
NA_real_, NA_real_), `Área total do Reservatório (km²)` = c("31.25", 
"37.049999999999997", "22.23", "51.89", "349.37", "185.02", "185.02", 
"293.39999999999998", "120.81", "31.08"), `Área Alagada (km²)` = c(NA, 
NA, NA, NA, NA, "-", NA, NA, NA, NA), `Potência /área (MW/km²)` = c(0.138458130261409, 
0.263701067615658, 0.119323671497585, 0.498463016330452, 1.96496062992126, 
NA, 4.70435628580694, 0.353026109974732, 1.65493150684932, 0.947560975609756
), `Margem do Rio Amazonas` = c("ME", "ME", "ME", "ME", "ME", 
"ME", "ME", "ME", NA, NA), `Reservatório
a Fio d'Água / Regularização
` = c("Fio d'Água", 
"Fio d'Água", "fio d'agua", "Fio d'Água", "Fio d'Água", "Regularização", 
"Regularização\r\n Em 10/2015 o IBAMA arquivou o pedido de licenciamento. (ESEC do Jari)", 
"Regularização", "Regularização", "Fio d'Água"), `Nível dágua 
máximo normal (m)` = c("160", 
"208", "240", "268", "118", NA, "100", "86", "100", "310.89999999999998"
), `Depleção 
(m)` = c("0", "0", "0", "0", "0", "6", "6", "3.45", 
"8.5", "0"), `Queda de Referência` = c(39.9, 23.28, 31.04, 17.46, 
19.49, NA, 88.96, 53.04, NA, NA), `Volume Útil 
(hm³)` = c("0", 
"0", "0", "0", "0", NA, "1059.5800000000004", "916.65", "777.255", 
"0"), `Energia Armazenável - Curto prazo / Longo Prazo (MWmed)` = c("0", 
NA, "0", "0", "0", "306", "306", "246", NA, NA), `Origem do Orçamento
` = c("Estudos de Inventário", 
"Estudos de Inventário", "Estudos de Inventário", "Estudos de Inventário", 
"Estudos de Inventário", "Estudos de Inventário / Reavaliação da SEG", 
"Estudos de Inventário / Reavaliação da SEG", "Estudos de Inventário", 
"Estudos de Inventário", "Estudos de Inventário"), `Data Base do OPE Original, S/ Conexão` = structure(c(1314835200, 
1314835200, 1314835200, 1314835200, 1314835200, 1480550400, 1480550400, 
1228089600, 880934400, 1377993600), class = c("POSIXct", "POSIXt"
), tzone = "UTC"), `Custo Implantação na data base OPE Original, S/ Conexão (s/JDC) 
[R$ x 10³]` = c(1016371.31787006, 
794210.237796087, 889947.354731503, 662617.059897763, 997238.13643928, 
3382790, 3382790, 2207621.82366692, 122780.475241675, 209490.255146927
), `Fator de Atualização do do Custo para Data de Referência` = c(1.39860629739097, 
1.39860629739097, 1.39860629739097, 1.39860629739097, 1.39860629739097, 
0.989168913267202, 0.989168913267202, 1.60042554770711, 4.46840051117328, 
1.23757485316344), `Custo Implantação sem Conexão (s/JDC) na Data de Referência 
[R$ x 10³]` = c(1421503.32566063, 
1110787.44003399, 1244685.97467392, 926740.392731701, 1394743.53762241, 
3346150.70811116, 3346150.70811116, 3533134.36627231, 548632.338332, 
259259.87175263), `Data Base do Custo de CONEXÃO` = structure(c(NA, 
NA, NA, NA, NA, 1480550400, 1480550400, NA, NA, NA), class = c("POSIXct", 
"POSIXt"), tzone = "UTC"), `Custo de CONEXÃO (s/JDC)  (R$ x 10³) - Para os Estudos de inventário: Valor conexão Estimado pela EPE` = c(NA, 
NA, NA, NA, NA, 203287, 203287, NA, NA, NA), `Fator de Atualização do Custo de CONEXÃO para Data de Referência` = c(NA, 
NA, NA, NA, NA, 0.989168913267202, 0.989168913267202, NA, NA, 
NA), `VALOR TOTAL, incluindo conexão, na Data de Referência (s/ JDC)
 (RS x 10³)` = c(1421503.32566063, 
1110787.44003399, 1244685.97467392, 926740.392731701, 1394743.53762241, 
3547235.88898251, 3547235.88898251, 3533134.36627231, 548632.338332, 
259259.87175263), `Fator de Ajuste decorrente da origem do Orçamento` = c(1.35, 
1.35, 1.35, 1.35, 1.35, 1.35, 1.35, 1.35, 1.35, 1.35), `CAPEX - Valor atualizado para cálculo da Tarifa (R$ x 10³)` = c(1919029.48964185, 
1499563.04404588, 1680326.06580979, 1251099.5301878, 1882903.77579026, 
4788768.45012639, 4788768.45012639, 4769731.39446762, 740653.6567482, 
350000.826866051), `Tarifa Estimada (R$/MWh)` = c(NA_real_, NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
NA_real_), `ICB
(R$/MWh)` = c(207.193174371653, 260.059956906871, 
220.065748818707, 292.657506797305, 257.680662472674, NA, 130.608898001293, 
136.162775727809, 454.538990073241, 191.258479309137), `CAPEX 
PNE 2050
(S/ JUROS)
R$ x 1000` = c(1810034.8517104, 
1414392.73690711, 1584889.00653672, 1180041.1430994, 1775960.96099259, 
4516781.96624151, 4516781.96624151, 4498826.1534714, 698586.936259741, 
330121.917445491), `CAPEX 
PNE 2050
(S/ JUROS)
R$/kW` = c(8019.64932082587, 
10066.8522199794, 8507.18736734685, 11335.6497896196, 9988.53183910346, 
NA, 5189.31751636203, 5413.09848811383, 9569.68405835261, 10064.6926050455
), `
PDE2029
DEZ-18
CAPEX 


(S/ JUROS)
R$ x 1000` = c(2069076.5989858, 
1616812.46686747, 1811709.17914439, 1348921.82474842, 2030126.24957458, 
5163197.74740263, 5163197.74740263, 5142672.20228217, 798564.668965547, 
377367.062050402), `OBSERVAÇÃO SEG...52` = c(NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_), `TERRAS INDIGENAS (TI)` = c("NÃO", 
"NÃO", "NÃO", "NÃO", NA, NA, NA, "NÃO", "SIM", NA), QUILOMBOLAS = c("NÃO", 
"NÃO", "NÃO", "NÃO", NA, "NÃO", "NÃO", "NÃO", NA, NA), `UNIDADE CONSERVAÇÃO (UC)` = c("FLOTA DO PARU", 
"FLOTA DO PARU E REBIO DO MAICURU", "FLOTA DO PARU E REBIO DO MAICURU", 
"FLOTA DO PARU E REBIO DO MAICURU", "FLOTA DO PARU", "FLOTA DO PARU e ESEC DO JARI", 
"FLOTA DO PARU e ESEC DO JARI", "UC, RDS e ESEC", "SIM", NA), 
    `Classificação Socioambiental` = c("Interfere em UC de US", 
    "Interfere em UC de PI", "Interfere em UC de US", "Interfere em UC de PI", 
    "Interfere em UC de US", NA, "Interfere em UC de PI", "Interfere em UC de PI", 
    "Interfere em UC de PI", "Não interfere em UC, TI e TQ"), 
    `Avaliação Processual  
Cenário 1` = c("2027", "2030", "2027", 
    "2030", "2027", NA, "2030", "2030", "2030", "2027"), `Avaliação Processual  
Cenário 2` = c("2027", 
    "2030", "2027", "2030", "2027", NA, "2030", "2030", "2030", 
    "2027"), `Órgão Ambiental` = c(NA, NA, NA, NA, NA, NA, "IBAMA", 
    NA, NA, NA), `Resumo Situação` = c(NA, NA, NA, NA, NA, "sem movimentação", 
    "ESTUDO DE VIABILIDADE PARALIZADO AGUARDANDO RESPOSTA DA ANEEL QUANTO A POSSIBILIDADE DE REDUZIR O NA CONFORME INVENTÁRIO VER SITUAÇÃO ATUAL \r\nem 04/2015 o desenvolvedor(Omega)  solicitou o TR ao IBAMA. Em 10/2015 o IBAMA respondeu que devido a sobreposição do reservatório da UHE com a ESEC do Jari arquivou o pedido de licenciamento.Em 10/16 a Omega encaminha carta para ANEEL informando o arquivamento do licenciamento e solicitando revogar o despacho de aprovação do inventário e emitir novo despacho aprovando a alternativa de divisão de queda proposta pelo desenvolvedor onde a UHE Panamã não interfere com a ESEC e nem com a FLOTA do Paru\r\n•  Processo arquivado no Ibama por interferir na ESEC. (info MME - mar/16)\r\n•  TR não emitido. EIA não iniciado.", 
    NA, NA, NA), `Afeta UC?` = c(NA_character_, NA_character_, 
    NA_character_, NA_character_, NA_character_, NA_character_, 
    NA_character_, NA_character_, NA_character_, NA_character_
    ), `Trativas UC *2` = c(NA_character_, NA_character_, NA_character_, 
    NA_character_, NA_character_, NA_character_, NA_character_, 
    NA_character_, NA_character_, NA_character_), `Afeta TI?` = c(NA_character_, 
    NA_character_, NA_character_, NA_character_, NA_character_, 
    NA_character_, NA_character_, NA_character_, NA_character_, 
    NA_character_), `Tratativas TI` = c(NA_character_, NA_character_, 
    NA_character_, NA_character_, NA_character_, NA_character_, 
    NA_character_, NA_character_, NA_character_, NA_character_
    ), `Tem TR?  *3` = c(NA_character_, NA_character_, NA_character_, 
    NA_character_, NA_character_, NA_character_, NA_character_, 
    NA_character_, NA_character_, NA_character_), `Emissão do TR` = c(NA_character_, 
    NA_character_, NA_character_, NA_character_, NA_character_, 
    NA_character_, NA_character_, NA_character_, NA_character_, 
    NA_character_), `EIA/RIMA +ECI *4` = c(NA_character_, NA_character_, 
    NA_character_, NA_character_, NA_character_, NA_character_, 
    NA_character_, NA_character_, NA_character_, NA_character_
    ), `Atendimento a demandas judiciais` = c(NA_character_, 
    NA_character_, NA_character_, NA_character_, NA_character_, 
    NA_character_, NA_character_, NA_character_, NA_character_, 
    NA_character_), LP = c(NA_character_, NA_character_, NA_character_, 
    NA_character_, NA_character_, NA_character_, NA_character_, 
    NA_character_, NA_character_, NA_character_), `DATA SMA LEILÃO GERAÇÃO` = c(NA_real_, 
    NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
    NA_real_, NA_real_, NA_real_), `CONEXÃO 
SIN` = c(NA_real_, 
    NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
    NA_real_, NA_real_, NA_real_), `SITUAÇÃO
 SIN` = c(NA, NA, 
    NA, NA, NA, NA, NA, NA, NA, NA), GET = c(NA, NA, NA, NA, 
    NA, NA, NA, NA, NA, NA), LEILÃO = c(NA, NA, NA, NA, NA, NA, 
    NA, NA, NA, NA), `PDE 2026` = c(NA, NA, NA, NA, NA, NA, NA, 
    NA, NA, NA), `PDE 2027` = c(NA_character_, NA_character_, 
    NA_character_, NA_character_, NA_character_, NA_character_, 
    NA_character_, NA_character_, NA_character_, NA_character_
    ), `PDE 2029` = c(NA, NA, NA, NA, NA, NA, "sim", NA, NA, 
    NA), `OBSERVAÇÃO SEG...78` = c(NA, NA, NA, NA, NA, "Estudo não disponível na EPE", 
    NA, NA, NA, NA), `Incluído no PNE?` = c("SIM", "SIM", "SIM", 
    "SIM", "SIM", NA, "SIM", "SIM", "SIM", "SIM")), row.names = c(NA, 
-10L), class = c("tbl_df", "tbl", "data.frame"))
r updating spreadsheets
  • 1

    Can you please, edit the question with the departure of dput(dados) or, if the base is too large, dput(head(dados, 10)) for each of the spreadsheets A and B? And what is UHE? Error message happens when you run that line of code?

  • 1

    The mistake is saying that A is an atomic vector and therefore the operator $ is not applicable. Here is the source of your error.Check the object A with functions such as str() or class().

  • @Tomásbarcellos A is capable of being a matrix. The operator $ is not valid but the matrices have columns and sometimes appear users who make the mistake of thinking that the access is done in the same way as with data frames..

  • @Noisy-Reinstatemonic I will edit the question with the features you asked me to use. I also edited the UHE. In this case I called spreadsheets, but they are two dataframes

  • The problem is actually the following: I have two spreadsheets in Excel, one basic and one monthly. I want to update the database automatically with the monthly data, but only in cases where the enterprise column, present of the two spreadsheets, are equal.

  • In A has Empreendimento equal to "Água Branca I" and in B it’s just "Água Branca" Are the same?

  • @Noisy-Reinstatemonic are different

Show 2 more comments

1 answer

0


The following code should solve the question problem.

  1. For each line of A,
  2. Determines the line of B that has the same Empreendimento.
  3. If you found one and only one, refresh the question columns.

This is done in a cycle for.

for(i in seq_len(nrow(A))){
  j <- which(B$Empreendimento == A$Empreendimento[i])
  if(length(j) == 1){
    A[["POT [MW]"]][i] <- B[["POT [MW]"]][j]
    A[["STATUS"]][i] <- B[["STATUS"]][j]
    A[["VALIDADE ATUAL"]][i] <- B[["Validade"]][j]
    A[["RESOLUÇÃO"]][i] <- B[["Resolução"]][j]
  }
}

  • Apparently worked, ran without giving errors, I will check the resulting basis to check if met what I needed. Thank you very much for the help!

Browser other questions tagged r updating spreadsheets

You are not signed in. Login or sign up in order to post.