How to reshape this database in Excel or R

Question

How to transform database organized like A into B-type?

fruits	marker	conf_1	conf_2
Orange	C	123	456
Orange	B	123	456
Orange	A	123	456
Apple	C	987	654
Apple	B	987	654
Apple	A	987	654
Plum	C	321	654
Plum	B	321	654
Plum	A	321	654
Pear	C	765	890
Pear	B	765	890
Pear	A	765	890
Grape	C	235	652
Peach	A	876	325
Peach	C	876	325

I have this data set in Excel, but also work in R. So I can perform this operation in Excel or in R. But how?

Answer 1

This PowerQuery would do it:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ChangeType = Table.TransformColumnTypes(Source,{{"fruits", type text}, {"marker", type text}, {"conf_1", Int64.Type}, {"conf_2", Int64.Type}}),
    RemoveDuplicate = Table.Distinct(ChangeType, {"fruits", "conf_1", "conf_2"}),

    PivotColumn = Table.Pivot(ChangeType, List.Distinct(ChangeType[marker]), "marker", "fruits", List.Count),
    MergeData = Table.NestedJoin(PivotColumn, {"conf_1", "conf_2"}, RemoveDuplicate, {"conf_1", "conf_2"}, "Fruits", JoinKind.LeftOuter),
    ExpandData = Table.ExpandTableColumn(MergeData, "Fruits", {"fruits"}, {"fruits"}),
    ReorderColumns = Table.ReorderColumns(ExpandData,{"fruits", "A", "B", "C", "conf_1", "conf_2"})
in
    ReorderColumns  

Or as @Zack suggested you could use a pivot table.
Pivot tables definitely aren't my strong point, so is probably a better way.

• Add fruits, conf_1 and conf_2 to the Rows.
• Add marker to the Columns and Values.
• Right-click the pivot table and select PivotTable Options
  Remove the tick from grand totals for rows and grand total for columns under the Totals & Filters tab.
• In the Design tab for the pivot table select Show in Tabular Form from the Report Layout drop-down.
• Select each of the Totals and right-click and select Field Settings.
  Select None for the subtotals.