grouping observations with specific characteristics

Hi all,

I have the following table in excel

time  country  Location  price2
1/09  Austria  l1  12
2/09  Austria  l1  4
3/09  Austria  l1  2
1/09  Austria  l2  1
2/09  Austria  l2  23
3/09  Austria  l2  14
1/09  Germany  l1  100
2/09  Germany  l1  110
3/09  Germany  l1  120
1/09  Germany  l2  100
2/09  Germany  l2  110
3/09  Germany  l2  120

I want to obtain the following structure

1/09 Austria l1 12

1/09 Germany l1 100

 2/09   Austria  l1  4

2/09 Germany l1 110

3/09 Austria l1 2

3/09 Germany l1 120

1/09 Austria l2 1

1/09 Germany l2 100

2/09 Austria l2 23

2/09 Germany l2 110

and so forth...

the first 3 varibles are string varibles.

Is there any "qick code" for this as i have a large matrix?

Thanks in advance,

[Oleg Komarov]

This post has been duplicated on: http://www.mathworks.com/matlabcentral/answers/38955-conveting-a-string-of-variables-to-numbers

Answer 1

[Geoff]

You mean you want to sort by Location, then Time, then Country, then Price?

Before you go MatLabbing this... Would you consider that you can do this very easily in Excel?

Now, if you want to do it in MatLab, read all your data into an N-by-4 cell array (cell matrix, I guess), then call sortrows with your column order:

sortedData = sortrows( origData, [3, 1, 2, 4] );

[Geoff]

Oh yes, and if your dates are not immediately sortable as a string, you will need to either modify them to be sortable, or convert them to a value that is.

[ektor]

Hi Geoff,

I want to gather together first all l1s that correspond to 1/09

then, i want to gather all l1s that correspond to 2/09

then, i want to gather all l1s that correspond to 3/09

then, i want to gather all l2s that correspond to `1/09

then, i want to gather all l2s that correspond to `2/09

then, i want to gather all l2s that correspond to `3/09

and so forth.....

Thanks

[Geoff]

Like I said. Sort by location and date (others are optional). If you can have I10 or more, or dates 10/09 or more, then you need to convert those... You could convert the 'I' column with something like this - might need syntax tweaks:

data{:,3} = cellfun( @(x) str2double(x(2:end)), {data{:,3}});

For the dates, you could pad a leading zero onto anything with only 4 characters.

[ektor]

thank you Geoff!