Create a Table based on a Total and a Category from an Excel File

Clash Royale CLAN TAG#URR8PPP

up vote
1
down vote

favorite

1

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.

wolfram

share|improve this question

asked 4 hours ago

guasabito

62

New contributor

guasabito is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

up vote
1
down vote

favorite

1

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.

wolfram

share|improve this question

asked 4 hours ago

guasabito

62

New contributor

guasabito is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

up vote
1
down vote

favorite

1

up vote
1
down vote

favorite

1

1

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.

wolfram

share|improve this question

asked 4 hours ago

guasabito

62

New contributor

guasabito is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

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.

wolfram

wolfram

share|improve this question

asked 4 hours ago

guasabito

62

New contributor

guasabito is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share|improve this question

asked 4 hours ago

guasabito

62

New contributor

guasabito is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share|improve this question

share|improve this question

asked 4 hours ago

guasabito

62

New contributor

guasabito is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 4 hours ago

guasabito

62

asked 4 hours ago

guasabito

62

62

New contributor

guasabito is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

New contributor

guasabito is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

guasabito is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

add a comment | 

2 Answers
2

active

oldest

votes

up vote
2
down vote

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] 

share|improve this answer

answered 4 hours ago

Mike Honeychurch

30.7k268143

• 
  
  
  

  
  

  
  
  
  
  
  

  
  Thanks! How can I put that output on a table or barchart?
  – guasabito
  3 hours ago
  

  
  
  
  

add a comment | 

up vote
1
down vote

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] &

share|improve this answer

edited 2 hours ago

answered 3 hours ago

Bob Hanlon

56.6k23591

• 
  
  
  

  
  

  
  
  
  
  
  

  
  Thanks! How can I put that output on a table?
  – guasabito
  3 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Thanks! Everything worked perfectly.
  – guasabito
  2 hours ago
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
return StackExchange.using("mathjaxEditing", function ()
StackExchange.MarkdownEditor.creationCallbacks.add(function (editor, postfix)
StackExchange.mathjaxEditing.prepareWmdForMathJax(editor, postfix, [["$", "$"], ["\\(","\\)"]]);
);
);
, "mathjax-editing");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "387"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

guasabito is a new contributor. Be nice, and check out our Code of Conduct.

 

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name Email

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fmathematica.stackexchange.com%2fquestions%2f185315%2fcreate-a-table-based-on-a-total-and-a-category-from-an-excel-file%23new-answer', 'question_page');

);

Post as a guest

Name Email

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

active

oldest

votes

active

oldest

votes

up vote
2
down vote

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] 

share|improve this answer

answered 4 hours ago

Mike Honeychurch

30.7k268143

• 
  
  
  

  
  

  
  
  
  
  
  

  
  Thanks! How can I put that output on a table or barchart?
  – guasabito
  3 hours ago
  

  
  
  
  

add a comment | 

up vote
2
down vote

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] 

share|improve this answer

answered 4 hours ago

Mike Honeychurch

30.7k268143

• 
  
  
  

  
  

  
  
  
  
  
  

  
  Thanks! How can I put that output on a table or barchart?
  – guasabito
  3 hours ago
  

  
  
  
  

add a comment | 

up vote
2
down vote

up vote
2
down vote

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] 

share|improve this answer

answered 4 hours ago

Mike Honeychurch

30.7k268143

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] 

share|improve this answer

answered 4 hours ago

Mike Honeychurch

30.7k268143

share|improve this answer

share|improve this answer

answered 4 hours ago

Mike Honeychurch

30.7k268143

answered 4 hours ago

Mike Honeychurch

30.7k268143

answered 4 hours ago

Mike Honeychurch

30.7k268143

30.7k268143

• 
  
  
  

  
  

  
  
  
  
  
  

  
  Thanks! How can I put that output on a table or barchart?
  – guasabito
  3 hours ago
  

  
  
  
  

add a comment | 

• 
  
  
  

  
  

  
  
  
  
  
  

  
  Thanks! How can I put that output on a table or barchart?
  – guasabito
  3 hours ago
  

  
  
  
  

Thanks! How can I put that output on a table or barchart?
– guasabito
3 hours ago

Thanks! How can I put that output on a table or barchart?
– guasabito
3 hours ago

add a comment | 

up vote
1
down vote

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] &

share|improve this answer

edited 2 hours ago

answered 3 hours ago

Bob Hanlon

56.6k23591

• 
  
  
  

  
  

  
  
  
  
  
  

  
  Thanks! How can I put that output on a table?
  – guasabito
  3 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Thanks! Everything worked perfectly.
  – guasabito
  2 hours ago
  

  
  
  
  

add a comment | 

up vote
1
down vote

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] &

share|improve this answer

edited 2 hours ago

answered 3 hours ago

Bob Hanlon

56.6k23591

• 
  
  
  

  
  

  
  
  
  
  
  

  
  Thanks! How can I put that output on a table?
  – guasabito
  3 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Thanks! Everything worked perfectly.
  – guasabito
  2 hours ago
  

  
  
  
  

add a comment | 

up vote
1
down vote

up vote
1
down vote

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] &

share|improve this answer

edited 2 hours ago

answered 3 hours ago

Bob Hanlon

56.6k23591

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] &

share|improve this answer

edited 2 hours ago

answered 3 hours ago

Bob Hanlon

56.6k23591

share|improve this answer

share|improve this answer

edited 2 hours ago

edited 2 hours ago

edited 2 hours ago

answered 3 hours ago

Bob Hanlon

56.6k23591

answered 3 hours ago

Bob Hanlon

56.6k23591

answered 3 hours ago

Bob Hanlon

56.6k23591

56.6k23591

• 
  
  
  

  
  

  
  
  
  
  
  

  
  Thanks! How can I put that output on a table?
  – guasabito
  3 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Thanks! Everything worked perfectly.
  – guasabito
  2 hours ago
  

  
  
  
  

add a comment | 

• 
  
  
  

  
  

  
  
  
  
  
  

  
  Thanks! How can I put that output on a table?
  – guasabito
  3 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Thanks! Everything worked perfectly.
  – guasabito
  2 hours ago
  

  
  
  
  

Thanks! How can I put that output on a table?
– guasabito
3 hours ago

Thanks! How can I put that output on a table?
– guasabito
3 hours ago

Thanks! Everything worked perfectly.
– guasabito
2 hours ago

Thanks! Everything worked perfectly.
– guasabito
2 hours ago

add a comment | 

guasabito is a new contributor. Be nice, and check out our Code of Conduct.

 

draft saved

draft discarded

guasabito is a new contributor. Be nice, and check out our Code of Conduct.

guasabito is a new contributor. Be nice, and check out our Code of Conduct.

guasabito is a new contributor. Be nice, and check out our Code of Conduct.

 

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name Email

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fmathematica.stackexchange.com%2fquestions%2f185315%2fcreate-a-table-based-on-a-total-and-a-category-from-an-excel-file%23new-answer', 'question_page');

);

Post as a guest

Name Email

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name Email

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name Email

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name Email

Name Email

Name

Email

Name Email

Name

Email