Delete CTABLE rows with Python
Locked
Delete CTABLE rows with Python
 
|
* Hello, SPSS friends, I have a question about post modifying custom tables with Python: Say in the viewer window I have a table with Rows that are superfluous and I want to delete these rows; I could not find a suitable method in the reference guide but maybe I'm missing something. * Here's a background for this which might help:. * ========================================================. DATA LIST FREE (' ') / bought1 bought2. BEGIN DATA 1 1 1 0 1 1 1 0 9 1 END DATA. VARIABLE LABELS bought1 "Bought product 1" / bought2 "Bought product 2". VALUE LABELS bought1 bought2 0 'No' 1 'Yes' 9 'Question not received'. MISSING VALUES bought1 bought2 (9). MRSETS /MDGROUP NAME=$bought CATEGORYLABELS=VARLABELS VARIABLES=bought1 bought2 VALUE=1 /DISPLAY NAME=[$bought]. CTABLES /TABLE $bought [COUNT F40.0, COLPCT.VALIDN PCT40.1] /TITLES TITLE = 'Table 1: Wrong col% with MR CTABLES'. CTABLES /TABLE (bought1 + bought2) [COUNT F40.0, COLPCT.VALIDN PCT40.1] /TITLES TITLE = 'Table 2: Correct col% with stacked CTABLES'. * ========================================================. * In table 1 bought1 gets 80% "yes" answers as four out of 5 respondents bought that product. * However, I want to have 100% as all respondents who received this question answered "yes". * So I have to take table 2. * But table 2 has redundant information which disturbs transfer to powerpoint. * So I want to delete rows with 'no' from the output and export the result to xlsx. * OMS does not work for xlsx export and OUTPUT EXPORT does not allow row deletion. * Therefore deleting the rows with Python seems to be my last resort. * Thanks for any hint, Mario . |
|
|
Actually, since V21, OMS does support xlsx export (xls was added in V17). The Python scripting apis do not provide a delete method for pivot tables, unfortunately, but it is possible to hide rows or columns. Here is an example of using SPSSINC MODIFY TABLES to hide the "No" rows. SPSSINC MODIFY TABLES subtype="customtable" SELECT="No" DIMENSION= ROWS LEVEL = -1 HIDE=TRUE. On Wed, May 18, 2016 at 8:08 AM, Mario Giesel <[hidden email]> wrote:
|
Re: Delete CTABLE rows with Python
|
|
Hi, Jon, I tested both about a year ago and they did not work. Reason: Hide does not delete and xlsx resurrects the hidden row. Did it change meanwhile? I think this would be a good addendum to custom tables functionality. Seems a bit inconvenient to postcorrect a table that is needed often this way. Thx, Mario Jon Peck <[hidden email]> schrieb am 16:19 Mittwoch, 18.Mai 2016: Actually, since V21, OMS does support xlsx export (xls was added in V17). The Python scripting apis do not provide a delete method for pivot tables, unfortunately, but it is possible to hide rows or columns. Here is an example of using SPSSINC MODIFY TABLES to hide the "No" rows. SPSSINC MODIFY TABLES subtype="customtable" SELECT="No" DIMENSION= ROWS LEVEL = -1 HIDE=TRUE. On Wed, May 18, 2016 at 8:08 AM, Mario Giesel <[hidden email]> wrote:
|
|
|
I tested this syntax in V23 (with fixpack) and V24, and the hidden rows did not appear in the Excel file. I don't have any older versions. OMS only captures entire items, so filtering the table would not apply. The reason that this table type is not supported in CTABLES is because of transparency. The design attempts to show the basis for the calculations. With regard to the second version of the table, that would mean showing both the zeros and ones. However, for the MRSETS version, I thought we had a statistic for case counts in MC percentages, but apparently we don't. I agree that it would be useful. However, if you define your data as a multiple response set rather than multiple categories, you can get the table you want directly. For example, DATA LIST fixed / bought1 bought2(2a2). BEGIN DATA P1P2 P1 P1P2 P1 P1P2 END DATA. MRSETS /MCGROUP NAME=$MR VARIABLES=bought1 bought2 /DISPLAY NAME=[$MR]. CTABLES /TABLE $mr [C][COUNT COLPCT.COUNT] /CATEGORIES VARIABLES=$mr ["P1" "P2"]. A bit roundabout, but it works. If you also need the data as an mcset, the STATS MCSET CONVERT extension command can convert from MR to MC. On Wed, May 18, 2016 at 9:01 AM, Mario Giesel <[hidden email]> wrote:
|
Re: Delete CTABLE rows with Python
|
|
Hi, Jon, I tried SPSSINC MODIFY TABLES at first and ideed it worked with OUTPUT EXPORT. That's good. Now in an earlier post you mentioned there are html document which explain the syntax options. "The syntax help for all of our extension commands has been rewritten for display as html in a browser window, and it will contain these corrections, but the new versions have not yet been posted. " I could not find them on the IBM Website - where are they? There must be a syntax chart etc. on this command somewhere?! It's not included in the syntax reference. Btw. I could not get STATS MCSET CONVERT working but it seems to convert from MC to MD which is not what I need. Thanks a lot, Mario Jon Peck <[hidden email]> schrieb am 17:36 Mittwoch, 18.Mai 2016: I tested this syntax in V23 (with fixpack) and V24, and the hidden rows did not appear in the Excel file. I don't have any older versions. OMS only captures entire items, so filtering the table would not apply. The reason that this table type is not supported in CTABLES is because of transparency. The design attempts to show the basis for the calculations. With regard to the second version of the table, that would mean showing both the zeros and ones. However, for the MRSETS version, I thought we had a statistic for case counts in MC percentages, but apparently we don't. I agree that it would be useful. However, if you define your data as a multiple response set rather than multiple categories, you can get the table you want directly. For example, DATA LIST fixed / bought1 bought2(2a2). BEGIN DATA P1P2 P1 P1P2 P1 P1P2 END DATA. MRSETS /MCGROUP NAME=$MR VARIABLES=bought1 bought2 /DISPLAY NAME=[$MR]. CTABLES /TABLE $mr [C][COUNT COLPCT.COUNT] /CATEGORIES VARIABLES=$mr ["P1" "P2"]. A bit roundabout, but it works. If you also need the data as an mcset, the STATS MCSET CONVERT extension command can convert from MR to MC. On Wed, May 18, 2016 at 9:01 AM, Mario Giesel <[hidden email]> wrote:
|
|
|
Regarding, "The syntax help for all of our extension commands has been rewritten for display as html in a browser window", the help for extension commands is not part of the regular help system. You access it by pressing F1 in the syntax editor with the cursor in the command or by running, e.g., SPSSINC MODIFY TABLES /HELP. That will open a browser on the help file. The F1 option works with Statistics 23 or later. There were some changes in FireFox behavior after the F1 option was implemented that broke this behavior with that browser in particular in V23. I worked around that in V24, but if the help does not appear, you can bring it up another way. When an extension is installed, a subdirectory with its name is created, and the html help is placed there. So, if, say the extension was installed in directory x, you can open it in your browser at the url, using modify tables as the example, file:///x/SPSSINC_MODIFY_TABLES/SPSSINC_MODIFY_TABLES.html If you are not sure where the extension was installed, running SHOW EXTPATH will list the possible locations. Regarding STATS MCSET CONVERT, you are right that it goes from MR to MC structure. I mentioned it because if your input data were restructured into MR format, you could create the MC equivalent if needed for other purposes by using that command. On Thu, May 19, 2016 at 6:56 AM, Mario Giesel <[hidden email]> wrote:
|
Re: Delete CTABLE rows with Python
|
|
Thanks, Jon - this is VERY helpful!
=====================
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
Good luck, Mario Jon Peck <[hidden email]> schrieb am 15:54 Donnerstag, 19.Mai 2016: Regarding, "The syntax help for all of our extension commands has been rewritten for display as html in a browser window", the help for extension commands is not part of the regular help system. You access it by pressing F1 in the syntax editor with the cursor in the command or by running, e.g., SPSSINC MODIFY TABLES /HELP. That will open a browser on the help file. The F1 option works with Statistics 23 or later. There were some changes in FireFox behavior after the F1 option was implemented that broke this behavior with that browser in particular in V23. I worked around that in V24, but if the help does not appear, you can bring it up another way. When an extension is installed, a subdirectory with its name is created, and the html help is placed there. So, if, say the extension was installed in directory x, you can open it in your browser at the url, using modify tables as the example, file:///x/SPSSINC_MODIFY_TABLES/SPSSINC_MODIFY_TABLES.html If you are not sure where the extension was installed, running SHOW EXTPATH will list the possible locations. Regarding STATS MCSET CONVERT, you are right that it goes from MR to MC structure. I mentioned it because if your input data were restructured into MR format, you could create the MC equivalent if needed for other purposes by using that command. On Thu, May 19, 2016 at 6:56 AM, Mario Giesel <[hidden email]> wrote:
|
«
Return to SPSSX Discussion
|
1 view|%1 views
| Free forum by Nabble | Edit this page |