Subtraction with condition in the same column

[umrubens]

I have the Finbra database with observations from all Brazilian municipalities and I need to perform the following operation: subtract account 12.364 - Ensino Superior from account 12 - Educacao from all municipalities. One observation is that not all municipalities have an account of 12.364 - Higher Education. Image attached. Please help!

[andresrcs]

Can you please share a small part of the data set in a copy-paste friendly format?

In case you don't know how to do it, there are many options, which include:

1. If you have stored the data set in some R object, dput function is very handy.

2. In case the data set is in a spreadsheet, check out the datapasta package. Take a look at this link.

[umrubens]

@andresrcs, thank you! Here it is.

                                            ~Institution, ~IBGE.Code,                   ~Account,     ~Value,
             "Prefeitura Municipal de Bonfinopolis - GO",   5203559L,            "12 - Educacao",  568195416,
             "Prefeitura Municipal de Bonfinopolis - GO",   5203559L, "12.364 - Ensino Superior",       8291,
    "Prefeitura Municipal de Sao Jose do Hortencio - RS",   4318481L,            "12 - Educacao",  464725659,
    "Prefeitura Municipal de Sao Jose do Hortencio - RS",   4318481L, "12.364 - Ensino Superior",    7940899,
           "Prefeitura Municipal de Coqueiro Baixo - RS",   4305835L,            "12 - Educacao",  262883731,
           "Prefeitura Municipal de Coqueiro Baixo - RS",   4305835L, "12.364 - Ensino Superior",    1037466,
          "Prefeitura Municipal de Cruzeiro do Sul - RS",   4306205L,            "12 - Educacao",   89843484,
                 "Prefeitura Municipal de Planalto - PR",   4119806L,            "12 - Educacao",  872323295,
                 "Prefeitura Municipal de Planalto - PR",   4119806L, "12.364 - Ensino Superior",   11288845,
"Prefeitura Municipal de Santo Antonio das Missoes - RS",   4317707L,            "12 - Educacao",  945573686,
           "Prefeitura Municipal de Doutor Ricardo - RS",   4306759L,            "12 - Educacao",  268365105,
           "Prefeitura Municipal de Doutor Ricardo - RS",   4306759L, "12.364 - Ensino Superior",     432934,
                    "Prefeitura Municipal de Barao - RS",   4301651L,            "12 - Educacao",  568133091,
                    "Prefeitura Municipal de Barao - RS",   4301651L, "12.364 - Ensino Superior",   29224727,
                  "Prefeitura Municipal de Relvado - RS",   4315453L,            "12 - Educacao",   20782943,
                  "Prefeitura Municipal de Relvado - RS",   4315453L, "12.364 - Ensino Superior",     833856,
              "Prefeitura Municipal de Barra Funda - RS",   4301958L,            "12 - Educacao",  319011753,
              "Prefeitura Municipal de Barra Funda - RS",   4301958L, "12.364 - Ensino Superior",     952447,
        "Prefeitura Municipal de Monte Belo do Sul - RS",   4312385L,            "12 - Educacao",  311257417,
        "Prefeitura Municipal de Monte Belo do Sul - RS",   4312385L, "12.364 - Ensino Superior",   17407447

[andresrcs]

Well, that is not exactly what I meant but anyways, is this what you are trying to do?

library(tidyverse)

sample_df <- tribble(~Institution, ~IBGE.Code,                   ~Account,     ~Value,
        "Prefeitura Municipal de Bonfinopolis - GO",   5203559L,            "12 - Educacao",  568195416,
        "Prefeitura Municipal de Bonfinopolis - GO",   5203559L, "12.364 - Ensino Superior",       8291,
        "Prefeitura Municipal de Sao Jose do Hortencio - RS",   4318481L,            "12 - Educacao",  464725659,
        "Prefeitura Municipal de Sao Jose do Hortencio - RS",   4318481L, "12.364 - Ensino Superior",    7940899,
        "Prefeitura Municipal de Coqueiro Baixo - RS",   4305835L,            "12 - Educacao",  262883731,
        "Prefeitura Municipal de Coqueiro Baixo - RS",   4305835L, "12.364 - Ensino Superior",    1037466,
        "Prefeitura Municipal de Cruzeiro do Sul - RS",   4306205L,            "12 - Educacao",   89843484,
        "Prefeitura Municipal de Planalto - PR",   4119806L,            "12 - Educacao",  872323295,
        "Prefeitura Municipal de Planalto - PR",   4119806L, "12.364 - Ensino Superior",   11288845,
        "Prefeitura Municipal de Santo Antonio das Missoes - RS",   4317707L,            "12 - Educacao",  945573686,
        "Prefeitura Municipal de Doutor Ricardo - RS",   4306759L,            "12 - Educacao",  268365105,
        "Prefeitura Municipal de Doutor Ricardo - RS",   4306759L, "12.364 - Ensino Superior",     432934,
        "Prefeitura Municipal de Barao - RS",   4301651L,            "12 - Educacao",  568133091,
        "Prefeitura Municipal de Barao - RS",   4301651L, "12.364 - Ensino Superior",   29224727,
        "Prefeitura Municipal de Relvado - RS",   4315453L,            "12 - Educacao",   20782943,
        "Prefeitura Municipal de Relvado - RS",   4315453L, "12.364 - Ensino Superior",     833856,
        "Prefeitura Municipal de Barra Funda - RS",   4301958L,            "12 - Educacao",  319011753,
        "Prefeitura Municipal de Barra Funda - RS",   4301958L, "12.364 - Ensino Superior",     952447,
        "Prefeitura Municipal de Monte Belo do Sul - RS",   4312385L,            "12 - Educacao",  311257417,
        "Prefeitura Municipal de Monte Belo do Sul - RS",   4312385L, "12.364 - Ensino Superior",   17407447)

sample_df %>% 
    spread(Account, Value) %>%
    mutate(`12.364 - Ensino Superior` = replace_na(`12.364 - Ensino Superior`, 0),
           substraction = `12 - Educacao` - `12.364 - Ensino Superior`)
#> # A tibble: 11 x 5
#>    Institution          IBGE.Code `12 - Educacao` `12.364 - Ensino… substraction
#>    <chr>                    <int>           <dbl>             <dbl>        <dbl>
#>  1 Prefeitura Municipa…   4301651       568133091          29224727    538908364
#>  2 Prefeitura Municipa…   4301958       319011753            952447    318059306
#>  3 Prefeitura Municipa…   5203559       568195416              8291    568187125
#>  4 Prefeitura Municipa…   4305835       262883731           1037466    261846265
#>  5 Prefeitura Municipa…   4306205        89843484                 0     89843484
#>  6 Prefeitura Municipa…   4306759       268365105            432934    267932171
#>  7 Prefeitura Municipa…   4312385       311257417          17407447    293849970
#>  8 Prefeitura Municipa…   4119806       872323295          11288845    861034450
#>  9 Prefeitura Municipa…   4315453        20782943            833856     19949087
#> 10 Prefeitura Municipa…   4317707       945573686                 0    945573686
#> 11 Prefeitura Municipa…   4318481       464725659           7940899    456784760

^{Created on 2020-03-01 by the reprex package (v0.3.0.9001)}

[umrubens]

@andresrcs perfect. Thank you very much for your help. I'm sorry for the bad writing on this question. I'll try to improve on the next ones.

[andresrcs]

If your question's been answered (even by you!), would you mind choosing a solution? It helps other people see which questions still need help, or find solutions if they have similar problems. Here’s how to do it:

FAQ: How do I mark a solution?

If your question has been answered, don't forget to mark the solution! How do I mark a solution? Find the reply you want to mark as the solution and look for the row of small gray icons at the bottom of that reply. Click the one that looks like a box with a checkmark in it: [image] Hovering over the mark solution button shows the label, "Select if this reply solves the problem". If you don't see the mark solution button, try clicking the three dots button ( ••• ) to expand the full set of options. When a solution is chosen, the icon turns green and the hover label changes to: "Unselect if this reply no longer solves the problem". Success! [solution_reply_author] …