Creating a Pivot Table

How can I generate a pivot table using this data?

Sure it's possible. As shown in your example, you just need to change the special character "✔️" by the number 1 using "ctrl + f". Then insert a Pivot Table with the data. And finally, create a new column named TOTAL and add the "=sum()" formula to count the items from each row. Something like that:

Create a pivot table with multiple hierarchical column groups

Adding on @I_O data transformation, the header for the groups you could achieve with the kableExtra package, i.e.

library(dplyr)
library(tidyr)
library(kableExtra)

options(knitr.kable.NA = '')

df %>% 
  pivot_longer(cols = starts_with('var'),
               names_to = 'var_name',
               values_to = 'value'
  ) %>% pivot_wider(id_cols = ID,
               names_from = c('group', 'var_name'),
               names_sep = '\n', ## wrap line after group name
               values_from = 'value'
  ) %>% 
  kbl(col.names = c("ID", "var1", "var2","var1", "var2","var1", "var2")) %>% 
  add_header_above(c(" ", "groupA" = 2,"groupB" = 2,"groupC" = 2 )) %>% 
  kable_styling(bootstrap_options = "striped", full_width = F)

How to create pivot Table from multiple tables

If this data is a pivot table, you have access to the source data, so you can change the pivot table to show what you need.

If that data is a dump with the layout as shown, then in order to use that data as a source for a pivot table, you first need to unpivot/normalize it, so it has the columns Category, Item description, Year and Value.

This can be done by loading the data into Power Query and clicking a few buttons. There are lots of tutorials out there for unpivoting in Power Query and how to transform data with it.

The green table has been generated with a few clicks in Power Query. The recorded code is below. (Note that none of this code was typed. All I did was click icons in a ribbon.)

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}, {"Column3", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([Column1],"Category:") then [Column1] else null),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom","Category: ","",Replacer.ReplaceText,{"Custom"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"Custom"}),
    #"Renamed Columns" = Table.RenameColumns(#"Filled Down",{{"Column1", "Item"}, {"Custom", "Category"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Item", "Category", "Column2", "Column3"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns",{{"Column2", "2018"}, {"Column3", "2019"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns1", each ([Item] <> "Category: Accessories" and [Item] <> "Category: Pants" and [Item] <> "Category: Shirt" and [Item] <> "Total")),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"Item", "Category"}, "Attribute", "Value"),
    #"Renamed Columns2" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Year"}}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Renamed Columns2",{"Category", "Item", "Year", "Value"})
in
    #"Reordered Columns1"

How to create an Excel pivot table from a table (ListObject object) via PyWin32?

These are guesses, but try using the table name for SourceData:

SourceData='SummaryTable'

and an R1C1 reference for the TableDestination, including the sheet name:

TableDestination="'People Summary'!R5C3"

---

Related Topics