Arranging data by combining three columns in r
I want to create a three layer pie chart using ggplot2 which requires that the data is arrange in certain way.
This is the data I have:
| Level_1 | Level_2 | Level_3 |
|---|---|---|
| Theme A | Subtheme 1 | A |
| Theme A | Subtheme 1 | B |
| Theme A | Subtheme 1 | C |
| Theme A | Subtheme 1 | D |
| Theme A | Subtheme 2 | E |
| Theme A | Subtheme 2 | F |
| Theme A | Subtheme 2 | G |
| Theme A | Subtheme 2 | H |
| Theme B | Subtheme 1 | I |
| Theme B | Subtheme 1 | J |
| Theme B | Subtheme 1 | K |
| Theme B | Subtheme 1 | L |
| Theme B | Subtheme 2 | M |
| Theme B | Subtheme 2 | N |
| Theme B | Subtheme 2 | O |
| Theme B | Subtheme 2 | P |
| Theme C | Subtheme 1 | Q |
| Theme C | Subtheme 1 | R |
| Theme C | Subtheme 1 | S |
| Theme C | Subtheme 1 | T |
| Theme C | Subtheme 2 | U |
| Theme C | Subtheme 2 | V |
| Theme C | Subtheme 2 | V |
| Theme C | Subtheme 2 | W |
| Theme C | Subtheme 2 | W |
| Theme C | Subtheme 2 | X |
| Theme C | Subtheme 2 | Y |
| Theme C | Subtheme 2 | Z |
I need to combine Level_1, Level_2, and Level_3 into one column where the the first unique item in Level_1 (without duplicates) is followed by the first unique item in Level_2 and then all unique items in Level_3 for the corresponding items (in Level 1 and Level 2). Then the second unique item in Level_3 and so on, as in the following table
| Name | Freq |
|---|---|
| Theme A | 8 |
| Subtheme 1 | 4 |
| A | 1 |
| B | 1 |
| C | 1 |
| D | 1 |
| Subtheme 2 | 4 |
| E | 1 |
| F | 1 |
| G | 1 |
| H | 1 |
| Theme B | 8 |
| Subtheme 1 | 4 |
| I | 1 |
| J | 1 |
| K | 1 |
| L | 1 |
| Subtheme 2 | 4 |
| M | 1 |
| N | 1 |
| O | 1 |
| P | 1 |
| Theme C | 16 |
| Subtheme 1 | 4 |
| Q | 1 |
| R | 1 |
| S | 1 |
| T | 1 |
| Subtheme 2 | 12 |
| U | 1 |
| V | 2 |
| W | 2 |
| X | 1 |
| Y | 1 |
| Z | 1 |
I wonder if anyone has done the same or might have an idea on how to make this happens efficiently.
Allow me to create the data to make it easy to replicate it.
df <- data.frame(Level_1 = c(rep("Theme A", 8), rep("Theme B", 8), rep('Theme C', 12)),
Level_2 = c(rep("Subtheme 1", 4), rep("Subtheme 2", 4),rep("Subtheme 1", 4), rep("Subtheme 2", 4), rep("Subtheme 1", 4), rep("Subtheme 2", 8)),
Level_3 = c(LETTERS[1:22], "V", "W", "W", "X", "Y", "Z"))
Your help and advice are much appreciated.