Merge rows with same names and sum other values in other column rows

Question

2000	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sept	Oct	Nov	Dec	Total
Europe	51,334	52,742	56,661	40,022	34,085	23,549	30,658	38,977	34,730	44,761	47,895	50,007	505,421
Belarus	108	58	81	99	40	55	50	76	53	74	96	108	898
Bulgaria	203	120	110	82	32	28	34	47	58	74	75	188	1,051
Czech Republic	489	640	570	342	236	236	185	360	273	377	536	533	596

2010	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sept	Oct	Nov	Dec	Total
Europe	51,334	52,742	56,661	40,022	34,085	23,549	30,658	38,977	34,730	44,761	47,895	50,007	505,421
Belarus	108	58	81	99	40	55	50	76	53	74	96	108	898
Bulgaria	203	120	110	82	32	28	34	47	58	74	75	188	1,051
Czech Republic	489	640	570	342	236	236	185	360	273	377	536	533	596

Please use ``dput()`` to share your data and don't post images of data. Thank you. — user438383, Jul 20 '21 at 12:09
It would be easier to help if you create a small reproducible example along with expected output. Read about [how to give a reproducible example](http://stackoverflow.com/questions/5963269). Images are not the right way to share data/code. — Ronak Shah, Jul 20 '21 at 12:18
This question is even more unclear now that you've deleted all the text from it and just posted the same table twice, and it's now a table with no repeated groups, so whatever question used to be here no longer applies. Also what are "column rows"? — camille, Aug 02 '21 at 15:23

score 1 · Accepted Answer · answered Jul 20 '21 at 12:36

We could first group_by country

and then use summarise with across

library(dplyr)
df %>% 
  group_by(country) %>% 
  summarise(across(everything(), sum))

Output:

 country new_persons_vac~ total_persons_v~ new_persons_ful~ total_persons_f~ new_vaccine_dos~ total_vaccine_d~
   <chr>              <dbl>            <dbl>            <dbl>            <dbl>            <dbl>            <dbl>
 1 Afghan~           294056          8452317           163535          2313338           457591         10765655
 2 Albania           601152         27639676           465433         18105836           459226         45745512
 3 Andorra            40569           360995            25838           144358            58535           506402
 4 Angola            371996          9545624           559633          4688357           931629         14233981
 5 Anguil~             3206            73046             6847            48524            10053           121570
 6 Antigu~             5232           770379            26084           485839            31316          1256218
 7 Argent~         65820302       3858592405         16136889        917220373         81957191       4775812778
 8 Armenia           138306           426851            58214           135848           196520           562699
 9 Aruba              55435          4907836            52549          3439184           107984          8347020
10 Austra~         14227655        811027845          5722445        163311327         19238830        974339172
# ... with 183 more rows

head of data:

df <- structure(list(country = c("Brazil", "Brazil", "Brazil", "Brazil", 
"Brazil", "Brazil"), new_persons_vaccinated = c(1, 1, 1, 1, 1, 
1), total_persons_vaccinated = c(1, 1, 1, 2, 1, 1), new_persons_fully_vaccinated = c(0, 
0, 0, 0, 0, 0), total_persons_fully_vaccinated = c(0, 0, 0, 0, 
0, 0), new_vaccine_doses_administered = c(1, 1, 1, 1, 1, 1), 
    total_vaccine_doses_administered = c(1, 1, 1, 2, 1, 1)), row.names = c(NA, 
-6L), class = c("tbl_df", "tbl", "data.frame"))

in this solution it make another table with same country name with other column values. i want merge all rows with same names into same table — Academy Tracker, Jul 20 '21 at 12:52
@AcademyTracker please include a reproducuble example of your data then. — user438383, Jul 20 '21 at 13:01
How do you get 1stdoze and seconddoze in India of 260 and 90? — TarJae, Jul 20 '21 at 13:02
by adding all the values in 1st doze in all the field named india, 2nd doze also same way, i want output like that — Academy Tracker, Jul 20 '21 at 13:09
wicht of these columns assign for 1stdoze and which for 2nd dose: `> colnames(df) [1] "country" "new_persons_vaccinated" [3] "total_persons_vaccinated" "new_persons_fully_vaccinated" [5] "total_persons_fully_vaccinated" "new_vaccine_doses_administered" [7] "total_vaccine_doses_administered"` — TarJae, Jul 20 '21 at 13:14
I don't mind as long as I can sum all the fields with country same country name. this row "new_persons_vaccinated" or "total_persons_vaccinated" any row is ok — Academy Tracker, Jul 20 '21 at 13:26

score -2 · Answer 2 · answered Jul 20 '21 at 12:23

-2

You can use baseR aggregate() or dplyr group_by() then summarise().

answered Jul 20 '21 at 12:23

rbasa

452
3
5

Take a look again at [answer] to see how you can make this a specific answer rather than a general suggestion – camille Aug 02 '21 at 15:24
@camille from that “Any answer that gets the asker going in the right direction is helpful, but do try to mention any limitations, assumptions or simplifications in your answer. Brevity is acceptable, but fuller explanations are better.” My answer is short but points in the right direction. – rbasa Aug 03 '21 at 09:58

Merge rows with same names and sum other values in other column rows

2 Answers2