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 201183 articles. 512 followers. Post

5 Replies
251 Views

Similar Articles

[PageSpeed] 24

  • 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....

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...

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...