I have an excel file with two Columns and 28664 Rows. Columns are Fuel and Power. I want to create a summary in a table of the total Power of each Fuel type. In other words, I want the Total for each fuel type. I started using Transpose to create a list containing each Fuel cell with its corresponding Power but I'm really stuck at this point. How can I create this summary?


enter image description here

data = Import[FileNameJoin[{NotebookDirectory[], "workbook1.xlsx"}], {"Data", 1}]

blank entries may be imported as empty strings so:

data[[All, 2]] = data[[All, 2]] /. _String -> 0;


GroupBy[data, First -> Last, Total] 
  • $\begingroup$ Thanks! How can I put that output on a table or barchart? $\endgroup$ – guasabito Nov 5 '18 at 3:57
  • $\begingroup$ Please at least try something before asking. BarChart[GroupBy[data, First -> Last, Total]] ???? $\endgroup$ – Mike Honeychurch Nov 5 '18 at 7:39
fuels = {"Coal", "Gas", "Hydro", "Nuclear", "Oil", "Other", "Wind"};

n = 50;

Generate random test data and Export as Excel document


  Transpose[{RandomChoice[fuels, n], RandomReal[{0, 5000}, n]}]];

You are starting here. Import data from Excel document

data = Import["data.xls"][[1]];

Total by fuel EDIT: Added clean-up for blank values (issue identified by Mike Honeychurch)

summary = Sort[{#[[1, 1]], Total[#[[All, 2]]]} & /@ GatherBy[data, First]]/. 
  Plus[x_?NumericQ, _. * _String] :> x

(* {{"Coal", 14230.6}, {"Gas", 11889.7}, {"Hydro", 20580.6}, {"Nuclear", 
  13221.4}, {"Oil", 14643.9}, {"Other", 17112.9}, {"Wind", 15121.8}} *)

EDIT 2: Added table

summary // Prepend[#, Style[#, Bold, 14] & /@ {"Fuel", "Power"}] & // 
 Grid[#, Frame -> All] &

enter image description here

  • $\begingroup$ Thanks! How can I put that output on a table? $\endgroup$ – guasabito Nov 5 '18 at 4:04
  • $\begingroup$ Thanks! Everything worked perfectly. $\endgroup$ – guasabito Nov 5 '18 at 4:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.