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
HEIGHTcolumn - 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_DATEcolumn - 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
ATYPEcolumn - 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]