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     | VisiData 3.0.2 | Alt+H for help men
 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:0┌─────────────────────────────| statuses |─┐ 
 BUSINESS            20  C-172         05/08/15 00:0│ set 73448 cells to 73448 values          │ 
 SPIRIT AIRLINES     21  A-319         05/10/15 00:0└──────────────────────────────────────────┘ 
1› faa-wildlife-strikes|                                       i   addcol-incr      73448 rows  [M]

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     | VisiData 3.0.2 | Alt+H for help men
 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|                                       =   addcol-expr      73448 rows  [M]

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     | VisiData 3.0.2 | Alt+H for help men
<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|                                       =   addcol-expr      73448 rows  [M]

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     | VisiData 3.0.2 | Alt+H for help men
 OPERATOR            ATYPE         INCIDENT_DATE      INCIDENT_DATE_re    STATE  AIRPORT     >
 BUSINESS           │ PA-28        │ 05/22/15 00:00:00 │ [2] 05/22/15; 00:0…│ FL    │ VERO BEACH M… 
 BUSINESS            BE-1900       06/18/15 00:00:00  [2] 06/18/15; 00:0… AK     KENAI MUNICI… 
 BUSINESS            PA-46 MALIBU  09/20/15 00:00:00  [2] 09/20/15; 00:0… TX     DAVID WAYNE … 
 DELTA AIR LINES     B-717-200     11/07/15 00:00:00  [2] 11/07/15; 00:0… MO     LAMBERT-ST L… 
 BUSINESS            BE-90 KING    12/17/15 00:00:00  [2] 12/17/15; 00:0… FL     POMPANO BEAC… 
 DELTA AIR LINES     B-757         07/17/15 00:00:00  [2] 07/17/15; 00:0… VI     HENRY E ROHL… 
 DELTA AIR LINES     B-717-200     08/02/15 00:00:00  [2] 08/02/15; 00:0… TX     SAN ANTONIO … 
 BUSINESS            C-414         08/03/15 00:00:00  [2] 08/03/15; 00:0… TX     LONE STAR EX… 
 ALLEGIANT AIR       MD-80         09/02/15 00:00:00  [2] 09/02/15; 00:0… FL     TAMPA INTL    
 TRANS STATES AIRLI… EMB-145       09/07/15 00:00:00  [2] 09/07/15; 00:0… MO     LAMBERT-ST L… 
 BUSINESS            C-172         11/28/15 00:00:00  [2] 11/28/15; 00:0… FL     OPA-LOCKA EX… 
 GOVERNMENT          EC120         12/08/15 00:00:00  [2] 12/08/15; 00:0… CA     NORMAN Y. MI… 
 AMERICAN AIRLINES   A-321         05/06/15 00:00:00  [2] 05/06/15; 00:0… FL     FORT LAUDERD… 
 EXPRESSJET AIRLINES CRJ100/200    05/06/15 00:00:00  [2] 05/06/15; 00:0… AR     FORT SMITH R… 
 MESA AIRLINES       CRJ900        05/08/15 00:00:00  [2] 05/08/15; 00:0… AR     BILL AND  HI… 
 BUSINESS            HELICOPTER    05/06/15 00:00:00  [2] 05/06/15; 00:0…        UNKNOWN       
 DELTA AIR LINES     A-320         05/07/15 00:00:00  [2] 05/07/15; 00:0… CA     METRO OAKLAN… 
 DELTA AIR LINES     A-320         05/08/15 00:00:00  [2] 05/08/15; 00:0… UT     SALT LAKE CI… 
 LUFTHANSA           A-380         05/10/15 00:00:00  [2] 05/10/15; 00:0… TX     GEORGE BUSH … 
 BUSINESS            C-172         05/08/15 00:00:00  [2] 05/08/15; 00:0… FL     ORLANDO SANF… 
 SPIRIT AIRLINES     A-319         05/10/15 00:00:00  [2] 05/10/15; 00:0… IL     CHICAGO O'HA… 
1› faa-wildlife-strikes|                                      :   addcol-split      73448 rows  [M]

Each value in the new column is a list of the pieces that resulted from the split. But you probably want them each piece in its own column. To do that press (, which is the “expand column” command. Now you should see something like this:

  File  Edit  View  Column  Row  Data  Plot  System  Help     | VisiData 3.0.2 | Alt+H for help men
 OPERATOR            ATYPE         INCIDENT_DATE      INCIDENT_DATE_re[0] INCIDENT_DATE_re[1]>
 BUSINESS           │ PA-28        │ 05/22/15 00:00:00 │ 05/22/15           │ 00:00:00           │F 
 BUSINESS            BE-1900       06/18/15 00:00:00  06/18/15            00:00:00           A 
 BUSINESS            PA-46 MALIBU  09/20/15 00:00:00  09/20/15            00:00:00           T 
 DELTA AIR LINES     B-717-200     11/07/15 00:00:00  11/07/15            00:00:00           M 
 BUSINESS            BE-90 KING    12/17/15 00:00:00  12/17/15            00:00:00           F 
 DELTA AIR LINES     B-757         07/17/15 00:00:00  07/17/15            00:00:00           V 
 DELTA AIR LINES     B-717-200     08/02/15 00:00:00  08/02/15            00:00:00           T 
 BUSINESS            C-414         08/03/15 00:00:00  08/03/15            00:00:00           T 
 ALLEGIANT AIR       MD-80         09/02/15 00:00:00  09/02/15            00:00:00           F 
 TRANS STATES AIRLI… EMB-145       09/07/15 00:00:00  09/07/15            00:00:00           M 
 BUSINESS            C-172         11/28/15 00:00:00  11/28/15            00:00:00           F 
 GOVERNMENT          EC120         12/08/15 00:00:00  12/08/15            00:00:00           C 
 AMERICAN AIRLINES   A-321         05/06/15 00:00:00  05/06/15            00:00:00           F 
 EXPRESSJET AIRLINES CRJ100/200    05/06/15 00:00:00  05/06/15            00:00:00           A 
 MESA AIRLINES       CRJ900        05/08/15 00:00:00  05/08/15            00:00:00           A 
 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           C 
 DELTA AIR LINES     A-320         05/08/15 00:00:00  05/08/15            00:00:00           U 
 LUFTHANSA           A-380         05/10/15 00:00:00  05/10/15            00:00:00           T 
 BUSINESS            C-172         05/08/15 00:00:00  05/08/15            00:00:00           F 
 SPIRIT AIRLINES     A-319         05/10/15 00:00:00  05/10/15            00:00:00           I 
1› faa-wildlife-strikes|                                        (   expand-col      73448 rows  [M]

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
  • Press ( to expand the new column’s lists

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

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