Creating New Columns

In any VisiData sheet, you can create a blank column by typing za. But VisiData also provides many powerful ways to create new, dyanamic columns. These are four of the most useful ways:

  • Increments
  • 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 incremented column

You can add an incremented column with sequential values (akin to row numbers) by pressing i. Once you do that, VisiData will create this new column directly to the right of your current column:

  File  Edit  View  Column  Row  Data  Plot  System  Help     Ctrl+H for help menu                  
 OPERATOR           | A #| ATYPE        | INCIDENT_DATE     | STATE | AIRPORT            | PHASE_O> 
 BUSINESS           |  1 | PA-28        | 05/22/15 00:00:00 | FL    | VERO BEACH MUNICIP…| APPROACH 
 BUSINESS           |  2 | BE-1900      | 06/18/15 00:00:00 | AK    | KENAI MUNICIPAL AR…| APPROACH 
 BUSINESS           |  3 | PA-46 MALIBU | 09/20/15 00:00:00 | TX    | DAVID WAYNE HOOKS …|          
 DELTA AIR LINES    |  4 | B-717-200    | 11/07/15 00:00:00 | MO    | LAMBERT-ST LOUIS I…| APPROACH 
 BUSINESS           |  5 | BE-90 KING   | 12/17/15 00:00:00 | FL    | POMPANO BEACH AIRP…| LANDING… 
 DELTA AIR LINES    |  6 | B-757        | 07/17/15 00:00:00 | VI    | HENRY E ROHLSEN AR…|          
 DELTA AIR LINES    |  7 | B-717-200    | 08/02/15 00:00:00 | TX    | SAN ANTONIO INTL   | APPROACH 
 BUSINESS           |  8 | C-414        | 08/03/15 00:00:00 | TX    | LONE STAR EXECUTIV…| DEPARTU… 
 ALLEGIANT AIR      |  9 | MD-80        | 09/02/15 00:00:00 | FL    | TAMPA INTL         | APPROACH 
 TRANS STATES AIRLI…| 10 | EMB-145      | 09/07/15 00:00:00 | MO    | LAMBERT-ST LOUIS I…| APPROACH 
 BUSINESS           | 11 | C-172        | 11/28/15 00:00:00 | FL    | OPA-LOCKA EXECUTIV…| APPROACH 
 GOVERNMENT         | 12 | EC120        | 12/08/15 00:00:00 | CA    | NORMAN Y. MINETA S…|          
 AMERICAN AIRLINES  | 13 | A-321        | 05/06/15 00:00:00 | FL    | FORT LAUDERDALE/HO…| APPROACH 
 EXPRESSJET AIRLINES| 14 | CRJ100/200   | 05/06/15 00:00:00 | AR    | FORT SMITH REGIONA…| CLIMB    
 MESA AIRLINES      | 15 | CRJ900       | 05/08/15 00:00:00 | AR    | BILL AND  HILLARY …| LANDING… 
 BUSINESS           | 16 | HELICOPTER   | 05/06/15 00:00:00 |       | UNKNOWN            | En Route 
 DELTA AIR LINES    | 17 | A-320        | 05/07/15 00:00:00 | CA    | METRO OAKLAND INTL |          
 DELTA AIR LINES    | 18 | A-320        | 05/08/15 00:00:00 | UT    | SALT LAKE CITY INTL|          
 LUFTHANSA          | 19 | A-380        | 05/10/15 00:00:00 | TX    | GEORGE BUSH INTERC…| CLIMB    
 BUSINESS           | 20 | C-172        | 05/08/15 00:00:00 | FL    | ORLANDO SANFORD IN…| APPROACH 
 SPIRIT AIRLINES    | 21 | A-319        | 05/10/15 00:00:00 | IL    | CHICAGO O'HARE INT…| CLIMB    
1› faa-wildlife-strikes| set 73448 cells to 73448 values     i   addcol-incr     73448 rows  [M] •0 

By default, VisiData will name the column A (or B if VisiData has already created an A column this session, and so on).

Note

You can customize the “step” between values; to do that, type zi, and specify a number at the prompt. (Specifying 3, for instance, would create the sequence 1, 4, 7, ....)

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:

  File  Edit  View  Column  Row  Data  Plot  System  Help     Ctrl+H for help menu                  
 OPERATOR           | 1  | ATYPE        | INCIDENT_DATE     | STATE | AIRPORT            | PHASE_O> 
 BUSINESS           | 1 #| PA-28        | 05/22/15 00:00:00 | FL    | VERO BEACH MUNICIP…| APPROACH 
 BUSINESS           | 1 #| BE-1900      | 06/18/15 00:00:00 | AK    | KENAI MUNICIPAL AR…| APPROACH 
 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…| APPROACH 
 BUSINESS           | 1 #| BE-90 KING   | 12/17/15 00:00:00 | FL    | POMPANO BEACH AIRP…| LANDING… 
 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   | APPROACH 
 BUSINESS           | 1 #| C-414        | 08/03/15 00:00:00 | TX    | LONE STAR EXECUTIV…| DEPARTU… 
 ALLEGIANT AIR      | 1 #| MD-80        | 09/02/15 00:00:00 | FL    | TAMPA INTL         | APPROACH 
 TRANS STATES AIRLI…| 1 #| EMB-145      | 09/07/15 00:00:00 | MO    | LAMBERT-ST LOUIS I…| APPROACH 
 BUSINESS           | 1 #| C-172        | 11/28/15 00:00:00 | FL    | OPA-LOCKA EXECUTIV…| APPROACH 
 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…| APPROACH 
 EXPRESSJET AIRLINES| 1 #| CRJ100/200   | 05/06/15 00:00:00 | AR    | FORT SMITH REGIONA…| CLIMB    
 MESA AIRLINES      | 1 #| CRJ900       | 05/08/15 00:00:00 | AR    | BILL AND  HILLARY …| LANDING… 
 BUSINESS           | 1 #| HELICOPTER   | 05/06/15 00:00:00 |       | UNKNOWN            | En Route 
 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…| CLIMB    
 BUSINESS           | 1 #| C-172        | 05/08/15 00:00:00 | FL    | ORLANDO SANFORD IN…| APPROACH 
 SPIRIT AIRLINES    | 1 #| A-319        | 05/10/15 00:00:00 | IL    | CHICAGO O'HARE INT…| CLIMB    
1› faa-wildlife-strikes| "1"                                 =   addcol-expr     73448 rows  [M] •0 

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-H and Shift-L.

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:

  File  Edit  View  Column  Row  Data  Plot  System  Help     Ctrl+H for help menu                  
<STATE | AIRPORT            | PHASE_OF_FLT | HEIGHT#| HEIGHT >= 100 | SPEED | SPECIES            |> 
 FL    | VERO BEACH MUNICIP…| APPROACH     |       !| False         |       | Unknown bird       |1 
 AK    | KENAI MUNICIPAL AR…| APPROACH     |       !| False         |       | Unknown bird       |1 
 TX    | DAVID WAYNE HOOKS …|              |       !| False         |       | Unknown bird       |1 
 MO    | LAMBERT-ST LOUIS I…| APPROACH     |       !| False         |       | Unknown bird       |1 
 FL    | POMPANO BEACH AIRP…| LANDING ROLL |      0 | False         |       | Unknown bird       |1 
 VI    | HENRY E ROHLSEN AR…|              |       !| False         |       | Unknown bird       |1 
 TX    | SAN ANTONIO INTL   | APPROACH     |       !| False         |       | Unknown bird       |1 
 TX    | LONE STAR EXECUTIV…| DEPARTURE    |       !| False         |       | Unknown bird       |1 
 FL    | TAMPA INTL         | APPROACH     |   6000 | True          |       | Unknown bird       |1 
 MO    | LAMBERT-ST LOUIS I…| APPROACH     |       !| False         |       | Owls               |1 
 FL    | OPA-LOCKA EXECUTIV…| APPROACH     |       !| False         |       | Hawks              |1 
 CA    | NORMAN Y. MINETA S…|              |       !| False         |       | Gulls              |1 
 FL    | FORT LAUDERDALE/HO…| APPROACH     |   1500 | True          |       | Unknown bird - sma…|1 
 AR    | FORT SMITH REGIONA…| CLIMB        |       !| False         |       | Unknown bird - sma…|1 
 AR    | BILL AND  HILLARY …| LANDING ROLL |      0 | False         |       | Unknown bird - sma…|1 
       | UNKNOWN            | En Route     |       !| False         |       | Unknown bird       |1 
 CA    | METRO OAKLAND INTL |              |       !| False         |       | Unknown bird       |1 
 UT    | SALT LAKE CITY INTL|              |       !| False         |       | Unknown bird       |1 
 TX    | GEORGE BUSH INTERC…| CLIMB        |       !| False         |       | Unknown bird       |1 
 FL    | ORLANDO SANFORD IN…| APPROACH     |       !| False         |       | Unknown bird       |1 
 IL    | CHICAGO O'HARE INT…| CLIMB        |  12000 | True          |       | Unknown bird       |1 
1› faa-wildlife-strikes| "HEIGHT >= 100"                     =   addcol-expr     73448 rows  [M] •0 

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, press Space (since we want to split on the column’s whitespace), and then hit Enter

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

  File  Edit  View  Column  Row  Data  Plot  System  Help     Ctrl+H for help menu                  
 OPERATOR           | ATYPE        | INCIDENT_DATE     | INCIDENT_DATE_re0 | INCIDENT_DATE_re1 | S> 
 BUSINESS           | PA-28        | 05/22/15 00:00:00 | 05/22/15          | 00:00:00          | FL 
 BUSINESS           | BE-1900      | 06/18/15 00:00:00 | 06/18/15          | 00:00:00          | AK 
 BUSINESS           | PA-46 MALIBU | 09/20/15 00:00:00 | 09/20/15          | 00:00:00          | TX 
 DELTA AIR LINES    | B-717-200    | 11/07/15 00:00:00 | 11/07/15          | 00:00:00          | MO 
 BUSINESS           | BE-90 KING   | 12/17/15 00:00:00 | 12/17/15          | 00:00:00          | FL 
 DELTA AIR LINES    | B-757        | 07/17/15 00:00:00 | 07/17/15          | 00:00:00          | VI 
 DELTA AIR LINES    | B-717-200    | 08/02/15 00:00:00 | 08/02/15          | 00:00:00          | TX 
 BUSINESS           | C-414        | 08/03/15 00:00:00 | 08/03/15          | 00:00:00          | TX 
 ALLEGIANT AIR      | MD-80        | 09/02/15 00:00:00 | 09/02/15          | 00:00:00          | FL 
 TRANS STATES AIRLI…| EMB-145      | 09/07/15 00:00:00 | 09/07/15          | 00:00:00          | MO 
 BUSINESS           | C-172        | 11/28/15 00:00:00 | 11/28/15          | 00:00:00          | FL 
 GOVERNMENT         | EC120        | 12/08/15 00:00:00 | 12/08/15          | 00:00:00          | CA 
 AMERICAN AIRLINES  | A-321        | 05/06/15 00:00:00 | 05/06/15          | 00:00:00          | FL 
 EXPRESSJET AIRLINES| CRJ100/200   | 05/06/15 00:00:00 | 05/06/15          | 00:00:00          | AR 
 MESA AIRLINES      | CRJ900       | 05/08/15 00:00:00 | 05/08/15          | 00:00:00          | AR 
 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          | CA 
 DELTA AIR LINES    | A-320        | 05/08/15 00:00:00 | 05/08/15          | 00:00:00          | UT 
 LUFTHANSA          | A-380        | 05/10/15 00:00:00 | 05/10/15          | 00:00:00          | TX 
 BUSINESS           | C-172        | 05/08/15 00:00:00 | 05/08/15          | 00:00:00          | FL 
 SPIRIT AIRLINES    | A-319        | 05/10/15 00:00:00 | 05/10/15          | 00:00:00          | IL 
1› faa-wildlife-strikes| sampling 100 rows | " "               :   split-col     73448 rows  [M] •0 

Note

The number of new columns created will be determined by split-size of the values in the 100 rows surrounding the cursor. (That 100 number is configurable via the default_sample_size option.)

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:

  File  Edit  View  Column  Row  Data  Plot  System  Help     Ctrl+H for help menu                  
 OPERATOR           | ATYPE        | ATYPE_re0 | INCIDENT_DATE     | INCIDENT_DATE_re1 | STATE | A> 
 BUSINESS           | PA-28        | 28        | 05/22/15 00:00:00 | 00:00:00          | FL    | V… 
 BUSINESS           | BE-1900      | 1900      | 06/18/15 00:00:00 | 00:00:00          | AK    | K… 
 BUSINESS           | PA-46 MALIBU | 46        | 09/20/15 00:00:00 | 00:00:00          | TX    | D… 
 DELTA AIR LINES    | B-717-200    | 717       | 11/07/15 00:00:00 | 00:00:00          | MO    | L… 
 BUSINESS           | BE-90 KING   | 90        | 12/17/15 00:00:00 | 00:00:00          | FL    | P… 
 DELTA AIR LINES    | B-757        | 757       | 07/17/15 00:00:00 | 00:00:00          | VI    | H… 
 DELTA AIR LINES    | B-717-200    | 717       | 08/02/15 00:00:00 | 00:00:00          | TX    | S… 
 BUSINESS           | C-414        | 414       | 08/03/15 00:00:00 | 00:00:00          | TX    | L… 
 ALLEGIANT AIR      | MD-80        | 80        | 09/02/15 00:00:00 | 00:00:00          | FL    | T… 
 TRANS STATES AIRLI…| EMB-145      | 145       | 09/07/15 00:00:00 | 00:00:00          | MO    | L… 
 BUSINESS           | C-172        | 172       | 11/28/15 00:00:00 | 00:00:00          | FL    | O… 
 GOVERNMENT         | EC120        | 120       | 12/08/15 00:00:00 | 00:00:00          | CA    | N… 
 AMERICAN AIRLINES  | A-321        | 321       | 05/06/15 00:00:00 | 00:00:00          | FL    | F… 
 EXPRESSJET AIRLINES| CRJ100/200   | 100       | 05/06/15 00:00:00 | 00:00:00          | AR    | F… 
 MESA AIRLINES      | CRJ900       | 900       | 05/08/15 00:00:00 | 00:00:00          | AR    | B… 
 BUSINESS           | HELICOPTER   |          !| 05/06/15 00:00:00 | 00:00:00          |       | U… 
 DELTA AIR LINES    | A-320        | 320       | 05/07/15 00:00:00 | 00:00:00          | CA    | M… 
 DELTA AIR LINES    | A-320        | 320       | 05/08/15 00:00:00 | 00:00:00          | UT    | S… 
 LUFTHANSA          | A-380        | 380       | 05/10/15 00:00:00 | 00:00:00          | TX    | G… 
 BUSINESS           | C-172        | 172       | 05/08/15 00:00:00 | 00:00:00          | FL    | O… 
 SPIRIT AIRLINES    | A-319        | 319       | 05/10/15 00:00:00 | 00:00:00          | IL    | C… 
1› faa-wildlife-strikes| sampling 100 rows | "(\d+)"         ;   capture-col     73448 rows  [M] •0