Pivoting multiple colums to variable for repeated measures

General
1

Hello, im trying to merge a several colums into a variable MX_total, it is measured using variables mx01 to mx36 each measured in 1990 - 2014. So the structure of the dataset is MX01X1990, MX01X1991.... MX02X1990, MX02X1991.....MX36X1990, MX36X1991.......

Here is the dataset in trying to analyze.
https://www.thearda.com/ras/downloads/

To select relevant variables i used:

MX <- DF %>% select(COUNTRY|REGION|(starts_with("MX")& !ends_with("X")))

$ COUNTRY  : chr [1:183] "Afghanistan" "Albania" "Algeria" "Andorra" ...
 $ REGION   : num [1:183] 2 1 3 0 4 5 1 0 0 1 ...
 $ MX01X1990: num [1:183] NA 0 1 0 1 0 NA 0 0 NA ...
 $ MX01X1991: num [1:183] NA 0 1 0 1 0 2 0 0 2 ...
 $ MX01X1992: num [1:183] 0 0 1 0 1 0 2 0 0 2 ...
 $ MX01X1993: num [1:183] 0 0 1 0 1 0 2 0 0 2 ...
 $ MX01X1994: num [1:183] 0 0 1 0 1 0 2 0 0 2 ...
 $ MX01X1995: num [1:183] 0 0 1 0 1 0 2 0 0 2 ...
 $ MX01X1996: num [1:183] 2 0 1 0 1 0 2 0 0 2 ...
 $ MX01X1997: num [1:183] 2 0 1 0 1 0 2 0 0 2 ...
 $ MX01X1998: num [1:183] 2 0 1 0 1 0 2 0 0 2 ...
 $ MX01X1999: num [1:183] 2 0 1 0 1 0 2 0 0 2 ...
 $ MX01X2000: num [1:183] 2 0 1 0 1 0 2 0 0 2 ...
 $ MX01X2001: num [1:183] 2 0 1 0 1 0 2 0 0 2 ...
 $ MX01X2002: num [1:183] 0 0 1 0 1 0 2 0 0 2 ...
 $ MX01X2003: num [1:183] 0 0 1 0 1 0 2 0 0 2 ...
 $ MX01X2004: num [1:183] 0 0 1 0 1 0 2 0 0 2 ...
 $ MX01X2005: num [1:183] 0 0 1 0 1 0 2 0 0 2 ...
 $ MX01X2006: num [1:183] 0 0 1 0 1 0 2 0 0 2 ...
 $ MX01X2007: num [1:183] 0 0 1 0 1 0 2 0 0 2 ...
 $ MX01X2008: num [1:183] 0 0 2 0 1 0 2 0 1 2 ...
 $ MX01X2009: num [1:183] 0 0 2 0 1 0 2 0 0 2 ...
 $ MX01X2010: num [1:183] 0 0 2 0 1 0 2 0 0 2 ...
 $ MX01X2011: num [1:183] 0 0 2 0 1 0 2 0 0 2 ...
 $ MX01X2012: num [1:183] 0 0 2 0 1 0 2 0 0 2 ...
 $ MX01X2013: num [1:183] 0 0 2 0 1 0 2 0 0 2 ...
 $ MX01X2014: num [1:183] 0 0 2 0 1 0 2 0 0 2 ...
 $ MX02X1990: num [1:183] NA 0 0 0 0 0 NA 0 0 NA ...
 $ MX02X1991: num [1:183] NA 0 0 0 0 0 0 0 0 0 ...
 $ MX02X1992: num [1:183] 0 0 0 0 0 0 0 0 0 0 ...
 $ MX02X1993: num [1:183] 0 0 0 0 0 0 0 0 0 0 ...
 $ MX02X1994: num [1:183] 0 0 0 0 0 0 0 0 0 0 ...
 $ MX02X1995: num [1:183] 0 0 0 0 0 0 0 0 0 0 ...
 $ MX02X1996: num [1:183] 0 0 0 0 0 0 0 0 0 0 ...
 $ MX02X1997: num [1:183] 0 0 0 0 0 0 0 0 0 0 ...
 $ MX02X1998: num [1:183] 0 0 0 0 0 0 0 0 0 1 ...
 $ MX02X1999: num [1:183] 0 0 0 0 0 0 0 0 0 1 ...
 $ MX02X2000: num [1:183] 0 0 0 0 0 0 0 0 0 1 ...
 $ MX02X2001: num [1:183] 0 0 0 0 0 0 0 0 0 1 ...
 $ MX02X2002: num [1:183] 0 0 0 0 0 0 0 0 0 1 ...
 $ MX02X2003: num [1:183] 0 0 0 0 0 0 0 0 0 1 ...
 $ MX02X2004: num [1:183] 0 0 0 0 0 0 0 0 0 1 ...
 $ MX02X2005: num [1:183] 0 0 0 0 0 0 0 0 0 1 ...
 $ MX02X2006: num [1:183] 0 0 0 0 0 0 0 0 0 1 ...
 $ MX02X2007: num [1:183] 0 0 0 0 0 0 0 0 0 1 ...
 $ MX02X2008: num [1:183] 0 0 2 0 0 0 0 0 0 1 ...
 $ MX02X2009: num [1:183] 0 0 2 0 0 0 0 0 0 1 ...
 $ MX02X2010: num [1:183] 0 0 2 0 0 0 0 0 0 1 ...
 $ MX02X2011: num [1:183] 0 0 2 0 0 0 0 0 0 1 ...
 $ MX02X2012: num [1:183] 0 0 2 0 0 0 0 0 0 1 ...
 $ MX02X2013: num [1:183] 0 0 2 0 0 0 0 0 0 1 ...
 $ MX02X2014: num [1:183] 0 0 2 0 0 0 0 0 0 1 ...
 $ MX03X1990: num [1:183] NA 0 0 0 0 0 NA 0 0 NA ...
 $ MX03X1991: num [1:183] NA 0 0 0 0 0 0 0 0 0 ...
 $ MX03X1992: num [1:183] 2 0 0 0 0 0 0 0 0 0 ...
 $ MX03X1993: num [1:183] 2 0 0 0 0 0 0 0 0 0 ...
 $ MX03X1994: num [1:183] 2 0 0 0 0 0 0 0 0 0 ...
 $ MX03X1995: num [1:183] 2 0 0 0 0 0 0 0 0 0 ...
 $ MX03X1996: num [1:183] 0 0 0 0 0 0 0 0 0 0 ...
 $ MX03X1997: num [1:183] 0 0 0 0 0 0 0 0 0 0 ...
 $ MX03X1998: num [1:183] 0 0 0 0 0 0 0 0 0 0 ...
 $ MX03X1999: num [1:183] 0 0 0 0 0 0 0 0 0 0 ...
 $ MX03X2000: num [1:183] 0 0 0 0 0 0 0 0 0 0 ...
 $ MX03X2001: num [1:183] 0 0 0 0 0 0 0 0 0 0 ...
 $ MX03X2002: num [1:183] 0 0 0 0 0 0 0 0 0 0 ...
 $ MX03X2003: num [1:183] 0 0 0 0 0 0 0 0 0 0 ...
 $ MX03X2004: num [1:183] 0 0 0 0 0 0 0 0 0 0 ...
 $ MX03X2005: num [1:183] 0 0 0 0 0 0 0 0 0 0 ...
 $ MX03X2006: num [1:183] 0 0 0 0 0 0 0 0 0 0 ...
 $ MX03X2007: num [1:183] 0 0 0 0 0 0 0 0 0 0 ...
 $ MX03X2008: num [1:183] 0 0 0 0 0 0 0 0 0 0 ...
 $ MX03X2009: num [1:183] 0 0 0 0 0 0 0 0 0 0 ...
 $ MX03X2010: num [1:183] 0 0 0 0 0 0 0 0 0 0 ...
 $ MX03X2011: num [1:183] 0 0 0 0 0 0 0 0 0 0 ...
 $ MX03X2012: num [1:183] 0 0 0 0 0 0 0 0 0 0 ...
 $ MX03X2013: num [1:183] 0 0 0 0 0 0 0 0 0 0 ...
 $ MX03X2014: num [1:183] 0 0 0 0 0 0 0 0 0 0 ...
 $ MX04X1990: num [1:183] NA 0 0 0 0 0 NA 0 0 NA ...
 $ MX04X1991: num [1:183] NA 0 0 0 0 0 2 0 0 1 ...
 $ MX04X1992: num [1:183] 0 0 0 0 0 0 2 0 0 1 ...
 $ MX04X1993: num [1:183] 0 0 0 0 0 0 2 0 0 1 ...
 $ MX04X1994: num [1:183] 0 0 0 0 0 0 2 0 0 1 ...
 $ MX04X1995: num [1:183] 0 0 0 0 0 0 2 0 0 1 ...
 $ MX04X1996: num [1:183] 0 0 0 0 0 0 2 0 0 1 ...
 $ MX04X1997: num [1:183] 0 0 0 0 0 0 2 0 0 1 ...
 $ MX04X1998: num [1:183] 0 0 0 0 0 0 2 0 0 1 ...
 $ MX04X1999: num [1:183] 0 0 0 0 0 0 2 0 0 1 ...
 $ MX04X2000: num [1:183] 0 0 0 0 0 0 2 0 0 1 ...
 $ MX04X2001: num [1:183] 0 0 0 0 0 0 2 0 0 1 ...
 $ MX04X2002: num [1:183] 0 0 0 0 0 0 2 0 0 1 ...
 $ MX04X2003: num [1:183] 0 0 0 0 0 0 2 0 0 1 ...
 $ MX04X2004: num [1:183] 0 0 0 0 0 0 2 0 0 1 ...
 $ MX04X2005: num [1:183] 0 0 0 0 0 0 2 0 0 1 ...
 $ MX04X2006: num [1:183] 0 0 0 0 0 0 2 0 0 1 ...
 $ MX04X2007: num [1:183] 0 0 0 0 0 0 2 0 0 1 ...
 $ MX04X2008: num [1:183] 0 0 0 0 0 0 2 0 0 1 ...
 $ MX04X2009: num [1:183] 0 0 0 0 0 0 2 0 0 1 ...
 $ MX04X2010: num [1:183] 0 0 0 0 0 0 2 0 0 1 ...
 $ MX04X2011: num [1:183] 0 0 0 0 0 0 2 0 0 1 ...
  [list output truncated]

#Pivoting to long
MXlong <- MX %>% pivot_longer(-c(COUNTRY, REGION), names_to = "ColName",
values_to = "Value")

#Extracting a variable Year
MXlong <- MXlong %>% mutate(Year = str_extract(ColName, "\d{4}"))

#trying to make a sum score per year, per country.
MX_Summary <- MXlong %>% group_by(Year, COUNTRY) %>%
mutate(Total_MX = sum(Value, na.rm = TRUE))

When trying to pivot and summarize them by year, to get an MX-total per year, grouped by country i get repetitions in the data. I get the total score per year, repeated 36 times.

str(MX_Summary)

grouped_df [169,275 x 6] (S3: grouped_df/tbl_df/tbl/data.frame)
 $ COUNTRY : chr [1:169275] "Afghanistan" "Afghanistan" "Afghanistan" "Afghanistan" ...
 $ REGION  : num [1:169275] 2 2 2 2 2 2 2 2 2 2 ...
 $ ColName : chr [1:169275] "MX01X1990" "MX01X1991" "MX01X1992" "MX01X1993" ...
 $ Value   : num [1:169275] NA NA 0 0 0 0 2 2 2 2 ...
 $ Year    : chr [1:169275] "1990" "1991" "1992" "1993" ...
 $ Total_MX: num [1:169275] 0 0 34 34 34 30 46 46 50 56 ...
 - attr(*, "groups")= tibble [4,575 x 3] (S3: tbl_df/tbl/data.frame)
  ..$ Year   : chr [1:4575] "1990" "1990" "1990" "1990" ...
  ..$ COUNTRY: chr [1:4575] "Afghanistan" "Albania" "Algeria" "Andorra" ...
  ..$ .rows  : list<int> [1:4575] 
  .. ..$ : int [1:37] 1 26 51 76 101 126 151 176 201 226 ...
  .. ..$ : int [1:37] 926 951 976 1001 1026 1051 1076 1101 1126 1151 ...
  .. ..$ : int [1:37] 1851 1876 1901 1926 1951 1976 2001 2026 2051 2076 ...
  .. ..$ : int [1:37] 2776 2801 2826 2851 2876 2901 2926 2951 2976 3001 ...
  .. ..$ : int [1:37] 3701 3726 3751 3776 3801 3826 3851 3876 3901 3926 ...
  .. ..$ : int [1:37] 4626 4651 4676 4701 4726 4751 4776 4801 4826 4851 ...
  .. ..$ : int [1:37] 5551 5576 5601 5626 5651 5676 5701 5726 5751 5776 ...
  .. ..$ : int [1:37] 6476 6501 6526 6551 6576 6601 6626 6651 6676 6701 ...
  .. ..$ : int [1:37] 7401 7426 7451 7476 7501 7526 7551 7576 7601 7626 ...
  .. ..$ : int [1:37] 8326 8351 8376 8401 8426 8451 8476 8501 8526 8551 ...
  .. ..$ : int [1:37] 9251 9276 9301 9326 9351 9376 9401 9426 9451 9476 ...
  .. ..$ : int [1:37] 10176 10201 10226 10251 10276 10301 10326 10351 10376 10401 ...
  .. ..$ : int [1:37] 11101 11126 11151 11176 11201 11226 11251 11276 11301 11326 ...
  .. ..$ : int [1:37] 12026 12051 12076 12101 12126 12151 12176 12201 12226 12251 ...
  .. ..$ : int [1:37] 12951 12976 13001 13026 13051 13076 13101 13126 13151 13176 ...
  .. ..$ : int [1:37] 13876 13901 13926 13951 13976 14001 14026 14051 14076 14101 ...
  .. ..$ : int [1:37] 14801 14826 14851 14876 14901 14926 14951 14976 15001 15026 ...
  .. ..$ : int [1:37] 15726 15751 15776 15801 15826 15851 15876 15901 15926 15951 ...
  .. ..$ : int [1:37] 16651 16676 16701 16726 16751 16776 16801 16826 16851 16876 ...
  .. ..$ : int [1:37] 17576 17601 17626 17651 17676 17701 17726 17751 17776 17801 ...
  .. ..$ : int [1:37] 18501 18526 18551 18576 18601 18626 18651 18676 18701 18726 ...
  .. ..$ : int [1:37] 19426 19451 19476 19501 19526 19551 19576 19601 19626 19651 ...
  .. ..$ : int [1:37] 20351 20376 20401 20426 20451 20476 20501 20526 20551 20576 ...
  .. ..$ : int [1:37] 21276 21301 21326 21351 21376 21401 21426 21451 21476 21501 ...
  .. ..$ : int [1:37] 22201 22226 22251 22276 22301 22326 22351 22376 22401 22426 ...
  .. ..$ : int [1:37] 23126 23151 23176 23201 23226 23251 23276 23301 23326 23351 ...
  .. ..$ : int [1:37] 24051 24076 24101 24126 24151 24176 24201 24226 24251 24276 ...
  .. ..$ : int [1:37] 24976 25001 25026 25051 25076 25101 25126 25151 25176 25201 ...
  .. ..$ : int [1:37] 25901 25926 25951 25976 26001 26026 26051 26076 26101 26126 ...
  .. ..$ : int [1:37] 26826 26851 26876 26901 26926 26951 26976 27001 27026 27051 ...
  .. ..$ : int [1:37] 27751 27776 27801 27826 27851 27876 27901 27926 27951 27976 ...
  .. ..$ : int [1:37] 28676 28701 28726 28751 28776 28801 28826 28851 28876 28901 ...
  .. ..$ : int [1:37] 29601 29626 29651 29676 29701 29726 29751 29776 29801 29826 ...
  .. ..$ : int [1:37] 30526 30551 30576 30601 30626 30651 30676 30701 30726 30751 ...
  .. ..$ : int [1:37] 31451 31476 31501 31526 31551 31576 31601 31626 31651 31676 ...
  .. ..$ : int [1:37] 32376 32401 32426 32451 32476 32501 32526 32551 32576 32601 ...
  .. ..$ : int [1:37] 33301 33326 33351 33376 33401 33426 33451 33476 33501 33526 ...
  .. ..$ : int [1:37] 34226 34251 34276 34301 34326 34351 34376 34401 34426 34451 ...
  .. ..$ : int [1:37] 35151 35176 35201 35226 35251 35276 35301 35326 35351 35376 ...
  .. ..$ : int [1:37] 36076 36101 36126 36151 36176 36201 36226 36251 36276 36301 ...
  .. ..$ : int [1:37] 37001 37026 37051 37076 37101 37126 37151 37176 37201 37226 ...
  .. ..$ : int [1:37] 37926 37951 37976 38001 38026 38051 38076 38101 38126 38151 ...
  .. ..$ : int [1:37] 38851 38876 38901 38926 38951 38976 39001 39026 39051 39076 ...
  .. ..$ : int [1:37] 39776 39801 39826 39851 39876 39901 39926 39951 39976 40001 ...
  .. ..$ : int [1:37] 40701 40726 40751 40776 40801 40826 40851 40876 40901 40926 ...
  .. ..$ : int [1:37] 41626 41651 41676 41701 41726 41751 41776 41801 41826 41851 ...
  .. ..$ : int [1:37] 42551 42576 42601 42626 42651 42676 42701 42726 42751 42776 ...
  .. ..$ : int [1:37] 43476 43501 43526 43551 43576 43601 43626 43651 43676 43701 ...
  .. ..$ : int [1:37] 44401 44426 44451 44476 44501 44526 44551 44576 44601 44626 ...
  .. ..$ : int [1:37] 45326 45351 45376 45401 45426 45451 45476 45501 45526 45551 ...
  .. ..$ : int [1:37] 46251 46276 46301 46326 46351 46376 46401 46426 46451 46476 ...
  .. ..$ : int [1:37] 47176 47201 47226 47251 47276 47301 47326 47351 47376 47401 ...
  .. ..$ : int [1:37] 48101 48126 48151 48176 48201 48226 48251 48276 48301 48326 ...
  .. ..$ : int [1:37] 49026 49051 49076 49101 49126 49151 49176 49201 49226 49251 ...
  .. ..$ : int [1:37] 49951 49976 50001 50026 50051 50076 50101 50126 50151 50176 ...
  .. ..$ : int [1:37] 50876 50901 50926 50951 50976 51001 51026 51051 51076 51101 ...
  .. ..$ : int [1:37] 51801 51826 51851 51876 51901 51926 51951 51976 52001 52026 ...
  .. ..$ : int [1:37] 52726 52751 52776 52801 52826 52851 52876 52901 52926 52951 ...
  .. ..$ : int [1:37] 53651 53676 53701 53726 53751 53776 53801 53826 53851 53876 ...
  .. ..$ : int [1:37] 54576 54601 54626 54651 54676 54701 54726 54751 54776 54801 ...
  .. ..$ : int [1:37] 55501 55526 55551 55576 55601 55626 55651 55676 55701 55726 ...
  .. ..$ : int [1:37] 56426 56451 56476 56501 56526 56551 56576 56601 56626 56651 ...
  .. ..$ : int [1:37] 57351 57376 57401 57426 57451 57476 57501 57526 57551 57576 ...
  .. ..$ : int [1:37] 58276 58301 58326 58351 58376 58401 58426 58451 58476 58501 ...
  .. ..$ : int [1:37] 59201 59226 59251 59276 59301 59326 59351 59376 59401 59426 ...
  .. ..$ : int [1:37] 60126 60151 60176 60201 60226 60251 60276 60301 60326 60351 ...
  .. ..$ : int [1:37] 61051 61076 61101 61126 61151 61176 61201 61226 61251 61276 ...
  .. ..$ : int [1:37] 61976 62001 62026 62051 62076 62101 62126 62151 62176 62201 ...
  .. ..$ : int [1:37] 62901 62926 62951 62976 63001 63026 63051 63076 63101 63126 ...
  .. ..$ : int [1:37] 63826 63851 63876 63901 63926 63951 63976 64001 64026 64051 ...
  .. ..$ : int [1:37] 64751 64776 64801 64826 64851 64876 64901 64926 64951 64976 ...
  .. ..$ : int [1:37] 65676 65701 65726 65751 65776 65801 65826 65851 65876 65901 ...
  .. ..$ : int [1:37] 66601 66626 66651 66676 66701 66726 66751 66776 66801 66826 ...
  .. ..$ : int [1:37] 67526 67551 67576 67601 67626 67651 67676 67701 67726 67751 ...
  .. ..$ : int [1:37] 68451 68476 68501 68526 68551 68576 68601 68626 68651 68676 ...
  .. ..$ : int [1:37] 69376 69401 69426 69451 69476 69501 69526 69551 69576 69601 ...
  .. ..$ : int [1:37] 70301 70326 70351 70376 70401 70426 70451 70476 70501 70526 ...
  .. ..$ : int [1:37] 71226 71251 71276 71301 71326 71351 71376 71401 71426 71451 ...
  .. ..$ : int [1:37] 72151 72176 72201 72226 72251 72276 72301 72326 72351 72376 ...
  .. ..$ : int [1:37] 73076 73101 73126 73151 73176 73201 73226 73251 73276 73301 ...
  .. ..$ : int [1:37] 74001 74026 74051 74076 74101 74126 74151 74176 74201 74226 ...
  .. ..$ : int [1:37] 74926 74951 74976 75001 75026 75051 75076 75101 75126 75151 ...
  .. ..$ : int [1:37] 75851 75876 75901 75926 75951 75976 76001 76026 76051 76076 ...
  .. ..$ : int [1:37] 76776 76801 76826 76851 76876 76901 76926 76951 76976 77001 ...
  .. ..$ : int [1:37] 77701 77726 77751 77776 77801 77826 77851 77876 77901 77926 ...
  .. ..$ : int [1:37] 78626 78651 78676 78701 78726 78751 78776 78801 78826 78851 ...
  .. ..$ : int [1:37] 79551 79576 79601 79626 79651 79676 79701 79726 79751 79776 ...
  .. ..$ : int [1:37] 80476 80501 80526 80551 80576 80601 80626 80651 80676 80701 ...
  .. ..$ : int [1:37] 81401 81426 81451 81476 81501 81526 81551 81576 81601 81626 ...
  .. ..$ : int [1:37] 82326 82351 82376 82401 82426 82451 82476 82501 82526 82551 ...
  .. ..$ : int [1:37] 83251 83276 83301 83326 83351 83376 83401 83426 83451 83476 ...
  .. ..$ : int [1:37] 84176 84201 84226 84251 84276 84301 84326 84351 84376 84401 ...
  .. ..$ : int [1:37] 85101 85126 85151 85176 85201 85226 85251 85276 85301 85326 ...
  .. ..$ : int [1:37] 86026 86051 86076 86101 86126 86151 86176 86201 86226 86251 ...
  .. ..$ : int [1:37] 86951 86976 87001 87026 87051 87076 87101 87126 87151 87176 ...
  .. ..$ : int [1:37] 87876 87901 87926 87951 87976 88001 88026 88051 88076 88101 ...
  .. ..$ : int [1:37] 88801 88826 88851 88876 88901 88926 88951 88976 89001 89026 ...
  .. ..$ : int [1:37] 89726 89751 89776 89801 89826 89851 89876 89901 89926 89951 ...
  .. ..$ : int [1:37] 90651 90676 90701 90726 90751 90776 90801 90826 90851 90876 ...
  .. .. [list output truncated]
  .. ..@ ptype: int(0) 
  ..- attr(*, ".drop")= logi TRUE

The dataset becomes very messy with 169,275 rows. Is there a way to have a total score in MX sorted by country but only preserve it for the 14 time points? Instead of repeating the values 14*36 times.

2

In the above step, change the mutate() function to summarize().

3

Worked like a charm. From 169,275 rows to 4575, making it possible to work on the dataset. Thank you.

4

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.