reading data from excel and summing it up

  • Permalink
  • submit to reddit
  • Email
  • Follow


Hello. I'm having problems figuring out how to do this script:

I have an excel file containing: Dates, Time, Value

When I tried to read the excel file through Matlab, it changes the my 24hour time to decimal value. How do I keep it as 24 hour time values?

I need to somehow match the date and sum up values within every 15 minutes

Example:

12	10	9	13:01:00		0.01
12	10	9	22:36:52		0.01
12	10	9	22:40:27		0.01
12	10	9	22:42:41		0.01
12	10	9	22:45:37		0.01
12	10	9	22:47:25		0.01
12	10	9	22:51:12		0.01
12	10	9	22:54:25		0.01
12	10	9	22:58:04		0.01
12	10	9	23:01:38		0.01

So can anyone help me create a code to sum the 0.01 values for every 15 minutes and then combine the date that falls within the same time intervals?

Thank you
0
Reply Andrew 7/6/2010 2:07:04 AM

See related articles to this posting


On Jul 6, 2:07=A0pm, "Andrew Liu" <nos...@gmail.com> wrote:
> Hello. I'm having problems figuring out how to do this script:
>
> I have an excel file containing: Dates, Time, Value
>
> When I tried to read the excel file through Matlab, it changes the my 24h=
our time to decimal value. How do I keep it as 24 hour time values?
>
> I need to somehow match the date and sum up values within every 15 minute=
s
>
> Example:
>
> 12 =A0 =A0 =A010 =A0 =A0 =A09 =A0 =A0 =A0 13:01:00 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A00.01
> 12 =A0 =A0 =A010 =A0 =A0 =A09 =A0 =A0 =A0 22:36:52 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A00.01
> 12 =A0 =A0 =A010 =A0 =A0 =A09 =A0 =A0 =A0 22:40:27 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A00.01
> 12 =A0 =A0 =A010 =A0 =A0 =A09 =A0 =A0 =A0 22:42:41 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A00.01
> 12 =A0 =A0 =A010 =A0 =A0 =A09 =A0 =A0 =A0 22:45:37 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A00.01
> 12 =A0 =A0 =A010 =A0 =A0 =A09 =A0 =A0 =A0 22:47:25 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A00.01
> 12 =A0 =A0 =A010 =A0 =A0 =A09 =A0 =A0 =A0 22:51:12 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A00.01
> 12 =A0 =A0 =A010 =A0 =A0 =A09 =A0 =A0 =A0 22:54:25 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A00.01
> 12 =A0 =A0 =A010 =A0 =A0 =A09 =A0 =A0 =A0 22:58:04 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A00.01
> 12 =A0 =A0 =A010 =A0 =A0 =A09 =A0 =A0 =A0 23:01:38 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A00.01
>
> So can anyone help me create a code to sum the 0.01 values for every 15 m=
inutes and then combine the date that falls within the same time intervals?
>
> Thank you

How is this post different from your post 3 days ago:
http://groups.google.com/group/comp.soft-sys.matlab/browse_thread/thread/2b=
99ef0b02c4c157/d592d48b00246d8a#d592d48b00246d8a

And have you tried the suggestions given you back then?
If so, show us your code and we'll help you with any problems.

0
Reply mulgor (3012) 7/6/2010 2:38:08 AM

Hello

Thanks for responding. I couldn't figure it out before. I'm not quite sure how to go at it. So far I've only read the data. I'm not sure if I should read all the data first? Or one at a time (date, time, value)

I'm having trouble getting started. I was thinking of doing a "for loop" and "if statements". 

I took a class before, about a year ago, but I totally forgot how to write codes

-----------
clear all;
clc;

value = xlsread('Station_fire_tips_121009-061510-SF3', 'F2:F4098');
time = xlsread('Station_fire_tips_121009-061510-SF3', 'D2:D4098');

for i=1:4097 %number of rows
    
end

------------

so far I only got that. so I need to read 4097 data. I just don't know where to start. i'm in desperate need of assistance.

Thank you for your time.
0
Reply Andrew 7/6/2010 3:05:04 AM

help anyone?
0
Reply Andrew 7/6/2010 3:42:22 PM

On Jul 7, 3:42=A0am, "Andrew Liu" <nos...@gmail.com> wrote:
> help anyone?

Instead of sitting there waiting for others to solve YOUR problem, you
should be trying to solve it yourself.
It's pretty basic problem solving.  It's not rocket science.

What did you get when you executed those two xlsread commands?
Do value and time have any data in them?
If not, you need to read the help xlsread again and see what you have
missed out.
If they are not empty, what do the first few data look like?
Are they sensible?
If not, go back and read help xlsread again and see what you are doing
wrong.
0
Reply TideMan 7/6/2010 7:58:54 PM

I'm not asking for anyone to solve my problem. I'm asking someone who can give me guidance and help. So far no one has provided any...

anyways,

this is my code

-----

clear all;
clc;

% input data
as = load('data.txt');

% the output file
fid = fopen('dataout.txt','w');

%assign data
date = as(:,1:3);
ppt_data = as(:,end);
time = as(:,4:6);
k = 0;

for i=1:length(time)
if time(i,1:3) < [13 15 0]
    for ii = 1
        k = k+1;
        sum(k,:) = sum(ppt_data(ii,:));
    end
elseif time(i,1:3) > [22 16 0] & time(1,i:3) < [22 60 0]
    for ii = 1
        k = k+1;
        sum(k,:) = sum(ppt_data(ii,:));
    end
end
end
------

So I have a list of time data which consists of 3 columns (each column corresponds to the hour, minutes, seconds, respectively). I'm trying to experiment with this data seeing if I can add values together that corresponds to the times (within 15 minutes of each other).

Also, with the times, I have dates, that corresponds to the times. The times are set in 24 hours time frame.
0
Reply Andrew 7/6/2010 8:13:06 PM
comp.soft-sys.matlab 202866 articles. 523 followers. Post

5 Replies
281 Views

Similar Articles

[PageSpeed] 20


  • Permalink
  • submit to reddit
  • Email
  • Follow


Reply:

Similar Artilces:

SAS to excel
Hello, I used ODS to output data as an excel file. In the file there is a field with numbers like '95-1234', '03-2345', etc. However, when I opened the file in excel, it displays a date for these numbers. What do I need to do to keep numbers the way they should be? TIA Here is the code to output the data: ods html file='/projects/example.xls' style=minimal; proc print data=test; run ; ods html close ; With this kind of transfer sometimes it is necessary to send Excel instructions on formatting a particular item.... Try something like this in your proc print.....

User Entered Data(Read Only Data) to Excel
Hi All, I am new to Ruby, new task has been given to me by the company and they asked me to use ruby for it 1. Capture the User Entered data's in a webpage which is Readonly ( Data's can be in Text, List, Radio button, Check box) i need to capture the datas present in above fields and export to Excel with column heading. People please help me to solve this problem i dont know how and where to start Regards Md Rafiq -- Posted via http://www.ruby-forum.com/. ...

the data that is in the table is from excel, so the data to be read can changed?
for example the data before is EXEC 1,how if i'll changed the value to 1 how can i read those data if he changed the EXEC 1 to 1. that's why i said the data is changeable. can you give me a simple example about this? Thank You thank you for your help, but how if EXEC 1 can be EXECUTE 1 of EXE 1. I use the scan from string and it only reads EXEC, EXECUTE and EXE not 1 and space. Either EXECUTE 1, EXEC 1, EXE 1 and 1 can be the value that can get from excel. The string parse example is working at LV 7.0, I'm using LV 6.1. ...

read data from 2 excel sheets and write both into 1 excel sheet
Greetings.&nbsp; Let me explain this a bit.&nbsp; I am wondering what would be the best way to go about this. I have 2 small Excel spreadsheets.&nbsp; I want to be able to read the first 5 cells&nbsp;in the first&nbsp;row from each spreadsheet. I then want to write the data from those 2 sheets into a third different spreadsheet starting in different columns. I do not have the Excel Report set.&nbsp; I was hoping to do this with active X, but I'm not sure if this can be done. &nbsp; Thank you, bob Hi RLemo, yes it is possible. See this thread for more informatio...

Problem in reading excel sheet data from sheet .How to usw this data for further for identification of model
Hello, 1)I have CSTR data for Two In put Two Out put System(Temp, Flow). Data is in Excel sheet format(stored in PC desk top). I have use xlsread function (by giving correct path ) to read data .It gives me error. what should I do now to read my excel sheet data. 2) After getting the data from excel sheet .I want to find the model of this process. So is " iduidemo " will be helpful for me. In iduidemo file type is .sid how to create .sid file from excel sheet. data. 3) Is there any other way to find first order plus delay time model using process react...

How to Read data from excel file without converting a excel file into .csv or any other format
Hello, Can somebody suggest me how to read from an excel file (consisting of 10 work sheets) to an array? Thanks, She Hello, it's right that you can use activeX. (For example&nbsp;with rhe report generation toolbox from&nbsp;ni) But there are some problems: &nbsp; 1. You have to have&nbsp;excel installed on the targed PC. (to get the ActiveX interface) 2. There exists different "Microsoft Excel x.x Object Library Version x.x" which have different methods and&nbsp;properties.&nbsp;If a costumer has an other version, it could be that it doesn't work. 3....

Reading data from Excel...
Hi, I have a Excel sheet that have a column with mixed data: column can contain data like "892-234-32A" or like "892298343233432" I need to get all data column such as "text" to avoid numeric field show as exponential number. I'm trying with: SELECT CAST (CODICE as bigint(25)) FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;IMEX=1;HDR=YES;Database=C:\tempxadp\dist1.xls', Foglio1$) But I get error conversion type "from nvarchar to bigint" when query meet the alphanumeric field. Any tips ? Thanks in advance Piero Italy ...

how to read data from EXCEL
I need to read data from EXCEL worksheets in this way: The script perl asks me to write a number . I type a Number f.e. 1000 , the script should look for the number 1000 inside a excel worksheet file and if it find it, should capture all the other numbers reported in the raw where there is the number 1000. the mother number (1000) is always in the first colomn of excel sheet. col1 col2 col3 col4 col5 raw1 900 002 004 006 raw2 1000 345 445 888 777 once the script had found the wanted number (1000) in the raw2 col1 , it giv...

Read data from excel
Hi, I have a problem while reading data from excel. Every cell in my excel file is in the format: 1234;5678 However, I need to read both numbers, how can I do this? Is there any formula for this? "Ying Yap" <yyap1@iit.edu> wrote in message <g40a3a$lcr$1@fred.mathworks.com>... > Hi, I have a problem while reading data from excel. > Every cell in my excel file is in the format: > 1234;5678 > However, I need to read both numbers, how can I do this? > Is there any formula for this? If you excel file has data like 12;34;56, you can try to save this file as ...

Reading excel data
I have generated a "dat" file from a mathematica simulation. I then brought the data back into Mathematica to graph using the code below. All of this worked fine. Then, when I did a few manipulations in excel on the "dat" file (adding a column of data) and resaved the file as a tab delimited file as it was before my manipulations, the code below does not work. I get the following error messages. Transpose::nmtx: The first two levels of the one-dimensional list cannot be transposed Part::partw: Part All of the Transpose... does not exist. I did not manipulate at...

READ DATA IN EXCEL
Dear all, I made a SAS code to read some data in an excel file. It works fine. However, I have data in the excel spreadsheet up to line 61. However, SAS reads data until line 147 (it reads until line 147 but considers for analysis only the first 60 data, which is correct). Dou you know how I can teel SAS to read only the first 60 data? Please, see my code below. Thanks in advance for any help (sorry if it is a simple question, but I am new to SAS). Regards, R=F4mulo PROC IMPORT DBMS=3DEXCEL OUT=3DWORK.FABIO REPLACE DATAFILE=3D"C:\FABIOTE.XLS"; SHEET=3D"NNP"; GETNAMES=3D...

reading Excel data
I have a question on reading Excel data where all the Excel cells are not necessarily formatted the same way. I have an Excel file where one column contains ID numbers that need to be read as character variables (they are all numbers but many have leading zeros). In my Excel spreadsheet, the observations that have leading zeros have a "carrot" in the left part of the cell, and when you look at the actual contents of the cell, it looks like this: '000357849 (note the ' preceding the number). When I use proc import, SAS reads all these cells as missing. For Excel values tha...

reading data from excel
Dear friends, Does any one know how to read data from an excel sheet having data in many columns?. I tried xlsread, but it reads only the first column. thanks! sohail Maybe you didn't use the function xlsread correctly. Here is an example of an Excel file with 10 columns: A = rand(8,10); B = xlswrite('myfile.xls',A); C = xlsread('myfile.xls'); sohail wrote: > > > Dear friends, > > Does any one know how to read data from an excel sheet having data > in > many columns?. I tried xlsread, but it reads only the first column. > > thanks! > sohai...

read excel data
I I also have problem reading data from excel. I have a data table (tab delimited) that I would like to read into the vi. and consequently plot it into 3D graph. The data I attached &nbsp;is generated by me to simulate measured(real) data that I do not have yet. The vi. I?m using to read the data for some reason generates error. Can anyone help me in troubleshooting both the excel spreadsheet &nbsp;and the vi. Thanks, Milutin My missing attachments. 3d testdata sample.xls: http://forums.ni.com/attachments/ni/170/281978/1/3d testdata sample.xls Excel tabletoarr...

Reading Data from Excel
Hi every one, I am trying to build an applicatiuon which is supposed to read some data from excel spread sheet. can some one please help me in showing how. The excel file is created by other application and I have no control over it. My application will only open that file and read data from some cols specified by me. Thanks "Ajay Tiku" <TikuA@Lifepredictiontech.com> wrote in message news:RcadnTcvD5-qjundRVn-uQ@magma.ca... > > Hi every one, > > I am trying to build an applicatiuon which is supposed to read some data > from excel spread sheet. can some one p...

READING DATA FROM EXCEL
Hi i'd like to read data from excel. i found on the forum this VI (see attached) but it doesn't work for some unwired&nbsp;links. could some one help please? &nbsp; Regards &nbsp; Tchaou&nbsp;&nbsp; Read_Value_From_Excel.vi: http://forums.ni.com/attachments/ni/170/232148/1/Read_Value_From_Excel.vi readfromexel.vi: http://forums.ni.com/attachments/ni/170/232148/2/readfromexel.vi From the VBA help file concerning the change from&nbsp;Value to Value2: The only difference between this property and the Value property is that the Value2 property doesn?t use the C...

how to read data from excel?
We wrote the general code likie this: clear all;clc; [data, name]=xlsread('MFIN6201 Group Assignment Data Spreadsheet.xls'); y = getnameidx(name, {'ASX200'}); x1=getnameidx(name, {'Unemployment','D2:D65'}); Y=data(:,y); [m n]=size(data); m=length(data); one=ones(m,1); X1=[one data(:,x1)]; But it keeps showing error: Attempted to access data(:,0); index must be a positive integer or logical. Error in Group_assignment (line 7) Y=data(:,y); this is the first step of our assignments, really need to solve it asap. Many thanks On Sunday,...

Reading data from Excel
Hi All, Has anyone found a quick way of getting values from an Excel spreadsheet. When putting data into a spreadsheet you can get the range for the area and set the value to an array that then updates all of the cells in one go and its quite quick. When getting the data back I can't seem to find any other way than getting each cell as its own range - which is quite slow. For example this is what I'm currently doing: FOR dwCount := 1 UPTO dwMax //loop for each column we need to process for this row. sCellRef := aFields[dwCount,2]+NTrim(dwXLRow) ...

reading excel data
Hello, I've built a model in Scicos representing a physical system. I am now trying to set an input from some measurements I made to see the response of the system. The point is that I don't understand the way external data should be imported in scicos. Is it possible to find a source block to read data from an excel sheet? alexandre alexandre.cluzet@voila.fr wrote: > Hello, I've built a model in Scicos representing a physical system. I > am now trying to set an input from some measurements I made to see the > response of the system. The point is that I don&#...

HOW read a data from word and excel
Hi to all, i want to generate a report by reading the raw data from EXCEL and WORD , how use infile statement, can anybody help me have a nice day Srini ...

How to read excel data into an array?
Dear all: &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; I want to use the excel data to build an array (2D)and then operate it in labview 7.1, but I can just do my vi in the stage, can any one help me to read the excel data and build an array first.Here is my vi and the excel file.Thanks for you help. &nbsp; &nbsp; project.vi: http://forums.ni.com/attachments/ni/170/205282/1/project.vi data.xls: http://forums.ni.com/attachments/ni/170/205282/2/data.xls Is there a way to read excel data without select the range ? Where excel file takes the place i...

how to read datas from an excel file
i have datas in an excel file like this: names surnames jack white smith black marry brown how can i read these datas for ex i know that names in the cell A1 and surnames is next to it? Hi, Unless you know excell's format it probably is easier to save the excell sheet as csv file and then do something like this #include <ifstream> #include <string> #include <sstream> using namespace std; int main() { int RetVal = 0; try { ifstream Input( "excell.csv" ); // Testing if open etc. string Line Name, Surname; while( getline( Input...

Read data from Excel spreadsheet
Hello, I have NEVER used Labview before so I will be registering for the basic course soon. But one of the things I would like to do with Labview is to read two columns of X and Y data from an excel file, plot it and determine the area under the curve. I found various examples that would do this, but they were for older versions of Labview. I have also been TRYING with examples provided with the evaluation software and finally the help menu. But I lack experience, I found it difficult to adapt for my needs. Is there anyone would help me out here, please???? I am using Labview 7 Express. Reg...

Reading Data From an Excel Sheet
Hi all, I want a python script which takes in input an EXCEL sheet and then reads the data in it. Any code snippets will be fine and this i want this in windows XP . Thanks in Advance Sagar Meesala sagar wrote: > Hi all, > I want a python script which takes in input an EXCEL sheet > and then reads the data in it. > Any code snippets will be fine and this i want this in windows > XP . Might I humbly suggest that, instead of posting several somewhat demanding requests for help with no evidence of any effort on your part, that you put your...