Importing data from Excel (or csv) datalog

[Josh]

Hi - I have a machine that logs data, which can be downloaded in an Excel
format, and I have a question about importing that data into SPSS.
Unfortunately, the datalog files are not very conveniently structured,
however, I suspect (or hope) that there's some SPSS trickery which can get
around the issue.

The machine is a heat stress monitor. It collects data from 3 sensors, and
these are used to calculate 4 indexes. After recording the data, you can
plug it into a computer and download the 7 variables into an Excel file. The
Excel file, however, is structured like this:

______

Variable 1
Time Stamp / Value
1/4/2013 9:28 / 11.72
1/4/2013 9:29 / 12.01
1/4/2013 9:30 / 13.08
1/4/2013 9:31 / 15.1
1/4/2013 9:32 / 16.84
......
......
......
Variable 2
Time Stamp  /  Value
1/4/2013 9:28 / 20.45
1/4/2013 9:29 / 20.82
1/4/2013 9:30 / 21.25
1/4/2013 9:31 / 21.68
1/4/2013 9:32 / 22.2
......
......
......
Variable 3
Time Stamp / Value
1/4/2013 9:28 / 22.87
1/4/2013 9:29 / 23.06
1/4/2013 9:30 / 23.36
1/4/2013 9:31 / 23.71
1/4/2013 9:32 / 24.11
.....
.....
.....
etc.

_________


So the first row just contains the variable name (e.g., Variable 1). The
next rows contain time-stamped values for that variable, until the recording
stopped. Then there is another row with just variable name (e.g., Variable
2), etc.

I am very familiar with SPSS' importing features, and I have worked with
similar equipment which logs data more conveniently, with each column
representing a different time-stamped variable.

The manual solution, which I would very much like to avoid, would be to
paste the respective variable names onto each row.

Can anyone think of any other solutions using SPSS? Would it be possible to
have SPSS recognize the variables based on the headings?

Thanks

Josh

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Albert-Jan Roskam]

Hi,

 

If the data are not huge, I'd do something like this:

 

import xlrd
import xlwt

 

# read logfile
wb = xlrd.open_workbook("logfile.xls")
sheet = wb.sheets()[0]  # first sheet
xls_data = {}
varNames = []
dt_header = "Time Stamp / Value"
for row in range(sheet.nrows):
    value = sheet.cell(row, 0).value
    isVarName = "/" not in value
    if dt_header in value:
        varName = sheet.cell(row-1, 0).value
        varNames.append(sheet.cell(row-1, 0).value)
        rowNumber = 0
    if varNames and value != dt_header and not isVarName:
        rowNumber += 1
        xls_data.setdefault(rowNumber, []).append(value)

 

# write restructured file
wb = xlwt.Workbook()
sheet = wb.add_sheet("organized_log")
for col, varName in enumerate(varNames):
    sheet.write(0, col, varName)  # header
for row, records in xls_data.iteritems():
    for col, value in enumerate(records):
        sheet.write(row, col, value)
wb.save("organized_log.xls")

 

Regards,
Albert-Jan

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
All right, but apart from the sanitation, the medicine, education, wine, public order, irrigation, roads, a
fresh water system, and public health, what have the Romans ever done for us?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 

From: Josh <[hidden email]>
To: [hidden email]
Sent: Wednesday, January 9, 2013 4:13 PM
Subject: [SPSSX-L] Importing data from Excel (or csv) datalog

Hi - I have a machine that logs data, which can be downloaded in an Excel
format, and I have a question about importing that data into SPSS.
Unfortunately, the datalog files are not very conveniently structured,
however, I suspect (or hope) that there's some SPSS trickery which can get
around the issue.

The machine is a heat stress monitor. It collects data from 3 sensors, and
these are used to calculate 4 indexes. After recording the data, you can
plug it into a computer and download the 7 variables into an Excel file. The
Excel file, however, is structured like this:

______

Variable 1
Time Stamp / Value
1/4/2013 9:28 / 11.72
1/4/2013 9:29 / 12.01
1/4/2013 9:30 / 13.08
1/4/2013 9:31 / 15.1
1/4/2013 9:32 / 16.84
......
......
......
Variable 2
Time Stamp  /  Value
1/4/2013 9:28 / 20.45
1/4/2013 9:29 / 20.82
1/4/2013 9:30 / 21.25
1/4/2013 9:31 / 21.68
1/4/2013 9:32 / 22.2
......
......
......
Variable 3
Time Stamp / Value
1/4/2013 9:28 / 22.87
1/4/2013 9:29 / 23.06
1/4/2013 9:30 / 23.36
1/4/2013 9:31 / 23.71
1/4/2013 9:32 / 24.11
.....
.....
.....
etc.

_________


So the first row just contains the variable name (e.g., Variable 1). The
next rows contain time-stamped values for that variable, until the recording
stopped. Then there is another row with just variable name (e.g., Variable
2), etc.

I am very familiar with SPSS' importing features, and I have worked with
similar equipment which logs data more conveniently, with each column
representing a different time-stamped variable.

The manual solution, which I would very much like to avoid, would be to
paste the respective variable names onto each row.

Can anyone think of any other solutions using SPSS? Would it be possible to
have SPSS recognize the variables based on the headings?

Thanks

Josh

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Bruce Weaver]

Are the time stamp and value in the same cell, or in two separate cells?  I'd guess they're in one cell, given your example.  If they are, see if this NPR solution does what you want.


GET DATA /TYPE=XLSX
  /FILE='C:\Temp\TimeStampedData.xlsx'
  /SHEET=name 'Sheet1'
  /CELLRANGE=full
  /READNAMES=off
  /ASSUMEDSTRWIDTH=32767.

IF char.index(V1,"Variable") GT 0
   Index = number(char.substr(V1,10),f2).
IF missing(Index) Index = LAG(Index).
FORMATS Index (F2.0).
EXECUTE.
SELECT IF SUM(char.index(V1,"Variable"),char.index(V1,"Value")) EQ 0.
EXECUTE.

COMPUTE #SP1 = char.index(V1," ").
COMPUTE #slash = char.index(V1," / ").
COMPUTE date = number(char.substr(V1,1,#SP1),adate).
COMPUTE time = number(char.substr(V1,#SP1+1,5),time5).
COMPUTE V = number(char.substr(V1,#slash+2),f8.2).
EXECUTE.

FORMATS date (date11) / time(time5) / V(f8.2).

SORT CASES BY date time Index.
CASESTOVARS
  /ID=date time
  /INDEX=Index
  /DROP=V1
  /GROUPBY=VARIABLE.

LIST.

OUTPUT from LIST:
 
       date  time      V.1      V.2      V.3
 
04-JAN-2013  9:28    11.72    20.45    22.87
04-JAN-2013  9:29    12.01    20.82    23.06
04-JAN-2013  9:30    13.08    21.25    23.36
 
Number of cases read:  3    Number of cases listed:  


HTH.

Josh wrote

Hi - I have a machine that logs data, which can be downloaded in an Excel
format, and I have a question about importing that data into SPSS.
Unfortunately, the datalog files are not very conveniently structured,
however, I suspect (or hope) that there's some SPSS trickery which can get
around the issue.

The machine is a heat stress monitor. It collects data from 3 sensors, and
these are used to calculate 4 indexes. After recording the data, you can
plug it into a computer and download the 7 variables into an Excel file. The
Excel file, however, is structured like this:

______

Variable 1
Time Stamp / Value
1/4/2013 9:28 / 11.72
1/4/2013 9:29 / 12.01
1/4/2013 9:30 / 13.08
1/4/2013 9:31 / 15.1
1/4/2013 9:32 / 16.84
......
......
......
Variable 2
Time Stamp  /  Value
1/4/2013 9:28 / 20.45
1/4/2013 9:29 / 20.82
1/4/2013 9:30 / 21.25
1/4/2013 9:31 / 21.68
1/4/2013 9:32 / 22.2
......
......
......
Variable 3
Time Stamp / Value
1/4/2013 9:28 / 22.87
1/4/2013 9:29 / 23.06
1/4/2013 9:30 / 23.36
1/4/2013 9:31 / 23.71
1/4/2013 9:32 / 24.11
.....
.....
.....
etc.

_________


So the first row just contains the variable name (e.g., Variable 1). The
next rows contain time-stamped values for that variable, until the recording
stopped. Then there is another row with just variable name (e.g., Variable
2), etc.

I am very familiar with SPSS' importing features, and I have worked with
similar equipment which logs data more conveniently, with each column
representing a different time-stamped variable.

The manual solution, which I would very much like to avoid, would be to
paste the respective variable names onto each row.

Can anyone think of any other solutions using SPSS? Would it be possible to
have SPSS recognize the variables based on the headings?

Thanks

Josh

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Josh]

Thanks for both of your replies! I'll play around with some of those new
commands, the CHAR.INDEX and CHAR.SUBSTR commands look particularly useful.

Thanks for your help!

Josh

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Jo Fennessey]

I will be working from home part of the day Thursday, 1/17/13 but away from my computer for several hours in the late morning/early afternoon.  Will be back in the office Friday.