SPSSX Discussion

Aggregate or Restructure?

Classic

List

Threaded

8 messages Options

mils

Aggregate or Restructure?

Hi everyone,

I have the following data:

brand_a1 Brand_b2 Brand_c3
a1 a2 a3 b1 b2 b3 c1 c2 c3
respondent1 5 6 8 1 5 2 8 2 4
respondent2 4 3 2 8 6 7 6 5 2
respondent3 7 5 3 1 5 9 4 5 6

and I was wondering what is the best option to get the following set of data:

Brand Attribute Sum_Responses
1 1 16
1 2 14
1 3 13
2 1 10
2 2 16
2 3 18
3 1 18
3 2 12
3 3 12

Could you provide syntax?

data_aggregate_or_restructure.xlsx

Thanks in advance!!!

mils

Andy W

Re: Aggregate or Restructure?

I would use two separate VARSTOCASES followed by an aggregate.

************************************************************.
data list free / a1 a2 a3 b1 b2 b3 c1 c2 c3.
begin data
5 6 8 1 5 2 8 2 4
4 3 2 8 6 7 6 5 2
7 5 3 1 5 9 4 5 6
end data.

varstocases
/make a from a1 to a3
/make b from b1 to b3
/make c from c1 to c3
/index attribute.

varstocases
/make responses from a to c
/index brand.

DATASET DECLARE aggResponse.
AGGREGATE
/OUTFILE='aggResponse'
/BREAK=brand attribute
/Sum_Responses = SUM(responses).
************************************************************.

Andy W
apwheele@gmail.com
http://andrewpwheeler.wordpress.com/

mils

Re: Aggregate or Restructure?

Wow, that worked like a charm!!!

I run a correspondence analysis and got the plot, but I was wondering which table should I use if I want to create that chart in an excel file, so I can change the scale or colours, etc...

WEIGHT by Sum_Responses.

CORRESPONDENCE
TABLE =brand(1 3) BY attribute(1 3)
/DIMENSIONS = 2
/MEASURE = CHISQ
/STANDARDIZE = RCMEAN
/NORMALIZATION = SYMMETRICAL
/PRINT = TABLE RPOINTS CPOINTS RPROTIPOES CPROTIPOES
/PLOT = NDIM(1,2) BIPLOT(20).

WEIGHT off.

Thanks in advance!!!

mils

Andy W

Re: Aggregate or Restructure?

1) You should ask a new question, to keep the thread consistent on NABBLE.

2) You can change colors and scales in SPSS charts, so those aren't very good reasons to want to use excel charts.

Beyond that I can't help, I don't have the license for CORRESPONDENCE.

Andy W
apwheele@gmail.com
http://andrewpwheeler.wordpress.com/

Bruce Weaver

Re: Aggregate or Restructure?

Administrator

In reply to this post by mils

Here's a variation on Andy's solution with one AGGREGATE and one VARSTOCASES (and use of the keyword TO in a few places).

NEW FILE.
DATASET CLOSE all.
data list free / respondent a1 a2 a3 b1 b2 b3 c1 c2 c3 (10f2.0).
begin data
1 5 6 8 1 5 2 8 2 4
2 4 3 2 8 6 7 6 5 2
3 7 5 3 1 5 9 4 5 6
end data.

AGGREGATE
/OUTFILE=*
/BREAK=
/a1 to a3 = SUM(a1 to a3)
/b1 to b3 = SUM(b1 to b3)
/c1 to c3 = SUM(c1 to c3)
.

VARSTOCASES
/MAKE Sum_Responses FROM a1 to c3
/INDEX=Brand(3) Attribute(3)
/KEEP=
/NULL=KEEP.

FORMATS Sum_Responses(f5.0).
LIST.

OUTPUT:

Brand Attribute Sum_Responses

1 1 16
1 2 14
1 3 13
2 1 10
2 2 16
2 3 18
3 1 18
3 2 12
3 3 12

Number of cases read: 9 Number of cases listed: 9

mils wrote

Hi everyone,

I have the following data:

brand_a1 Brand_b2 Brand_c3
a1 a2 a3 b1 b2 b3 c1 c2 c3
respondent1 5 6 8 1 5 2 8 2 4
respondent2 4 3 2 8 6 7 6 5 2
respondent3 7 5 3 1 5 9 4 5 6

and I was wondering what is the best option to get the following set of data:

Brand Attribute Sum_Responses
1 1 16
1 2 14
1 3 13
2 1 10
2 2 16
2 3 18
3 1 18
3 2 12
3 3 12

Could you provide syntax?

data_aggregate_or_restructure.xlsx

Thanks in advance!!!

--
Bruce Weaver
bweaver@lakeheadu.ca
http://sites.google.com/a/lakeheadu.ca/bweaver/

"When all else fails, RTFM."

PLEASE NOTE THE FOLLOWING:
1. My Hotmail account is not monitored regularly. To send me an e-mail, please use the address shown above.
2. The SPSSX Discussion forum on Nabble is no longer linked to the SPSSX-L listserv administered by UGA (https://listserv.uga.edu/).

Rich Ulrich

Re: Aggregate or Restructure?

In reply to this post by mils

You can try reading in just the nine, with better names.

FLIP.
COMPUTE sumR= sum(var1 to var9).
LIST var_name sumR.

You might need to fix the names of the variables that come
out if FLIP.

--
Rich Ulrich

> Date: Wed, 21 Aug 2013 09:18:22 -0700

> From: [hidden email]
> Subject: Aggregate or Restructure?
> To: [hidden email]
>
> Hi everyone,
>
> I have the following data:
>
>
> brand_a1 Brand_b2 Brand_c3
> a1 a2 a3 b1 b2 b3 c1 c2 c3
> respondent1 5 6 8 1 5 2 8 2 4
> respondent2 4 3 2 8 6 7 6 5 2
> respondent3 7 5 3 1 5 9 4 5 6
>
> and I was wondering what is the best option to get the following set of
> data:
>
> Brand Attribute Sum_Responses
> 1 1 16
> 1 2 14
> 1 3 13
> 2 1 10
> 2 2 16
> 2 3 18
> 3 1 18
> 3 2 12
> 3 3 12
>
> Could you provide syntax?
>
> data_aggregate_or_restructure.xlsx
> <http://spssx-discussion.1045642.n5.nabble.com/file/n5721661/data_aggregate_or_restructure.xlsx>
>
> ...

David Marso

Re: Aggregate or Restructure?

Administrator

With a large number of cases FLIP will not be scalable!
I would first aggregate without a break and then use VARSTOCASES with 2 indexes.

Rich Ulrich wrote

You can try reading in just the nine, with better names.

FLIP.
COMPUTE sumR= sum(var1 to var9).
LIST var_name sumR.

You might need to fix the names of the variables that come
out if FLIP.

--
Rich Ulrich

> Date: Wed, 21 Aug 2013 09:18:22 -0700
> From: [hidden email]
> Subject: Aggregate or Restructure?
> To: [hidden email]
>
> Hi everyone,
>
> I have the following data:
>
>
> brand_a1 Brand_b2 Brand_c3
> a1 a2 a3 b1 b2 b3 c1 c2 c3
> respondent1 5 6 8 1 5 2 8 2 4
> respondent2 4 3 2 8 6 7 6 5 2
> respondent3 7 5 3 1 5 9 4 5 6
>
> and I was wondering what is the best option to get the following set of
> data:
>
> Brand Attribute Sum_Responses
> 1 1 16
> 1 2 14
> 1 3 13
> 2 1 10
> 2 2 16
> 2 3 18
> 3 1 18
> 3 2 12
> 3 3 12
>
> Could you provide syntax?
>
> data_aggregate_or_restructure.xlsx
> <http://spssx-discussion.1045642.n5.nabble.com/file/n5721661/data_aggregate_or_restructure.xlsx>
>
> ...

Please reply to the list and not to my personal email.
Those desiring my consulting or training services please feel free to email me.
---
"Nolite dare sanctum canibus neque mittatis margaritas vestras ante porcos ne forte conculcent eas pedibus suis."
Cum es damnatorum possederunt porcos iens ut salire off sanguinum cliff in abyssum?"

David Marso

Re: Aggregate or Restructure?

Administrator

Don't try this at home ;-)
MATRIX.
SAVE (UNIFORM(100000,200))/OUTFILE * / VARIABLES v001 TO V200.
END MATRIX.
FLIP.

David Marso wrote

With a large number of cases FLIP will not be scalable!
I would first aggregate without a break and then use VARSTOCASES with 2 indexes.

Rich Ulrich wrote

You can try reading in just the nine, with better names.

FLIP.
COMPUTE sumR= sum(var1 to var9).
LIST var_name sumR.

You might need to fix the names of the variables that come
out if FLIP.

--
Rich Ulrich

> Date: Wed, 21 Aug 2013 09:18:22 -0700
> From: [hidden email]
> Subject: Aggregate or Restructure?
> To: [hidden email]
>
> Hi everyone,
>
> I have the following data:
>
>
> brand_a1 Brand_b2 Brand_c3
> a1 a2 a3 b1 b2 b3 c1 c2 c3
> respondent1 5 6 8 1 5 2 8 2 4
> respondent2 4 3 2 8 6 7 6 5 2
> respondent3 7 5 3 1 5 9 4 5 6
>
> and I was wondering what is the best option to get the following set of
> data:
>
> Brand Attribute Sum_Responses
> 1 1 16
> 1 2 14
> 1 3 13
> 2 1 10
> 2 2 16
> 2 3 18
> 3 1 18
> 3 2 12
> 3 3 12
>
> Could you provide syntax?
>
> data_aggregate_or_restructure.xlsx
> <http://spssx-discussion.1045642.n5.nabble.com/file/n5721661/data_aggregate_or_restructure.xlsx>
>
> ...