HOW TO ADD ONE COLUMN BASED ON ANOTHER COLUMN

I have a file containing some columns.

hru sub year mon area gw_rchrg

1 1 2016 1 61 1.87

2 1 2016 1 233 2.91

3 1 2016 1 345 5.45

4 1 2016 1 600 1.23

5 1 2016 1 400 2.67

6 1 2016 1 235 1.34

7 2 2016 1 123 3.67

8 2 2016 1 178 4.78

9 2 2016 1 345 1.56

10 2 2016 1 430 2.67

11 2 2016 1 250 1.12

12 2 2016 1 278 2.12

I have a excel file which has 7 colums. In colum 2 there are rows containing 1 and 2. I want to do some operations.

The second column as 1 value. I want to multiply the values of column 5 and column 6 and add them according to 1 value if column 2. Similarly do it for 2 values of column 2. If there is any code for this?

Answer 1

[Matlab Pro]

Hi @Tanmoyee Bhattacharya

You need to write more clearer with less errors;

You wrote

• "I have a excel file which has 7 colums" - it has only 6 columns
• "The second column as 1 value" - not clear
• "I want to multiply the values of column 5 and column 6 and add them" Multiply - OK, "and then add them - what exactly to add to the multiplication result?

Anyhow , here is a simple example that might help you:

data = readtable('HRU.xlsx');
idx = data.SUB == 1;
mult = data.AREAkm2 .* data.GW_RCHGmm;
mult(~idx) = nan; % Make all values where "SUB ~=1" : NaN
data.('new_column') = mult;

Good luck

[Tanmoyee Bhattacharya]

Sir,

Thank you for your response.

I mentioned earlier 7 columns. It was wrong. It has 6 columns. Sir I want to multiply column 5 and column 6 as column 7 for values which matches the 1 value of column 2 and add all the value of column 7 which matches 1 value of column 2.

Sir I have provided only 1 and 2 value in column 2. But I have to do it for 1000 values. Then selecting single values take more time. If there is any way to do it for 1 to 100 values.

[Matlab Pro]

Hi again @Tanmoyee Bhattacharya

Do you mean you want to create a new column for each unique value in Column #2?

If so- here is a possible solution. I have timed it. it take no time to create 60 new columns based on 60 unique values in colunmn #2:

function tanmoyee_code()
data = readtable('HRU.xlsx');

% Creating logical indexes per value of the 'SUB' column (#2)
un_subs = unique(data.SUB);
idxes = arrayfun(@(x) data.SUB==x, un_subs, 'UniformOutput', false);

fld_names = strsplit(sprintf('sub_%d~',un_subs),'~');
fld_names(cellfun(@isempty ,fld_names)) = []; % chop empty entries

mult = data.AREAkm2 .* data.GW_RCHGmm;
tic
for iFld = 1:length(fld_names)
    fld1 = fld_names{iFld};
    tmp = mult;
    tmp(~idxes{iFld}) = nan;
    data.(fld1) = tmp;
end
toc

[Tanmoyee Bhattacharya]

Sir

Thank you for you kind reply.

I think I again fail to make you understand. Sir, This time I give an example

hru sub year mon area gw_rchrg multiplication sum

1 1 2016 1 61 1.87 61*1.87 =114.07 114.07+678.03+1880.25+738+1068+314.9

2 1 2016 1 233 2.91 233*2.91=678.03 = 4793.25 (for 1)

3 1 2016 1 345 5.45 345*5.45=1880.25

4 1 2016 1 600 1.23 600*1.23=738

5 1 2016 1 400 2.67 400*2.67=1068

6 1 2016 1 235 1.34 235*1.34=314.9

7 2 2016 1 123 3.67 123*3.67=451.41 451.41 +850.84+538.20+1148.10+280+589.36

8 2 2016 1 178 4.78 178*4.78=850.84 = 3857.91 (for 2)

9 2 2016 1 345 1.56 345*1.56=538.20

10 2 2016 1 430 2.67 430*2.67=1148.10

11 2 2016 1 250 1.12 250*1.12=280

12 2 2016 1 278 2.12 278*2.12=589.36