0

I have a large Excel file with data that looks like the following throughout:

Sample of Excel file.

I know I can use something like

data[which(data$Label == "Crop Sales ($)"),c(1,5,6,7)]

to be able to dictate which value in the "Label" column I want to be shown. However, what I want to be able to do is calculate the total amount of "Crop Sales($)" from each state to be able to see which state had the highest or lowest amount of crop sales.

For example, I want the table to look like the following :

Example of table

except I would like the "Value" column to be combined so that there is only one row for each state with a combined total of crop sales.

Is there any way that this can be done?

Also, some of the values in the "Value" column contain values of "-" or "(D)" and are therefore not integer values, will this make a difference?

Luke Ford
  • 19
  • 2
  • 2
    `aggregate(Value~state+Label,data,sum)`? – Onyambu May 02 '18 at 03:32
  • @Onyambu when I run that code I get the following error: "Error in Summary.factor(c(18912L, 10203L, 34345L, 32287L, 11662L, 20207L, : ‘sum’ not meaningful for factors".. any ideas? – Luke Ford May 02 '18 at 04:05
  • 1
    That is because your values were read in as factors... – Onyambu May 02 '18 at 04:07
  • 1
    first, do `aggregate(Value~state+Label,transform(data,Values=as.numeric(as.character(Values))),sum)` there will be warning due to the fact that there is `(D)` within that row... – Onyambu May 02 '18 at 04:08
  • oh okay I see what you mean, I think I got it now, thanks! – Luke Ford May 02 '18 at 04:19

0 Answers0