{"action":"closed","issue":{"url":"https://api.github.com/repos/iangow/wrds_pg/issues/23","repository_url":"https://api.github.com/repos/iangow/wrds_pg","labels_url":"https://api.github.com/repos/iangow/wrds_pg/issues/23/labels{/name}","comments_url":"https://api.github.com/repos/iangow/wrds_pg/issues/23/comments","events_url":"https://api.github.com/repos/iangow/wrds_pg/issues/23/events","html_url":"https://github.com/iangow/wrds_pg/issues/23","id":251521302,"number":23,"title":"Solve import issue with audit.nt.","user":{"login":"iangow","id":1708790,"avatar_url":"https://avatars1.githubusercontent.com/u/1708790?v=4","gravatar_id":"","url":"https://api.github.com/users/iangow","html_url":"https://github.com/iangow","followers_url":"https://api.github.com/users/iangow/followers","following_url":"https://api.github.com/users/iangow/following{/other_user}","gists_url":"https://api.github.com/users/iangow/gists{/gist_id}","starred_url":"https://api.github.com/users/iangow/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/iangow/subscriptions","organizations_url":"https://api.github.com/users/iangow/orgs","repos_url":"https://api.github.com/users/iangow/repos","events_url":"https://api.github.com/users/iangow/events{/privacy}","received_events_url":"https://api.github.com/users/iangow/received_events","type":"User","site_admin":false},"labels":[],"state":"closed","locked":false,"assignee":{"login":"iangow","id":1708790,"avatar_url":"https://avatars1.githubusercontent.com/u/1708790?v=4","gravatar_id":"","url":"https://api.github.com/users/iangow","html_url":"https://github.com/iangow","followers_url":"https://api.github.com/users/iangow/followers","following_url":"https://api.github.com/users/iangow/following{/other_user}","gists_url":"https://api.github.com/users/iangow/gists{/gist_id}","starred_url":"https://api.github.com/users/iangow/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/iangow/subscriptions","organizations_url":"https://api.github.com/users/iangow/orgs","repos_url":"https://api.github.com/users/iangow/repos","events_url":"https://api.github.com/users/iangow/events{/privacy}","received_events_url":"https://api.github.com/users/iangow/received_events","type":"User","site_admin":false},"assignees":[{"login":"iangow","id":1708790,"avatar_url":"https://avatars1.githubusercontent.com/u/1708790?v=4","gravatar_id":"","url":"https://api.github.com/users/iangow","html_url":"https://github.com/iangow","followers_url":"https://api.github.com/users/iangow/followers","following_url":"https://api.github.com/users/iangow/following{/other_user}","gists_url":"https://api.github.com/users/iangow/gists{/gist_id}","starred_url":"https://api.github.com/users/iangow/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/iangow/subscriptions","organizations_url":"https://api.github.com/users/iangow/orgs","repos_url":"https://api.github.com/users/iangow/repos","events_url":"https://api.github.com/users/iangow/events{/privacy}","received_events_url":"https://api.github.com/users/iangow/received_events","type":"User","site_admin":false}],"milestone":null,"comments":1,"created_at":"2017-08-20T23:12:42Z","updated_at":"2017-09-29T06:48:34Z","closed_at":"2017-09-29T06:48:34Z","author_association":"OWNER","body":"Issue encountered by @jingyujzhang in addressing issue #22.\r\n\r\n> @iangow : While download table audit.nt, I met this error. It implies that line 5945 lacks a column (`best_efgar_ticker` is the last col).\r\n> > psycopg2.DataError: missing data for column \"best_edgar_ticker\"\r\n> > CONTEXT:  COPY nt, line 5945: \"32941,2006-09-30,0,0,0,0,,,\"Fannie Mae (formally, the Federal National Mortgage Association) > has det...\"\r\n> \r\n> I then grabbed the csv file `wrds_fetch.py` downloaded from wrds. On this row (`nt_notify_key = 32941`), the code **failed to pull the last 15 cols from wrds**.   \r\n> \r\n> The affected cols are `file_size, http_name_html, http_name_text, ac_file_fkey, ac_form_fkey, ac_file_date, ac_file_accepted, ac_file_size, ac_http_name_html, ac_http_name_text, nt_reas_keys, nt_reas_titles, nt_reas_descrip, company_fkey, best_edgar_ticker`.   \r\n> \r\n> All cols after `file_size` are NA except that `company_fkey` is correct. These cols should have value.\r\n> \r\n> ```\r\n> >>> list(df.ix[5944])[21:36]  <- This is the row after the incorrect row\r\n> ['111 KB', '/Archives/edgar/data/310522/000095013307000808/0000950133-07-000808-index.htm', '/Archives/edgar/data/310522/000095013307000808/0000950133-07-000808.txt', 'edgar/data/310522/0000950133-07-003508.txt', '10-K', '2007-08-16', '16AUG2007:08:30:06', '5 MB', '/Archives/edgar/data/310522/000095013307003508/0000950133-07-003508-index.htm', '/Archives/edgar/data/310522/000095013307003508/0000950133-07-003508.txt', '|5|16|28|65|26|10|', '|Internal Control / Sarbanes Oxley (404 or 302) assessment issues|Insufficient time to prepare or review report|Restatement of financials pending|Z - Expense recording issues|Bankruptcy, litigation, contingency, non-compliance of credit agreement|Material Weakness issues with ICFR or DC preparation|', '|The delay in the filing of the periodic report is attributable to difficulties surrounding the implementation of controls and procedures required to meet disclosure requirements pursuant to section 302 or section 404 of the Sarbanes-Oxley Act of 2002.|More time is needed to complete the periodic report.|Registrant has identified a restatement possible or definite.|Registrant has identified accounting issues associated with the expenses.|Registrant identifies pending litigation, a bankruptcy, or non-compliance of debt agreement terms (indebtedness).|Registrant identifies a material weakness in internal controls over financial reporting/disclosure controls.|', 310522.0, 'FNMA']\r\n> >>> list(df.ix[5943])[21:36]  <- This is the incorrect row\r\n> ['112 KB', nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, 310522.0, nan] <- nan should have value\r\n> >>> list(df.ix[5942])[21:36]  <- This is the row before the incorrect row\r\n> ['121 KB', '/Archives/edgar/data/310522/000095013306003655/0000950133-06-003655-index.htm', '/Archives/edgar/data/310522/000095013306003655/0000950133-06-003655.txt', nan, nan, nan, nan, nan, nan, nan, '|16|28|20|41|24|26|10|', '|Insufficient time to prepare or review report|Restatement of financials pending|Investigation underway, special committee (internal, external or SEC)|Liquidation or sale of assets|Change, newly hired, turnover, reduction or resignation of personnel, management, board, legal staff|Bankruptcy, litigation, contingency, non-compliance of credit agreement|Material Weakness issues with ICFR or DC preparation|', '|More time is needed to complete the periodic report.|Registrant has identified a restatement possible or definite.|The present undertaking of an internal or external investigation has delayed filing of the periodic report.  The SEC or a special committee is often identified with the investigation.|Registrant identifies the sale and/or liquidation of assets delayed the timely completion of the periodic filing.|The recent dismissal, resignation, reduction, turnover, and/or hiring of new personnel delayed the filing of the periodic report.|Registrant identifies pending litigation, a bankruptcy, or non-compliance of debt agreement terms (indebtedness).|Registrant identifies a material weakness in internal controls over financial reporting/disclosure controls.|', 310522.0, 'FNMA']\r\n> ```\r\n> However, the query result from wrds using SAS doesn't seem to be abnormal.\r\n> \r\n> ```\r\n>    NT_NOTIFY_    FILE_\r\n>    Obs    KEY        SIZE\r\n> \r\n>   5940       3254    73 KB\r\n>   5941        541    93 KB\r\n>   5942       7239    156 KB\r\n>   5943      15264    105 KB\r\n>   5944      24141    121 KB\r\n>   5945      32941    112 KB  <- Downloaded successfully\r\n>   5946      41349    111 KB\r\n> \r\n>    Obs HTTP_NAME_HTML\r\n> \r\n>   5940 /Archives/edgar/data/310522/000095013305003614/0000950133-05-003614-index.htm\r\n>   5941 /Archives/edgar/data/310522/000095013305005102/0000950133-05-005102-index.htm\r\n>   5942 /Archives/edgar/data/310522/000095013306001175/0000950133-06-001175-index.htm\r\n>   5943 /Archives/edgar/data/310522/000095013306002340/0000950133-06-002340-index.htm\r\n>   5944 /Archives/edgar/data/310522/000095013306003655/0000950133-06-003655-index.htm\r\n>   5945 /Archives/edgar/data/310522/000095013306004772/0000950133-06-004772-index.htm  <- Missing\r\n>   5946 /Archives/edgar/data/310522/000095013307000808/0000950133-07-000808-index.htm\r\n> ```\r\n> \r\n> 1. I called `wrds_fetch.py` to download the other six tables and all tables are fine. \r\n> \r\n> 2. Extra comma is not the reason. The higly suspected two cols are `http_name_html` (the first missing col) and `best_edgar_ticker` (the following/last col after `company_fkey` which is downloaded successfully). Unfortunately, these two cols are neat and clean, with no comma or extra whitespace.  \r\n> \r\n> 3. The rows around this missing-col-row experience very similar situations like being blank for several cols, but they can be downloaded normally.  \r\n> \r\n> 4. The SAS code used to download data from wrds is listed as below. All the modification it does is to change NA to \".\". But in the original dataset, these columns are not NA, so it might not be the reason.\r\n> ```sas\r\n> options nosource nonotes;\r\n> \r\n> libname pwd '/sastemp';\r\n> \r\n> * Fix missing values;\r\n> data pwd.auditnt;\r\n>     set audit.nt(drop=matchfy: matchqu: priorfy: priorqu: );\r\n> \r\n>     * dsf_fix;\r\n> \r\n> \r\n>     * fix_cr_code;\r\n> \r\n> \r\n>     array allvars _numeric_ ;\r\n> \r\n>     do over allvars;\r\n>       if missing(allvars) then allvars = . ;\r\n>     end;\r\n> run;\r\n> \r\n> * fund_names_fix;\r\n> \r\n> \r\n> proc export data=pwd.auditnt outfile=stdout dbms=csv;\r\n> run;\r\n> ```\r\n> I can't think of other possibilities at the moment. And meanwhile, I can't bypass this problem as it is not caused by further modification, the data downloaded is incorrect. Any suggestions would be highly appreciated. Thanks."}}
{"actor":{"display_login":"iangow"}}