How to plot a graph of columns grouped with ggplot

Question

How can I chart columns grouped by year? I have an extensive database with several variables and would like to plot a chart separately per year. How to do it? For some variables the chart is getting like this, but I still could not adjust by year:

So far my code is like this:

ggplot(data=espacial, aes(x=AREAURB_2018, y=microrregião, fill=microrregião))+
    geom_bar(aes(fill=microrregião), stat="identity", position="dodge")

Here only has the Cattle Herd (Pastebin):

CodMun;microrregião;MUNIC;EFETIVOBOV_2000;EFETIVOBOV_2001;EFETIVOBOV_2002;EFETIVOBOV_2003;EFETIVOBOV_2004;EFETIVOBOV_2005;EFETIVOBOV_2006;EFETIVOBOV_2007;EFETIVOBOV_2008;EFETIVOBOV_2009;EFETIVOBOV_2010;EFETIVOBOV_2011;EFETIVOBOV_2012;EFETIVOBOV_2013;EFETIVOBOV_2014;EFETIVOBOV_2015;EFETIVOBOV_2016;EFETIVOBOV_2017;EFETIVOBOV_2018
1500404,00;Santarém;Alenquer(PA);96000,00;99778,00;120000,00;106080,00;137164,00;151468,00;161306,00;167401,00;112400,00;147352,00;170396,00;166052,00;160908,00;168444,00;191140,00;189902,00;190903,00;188400,00;213741,00
1500503,00;Almeirim;Almeirim(PA);12031,00;18728,00;20600,00;21630,00;22170,00;23278,00;23976,00;25395,00;33108,00;34763,00;36501,00;38326,00;39475,00;25312,00;24124,00;21853,00;21100,00;23414,00;28110,00
1500602,00;Altamira;Altamira(PA);202965,00;203066,00;272839,00;291061,00;314217,00;339517,00;365034,00;402340,00;399512,00;413625,00;555324,00;688901,00;668541,00;711028,00;687535,00;628481,00;657436,00;656430,00;707958,00
1500859,00;Altamira;Anapu(PA);48950,00;83407,00;68747,00;95149,00;118600,00;139260,00;172456,00;280321,00;175384,00;177857,00;171721,00;175023,00;225093,00;270460,00;262642,00;251681,00;267860,00;202400,00;237584,00
1501006,00;SãoFelixdoXingu;Aveiro(PA);14300,00;15000,00;15300,00;19957,00;23450,00;25604,00;30540,00;37329,00;35447,00;37229,00;37736,00;37202,00;31160,00;43637,00;42160,00;45668,00;47187,00;50942,00;50227,00
1501253,00;SãoFelixdoXingu;Bannach(PA);152313,00;191673,00;171374,00;171342,00;257085,00;201557,00;193461,00;165102,00;142813,00;171387,00;192965,00;195272,00;211401,00;193334,00;240905,00;231288,00;240080,00;258244,00;248230,00
1501451,00;Altamira;Belterra(PA);10000,00;11200,00;12650,00;13477,00;21017,00;17730,00;15700,00;9491,00;12200,00;23835,00;21765,00;20729,00;24136,00;26490,00;23843,00;23405,00;20842,00;21310,00;23810,00
1501725,00;Altamira;BrasilNovo(PA);139905,00;196187,00;195748,00;213054,00;245507,00;263254,00;259546,00;225866,00;219847,00;222722,00;206099,00;211000,00;217881,00;235712,00;241027,00;233652,00;240470,00;223901,00;230385,00
1502764,00;SãoFelixdoXingu;CumarudoNorte(PA);195110,00;197062,00;200017,00;213118,00;611151,00;549673,00;550712,00;447412,00;440378,00;588925,00;638983,00;683072,00;749278,00;821185,00;743174,00;772554,00;808396,00;846495,00;807787,00
1502855,00;Santarém;Curuá(PA);11960,00;13395,00;14735,00;22000,00;25000,00;18674,00;26646,00;29868,00;28286,00;26020,00;32647,00;34034,00;32109,00;32181,00;34433,00;37187,00;37148,00;44068,00;44924,00
1503002,00;Óbidos;Faro(PA);3890,00;3306,00;4138,00;5793,00;6372,00;7646,00;8500,00;13937,00;9226,00;7934,00;7457,00;871,00;1019,00;12149,00;10966,00;11318,00;12109,00;13102,00;9757,00
1503606,00;Itaituba;Itaituba(PA);146000,00;140000,00;151200,00;168641,00;180160,00;243759,00;270698,00;174318,00;267526,00;270657,00;309894,00;325567,00;337737,00;360703,00;344197,00;331381,00;328979,00;368776,00;343981,00
1503754,00;Itaituba;Jacareacanga(PA);22700,00;25000,00;26250,00;30187,00;5165,00;6802,00;8467,00;26789,00;26250,00;27038,00;12992,00;12993,00;15602,00;16085,00;15396,00;14716,00;14468,00;80476,00;81350,00
1503903,00;Óbidos;Juruti(PA);40500,00;46575,00;46575,00;50000,00;48500,00;32000,00;43627,00;38906,00;43254,00;37198,00;29758,00;30137,00;40996,00;21745,00;28441,00;27872,00;31486,00;40347,00;42608,00
1504455,00;Altamira;Medicilândia(PA);49175,00;70323,00;105443,00;111910,00;124373,00;137864,00;157128,00;143359,00;135117,00;134380,00;103939,00;123212,00;133885,00;143371,00;152562,00;158768,00;165016,00;166100,00;147540,00
1504802,00;Santarém;MonteAlegre(PA);100000,00;106761,00;114706,00;171865,00;174302,00;177451,00;196200,00;128412,00;191680,00;191680,00;186473,00;192242,00;205728,00;214596,00;219112,00;221942,00;214361,00;236968,00;244500,00
1505031,00;Itaituba;NovoProgresso(PA);155974,00;185000,00;183568,00;231741,00;378573,00;463784,00;561628,00;105989,00;412400,00;763088,00;636227,00;680671,00;687142,00;632521,00;590273,00;567077,00;579218,00;618365,00;618876,00
1505106,00;Óbidos;Óbidos(PA);85681,00;100681,00;120817,00;126857,00;120000,00;128394,00;124526,00;127768,00;125428,00;113090,00;120713,00;127269,00;127687,00;127269,00;129823,00;133551,00;133989,00;143462,00;143128,00
1505304,00;Óbidos;Oriximiná(PA);102184,00;78638,00;82842,00;91486,00;104000,00;131563,00;123430,00;131525,00;130485,00;139852,00;135156,00;125350,00;147748,00;159623,00;140549,00;141576,00;140340,00;148328,00;139429,00
1505437,00;SãoFelixdoXingu;OurilândiadoNorte(PA);156078,00;188993,00;194656,00;207877,00;202944,00;216980,00;198936,00;103510,00;173569,00;158221,00;181515,00;191113,00;194935,00;187148,00;191441,00;181074,00;186959,00;204229,00;192522,00
1505486,00;Altamira;Pacajá(PA);97345,00;138319,00;166542,00;250643,00;283204,00;313579,00;334595,00;256420,00;370333,00;415724,00;400216,00;358037,00;432578,00;492442,00;536460,00;573471,00;585925,00;643200,00;620785,00
1505650,00;Santarém;Placas(PA);68000,00;62000,00;50142,00;62756,00;66257,00;74174,00;78658,00;59450,00;87605,00;90381,00;90823,00;72122,00;102831,00;116124,00;127830,00;132387,00;135286,00;140977,00;140480,00
1505908,00;Almeirim;PortodeMoz(PA);9516,00;12850,00;17850,00;18742,00;20451,00;20241,00;20853,00;47913,00;48215,00;51107,00;53662,00;56077,00;57759,00;20278,00;50030,00;46670,00;51433,00;52918,00;46893,00
1506005,00;Santarém;Prainha(PA);62609,00;63861,00;65138,00;66440,00;79581,00;95497,00;100986,00;102125,00;113821,00;115630,00;121412,00;127482,00;131306,00;114548,00;119353,00;110494,00;114728,00;123652,00;110253,00
1506195,00;SãoFelixdoXingu;Rurópolis(PA);50000,00;50000,00;57962,00;59599,00;88226,00;105872,00;115984,00;117821,00;102000,00;112044,00;114000,00;116907,00;123990,00;136626,00;142087,00;145251,00;152471,00;159930,00;158534,00
1506807,00;Santarém;Santarém(PA);105332,00;100000,00;95000,00;106088,00;153115,00;133218,00;126935,00;104529,00;130613,00;116503,00;132008,00;118861,00;138911,00;129954,00;132300,00;115052,00;113620,00;118897,00;120537,00
1507300,00;SãoFelixdoXingu;SãoFélixdoXingu(PA);682407,00;1030019,00;1157707,00;1264991,00;1527017,00;1581518,00;1596411,00;1653231,00;1812870,00;1912009,00;2022366,00;2101726,00;2143760,00;2282445,00;2213310,00;2222949,00;2200338,00;2240496,00;2256734,00
1507805,00;Altamira;SenadorJoséPorfírio(PA);12742,00;21385,00;26938,00;30900,00;36546,00;45432,00;57396,00;60899,00;48105,00;52427,00;44467,00;57029,00;64208,00;68790,00;70379,00;73405,00;71425,00;70300,00;79920,00
1507979,00;Óbidos;TerraSanta(PA);41541,00;47772,00;57326,00;60192,00;50000,00;52000,00;35000,00;40000,00;47178,00;39157,00;38373,00;25000,00;27325,00;36672,00;35419,00;30133,00;33442,00;36177,00;29557,00
1508050,00;SãoFelixdoXingu;Trairão(PA);20400,00;25000,00;27000,00;28753,00;36686,00;44053,00;53815,00;68497,00;46972,00;65262,00;77972,00;86865,00;86478,00;96839,00;96562,00;95934,00;102281,00;108399,00;106543,00
1508084,00;SãoFelixdoXingu;Tucumã(PA);344136,00;341604,00;355781,00;349637,00;372073,00;359975,00;315622,00;175778,00;291651,00;284979,00;268172,00;274254,00;282481,00;282064,00;283818,00;284593,00;305524,00;329551,00;332269,00
1508159,00;Altamira;Uruará(PA);95345,00;125207,00;158723,00;193905,00;232912,00;250739,00;295527,00;293640,00;291868,00;297341,00;290513,00;288172,00;298960,00;308125,00;313523,00;310109,00;280603,00;205800,00;271258,00
1508357,00;Altamira;VitóriadoXingu(PA);127520,00;166720,00;195642,00;235526,00;245590,00;250002,00;220292,00;195201,00;193924,00;195155,00;169964,00;152217,00;175376,00;166290,00;156564,00;146937,00;135543,00;121600,00;122015,00
;;;;;;;;;;;;;;;;;;;;;

Answer 1

This type of problem is usually related to data reformatting. The format should be long and the data is in broad format. See this post on how to reformat data from wide to long format.

In the code below I followed the following steps.

1. Filter the columns without microrregião;
2. Reformat data to long format;
3. Create a base chart and save it in the variable p.

Then just add the geom_* requested (geom_col or geom_bar) and improve the final aspect, as requested in this comment to the response of @abreums. The rotation of years on the x-axis comes from this response.

---

Reformat data and create graphical object.

library(tidyverse)

espacial %>%
  filter(microrregião != "") %>%
  pivot_longer(
    cols = starts_with("EFETIVOBOV"),
    names_to = "Ano",
    values_to = "Efetivo"
  ) %>%
  mutate(Ano = str_extract(Ano, "\\d+")) %>%
  ggplot(aes(x = Ano, y = Efetivo, fill = microrregião)) +
  labs(y = "Efetivo Bovino") -> p

---

Overlapping bars.

p + geom_col() +
  theme_bw() +
  theme(axis.text.x = element_text(angle = 45, hjust = 1))

Bars side by side.

p + geom_col(position = position_dodge()) +
  theme_bw() +
  theme(axis.text.x = element_text(angle = 45, hjust = 1))

Answer 2

...

library(tidyverse)
library(scales)

dt <- read_csv2("ImportacaoR.txt")

# é preciso "pivotar" / "rodar" os dados 
# para que haja uma coluna com os anos e outra com os efetivos
dt <- dt %>% 
  pivot_longer(EFETIVOBOV_2000:EFETIVOBOV_2018, 
               names_to = "ano", 
               values_to = "efetivo")

# vamos subtrair "EFETIVOBOV" da coluna "ano" (criada acima) 
# e transformar em inteiro
dt <- dt %>% 
  mutate(ano = as.integer(str_sub(ano, -4, -1)))

# gráfico empilhando os efetivos por ano para todos os municípios
dt %>% 
  ggplot(aes(x = ano, 
             y = efetivo, 
             group = microrregião, 
             fill = microrregião)) +
  geom_col() +
  # limita os "breaks" do eixo x
  scale_x_continuous(breaks = c(2000, 2004, 2008, 2012, 2016)) + 
  # acrescenta separador nos valores do eixo y
  scale_y_continuous(labels = scales::comma_format(big.mark = ".",
                                                   decimal.mark = ",")) +
  # inclinação para o texto do eixo x + ajuste horizontal 
  theme(
    axis.text.x = element_text(angle = 45, hjust = 1)
  ) +
  labs( 
    title = "Aumento do efetivo de cabeças de gado no PA",
    subtitle = "2000 - 2018",
    x = "Ano",
    y = "Efetivo"
  ) 


# outro gráfico separando os efetivos por município 
# em gráficos diferentes - facet_wrap
dt %>%
  # alguns registros não contém informação de microrregião, 
  # por isso incluimos este filtro para evitar o facet de NA 
  filter(!is.na(microrregião)) %>% 
  ggplot(aes(x = ano, 
             y = efetivo, 
             group = microrregião, 
             fill = microrregião)) +
  geom_col() +
  scale_x_continuous(breaks = c(2000, 2004, 2008, 2012, 2016)) + 
  # acrescenta separador nos valores do eixo
  scale_y_continuous(labels = scales::comma_format(big.mark = ".",
                                                   decimal.mark = ",")) +
  theme(
    # inclinação para o texto do eixo x + ajuste horizontal 
    axis.text.x = element_text(angle = 45, hjust = 1),
    # esconde a legenda
    legend.position = "none"
  ) +
  labs( title = "Aumento do efetivo de cabeças de gado no PA",
        subtitle = "2000 - 2018",
        x = element_blank(),
        y = element_blank()) +
  facet_wrap(~ microrregião)