2
$\begingroup$

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?

Thanks.

enter image description here

Improve this question
$\endgroup$

2 Answers 2

Reset to default
4
$\begingroup$ 
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;

then

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

n = 50;

Generate random test data and Export as Excel document

SeedRandom[0]

Export["data.xls", 
  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

Improve this answer
$\endgroup$
2
  • $\begingroup$ Thanks! How can I put that output on a table? $\endgroup$
    – guasabito
    Commented Nov 5, 2018 at 4:04
  • $\begingroup$ Thanks! Everything worked perfectly. $\endgroup$
    – guasabito
    Commented Nov 5, 2018 at 4:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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