title: Replicate Excel plot with ggplot2 in R
body: <p>This is a bit of a random ask, but i'm currently going through plotting some data and to get a basic idea I did this within excel to have a quick look.</p>
  <p>Now i've managed to get some decent data from <a href="https://nwssp.nhs.wales/ourservices/primary-care-services/primary-care-services-documents/gp-practice-analysis-docs/patient-registrations-april-2020" rel="nofollow noreferrer">here</a> - in Excel I created this plot with a pivot table which <em>sums</em> the male count</p>
  <p><a href="https://i.stack.imgur.com/j6sMk.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/j6sMk.png" alt="Male Count by Age &amp; LHB" /></a></p>
  <p>But when I try to recreate this within R with ggplot2 i'm stuck.</p>
  <p>I <em>think</em> i'm just unable to figure out how Excel is <strong>Summing</strong> the MaleCount and unable to replicate in R - This is the plot i'm getting in R without <em>summing</em>.</p>
  <p><a href="https://i.stack.imgur.com/JkXCT.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/JkXCT.png" alt="enter image description here" /></a></p>
  <p>And this is the code used to create:</p>
  <pre><code>ggplot(data = df, 
         aes(x = df$AgeBand, # Axis (Categories)
             y = df$MaleCount)) + # This should be summed somehow.
    geom_line(aes(colour = factor(HealthBoard))) + # Legend
    ggtitle(&quot;I have no idea&quot;)
  </code></pre>
  <p>Let me know you have any more questions.<br />
  Thanks in advance, Mark.</p>
  <p>EDIT: Adding structure below.</p>
  <pre><code>tibble [50 x 11] (S3: tbl_df/tbl/data.frame)
   $ Period            : num [1:50] 202004 202004 202004 202004 202004 ...
   $ PracticeCode      : chr [1:50] &quot;W96016&quot; &quot;W95001&quot; &quot;W93021&quot; &quot;W91054&quot; ...
   $ PostCode          : chr [1:50] &quot;NP8 1AG&quot; &quot;CF44 7DD&quot; &quot;NP16 5XR&quot; &quot;LL12 7TH&quot; ...
   $ OrgCode           : chr [1:50] &quot;7A7&quot; &quot;7A5&quot; &quot;7A6&quot; &quot;7A1&quot; ...
   $ AgeBand           : num [1:50] 8 24 11 14 68 24 4 56 85 17 ...
   $ MaleCount         : num [1:50] 37 94 49 41 28 53 16 20 4 40 ...
   $ FemaleCount       : num [1:50] 41 98 41 31 28 64 20 14 7 50 ...
   $ IndeterminateCount: num [1:50] 0 0 0 0 0 0 0 0 0 0 ...
   $ Count             : num [1:50] 78 192 90 72 56 117 36 34 11 90 ...
   $ Year              : num [1:50] 2020 2020 2020 2020 2020 2020 2020 2020 2020 2020 ...
   $ Month             : chr [1:50] &quot;April&quot; &quot;April&quot; &quot;April&quot; &quot;April&quot; ...
  </code></pre>
  <p>Adding dput below.</p>
  <pre><code>structure(list(Period = c(202004, 202004, 202004, 202004, 202004, 
  202004, 202004, 202004, 202004, 202004, 202004, 202004, 202004, 
  202004, 202004, 202004, 202004, 202004, 202004, 202004, 202004, 
  202004, 202004, 202004, 202004, 202004, 202004, 202004, 202004, 
  202004, 202004, 202004, 202004, 202004, 202004, 202004, 202004, 
  202004, 202004, 202004, 202004, 202004, 202004, 202004, 202004, 
  202004, 202004, 202004, 202004, 202004), PracticeCode = c(&quot;W95023&quot;, 
  &quot;W95086&quot;, &quot;W91015&quot;, &quot;W93045&quot;, &quot;W93125&quot;, &quot;W97623&quot;, &quot;W95073&quot;, &quot;W95042&quot;, 
  &quot;W94017&quot;, &quot;W97025&quot;, &quot;W95016&quot;, &quot;W92048&quot;, &quot;W98033&quot;, &quot;W94018&quot;, &quot;W93116&quot;, 
  &quot;W93059&quot;, &quot;W94035&quot;, &quot;W93046&quot;, &quot;W92058&quot;, &quot;W97016&quot;, &quot;W94021&quot;, &quot;W98048&quot;, 
  &quot;W94026&quot;, &quot;W97069&quot;, &quot;W98012&quot;, &quot;W92052&quot;, &quot;W93072&quot;, &quot;W91044&quot;, &quot;W96015&quot;, 
  &quot;W97060&quot;, &quot;W97008&quot;, &quot;W94609&quot;, &quot;W91038&quot;, &quot;W97010&quot;, &quot;W92023&quot;, &quot;W97067&quot;, 
  &quot;W93049&quot;, &quot;W97028&quot;, &quot;W91058&quot;, &quot;W97048&quot;, &quot;W92023&quot;, &quot;W93061&quot;, &quot;W91610&quot;, 
  &quot;W94007&quot;, &quot;W95034&quot;, &quot;W95024&quot;, &quot;W93075&quot;, &quot;W95032&quot;, &quot;W95087&quot;, &quot;W93029&quot;
  ), PostCode = c(&quot;CF48 1BZ&quot;, &quot;CF48 3AL&quot;, &quot;CH5 3PA&quot;, &quot;NP20 6EY&quot;, 
  &quot;NP18 2JB&quot;, &quot;CF5 5LQ&quot;, &quot;CF83 3JZ&quot;, &quot;CF45 4YB&quot;, &quot;LL55 4SU&quot;, &quot;CF14 3NB&quot;, 
  &quot;CF44 6HY&quot;, &quot;SA14 8TU&quot;, &quot;SA3 5UA&quot;, &quot;LL30 3EU&quot;, &quot;NP10 8UX&quot;, &quot;NP11 6BJ&quot;, 
  &quot;LL23 7BA&quot;, &quot;NP20 4JS&quot;, &quot;SA62 6SS&quot;, &quot;CF11 9SH&quot;, &quot;LL52 0RR&quot;, &quot;SA10 6UF&quot;, 
  &quot;LL65 1RA&quot;, &quot;CF3 0SH&quot;, &quot;SA4 3ED&quot;, &quot;SA15 3BD&quot;, &quot;NP25 3PL&quot;, &quot;CH7 4RQ&quot;, 
  &quot;SY16 1EF&quot;, &quot;CF24 1AG&quot;, &quot;CF23 9PN&quot;, &quot;LL54 6NN&quot;, &quot;LL22 8LJ&quot;, &quot;CF23 8SQ&quot;, 
  &quot;SA34 0AJ&quot;, &quot;CF11 9DG&quot;, &quot;NP19 7DQ&quot;, &quot;CF14 1LT&quot;, &quot;LL13 8RG&quot;, &quot;CF24 2HB&quot;, 
  &quot;SA34 0AJ&quot;, &quot;NP10 9DU&quot;, &quot;LL12 9LG&quot;, &quot;LL36 9HL&quot;, &quot;CF33 4LD&quot;, &quot;CF37 2DR&quot;, 
  &quot;NP13 1BQ&quot;, &quot;CF46 5HE&quot;, &quot;CF44 7AY&quot;, &quot;NP44 4TA&quot;), OrgCode = c(&quot;7A5&quot;, 
  &quot;7A5&quot;, &quot;7A1&quot;, &quot;7A6&quot;, &quot;7A6&quot;, &quot;7A4&quot;, &quot;7A6&quot;, &quot;7A5&quot;, &quot;7A1&quot;, &quot;7A4&quot;, 
  &quot;7A5&quot;, &quot;7A2&quot;, &quot;7A3&quot;, &quot;7A1&quot;, &quot;7A6&quot;, &quot;7A6&quot;, &quot;7A1&quot;, &quot;7A6&quot;, &quot;7A2&quot;, 
  &quot;7A4&quot;, &quot;7A1&quot;, &quot;7A3&quot;, &quot;7A1&quot;, &quot;7A4&quot;, &quot;7A3&quot;, &quot;7A2&quot;, &quot;7A6&quot;, &quot;7A1&quot;, 
  &quot;7A7&quot;, &quot;7A4&quot;, &quot;7A4&quot;, &quot;7A1&quot;, &quot;7A1&quot;, &quot;7A4&quot;, &quot;7A2&quot;, &quot;7A4&quot;, &quot;7A6&quot;, 
  &quot;7A4&quot;, &quot;7A1&quot;, &quot;7A4&quot;, &quot;7A2&quot;, &quot;7A6&quot;, &quot;7A1&quot;, &quot;7A1&quot;, &quot;7A5&quot;, &quot;7A5&quot;, 
  &quot;7A6&quot;, &quot;7A5&quot;, &quot;7A5&quot;, &quot;7A6&quot;), AgeBand = c(87, 31, 44, 53, 23, 
  91, 24, 12, 93, 83, 26, 38, 92, 47, NA, 23, 27, 80, 93, 2, 46, 
  82, 11, 45, 72, 18, 26, 54, 89, 71, 30, 27, 18, 37, 50, 4, 8, 
  51, 59, 8, 4, 64, 92, 13, 88, 85, 78, 56, 45, 44), MaleCount = c(12, 
  153, 52, 59, 16, 0, 10, 39, 1, 9, 33, 33, 13, 44, 3, 37, 31, 
  15, 0, 17, 18, 8, 39, 24, 143, 84, 24, 23, 6, 30, 129, 21, 61, 
  72, 55, 23, 86, 68, 82, 81, 42, 57, 0, 23, 12, 24, 27, 43, 18, 
  63), FemaleCount = c(14, 133, 73, 62, 22, 1, 18, 36, 3, 10, 36, 
  25, 21, 38, 20, 44, 24, 21, 1, 18, 21, 19, 30, 26, 151, 71, 23, 
  17, 27, 20, 132, 17, 65, 70, 55, 28, 73, 73, 69, 80, 28, 74, 
  2, 25, 24, 27, 24, 33, 33, 64), IndeterminateCount = c(0, 0, 
  0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
  0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
  0, 0, 0, 0, 0, 0), Count = c(26, 286, 125, 121, 38, 1, 28, 75, 
  4, 19, 69, 58, 34, 82, 23, 81, 55, 36, 1, 35, 39, 27, 69, 50, 
  294, 155, 47, 40, 33, 50, 261, 38, 126, 142, 110, 51, 159, 141, 
  151, 161, 70, 131, 2, 48, 36, 51, 51, 76, 51, 127), Year = c(2020, 
  2020, 2020, 2020, 2020, 2020, 2020, 2020, 2020, 2020, 2020, 2020, 
  2020, 2020, 2020, 2020, 2020, 2020, 2020, 2020, 2020, 2020, 2020, 
  2020, 2020, 2020, 2020, 2020, 2020, 2020, 2020, 2020, 2020, 2020, 
  2020, 2020, 2020, 2020, 2020, 2020, 2020, 2020, 2020, 2020, 2020, 
  2020, 2020, 2020, 2020, 2020), Month = c(&quot;April&quot;, &quot;April&quot;, &quot;April&quot;, 
  &quot;April&quot;, &quot;April&quot;, &quot;April&quot;, &quot;April&quot;, &quot;April&quot;, &quot;April&quot;, &quot;April&quot;, 
  &quot;April&quot;, &quot;April&quot;, &quot;April&quot;, &quot;April&quot;, &quot;April&quot;, &quot;April&quot;, &quot;April&quot;, 
  &quot;April&quot;, &quot;April&quot;, &quot;April&quot;, &quot;April&quot;, &quot;April&quot;, &quot;April&quot;, &quot;April&quot;, 
  &quot;April&quot;, &quot;April&quot;, &quot;April&quot;, &quot;April&quot;, &quot;April&quot;, &quot;April&quot;, &quot;April&quot;, 
  &quot;April&quot;, &quot;April&quot;, &quot;April&quot;, &quot;April&quot;, &quot;April&quot;, &quot;April&quot;, &quot;April&quot;, 
  &quot;April&quot;, &quot;April&quot;, &quot;April&quot;, &quot;April&quot;, &quot;April&quot;, &quot;April&quot;, &quot;April&quot;, 
  &quot;April&quot;, &quot;April&quot;, &quot;April&quot;, &quot;April&quot;, &quot;April&quot;)), row.names = c(NA, 
  -50L), class = c(&quot;tbl_df&quot;, &quot;tbl&quot;, &quot;data.frame&quot;))
  </code></pre>