How can I plot mixed data types of a csv file ?
Show older comments
I'm working on a .csv file that contains one column of mixed data types , the coulmn looks something like:
,"Admit","Gender","Dept","Freq"
1,"Admitted","Male","A",512
2,"Rejected","Male","A",313
How can I read the data from the file and plot them in a reasonable way to determine appropriate features for a classification problem ?
I've tried to use txt2str , csvread , and textscan functions beside some file format conversion functions as well (xlsread ,[num,txt,raw] ) and non of them worked with my data!!
Any good suggestions would be appreciated .
1 Comment
dpb
on 17 Apr 2014
If you have the Statistics Toolbox, create a nominal variable array from the non-numeric fields
doc nominal % and friends
If you don't have the toolbox, convert manually. unique could be of interest, perhaps to create the number of categories for each.
After the above, then you can do something with those values.
Accepted Answer
More Answers (1)
dpb
on 18 Apr 2014
Presuming do have the Statistics Toolboxen...
>> [a,g,d,f]=textread('bara.csv','%*d%s%s%s%d','headerlines',1,'delimiter',',')
a =
'"Admitted"'
'"Rejected"'
g =
'"Male"'
'"Male"'
d =
'"A"'
'"A"'
f =
512
313
>> an= nominal(a)
an =
"Admitted"
"Rejected"
>> cnt=hist(an)
cnt =
1
1
>>
etc., etc., etc., ...
15 Comments
Bara'a
on 20 Apr 2014
dpb
on 20 Apr 2014
If the file is like the one you posted, the textread format worked as demonstrated here. Need the actual line of code as used and anything that's different in the file itself to tell why it failed.
NB: the same error occurred here if I tried to remove the quote marks so since they're just categorical data I just left them in.
On no Stat Toolbox, then we'll convert the data to nominal w/o the class; not quite as clever but it'll work just fine. Let's figure out where the problem in the data file is first off...
I'd still like to see the actual file lines that failed and the textread and/or textscan that failed...surely can't be that tough.
As far as the categorical data, as above once it's read, unique is your friend. I duplicated the two rows you gave in obverse order to have
>> a
a =
'Admitted'
'Rejected'
'Rejected'
'Admitted'
>>
Then,
>> [ua,~,ic]=unique(a)
ua =
'Admitted'
'Rejected'
ic =
1
2
2
1
gives the two category names and their locations in the index vector. Use the latter for the analysis variables and associate w/ the appropriate string for output. Piece o' cake... :)
fid=fopen('UCBAdmissions.csv','r');
c=textscan(fid,repmat(%s,1,10),'delimiter',',','CollectOutput',true);
Also , I've tried textread as well :
[a,g,d,f]=textread('UCBAdmissions.csv','%*d%s%s%s%d','headerlines',1,'delimiter',',')
Again, need an actual sample data file record or three and the resultant error in context.
What I note in particular is you've got 10 string fields in one format and the numeric and three strings and numeric in the other for the same file.
ADDENDUM:
I DAGS and was unable to find a simple way to simply download the file--it's in an R dataset but I don't have R and don't want to go to the trouble to deal with it...figured somewhere there would be a link to raw data file, but it didn't show up. If you've a convenient spot or attach or post it on a sharing site I'd look at the file itself and see what's the issue.
ADDENDUM 2:
Another search found a shortened version of 24 records--it looks just like yours excepting the first column in also a quoted string instead of numeric. I downloaded it and the following worked just fine (changing the first '%*d' to '%*s', of course)...
>> type ucbadmissions.csv
"","Admit","Gender","Dept","Freq"
"1","Admitted","Male","A",512
"2","Rejected","Male","A",313
"3","Admitted","Female","A",89
"4","Rejected","Female","A",19
"5","Admitted","Male","B",353
...
"20","Rejected","Female","E",299
"21","Admitted","Male","F",22
"22","Rejected","Male","F",351
"23","Admitted","Female","F",24
"24","Rejected","Female","F",317
>> fmt=['%*s' repmat('%s',1,3) '%3d'];
[a,g,d,f]=textread('ucbadmissions.csv',fmt,'headerlines',1,'delimiter',',');
>> a=strrep(a,'"','')
a =
'Admitted'
'Rejected'
'Admitted'
'Rejected'
'Admitted'
...
'Rejected'
'Admitted'
'Rejected'
>>
Image Analyst
on 21 Apr 2014
I just ran this code and it ran fine:
folder = 'C:\Users\Bara\Documents';
baseFileName = 'UCBAdmissions.csv';
fullFileName = fullfile(folder, baseFileName)
t = readtable(fullFileName)
All the data are in variable t:
t =
Var1 Admit Gender Dept Freq
____ __________ ________ ____ ____
'1' 'Admitted' 'Male' 'A' 512
'2' 'Rejected' 'Male' 'A' 313
'3' 'Admitted' 'Female' 'A' 89
'4' 'Rejected' 'Female' 'A' 19
'5' 'Admitted' 'Male' 'B' 353
'6' 'Rejected' 'Male' 'B' 207
'7' 'Admitted' 'Female' 'B' 17
'8' 'Rejected' 'Female' 'B' 8
etc....
Bara'a
on 21 Apr 2014
Image Analyst
on 21 Apr 2014
I don't know what you want to plot. How about a bar chart showing the number of admitted and rejected people broken down by Gender? Specify what column you want plotted vs. another column. Or something else, like a histogram or something. I just don't know what to plot. Did you mention that yet? If so, I must have missed it, so please tell me again.
dpb
on 21 Apr 2014
It's the same (identical) answer as the first... :)
On plotting, and analyzing these kinds of data is where (imo) Matlab falls somewhat short of SAS, R and other statistical analysis systems in not having builtin categorizing keywords. You can always "get there from here" but it may take some additional effort/data rearrangement. Perhaps the new table structure that IA is enamored with does have the added ability; it has some features that undoubtedly will make such simpler.
Actually, that makes me realize I'm forgetting about the (relatively) new dataset class...let's see what mayhaps can do therein--I've not fiddled with it much yet meself...
>> UCB=dataset({nominal(a),'Admit'},{nominal(g),'Gender'},{nominal(d),'Department'},{f,'Freq'})
UCB =
Admit Gender Department Freq
Admitted Male "A" 512
Rejected Male "A" 313
Admitted Female "A" 89
Rejected Female "A" 19
Admitted Male "B" 353
Rejected Male "B" 207
Admitted Female "B" 17
Rejected Female "B" 8
Admitted Male "C" 120
Rejected Male "C" 205
Admitted Female "C" 202
Rejected Female "C" 391
Admitted Male "D" 138
Rejected Male "D" 279
Admitted Female "D" 131
Rejected Female "D" 244
Admitted Male "E" 53
Rejected Male "E" 138
Admitted Female "E" 94
Rejected Female "E" 299
Admitted Male "F" 22
Rejected Male "F" 351
Admitted Female "F" 24
Rejected Female "F" 317
>>
Ooohh!!!! :) I don't yet know what can do with it, but it's kewl, nonetheless. Unfortunately, I've got another commitment at the moment but maybe you can figure out something from here.
ADDENDUM:
I see I hadn't yet removed the double quotes from Department but that's easy enough to handle.
Bara'a
on 24 Apr 2014
dpb
on 24 Apr 2014
Well, I've not had time to actually try to learn much about the dataset methods other than just a (very) quick perusal thereof. Oh, bummer!! I went back and realized you said you don't have the Stat Toolbox so unless it's also in some other tooboxen you do have you're stuck with the basic Matlab cell/structure data types it would seem.
That makes going back to the original idea I showed of making your own nominal/ordinal variables by associating the string value with a level via unique then working with those numeric values.
The real pita in Matlab is that, unlike SAS, BMDP, R, the other really "statistics-aware" packages, there is no FREQUENCY variable, even in the Statistics Toolbox. Hence, you'll have to either
a) duplicate the data from the summary table form given to replicate each record type the frequency number of times to use builtin Matlab functions over those (a real waste of memory, of course), or
b) compute the proportions numerically from the frequencies themselves.
Again, it just isn't that convenient to do these kinds of analyses in Matlab unless there are some major tricks that aren't being revealed in the Stats Toolbox doc's...
Categories
Find more on Data Preprocessing in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!