about transposing-help

[Rodrigo Briceño]

Dear SPSS listers. I have a database with information about expenditure on a list of 45 products.
An identifier for each household is also provided.
The original format of my database is to have for example 45 rows for the first household that have reported consume on each of the 45 products, however the number of products consumed by households could vary between 1 and 45, and consequently varying number of rows.

The variables in my dataset are:
id: HH identifier
R802A: product name identifier
R805A: expenditure made in the product

What I need is to have each product as a column and each identifier as a row. I was trying to use the restructuring function, but some errors appeared: "The INDEX values for case 27964 have occurred before in the cases with the same ID values."

I also made a new empty database where I put ID in rows (so each line is a HH) and 45 columns (where each column is a product) but the missing part is how to put on each corresponding cell the expenditure made on each product. I was trying some kind of merge, but I have no idea about how to establish the filters to do it.

Any idea about how can I overcome this issue?

Thanks!

[ariel barak]

Hi Rodrigo,

You are on the right track with looking to restructure the data. The error you're describing is likely because you have two rows that are identical by what you want the identifying variables to be, likely the HH identifier and the produce name identifier.

For example, if you have two rows like this, you will get the error:

HH    R802A  R805A
0001  521      200
0001  521      125

So, the same family may have bought the same product twice and in your database, you have it as two rows. If you aggregate the data and break by HH, R802A, and then take the sum of R805A, you should then be able to restructure your data without the error(s) you observed. You want the row to look like:

HH    R802A  R805A_sum
0001  521      325

You could post a few lines of your data or some dummy data and what you'd like the result to look like and the list may be able to give you a more concrete solution.

Thanks,
Ariel

On Mon, May 11, 2009 at 3:10 PM, Rodrigo Briceño <[hidden email]> wrote:

Dear SPSS listers. I have a database with information about expenditure on a list of 45 products.
An identifier for each household is also provided.
The original format of my database is to have for example 45 rows for the first household that have reported consume on each of the 45 products, however the number of products consumed by households could vary between 1 and 45, and consequently varying number of rows.

The variables in my dataset are:
id: HH identifier
R802A: product name identifier
R805A: expenditure made in the product

What I need is to have each product as a column and each identifier as a row. I was trying to use the restructuring function, but some errors appeared: "The INDEX values for case 27964 have occurred before in the cases with the same ID values."

I also made a new empty database where I put ID in rows (so each line is a HH) and 45 columns (where each column is a product) but the missing part is how to put on each corresponding cell the expenditure made on each product. I was trying some kind of merge, but I have no idea about how to establish the filters to do it.

Any idea about how can I overcome this issue?

Thanks!

--

__________________________________________________________________

Rodrigo Briceño
Project Manager
Sanigest Internacional

[phone] ext. 113   Oficina Costa Rica
[phone]                  Fax
[hidden email]
www.sanigest.com

MSN: [hidden email]
SKYPE: rbriceno1087

_____________________

This communication contains legal information which is privileged and confidential. It is for the exclusive use of the address and distribution, dissemination, copying or use by others is strictly prohibited. If you have received this communication by error, please delete the original message and e-mail us.

Esta comunicación contiene información legal privilegiada y confidencial para el uso exclusivo del destinatario. La distribución, diseminación, copia u otro uso por terceras personas es estrictamente prohibida. Si usted ha recibido esta comunicación por error, le rogamos borrar el mensaje original y comunicárnoslo a esta misma dirección.

[ariel barak]

Glad to help. I am cc'ing the list so that everyone knows that your problem has been resolved.

-Ariel Barak

On Tue, May 12, 2009 at 9:32 AM, Rodrigo Briceño <[hidden email]> wrote:

Thanks for your mail Ariel. Actually you described the problem prefectly. Some rows were repeated and then I did a procedure to identify duplicates. When I managed those repeated figures the restructuring ran perfectly!
So I'm on the track again to finish my procedure!

__________________________________________________________________

Rodrigo Briceño
Project Manager
Sanigest Internacional

[phone] ext. 113   Oficina Costa Rica
[phone]                  Fax
[hidden email]
www.sanigest.com

MSN: [hidden email]
SKYPE: rbriceno1087

_____________________

This communication contains legal information which is privileged and confidential. It is for the exclusive use of the address and distribution, dissemination, copying or use by others is strictly prohibited. If you have received this communication by error, please delete the original message and e-mail us.

Esta comunicación contiene información legal privilegiada y confidencial para el uso exclusivo del destinatario. La distribución, diseminación, copia u otro uso por terceras personas es estrictamente prohibida. Si usted ha recibido esta comunicación por error, le rogamos borrar el mensaje original y comunicárnoslo a esta misma dirección.

Ariel Barak wrote:

Hi Rodrigo,

You are on the right track with looking to restructure the data. The error you're describing is likely because you have two rows that are identical by what you want the identifying variables to be, likely the HH identifier and the produce name identifier.

For example, if you have two rows like this, you will get the error:

HH    R802A  R805A
0001  521      200
0001  521      125

So, the same family may have bought the same product twice and in your database, you have it as two rows. If you aggregate the data and break by HH, R802A, and then take the sum of R805A, you should then be able to restructure your data without the error(s) you observed. You want the row to look like:

HH    R802A  R805A_sum
0001  521      325

You could post a few lines of your data or some dummy data and what you'd like the result to look like and the list may be able to give you a more concrete solution.

Thanks,
Ariel

On Mon, May 11, 2009 at 3:10 PM, Rodrigo Briceño <[hidden email]> wrote:

Dear SPSS listers. I have a database with information about expenditure on a list of 45 products.
An identifier for each household is also provided.
The original format of my database is to have for example 45 rows for the first household that have reported consume on each of the 45 products, however the number of products consumed by households could vary between 1 and 45, and consequently varying number of rows.

The variables in my dataset are:
id: HH identifier
R802A: product name identifier
R805A: expenditure made in the product

What I need is to have each product as a column and each identifier as a row. I was trying to use the restructuring function, but some errors appeared: "The INDEX values for case 27964 have occurred before in the cases with the same ID values."

I also made a new empty database where I put ID in rows (so each line is a HH) and 45 columns (where each column is a product) but the missing part is how to put on each corresponding cell the expenditure made on each product. I was trying some kind of merge, but I have no idea about how to establish the filters to do it.

Any idea about how can I overcome this issue?

Thanks!

--

__________________________________________________________________

Rodrigo Briceño
Project Manager
Sanigest Internacional

[phone] ext. 113   Oficina Costa Rica
[phone]                  Fax
[hidden email]
www.sanigest.com

MSN: [hidden email]
SKYPE: rbriceno1087

_____________________

This communication contains legal information which is privileged and confidential. It is for the exclusive use of the address and distribution, dissemination, copying or use by others is strictly prohibited. If you have received this communication by error, please delete the original message and e-mail us.

Esta comunicación contiene información legal privilegiada y confidencial para el uso exclusivo del destinatario. La distribución, diseminación, copia u otro uso por terceras personas es estrictamente prohibida. Si usted ha recibido esta comunicación por error, le rogamos borrar el mensaje original y comunicárnoslo a esta misma dirección.