- #1

- 6

- 0

I need to rearrange my data. I have an excel file with 12 columns and 2045 rows, which I transformed that into a .mat.

I need to query my data rearranged as follows (cf. example matrix below):

1-sum all values of Col 3 if:

a-they belong to a given value of Col 1 and to a given value in Col 2.

2-mean of values of Col 4 for each given value of Col 1

3-NaN's where conditions above are not met

4-all saved in a new matrix

The difficulty is to make this work for the 2045 rows of my database. I guess it needs a for-loop that reiterates the calculations and at each step saves the values in a matrix.

Here is an example:

input matrix A:

1 NaN NaN 20

1 2 4 10

1 2 3 15

1 NaN NaN 10

1 5 2 25

1 6 1 10

1 2 5 5

2 NaN NaN 5

2 1 3 10

2 1 2 25

3 6 5 15

3 4 3 10

3 3 1 30

This is what I'd like to get:

1 2 3 4 5 6 Mean

1 NaN 12 NaN NaN 2 1 13.57

2 5 NaN NaN NaN NaN NaN 13.33

3 NaN NaN 1 3 NaN 5 18.33

I'd really appreciate your help and will include you in the acknowledgments of the 2 papers I will get out of this.

Thanks a lot!!

g