Take the 2-minute tour ×
Mathematica Stack Exchange is a question and answer site for users of Mathematica. It's 100% free, no registration required.

I have an excel table that have 2 columns, the first one are dates, and the second one are values. I want to plot the value vs the date. I need to: first clean all the non numbers strings like {"Junio",""}, then make the date to something that Mathematica would understand.

{{"Unidad Indexada", ""}, {"Período Junio 2002 -", ""}, {"", ""}, {"", "Valor"}, {"Fecha", "Pesos ($)"}, {"", ""}, {"", ""}, {"2 0 0 2", ""}, {"", ""}, {"Junio", ""}, {"", ""}, {{2002, 6, 1, 0, 0, 0.}, 1.}, {{2002, 6, 2, 0, 0, 0.}, 1.0004}, {{2002, 6, 3, 0, 0, 0.}, 1.0008}, {{2002, 6, 4, 0, 0, 0.}, 1.0012}, {{2002, 6, 5, 0, 0, 0.}, 1.0016}, {{2002, 6, 6, 0, 0, 0.}, 1.002}, {{2002, 6, 7, 0, 0, 0.}, 1.0024}, {{2002, 6, 8, 0, 0, 0.}, 1.0029}, {{2002, 6, 9, 0, 0, 0.}, 1.0033}, {{2002, 6, 10, 0, 0, 0.}, 1.0037}, {{2002, 6, 11, 0, 0, 0.}, 1.0041}, {{2002, 6, 12, 0, 0, 0.}, 1.0045}, {{2002, 6, 13, 0, 0, 0.}, 1.0049}, {{2002, 6, 14, 0, 0, 0.}, 1.0053}, {{2002, 6, 15, 0, 0, 0.}, 1.0057}, {{2002, 6, 16, 0, 0, 0.}, 1.0061}, {{2002, 6, 17, 0, 0, 0.}, 1.0065}, {{2002, 6, 18, 0, 0, 0.}, 1.0069}, {{2002, 6, 19, 0, 0, 0.}, 1.0074}, {{2002, 6, 20, 0, 0, 0.}, 1.0078}, {{2002, 6, 21, 0, 0, 0.}, 1.0082}, {{2002, 6, 22, 0, 0, 0.}, 1.0086}, {{2002, 6, 23, 0, 0, 0.}, 1.009}, {{2002, 6, 24, 0, 0, 0.}, 1.0094}, {{2002, 6, 25, 0, 0, 0.}, 1.0098}, {{2002, 6, 26, 0, 0, 0.}, 1.0102}, {{2002, 6, 27, 0, 0, 0.}, 1.0106}, {{2002, 6, 28, 0, 0, 0.}, 1.0111}, {{2002, 6, 29, 0, 0, 0.}, 1.0115}, {{2002, 6, 30, 0, 0, 0.}, 1.0119}, {"", ""}, {"Julio", ""}, {"", ""}, {{2002, 7, 1, 0, 0, 0.}, 1.0125},...

share|improve this question

4 Answers 4

up vote 2 down vote accepted

Suppose you have your data stored in data, you can clean up the unwanted stuff using Cases:

newdata = Cases[data, {{__?NumericQ}, _}];

Then use DateListPlot to plot it:

DateListPlot[newdata]
share|improve this answer
    
How is that __?NumericQ is equal to 2002, 6, 1, 0, 0, 0. ?? –  Kafkarudo Aug 12 at 17:27
    
@Kafkarudo The __ is BlankSequence and matches a sequence of one or more expressions, and NumericQ gives True if an expression is numeric (so numbers or symbols such as Pi). –  DShort Aug 12 at 17:35
    
In my opinion this pattern was too general and therefore too risky for large data. –  hieron Aug 12 at 20:17

lets say your import data are

rawdata = {{"Unidad Indexada", ""}, {"Período Junio 2002 -", ""}, {"",    ""}, {"", "Valor"}, {"Fecha", "Pesos ($)"}, {"", ""}, {"", ""}, {"2 0 0 2", ""}, {"", ""}, {"Junio", ""}, {"", ""}, {{2002, 6, 1, 0, 0, 0.}, 1.}, {{2002, 6, 2, 0, 0, 0.},   1.0004}, {{2002, 6, 3, 0, 0, 0.}, 1.0008}, {{2002, 6, 4, 0, 0, 0.}, 1.0012}, {{2002, 6, 5, 0, 0, 0.}, 1.0016}, {{2002, 6, 6, 0, 0, 0.}, 1.002}, {{2002, 6, 7, 0, 0, 0.}, 1.0024}, {{2002, 6, 8, 0, 0, 0.}, 1.0029}, {{2002, 6, 9, 0, 0, 0.}, 1.0033}, {{2002, 6, 10, 0, 0, 0.}, 1.0037}, {{2002, 6, 11, 0, 0, 0.}, 1.0041}, {{2002, 6, 12, 0, 0, 0.}, 1.0045}, {{2002, 6, 13, 0, 0, 0.}, 1.0049}, {{2002, 6, 14, 0, 0, 0.}, 1.0053}, {{2002, 6, 15, 0, 0, 0.}, 1.0057}, {{2002, 6, 16, 0, 0, 0.}, 1.0061}, {{2002, 6, 17, 0, 0, 0.}, 1.0065}, {{2002, 6, 18, 0, 0, 0.}, 1.0069}, {{2002, 6, 19, 0, 0, 0.}, 1.0074}, {{2002, 6, 20, 0, 0, 0.}, 1.0078}, {{2002, 6, 21, 0, 0, 0.}, 1.0082}, {{2002, 6, 22, 0, 0, 0.}, 1.0086}, {{2002, 6, 23, 0, 0, 0.}, 1.009}, {{2002, 6, 24, 0, 0, 0.}, 1.0094}, {{2002, 6, 25, 0, 0, 0.}, 1.0098}, {{2002, 6, 26, 0, 0, 0.}, 1.0102}, {{2002, 6, 27, 0, 0, 0.}, 1.0106}, {{2002, 6, 28, 0, 0, 0.}, 1.0111}, {{2002, 6, 29, 0, 0, 0.}, 1.0115}, {{2002, 6, 30, 0, 0, 0.}, 1.0119}, {"", ""}, {"Julio", ""}, {"", ""}, {{2002, 7, 1, 0, 0, 0.}, 1.0125}};

And we just want to get all data of june in year 2002:

data = Select[rawdata, (#[[1, 1]](*year*)== 2002) && (#[[1, 2]](*month*)== 6) &]

you're lucky, the data have already DateList format

i.e.

DateString@data[[1, 1]]

(* out *)
"Sat 1 Jun 2002 00:00:00"

and you can do a plot immediately

DateListPlot@data

datelist

share|improve this answer

Suppose you have a Excel like this:

excel

The best way to import excel is through SemanticImport.(In version 10.0.0.0)

mydata = SemanticImport["d:/1.xlsx"]

and you will get

mathematica

In my computer, Mathematica does not Process Date properly,So I did this:

date = Normal@mydata[All, 1];

date = StringCases[#, WordCharacter ..] & /@ date

date = DateString /@ ToExpression[date]

DateListPlot[Transpose[{date, Normal@mydata[All, 2]}]]

and finally

enter image description here

share|improve this answer
(* Import your data from the Excel file *)
originalData = Import["your file name here"];

(* This checks to see if the data in the first column has been broken into a 6 part list and if the data in the second column is numeric; the Select function will return items that match both criteria*)
cleanData = Select[originalData, Length[#[[1]]] == 6 && NumericQ[#[[2]]] &];

(* Plot the cleaned data *)
DateListPlot[cleanData]
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.