How to Extract Columns from a CSV file (without using a spreadsheet)

One of my colleagues who does data analysis had a CSV (comma-separated values) file that he could not open in Excel nor in any other spreadsheet program he tried.

Either due to its sheer filesize of 125MB, or its number of rows of more than 1,500,000, the programs would gag.

It turned out that for his purposes, he did not need all of the data, only a subset of the columns. Maybe extracting only what he needed into a smaller CSV would enable him to be able to work with it.

I had earlier read parts of the book The Linux Command Line, by William Shotts. I vaguely remembered mention of a utility to selectively pull fields out of a text file. That turned out to be the cut command.

Here's what the first few rows of the original data file looked like.

id,type,distance,userid,charityID,time,lat,lon
1003529743,walk,4.48342,1000545086,2166731,"2015-06-30 00:00:05",40.2501,-76.6714
1003529744,Run,4.087,1000402641,15048,"2015-06-30 00:00:21",45.5244,-89.7398
1003529745,run,2.631135,1000258381,61635018,"2015-06-30 00:00:23",41.6281,-87.193
1003529746,Bike,1.216703,1000505816,18010,"2015-06-30 00:00:24",43.0306,-78.7963
1003529747,walk,2.069664,1000015957,18010,"2015-06-30 00:00:25",39.2481,-76.5165
1003529748,Bike,6.174,1000126350,18010,"2015-06-30 00:00:25",29.5913,-82.4298
1003529749,run,1.47652,1000542869,92985044,"2015-06-30 00:00:26",40.7115,-89.4287

Only the type and userid columns were actually required. Using the following command I was able to generate another CSV with just those two fields.

$ cut -f 2,4 -d, july.csv > type-userid.csv

The -f option specifies which fields to extract. In this case, its the 2nd and the 4th fields. The -d option is the field delimiting character, which in our case is the comma. It defaults to tabs.

july.csv is the input file, type-userid.csv captures the standard-out.

The first few rows of the resulting file were

type,userid
walk,1000545086
Run,1000402641
run,1000258381
Bike,1000505816
walk,1000015957
Bike,1000126350
run,1000542869

And the filesize was reduced to about 24MB, which was usable and much more manageable.

(However, depending on which spreadsheet app you are using, the row count might exceed the limit. For example, both Excel 2007 and LibreOffice 4.2 Calc can handle a maximum of 1,048,576 rows.)

  ***

The cut command works blazingly fast. It only took about a second to process the input file. After I handed off the outputted  file, I later realized that I might have been able to save my colleague some tedium and waiting time by applying other CLI text-processing commands as well, such as sort, uniq, and wc, depending on what he wanted to do.

Source:

The Linux Command Line, by William Shotts
http://linuxcommand.org/tlcl.php
(You can buy the paper book or download the pdf for free)

Drupal 8, Beta 15 --> RC 1: Eliminating use of checkPlain( )

Since SafeMarkup::checkPlain() is now deprecated and possibly intended to be removed along with other SafeMarkup methods, I decided to address this in migrating to RC 1.

In the common use case where D8's Twig templates are used for output, you can dispense with checkPlain() entirely and rely on Twig's autoescaping. That turned out to apply to our simple use of forms to input and render a few text values.

In the original code, checkPlain() was called both to process user-entered values as well as to sanitize those values before they were placed into render arrays. When I typed <script> into a form field, it was incorrectly escaped twice and displayed as   &lt;script&gt;

Removing those calls works fine and conforms to what is considered good practice for D8. Potentially unsafe markup is stored as is in the database, but Twig converts it before it is sent to the browser.

The article  SafeMarkup methods are removed  is extremely useful in how it breaks down the different use cases for checkPlain() and what needs to be done differently for each in D8 in order to prevent unsafe markup from being rendered.

Besides Twig templates, the other use cases are:

-  Text placed into a render array by using the #plain_text key
-  A mixture of escaped markup with markup not to be escaped
-  Non-HTML responses, eg. JSON

The discussion also applies to the check_plain() function in Drupal 7, for which checkPlain() was a replacement. If you're starting out with a conversion from D7, the article is a must read.

Sources:

SafeMarkup::set(), SafeMarkup::checkPlain(), and other methods are removed from Drupal 8 core
https://groups.drupal.org/node/478558

SafeMarkup methods are removed
https://www.drupal.org/node/2549395

Twig autoescape enabled and text sanitization APIs updated
https://www.drupal.org/node/2296163

Module: "Configuration inspector for Drupal 8"

In my recent post on Missing langcode in configuration schema I discovered that the langcode key in configuration schema is now required by default by Testing module automated tests.

The test results had error messages that said "Uncaught PHP Exception Drupal\Core\Config\Schema\SchemaIncompleteException" and "langcode missing schema".

As a result of that debugging, I got interested in a module called Configuration Inspector for Drupal 8.

Installing the module adds a new tab named Inspect to the page at  
/admin/config/development/configuration

That tab shows all of the Configuration Keys on the site, to which core contributes quite a few.

For the settings for our module, after I removed the langcode key from the .schema.yml file, the tab showed that the configuration key had 1 error. Going to the Raw Data page for the key then showed under Configuration Validation,

array ( 
  'optimizely.settings:langcode' => 'missing schema',
)

This is the gist of what the exceptions had said in the results from running the automated tests, but if I had used this config inspector early on to validate the schema, I would have spotted the error sooner rather than after the testing failed.

     * * *

The inspector also shows the types and the values of individual items. In our case, this includes a project id number whose value had been submitted through a form and stored programmatically by using the Simple Configuration API.

There was no entry shown for the langcode item, which is defined but had no value. I was able to provide one by adding code in hook_install(), again, by using the Simple Configuration API.

Sources:

Configuration inspector for Drupal 8
https://www.drupal.org/project/config_inspector

Configuration API in Drupal 8
https://www.drupal.org/node/1667894

Configuration schema/metadata
https://www.drupal.org/node/1905070
 

Drupal 8, Beta 14 --> Beta 15: Missing langcode in configuration schema

In migrating from Drupal 8 beta 14 to beta 15, the functionality of the module itself worked fine, but some of the automated tests were failing with error messages that included the following.

Uncaught PHP Exception Drupal\Core\Config\Schema\SchemaIncompleteException: "Schema errors for optimizely.settings with the following errors: optimizely.settings:langcode missing schema" at /var/www/html/opti/core/lib/Drupal/Core/Config/Testing/ConfigSchemaChecker.php line 98

In this message, optimizely.settings is the name of a group of configuration settings. It is also a configuration key in a .schema.yml file that is required for automated testing, which I blogged about earlier at Beta 3 --> Beta 4:  Configuration schema and metadata.

The article Fix config schema mentioned a very similar error message and stated "Because of a recent core change all tests are failing". I looked at the patches in that article, but I could not figure out what needed to be added in our case.

Then a search through the core code for the string "langcode:" led me to add langcode: as a key to the .schema.yml file as in the following.

optimizely.settings:
  type: mapping
  label: 'Optimizely Config Data'
  mapping:
    optimizely_id:
      type: integer
      label: 'Optimizely ID Number'
      translatable: false
    langcode:
      type: string
      label: 'Language code'

Problem solved. All automated tests passed after this change.

Update:  Also see my later post on  Module: Configuration Inspector for Drupal 8.

Sources:

Beta 3 --> Beta 4: Configuration schema and metadata
http://optimizely-to-drupal-8.blogspot.com/2014/12/beta-3-beta-4-configuration-schema-and.html

Fix config schema
https://www.drupal.org/node/2547365

All TestBase derived tests now enforce strict configuration schema adherence by default
https://www.drupal.org/node/2391795

Configuration schema/metadata
https://www.drupal.org/node/1905070

"Notice: Undefined index: und in eval() (line . . . . . /modules/php/php.module(80) : eval()'d code)"

On my local instance of a Drupal 7 site, I'd get warnings like the following on almost every page for a custom content type called Poem.

Notice: Undefined index: und in eval() (line 12 of /var/www/html/power-poetry/modules/php/php.module(80) : eval()'d code).

Although most probably innocuous, these warnings were also being logged numerous times in the system log, cluttering it up and making it harder to spot other, significant messages. It was enough of an annoyance that I decided to fix it.

I was able  to track this to a snippet of PHP code that is used in a Drupal block of ours. The code is part of the block's Visibility Settings. It determines if the current Poem being rendered satisfies certain criteria or not. If so, the block is made visible.

The offending line turned out to be

  $slam_id = $current_slam['und'][0]['target_id'];

The variable $current_slam was often an empty array. So by replacing the line with an additional check, the PHP warnings disappeared.

  if (!empty($current_slam)) {
    $slam_id = $current_slam['und'][0]['target_id'];
  }

The key to finding this quickly was remembering that we have this user-supplied snippet of code that needs to be evaluated at runtime. 

  - - - - -

While debugging, I wanted to see the type and the value of the variable $current_slam. The following worked to output it to the system log.

  watchdog('debug', print_r($current_slam, TRUE), array(),
           WATCHDOG_NOTICE);

The second parameter to print_r() determines what kind of return value the function passes back. By default, print_r() returns its status. But by setting the second param to TRUE, the return value is the output string instead.

  - - - - -

The site is hosted on Pantheon, which provides this status message:

• PHP Filter: PHP Filter is enabled! Executable code should never be stored in the database, and support for this feature was removed in Drupal 8 - https://drupal.org/node/1203886
  Remove all executable code from your content and move it to your codebase.

Source:

Remove the PHP module from Drupal core
https://drupal.org/node/1203886