How to autoassign labels to IDs from the existing fields (several in one table)
Locked
How to autoassign labels to IDs from the existing fields (several in one table)
 
|
Hi All,
I have a table from database, where I have several pairs of fields consisting of field ID and field name, as shown below. A_ID A_Label B_ID B_Label C_ID C_Label 1 aaa 11 aam 12 aaaam 2 bbb 12 bbn 14 mfdnvj 3 ccc 13 fgkj 25 lfflsx Is there an automated and efficient way to apply a string value label that is found in A_Label, B_Label and C_Label to variables A_ID, B_ID and C_ID? Thank you in advance, Jekaterina ===================== 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 |
Re: How to autoassign labels to IDs from the existing fields (several in one table)
|
|
Jekaterina,
One way... * Example data. DATA LIST FREE /A_ID (F6) A_Label (A10) B_ID (F6) B_Label (A10) C_ID (F6) C_Label (A10). BEGIN DATA 1 aaa 11 aam 12 aaaam 2 bbb 12 bbn 14 mfdnvj 3 ccc 13 fgkj 25 lfflsx END DATA. DO REPEAT J = A_ID B_ID C_ID / K = A B C / L = A_Label B_Label C_Label / M = A_New B_New C_New. STRING K (A10) M (A20). COMPUTE K = STRING(J,F6). COMPUTE M = CONCAT(RTRIM(LTRIM(K)),"_",RTRIM(LTRIM(L))). END REPEAT. MATCH FILES FILE=* /DROP A B C. EXECUTE. Bob Walker Surveys & Forecasts, LLC www.safllc.com -----Original Message----- From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Jekaterina Baubkuviene Sent: Monday, October 18, 2010 2:02 PM To: [hidden email] Subject: How to autoassign labels to IDs from the existing fields (several in one table) Hi All, I have a table from database, where I have several pairs of fields consisting of field ID and field name, as shown below. A_ID A_Label B_ID B_Label C_ID C_Label 1 aaa 11 aam 12 aaaam 2 bbb 12 bbn 14 mfdnvj 3 ccc 13 fgkj 25 lfflsx Is there an automated and efficient way to apply a string value label that is found in A_Label, B_Label and C_Label to variables A_ID, B_ID and C_ID? Thank you in advance, Jekaterina ===================== 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 ===================== 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 |
Re: How to autoassign labels to IDs from the existing fields (several in one table)
|
|
In reply to this post by Jekaterina
Bob, thank you for reply.
I think I need to restate my question as my initial post wasn't very specific and could be easily misunderstood. Essentially what I need to do is similar to what VARIABLE LABEL command does (assign category labels to numeric values), but to do it automatically by taking the string value from A_label and applying it to the corresponding existing numeric value of A_id. As a result there would be a single variable with properly defined categories (Ex. 6- Asanaviciute; 4- Fabijoniskes; 7- Impuls Plius; 8- Oaze; etc.). AUTORECODE could do similar thing, but it would assign new values in order the categories appear, whereas I need to use already existing IDs from the database. In the database each unique A_id corresponds to the unique string value of A_label, B_id to B_label, C_id to C_label and so forth. The sample of my database looks like the following: DATA LIST FREE / entry_id (F6) person_id (F6) A_id (F6) A_label (A30) B_id (F6) B_label (A50) C_id (F6) C_label (A30) D_id (F6) D_label (A30) E_id (F6) E_label (A30) F_id (F6) F_label (A30). BEGIN DATA 344623 16019 6 "Asanaviciute" 1227 "1 men. 12k, studentas, vidudienis" 55 "1men. 12k" 10 "Vidudienio studentas" 10 "Studentas/moksleivis" 9 "Vilnius gr." 329052 9774 6 "Asanaviciute" 1954 "4 men. 52k, vidudienis" 17 "4men. 52k" 8 "Vidudienio" 9 "Visi" 17 "Savanoriai + Asanaviciute gr." 240129 9866 6 "Asanaviciute" 1417 "3 men. 40k, visa diena" 10 "3men. 40k" 6 "Visos dienos" 9 "Visi" 17 "Savanoriai + Asanaviciute gr." 239070 9845 6 "Asanaviciute" 1607 "6 men. 84k, vidudienis" 11 "6men. 84k" 8 "Vidudienio" 9 "Visi" 17 "Savanoriai + Asanaviciute gr." 444279 0 4 "Fabijoniskes" 944 "Vienkartinis apsilankymas, visa diena" 6 "Vienkartinis apsilankymas" 6 "Visos dienos" 9 "Visi" 7 "Fabijoniskes gr." 177752 6600 4 "Fabijoniskes" 950 "5sav, visa diena" 13 "5 savaites" 6 "Visos dienos" 9 "Visi" 7 "Fabijoniskes gr." 99064 7289 4 "Fabijoniskes" 1068 "3 men. 40k, visa diena" 10 "3men. 40k" 6 "Visos dienos" 9 "Visi" 7 "Fabijoniskes gr." 489744 6064 7 "Impuls Plius" 809 "13 men. 188k, seima 4, visa diena" 12 "13men. 188k" 6 "Visos dienos" 16 "Seima 4" 9 "Vilnius gr." 423541 16526 7 "Impuls Plius" 1871 "2 men. 25k, visa diena" 23 "2men. 25k" 6 "Visos dienos" 9 "Visi" 9 "Vilnius gr." 389577 19052 7 "Impuls Plius" 2101 "5 sav. 14k, visa diena" 9 "5sav. 14 k" 6 "Visos dienos" 9 "Visi" 9 "Vilnius gr." 369304 18374 7 "Impuls Plius" 820 "5 sav .14k vaikas + suauges, vidudienis" 9 "5sav. 14 k" 8 "Vidudienio" 13 "Suauges + vaikas" 9 "Vilnius gr." 345350 803 7 "Impuls Plius" 805 "13 men. 188k, visa diena" 12 "13men. 188k" 6 "Visos dienos" 9 "Visi" 9 "Vilnius gr." 308209 7971 7 "Impuls Plius" 782 "5 sav. 14k, visa diena" 9 "5sav. 14 k" 6 "Visos dienos" 9 "Visi" 9 "Vilnius gr." 303390 12626 7 "Impuls Plius" 1227 "1 men. 12k, studentas, vidudienis" 55 "1men. 12k" 10 "Vidudienio studentas" 10 "Studentas/moksleivis" 9 "Vilnius gr." 301682 13650 7 "Impuls Plius" 1880 "4 men. 52k, visa diena" 17 "4men. 52k" 6 "Visos dienos" 9 "Visi" 9 "Vilnius gr." 174436 8434 7 "Impuls Plius" 782 "5 sav. 14k, visa diena" 9 "5sav. 14 k" 6 "Visos dienos" 9 "Visi" 9 "Vilnius gr." 171337 2603 7 "Impuls Plius" 817 "1 men. 10k, senjoras, vidudienis" 20 "1men. 10k" 8 "Vidudienio" 11 "Senjoras" 9 "Vilnius gr." 149549 1349 7 "Impuls Plius" 797 "6 men. 84k, visa diena" 11 "6men. 84k" 6 "Visos dienos" 9 "Visi" 9 "Vilnius gr." 144593 1172 7 "Impuls Plius" 817 "1 men. 10k, senjoras, vidudienis" 20 "1men. 10k" 8 "Vidudienio" 11 "Senjoras" 9 "Vilnius gr." 132881 839 7 "Impuls Plius" 782 "5 sav. 14k, visa diena" 9 "5sav. 14 k" 6 "Visos dienos" 9 "Visi" 9 "Vilnius gr." 132335 822 7 "Impuls Plius" 782 "5 sav. 14k, visa diena" 9 "5sav. 14 k" 6 "Visos dienos" 9 "Visi" 9 "Vilnius gr." 404441 20399 8 "Oaze" 2208 "13 menesiu, visa diena" 16 "13 menesiu" 6 "Visos dienos" 9 "Visi" 11 "Oaze gr." 390369 3621 3 "Ozo" 1789 "4 men. 52k, vidudienis" 17 "4men. 52k" 8 "Vidudienio" 9 "Visi" 5 "Ozas + Fabijoniskes gr." 297693 0 3 "Ozo" 2076 "Atviru duru diena" 6 "Vienkartinis apsilankymas" 6 "Visos dienos" 9 "Visi" 9 "Vilnius gr." 275839 3035 3 "Ozo" 379 "5 sav. 14k, visa diena" 9 "5sav. 14 k" 6 "Visos dienos" 9 "Visi" 5 "Ozas + Fabijoniskes gr." 272969 5125 3 "Ozo" 376 "5 sav. 14k, visa diena" 9 "5sav. 14 k" 8 "Vidudienio" 9 "Visi" 5 "Ozas + Fabijoniskes gr." 211992 3244 3 "Ozo" 387 "3 men. 40k, vidudienis" 10 "3men. 40k" 8 "Vidudienio" 9 "Visi" 5 "Ozas + Fabijoniskes gr." 186271 7462 3 "Ozo" 381 "5 sav .14k, seima 2, visa diena" 9 "5sav. 14 k" 6 "Visos dienos" 14 "Seima 2" 5 "Ozas + Fabijoniskes gr." 103574 3052 3 "Ozo" 421 "6 menesiai, visa diena" 15 "6 menesiu" 6 "Visos dienos" 9 "Visi" 6 "Ozas gr." 93718 0 3 "Ozo" 122 "Vienkartinis apsilankymas, vidudienis" 6 "Vienkartinis apsilankymas" 8 "Vidudienio" 9 "Visi" 5 "Ozas + Fabijoniskes gr." 435755 22358 10 "Panevezys" 2402 "13 men. 188k, vidudienis" 12 "13men. 188k" 8 "Vidudienio" 9 "Visi" 18 "Siauliai + Panevezys. gr" 305857 11318 5 "Savanoriai" 1541 "13 menesiu, visa diena" 16 "13 menesiu" 6 "Visos dienos" 9 "Visi" 16 "Savanoriai gr." 505886 26260 9 "Siauliai" 2434 "1 men. 12k, vidudienis" 57 "1men. 12k" 8 "Vidudienio" 9 "Visi" 18 "Siauliai + Panevezys. gr" 474486 24365 9 "Siauliai" 2403 "13 men. 188k, visa diena" 12 "13men. 188k" 6 "Visos dienos" 9 "Visi" 18 "Siauliai + Panevezys. gr" 468544 22063 9 "Siauliai" 2402 "13 men. 188k, vidudienis" 12 "13men. 188k" 8 "Vidudienio" 9 "Visi" 18 "Siauliai + Panevezys. gr" END DATA. I need to transform all the data in this way except first two variables (entry_id and person_id). Any help would be greatly appreciated. Regards, Jekaterina Baubkuviene Mob. +370 685 16933 Insight Solutions, Ltd WWW: www.insol.lt -----Original Message----- From: Bob Walker [mailto:[hidden email]] Sent: Tuesday, October 19, 2010 12:21 AM To: [hidden email] Cc: Jekaterina Baubkuvienė Subject: RE: How to autoassign labels to IDs from the existing fields (several in one table) Jekaterina, One way... * Example data. DATA LIST FREE /A_ID (F6) A_Label (A10) B_ID (F6) B_Label (A10) C_ID (F6) C_Label (A10). BEGIN DATA 1 aaa 11 aam 12 aaaam 2 bbb 12 bbn 14 mfdnvj 3 ccc 13 fgkj 25 lfflsx END DATA. DO REPEAT J = A_ID B_ID C_ID / K = A B C / L = A_Label B_Label C_Label / M = A_New B_New C_New. STRING K (A10) M (A20). COMPUTE K = STRING(J,F6). COMPUTE M = CONCAT(RTRIM(LTRIM(K)),"_",RTRIM(LTRIM(L))). END REPEAT. MATCH FILES FILE=* /DROP A B C. EXECUTE. Bob Walker Surveys & Forecasts, LLC www.safllc.com -----Original Message----- From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Jekaterina Baubkuviene Sent: Monday, October 18, 2010 2:02 PM To: [hidden email] Subject: How to autoassign labels to IDs from the existing fields (several in one table) Hi All, I have a table from database, where I have several pairs of fields consisting of field ID and field name, as shown below. A_ID A_Label B_ID B_Label C_ID C_Label 1 aaa 11 aam 12 aaaam 2 bbb 12 bbn 14 mfdnvj 3 ccc 13 fgkj 25 lfflsx Is there an automated and efficient way to apply a string value label that is found in A_Label, B_Label and C_Label to variables A_ID, B_ID and C_ID? Thank you in advance, Jekaterina ===================== 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 ===================== 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 |
Re: How to autoassign labels to IDs from the existing fields (several in one table)
|
|
In reply to this post by Jekaterina
Value labels can be generated from data
using programmability and a function in the spssaux2.py module available
from Developer Central (www.spss.com/devcentral).
Here is an example. I only listed a few of the variable pairs. begin program. # for each pair of variables, generate value labels for the first # variable in the pair from the values in the second import spss, spssaux, spssaux2 vardict = spssaux.VariableDict() pairs = [('pad_id','pad_pavadinimas'),('pard_padal_id', 'pard_padal_pavad'), ('nar_id','nar_pavadinimas')] for target, source in pairs: spssaux2.genValueLabels(target, source, vardict) end program. To use this, you will need in addition to the Python plugin, the spssaux2.py module. You also need to replace the spssaux.py module installed by the plugin with a newer one from Developer Central (there was a bug in handling extended characters in value labels). The spssaux.py module should be saved in /python26/lib/site-packages/spssaux (or python25 in older versions). The spssaux2.py module can be saved in site-packages or in the extensions subdirectory of your Statistics installation. HTH, Jon Peck Senior Software Engineer, IBM [hidden email] 312-651-3435 From: Jekaterina Baubkuvienė <[hidden email]> To: Jon K Peck/Chicago/IBM@IBMUS Date: 10/18/2010 11:55 PM Subject: RE: [SPSSX-L] How to autoassign labels to IDs from the existing fields (several in one table) Hi Jon, Thank you for reply. I'm not sure whether APPLY DICTIONARY would help in this case, so I will try to explain the objective in more detail. Essentially what I need to do is similar to what VARIABLE LABEL command does (assign labels to values), but to do it automatically by taking the string value from A_Label and applying it to the corresponding existing numeric value of A_ID. As a result there would be a single variable with properly defined categories (Ex. 1-'milk'; 2-'bread'; 3-'butter'; 4-'ham'; etc.). AUTORECODE will do similar thing, but it will assign new values in order the categories appear, whereas I need to use already existing IDs from the database. In the database each unique A_ID corresponds to the unique string value of A_Label, B_ID to B_Label, C_ID to C_Label and so forth. I'm attaching a sample of the database for your reference. Below is the list of pairs of variables (A_ID and A_Label) that I need to use. pad_id & pad_pavadinimas pard_padal_id & pard_padal_pavad prek_id & prek_pavadinimas nar_id & nar_pavadinimas pasl_id & pasl_pavadinimas laik_id & laik_pavadinimas truk_id & truk_pavadinimas tip_id & tip_pavadinimas pg_id & pg_pavadinimas Please let me know whether APPLY DICTIONARY or any other syntax could help to solve this objective? Thank you in advance, Jekaterina Baubkuviene Insight Solutions www.insol.lt +37038516933 ________________________________________ From: Jon K Peck [[hidden email]] Sent: Monday, October 18, 2010 11:10 PM To: Jekaterina Baubkuvienė Subject: Re: [SPSSX-L] How to autoassign labels to IDs from the existing fields (several in one table) Take a look at APPLY DICTIONARY. It can copied selected variable properties from one or more variables to others. Jon Peck Senior Software Engineer, IBM [hidden email] 312-651-3435 From: Jekaterina Baubkuvienė <[hidden email]> To: [hidden email] Date: 10/18/2010 01:50 PM Subject: [SPSSX-L] How to autoassign labels to IDs from the existing fields (several in one table) Sent by: "SPSSX(r) Discussion" <[hidden email]> ________________________________ Hi All, I have a table from database, where I have several pairs of fields consisting of field ID and field name, as shown below. A_ID A_Label B_ID B_Label C_ID C_Label 1 aaa 11 aam 12 aaaam 2 bbb 12 bbn 14 mfdnvj 3 ccc 13 fgkj 25 lfflsx Is there an automated and efficient way to apply a string value label that is found in A_Label, B_Label and C_Label to variables A_ID, B_ID and C_ID? Thank you in advance, Jekaterina ===================== 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 [attachment "Autolabeling_sample.sav" deleted by Jon K Peck/Chicago/IBM] |
Re: How to autoassign labels to IDs from the existing fields (several in one table)
|
|
Looks like ‘namedtuple.py’ (called from ‘spssdata.py’) also needs to be installed, otherwise an error is generated… Bob Walker Surveys & Forecasts, LLC From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Jon K Peck Value labels can be generated from data using programmability and a function in the spssaux2.py module available from Developer Central (www.spss.com/devcentral).
|
Re: How to autoassign labels to IDs from the existing fields (several in one table)
|
|
namedtuple.py is included in the Python
plugin or Essentials installer except in very old versions (spssaux.py
and spssdata.py are also included in the installer except for those older
versions).
Regards, Jon Peck Senior Software Engineer, IBM [hidden email] 312-651-3435 From: Bob Walker <[hidden email]> To: "[hidden email]" <[hidden email]> Cc: Jon K Peck/Chicago/IBM@IBMUS Date: 10/19/2010 11:06 AM Subject: RE: How to autoassign labels to IDs from the existing fields (several in one table) Looks like ‘namedtuple.py’ (called from ‘spssdata.py’) also needs to be installed, otherwise an error is generated… Bob Walker Surveys & Forecasts, LLC www.safllc.com From: SPSSX(r) Discussion [[hidden email]] On Behalf Of Jon K Peck Sent: Tuesday, October 19, 2010 10:06 AM To: [hidden email] Subject: Re: How to autoassign labels to IDs from the existing fields (several in one table) Value labels can be generated from data using programmability and a function in the spssaux2.py module available from Developer Central (www.spss.com/devcentral). Here is an example. I only listed a few of the variable pairs. begin program. # for each pair of variables, generate value labels for the first # variable in the pair from the values in the second import spss, spssaux, spssaux2 vardict = spssaux.VariableDict() pairs = [('pad_id','pad_pavadinimas'),('pard_padal_id', 'pard_padal_pavad'), ('nar_id','nar_pavadinimas')] for target, source in pairs: spssaux2.genValueLabels(target, source, vardict) end program. To use this, you will need in addition to the Python plugin, the spssaux2.py module. You also need to replace the spssaux.py module installed by the plugin with a newer one from Developer Central (there was a bug in handling extended characters in value labels). The spssaux.py module should be saved in /python26/lib/site-packages/spssaux (or python25 in older versions). The spssaux2.py module can be saved in site-packages or in the extensions subdirectory of your Statistics installation. HTH, Jon Peck Senior Software Engineer, IBM peck@... 312-651-3435 From: Jekaterina Baubkuvienė <jekaterina@...> To: Jon K Peck/Chicago/IBM@IBMUS Date: 10/18/2010 11:55 PM Subject: RE: [SPSSX-L] How to autoassign labels to IDs from the existing fields (several in one table) Hi Jon, Thank you for reply. I'm not sure whether APPLY DICTIONARY would help in this case, so I will try to explain the objective in more detail. Essentially what I need to do is similar to what VARIABLE LABEL command does (assign labels to values), but to do it automatically by taking the string value from A_Label and applying it to the corresponding existing numeric value of A_ID. As a result there would be a single variable with properly defined categories (Ex. 1-'milk'; 2-'bread'; 3-'butter'; 4-'ham'; etc.). AUTORECODE will do similar thing, but it will assign new values in order the categories appear, whereas I need to use already existing IDs from the database. In the database each unique A_ID corresponds to the unique string value of A_Label, B_ID to B_Label, C_ID to C_Label and so forth. I'm attaching a sample of the database for your reference. Below is the list of pairs of variables (A_ID and A_Label) that I need to use. pad_id & pad_pavadinimas pard_padal_id & pard_padal_pavad prek_id & prek_pavadinimas nar_id & nar_pavadinimas pasl_id & pasl_pavadinimas laik_id & laik_pavadinimas truk_id & truk_pavadinimas tip_id & tip_pavadinimas pg_id & pg_pavadinimas Please let me know whether APPLY DICTIONARY or any other syntax could help to solve this objective? Thank you in advance, Jekaterina Baubkuviene Insight Solutions www.insol.lt +37038516933 ________________________________________ From: Jon K Peck [[hidden email]] Sent: Monday, October 18, 2010 11:10 PM To: Jekaterina Baubkuvienė Subject: Re: [SPSSX-L] How to autoassign labels to IDs from the existing fields (several in one table) Take a look at APPLY DICTIONARY. It can copied selected variable properties from one or more variables to others. Jon Peck Senior Software Engineer, IBM peck@... 312-651-3435 From: Jekaterina Baubkuvienė <jekaterina@...> To: [hidden email] Date: 10/18/2010 01:50 PM Subject: [SPSSX-L] How to autoassign labels to IDs from the existing fields (several in one table) Sent by: "SPSSX(r) Discussion" <[hidden email]> ________________________________ Hi All, I have a table from database, where I have several pairs of fields consisting of field ID and field name, as shown below. A_ID A_Label B_ID B_Label C_ID C_Label 1 aaa 11 aam 12 aaaam 2 bbb 12 bbn 14 mfdnvj 3 ccc 13 fgkj 25 lfflsx Is there an automated and efficient way to apply a string value label that is found in A_Label, B_Label and C_Label to variables A_ID, B_ID and C_ID? Thank you in advance, Jekaterina ===================== To manage your subscription to SPSSX-L, send a message to LISTSERV@... (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 [attachment "Autolabeling_sample.sav" deleted by Jon K Peck/Chicago/IBM] |
Re: How to autoassign labels to IDs from the existing fields (several in one table)
|
In reply to this post by Jekaterina
Sorry to be a spoil-sport, but it would be a lot
easier if you started with syntax in the first place.
|
«
Return to SPSSX Discussion
|
1 view|%1 views
| Free forum by Nabble | Edit this page |