Creating New Columns

In any VisiData sheet, you can create new columns based on pre-existing columns. These are three of the most useful ways:

  • Expressions (like functions in Excel, etc.)
  • Splits (breaking up one text column into several)
  • Captures (extracting one chunk of a text column)

How to create an expression column

Expression columns evaluate a given Python expression for every row in your dataset.

These expressions can reference any column in your dataset (so long as the column name contains only letters, underscores, and numbers, and doesn’t start with a number).

Note

If you’re unfamiliar with Python, no worries. You can find an overview of simple and handy expressions here.

You can create an expression column by pressing =. Once you do so, you’ll see a prompt at the bottom of the screen. Then, type your desired expression and press Enter.

Perhaps the simplest expression column would be =1; for every row, the column’s value would be 1. Let’s see how that looks:

 OPERATOR           | 1   | ATYPE          | INCIDENT_DATE      | STATE   | AIRPORT            | P> 
 BUSINESS           | 1  #| PA-28          | 05/22/15 00:00:00  | FL      | VERO BEACH MUNICIP…| A… 
 BUSINESS           | 1  #| BE-1900        | 06/18/15 00:00:00  | AK      | KENAI MUNICIPAL AR…| A… 
 BUSINESS           | 1  #| PA-46 MALIBU   | 09/20/15 00:00:00  | TX      | DAVID WAYNE HOOKS …|    
 DELTA AIR LINES    | 1  #| B-717-200      | 11/07/15 00:00:00  | MO      | LAMBERT-ST LOUIS I…| A… 
 BUSINESS           | 1  #| BE-90 KING     | 12/17/15 00:00:00  | FL      | POMPANO BEACH AIRP…| L… 
 DELTA AIR LINES    | 1  #| B-757          | 07/17/15 00:00:00  | VI      | HENRY E ROHLSEN AR…|    
 DELTA AIR LINES    | 1  #| B-717-200      | 08/02/15 00:00:00  | TX      | SAN ANTONIO INTL   | A… 
 BUSINESS           | 1  #| C-414          | 08/03/15 00:00:00  | TX      | LONE STAR EXECUTIV…| D… 
 ALLEGIANT AIR      | 1  #| MD-80          | 09/02/15 00:00:00  | FL      | TAMPA INTL         | A… 
 TRANS STATES AIRLI…| 1  #| EMB-145        | 09/07/15 00:00:00  | MO      | LAMBERT-ST LOUIS I…| A… 
 BUSINESS           | 1  #| C-172          | 11/28/15 00:00:00  | FL      | OPA-LOCKA EXECUTIV…| A… 
 GOVERNMENT         | 1  #| EC120          | 12/08/15 00:00:00  | CA      | NORMAN Y. MINETA S…|    
 AMERICAN AIRLINES  | 1  #| A-321          | 05/06/15 00:00:00  | FL      | FORT LAUDERDALE/HO…| A… 
 EXPRESSJET AIRLINES| 1  #| CRJ100/200     | 05/06/15 00:00:00  | AR      | FORT SMITH REGIONA…| C… 
 MESA AIRLINES      | 1  #| CRJ900         | 05/08/15 00:00:00  | AR      | BILL AND  HILLARY …| L… 
 BUSINESS           | 1  #| HELICOPTER     | 05/06/15 00:00:00  |         | UNKNOWN            | E… 
 DELTA AIR LINES    | 1  #| A-320          | 05/07/15 00:00:00  | CA      | METRO OAKLAND INTL |    
 DELTA AIR LINES    | 1  #| A-320          | 05/08/15 00:00:00  | UT      | SALT LAKE CITY INTL|    
 LUFTHANSA          | 1  #| A-380          | 05/10/15 00:00:00  | TX      | GEORGE BUSH INTERC…| C… 
 BUSINESS           | 1  #| C-172          | 05/08/15 00:00:00  | FL      | ORLANDO SANFORD IN…| A… 
 SPIRIT AIRLINES    | 1  #| A-319          | 05/10/15 00:00:00  | IL      | CHICAGO O'HARE INT…| C… 
 EXPRESSJET AIRLINES| 1  #| EMB-145        | 05/11/15 00:00:00  | AL      | BIRMINGHAM-SHUTTLE…| L… 
faa-wildlife-strikes| "1"                                                         =     73448 rows  

Note

By default, the name of your new columns will be the expression you entered. As always, you can edit the column name by pressing ^ and typing the new name.

Also by default, the column will appear directly to the right of the column that was active when you pressed =. As always, you can move the column left or right using Shift-J and Shift-K.

Now, let’s try creating a column based on another column. Let’s say we want to identify wildlife strikes that were reported to have occurred at least 100 feet above ground. We could do the following:

  • Navigate to the HEIGHT column
  • Press # to tell VisiData the column’s values should be interpreted as integers
  • Press = to bring up the “new column expr=” prompt
  • In the prompt, type HEIGHT >= 100
  • Press Enter

You should see something like the following:

<STATE   | AIRPORT            | PHASE_OF_FLT   | HEIGHT  #| HEIGHT >= 100   | SPEED   | SPECIES   > 
 FL      | VERO BEACH MUNICIP…| APPROACH       |         !| False           |         | Unknown bi… 
 AK      | KENAI MUNICIPAL AR…| APPROACH       |         !| False           |         | Unknown bi… 
 TX      | DAVID WAYNE HOOKS …|                |         !| False           |         | Unknown bi… 
 MO      | LAMBERT-ST LOUIS I…| APPROACH       |         !| False           |         | Unknown bi… 
 FL      | POMPANO BEACH AIRP…| LANDING ROLL   |        0 | False           |         | Unknown bi… 
 VI      | HENRY E ROHLSEN AR…|                |         !| False           |         | Unknown bi… 
 TX      | SAN ANTONIO INTL   | APPROACH       |         !| False           |         | Unknown bi… 
 TX      | LONE STAR EXECUTIV…| DEPARTURE      |         !| False           |         | Unknown bi… 
 FL      | TAMPA INTL         | APPROACH       |     6000 | True            |         | Unknown bi… 
 MO      | LAMBERT-ST LOUIS I…| APPROACH       |         !| False           |         | Owls        
 FL      | OPA-LOCKA EXECUTIV…| APPROACH       |         !| False           |         | Hawks       
 CA      | NORMAN Y. MINETA S…|                |         !| False           |         | Gulls       
 FL      | FORT LAUDERDALE/HO…| APPROACH       |     1500 | True            |         | Unknown bi… 
 AR      | FORT SMITH REGIONA…| CLIMB          |         !| False           |         | Unknown bi… 
 AR      | BILL AND  HILLARY …| LANDING ROLL   |        0 | False           |         | Unknown bi… 
         | UNKNOWN            | En Route       |         !| False           |         | Unknown bi… 
 CA      | METRO OAKLAND INTL |                |         !| False           |         | Unknown bi… 
 UT      | SALT LAKE CITY INTL|                |         !| False           |         | Unknown bi… 
 TX      | GEORGE BUSH INTERC…| CLIMB          |         !| False           |         | Unknown bi… 
 FL      | ORLANDO SANFORD IN…| APPROACH       |         !| False           |         | Unknown bi… 
 IL      | CHICAGO O'HARE INT…| CLIMB          |    12000 | True            |         | Unknown bi… 
 AL      | BIRMINGHAM-SHUTTLE…| LANDING ROLL   |        0 | False           |         | Unknown bi… 
faa-wildlife-strikes| "HEIGHT >= 100"                                             =     73448 rows  

How to create new columns by splitting another one

You can split the text in any column in VisiData into two or more columns, based on a pattern (i.e., a “regular expression” a.k.a. “regex”) that you provide.

To split a column, navigate to that column, and press :. At the bottom of the screen, VisiData you’ll see a split regex: prompt. Enter your desired splitting pattern, and press Enter.

For a simple example, let’s say we want to split the INCIDENT_DATE column into the date and time. Because the date and time are separated by a space, we can do this:

  • Navigate to the INCIDENT_DATE column
  • Press :
  • At the prompt, type :kbd:` ` (a space), and then hit Enter

Once you do that, you should see something like this:

 OPERATOR           | ATYPE          | INCIDENT_DATE      | INCIDENT_DATE_re0  | INCIDENT_DATE_re1> 
 BUSINESS           | PA-28          | 05/22/15 00:00:00  | 05/22/15           | 00:00:00           
 BUSINESS           | BE-1900        | 06/18/15 00:00:00  | 06/18/15           | 00:00:00           
 BUSINESS           | PA-46 MALIBU   | 09/20/15 00:00:00  | 09/20/15           | 00:00:00           
 DELTA AIR LINES    | B-717-200      | 11/07/15 00:00:00  | 11/07/15           | 00:00:00           
 BUSINESS           | BE-90 KING     | 12/17/15 00:00:00  | 12/17/15           | 00:00:00           
 DELTA AIR LINES    | B-757          | 07/17/15 00:00:00  | 07/17/15           | 00:00:00           
 DELTA AIR LINES    | B-717-200      | 08/02/15 00:00:00  | 08/02/15           | 00:00:00           
 BUSINESS           | C-414          | 08/03/15 00:00:00  | 08/03/15           | 00:00:00           
 ALLEGIANT AIR      | MD-80          | 09/02/15 00:00:00  | 09/02/15           | 00:00:00           
 TRANS STATES AIRLI…| EMB-145        | 09/07/15 00:00:00  | 09/07/15           | 00:00:00           
 BUSINESS           | C-172          | 11/28/15 00:00:00  | 11/28/15           | 00:00:00           
 GOVERNMENT         | EC120          | 12/08/15 00:00:00  | 12/08/15           | 00:00:00           
 AMERICAN AIRLINES  | A-321          | 05/06/15 00:00:00  | 05/06/15           | 00:00:00           
 EXPRESSJET AIRLINES| CRJ100/200     | 05/06/15 00:00:00  | 05/06/15           | 00:00:00           
 MESA AIRLINES      | CRJ900         | 05/08/15 00:00:00  | 05/08/15           | 00:00:00           
 BUSINESS           | HELICOPTER     | 05/06/15 00:00:00  | 05/06/15           | 00:00:00           
 DELTA AIR LINES    | A-320          | 05/07/15 00:00:00  | 05/07/15           | 00:00:00           
 DELTA AIR LINES    | A-320          | 05/08/15 00:00:00  | 05/08/15           | 00:00:00           
 LUFTHANSA          | A-380          | 05/10/15 00:00:00  | 05/10/15           | 00:00:00           
 BUSINESS           | C-172          | 05/08/15 00:00:00  | 05/08/15           | 00:00:00           
 SPIRIT AIRLINES    | A-319          | 05/10/15 00:00:00  | 05/10/15           | 00:00:00           
 EXPRESSJET AIRLINES| EMB-145        | 05/11/15 00:00:00  | 05/11/15           | 00:00:00           
faa-wildlife-strikes| " "                                                         :     73448 rows  

Note

The number of new columns created will be determined by the active row. E.g., splitting on - would create two new columns if the column’s value for the active row was see-saw, but three columns if it were merry-go-round.

How to create a new column by “capturing” it from another column

Note

This approach requires a bit more knowlege of “regular expressions”. If you’re unfamiliar with regular expressions and don’t want to learn them right now, feel free to skip to the next chapter.

Just like you can split a column by using :, you can extract part of a column into a new column by using ;.

For instance, if you want to extract the first string of numbers from each aircraft type, (e.g., 28 from PA-28, 46 from PA-46 MALIBU, and 717 from B-717-200). To do that, take the following steps:

  • Navigate to the ATYPE column
  • Press ;
  • At the prompt, type (\d+) (with one set of parentheses for each capture group), and then hit Enter

Once you do that, you should see something like this:

 OPERATOR           | ATYPE          | ATYPE_re0   | INCIDENT_DATE      | INCIDENT_DATE_re1  | STA> 
 BUSINESS           | PA-28          | 28          | 05/22/15 00:00:00  | 00:00:00           | FL   
 BUSINESS           | BE-1900        | 1900        | 06/18/15 00:00:00  | 00:00:00           | AK   
 BUSINESS           | PA-46 MALIBU   | 46          | 09/20/15 00:00:00  | 00:00:00           | TX   
 DELTA AIR LINES    | B-717-200      | 717         | 11/07/15 00:00:00  | 00:00:00           | MO   
 BUSINESS           | BE-90 KING     | 90          | 12/17/15 00:00:00  | 00:00:00           | FL   
 DELTA AIR LINES    | B-757          | 757         | 07/17/15 00:00:00  | 00:00:00           | VI   
 DELTA AIR LINES    | B-717-200      | 717         | 08/02/15 00:00:00  | 00:00:00           | TX   
 BUSINESS           | C-414          | 414         | 08/03/15 00:00:00  | 00:00:00           | TX   
 ALLEGIANT AIR      | MD-80          | 80          | 09/02/15 00:00:00  | 00:00:00           | FL   
 TRANS STATES AIRLI…| EMB-145        | 145         | 09/07/15 00:00:00  | 00:00:00           | MO   
 BUSINESS           | C-172          | 172         | 11/28/15 00:00:00  | 00:00:00           | FL   
 GOVERNMENT         | EC120          | 120         | 12/08/15 00:00:00  | 00:00:00           | CA   
 AMERICAN AIRLINES  | A-321          | 321         | 05/06/15 00:00:00  | 00:00:00           | FL   
 EXPRESSJET AIRLINES| CRJ100/200     | 100         | 05/06/15 00:00:00  | 00:00:00           | AR   
 MESA AIRLINES      | CRJ900         | 900         | 05/08/15 00:00:00  | 00:00:00           | AR   
 BUSINESS           | HELICOPTER     |            !| 05/06/15 00:00:00  | 00:00:00           |      
 DELTA AIR LINES    | A-320          | 320         | 05/07/15 00:00:00  | 00:00:00           | CA   
 DELTA AIR LINES    | A-320          | 320         | 05/08/15 00:00:00  | 00:00:00           | UT   
 LUFTHANSA          | A-380          | 380         | 05/10/15 00:00:00  | 00:00:00           | TX   
 BUSINESS           | C-172          | 172         | 05/08/15 00:00:00  | 00:00:00           | FL   
 SPIRIT AIRLINES    | A-319          | 319         | 05/10/15 00:00:00  | 00:00:00           | IL   
 EXPRESSJET AIRLINES| EMB-145        | 145         | 05/11/15 00:00:00  | 00:00:00           | AL   
faa-wildlife-strikes| "(\d+)"                                                     ;     73448 rows