Importing and assigning values to CSV files.

Hello everyone,

I have three MS Excel files, file A has 20.000x1 rows of 5 digit numbers, file B contains 20.000x1 rows of numbers and letters, all with length 6, i.e E549T1, E96L1M etc. Lastly I have a file C, which contains combinations of elements from file B in each row and column. File C has 54.000 rows and 13 columns where each cell is either a value from file B or empty.

C : E549T1 E96L1M E549T1 E96L1M E549T1 E96L1M ...

I want to substitute each element in file B with the corresponding row element in file A. B(i) = A(i). Such that I can have a matrix C with only 5 digit numbers.

The problem is that C and B are imported as cell values and I can't simply assign ( = ) the file A values that way. I tried to import the values as string values and treat it like a matrix, but that didn't work either...

Can anyone give me some pointers?

Thanks in advance, Best regards, K

Answer 1

[dpb]

Just ran into the same kind of a searching problem a few weeks ago doing an analysis of the local community college foundation financial performance over the last decade or so in the process of writing new investment/spending policy. Had to find a given fund in the database given the name for various reasons. I posted a query at the time --

http://www.mathworks.com/matlabcentral/answers/157887-more-efficient-returning-string-position-in-cell-string-array

The ismember solution works for the exact match which is good for your case but the general inexact match case is still kinda a pain...

For your sample data--

>> c={'E549T1' 'E96L1M' 'E549T1' 'E96L1M' 'E549T1' 'E96L1M'};
>> b={'E549T1' 'E96L1M'};
>> cc(ismember(c,b{1}))={pi}
cc = 
  [3.1416]    []    [3.1416]    []    [3.1416]
>>

where I used a constant as the replacement value; just use the cell content corresponding in your case.

[Kasper]

Hi dpb, thanks for the reply.

The way you define C, is that how it is defined right after import? IF that's the case then

for i = 1:length(b)
  cc(ismember(c,b{i})) = A{1}
end

would substitute all values in b with the corresponding row value in a, and cc would be the substituted values of from c.

I'll try it as soon as I can. How did you import to get this file format?

Thanks for the inconvenience and help!

Regards, K

[dpb]

I don't know precisely your form; you didn't provide that level of detail. I just typed in the values you did give at the command line for demonstration purposes.