Sorting and Filtering

Now that you have a grasp of sheets, rows, and columns, let’s move on to the most basic of dataset operations: sorting and filtering rows.

How to sort rows

The keys [ and ] sort rows in ascending and descending order, respectively.

For instance, you could do the following with the FAA dataset:

  • Navigate to the COST_REPAIRS column
  • Press # (if you haven’t already) to tell VisiData it’s a numeric column
  • Press ] to sort the column in descending order — i.e., from highest to lowest

After that, you should see something like the following:

<SPEED   | SPECIES            | BIRDS_STRUCK   | EFFECT             | DAMAGE   | COST_REPAIRS  #| > 
 143     | Red-tailed hawk    | 1              | Engine Shut Down   | S        |        6500000 | … 
         | Gadwall            | 2-10           | Precautionary Land…| S        |        5818528 | 
         | Mallard            | 1              | None               | S        |        5548924 | 
         | Gray partridge     | 2-10           | Precautionary Land…| S        |        5427992 | 
         | American white pel…| 2-10           | Engine Shut Down   | S        |        5111000 |   
 150     | American coot      | 1              | Engine Shut Down   | S        |        5000000 | 
 180     | Northern pintail   | 2-10           | Engine Shut Down   | S        |        5000000 | 
 96      | Red-tailed hawk    | 2-10           | Engine Shut Down   | S        |        4600279 | 
 160     | Bald eagle         | 1              | Precautionary Land…| S        |        4500000 |   
 170     | Canada goose       | 2-10           | Precautionary Land…| S        |        3909837 | 
 130     | Rock pigeon        | 11-100         | Precautionary Land…| S        |        3250000 | 
 230     | Greater white-fron…| 2-10           | Precautionary Land…| S        |        3228053 | 
 141     | Mallard            | 2-10           | Precautionary Land…| S        |        3000000 | 
 120     | Bald eagle         | 1              | Engine Shut Down   | S        |        2900000 | 
         | Snow goose         | 11-100         | Precautionary Land…| S        |        2737462 | 
         | Red-tailed hawk    | 1              | Precautionary Land…| S        |        2300000 | 
 190     | Turkey vulture     | 2-10           | Precautionary Land…| S        |        2000000 | 
 160     | Unknown bird - med…| 1              | Engine Shut Down   | S        |        2000000 | 
 220     | Black vulture      | 1              | Engine Shut Down   | S        |        2000000 | 
         | Brown thrasher     | 1              | None               | M        |        2000000 | 
 140     | Great black-backed…| 1              |                    | S        |        1529013 | 
 90      | Mallard            | 2-10           | Other              | S        |        1500000 | 
faa-wildlife-strikes|                                                              ]     73448 rows 

Note

You can sort on multiple columns at once by “key”-ing those columns (via !) and then typing either g[ (ascending) or g] (descending).

How to filter rows

VisiData provides several ways to filter your datasets:

  • Row selection + "
  • Frequency tables + Enter
  • Frequency tables + row selection + "

The sections below walk you through each approach.

Filtering selected rows with "

In VisiData, pressing " will create a copy of your current sheet — but one that contains only selected rows.

So, to view only wildlife strikes that involved hawks, you could do the following:

  • Navigate to the SPECIES column
  • Press | to select by searching, then type hawk, and then press Enter

At this point, you should see something like the following:

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

Then, press ", which should give you something like the following:

<STATE   | AIRPORT            | PHASE_OF_FLT   | HEIGHT   | SPEED   | SPECIES            | BIRDS_S> 
 FL      | OPA-LOCKA EXECUTIV…| APPROACH       |          |         | Hawks              | 1        
 TX      | GEORGE BUSH INTERC…|                |          |         | Red-tailed hawk    | 1        
 CO      | DENVER INTL AIRPORT| Landing Roll   | 0        |         | Ferruginous hawk   | 1        
 PR      | LUIS MUNOZ MARIN I…| Landing Roll   | 0        |         | Hawks              | 1        
 GA      | DEKALB-PEACHTREE A…| APPROACH       |          |         | Hawks              | 1        
 OK      | RICHARD LLOYD JONE…| APPROACH       |          |         | Hawks              | 1        
 AR      | NW ARKANSAS REGION…| DEPARTURE      |          |         | Hawks              | 1        
 TX      | AUSTIN-BERGSTROM I…| APPROACH       |          |         | Hawks              | 2-10     
 IL      | QUAD CITY ARPT     | APPROACH       |          |         | Red-tailed hawk    | 1        
 MO      | LAMBERT-ST LOUIS I…| APPROACH       | 100      |         | Hawks              | 1        
         | UNKNOWN            | En Route       | 500      | 50      | Hawks              | 1        
 KY      | LOUISVILLE INTL AR…|                |          |         | Red-tailed hawk    | 1        
 MO      | KANSAS CITY INTL   |                |          |         | Red-tailed hawk    | 1        
 NY      | JOHN F KENNEDY INTL| Climb          | 20       | 155     | Red-tailed hawk    | 1        
 UT      | SALT LAKE CITY INTL| Approach       | 75       | 140     | Rough-legged hawk  | 1        
 BC      | VANCOUVER INTL     | Take-off run   | 0        |         | Rough-legged hawk  | 1        
 MD      | BALTIMORE/WASH INT…| Climb          | 100      |         | Red-tailed hawk    | 1        
 FL      | GAINESVILLE REG AR…|                |          |         | Common nighthawk   | 1        
 CA      | LONG BEACH-DAUGH F…| Landing Roll   | 0        |         | Red-tailed hawk    | 1        
 MD      | BALTIMORE/WASH INT…| Take-off run   | 0        | 140     | Hawks              | 1        
 IL      | CHICAGO O'HARE INT…|                |          |         | Red-tailed hawk    | 1        
 OH      | PORT COLUMBUS INTL | Take-off run   | 0        |         | Red-tailed hawk    | 1        
faa-wildlife-strikes_selectedref| selected 2164 rows                               "      2164 rows 

Note

If no rows are selected on your current sheet, using " will create a full copy of the sheet.

Filtering via frequency tables

From any row in any frequency table, you can press Enter to create a new dataset containing only the rows that match that value.

For instance, to view only the wildlife strikes that occurred in California, we might do the following from the main data sheet:

  • Navigate to the STATE column
  • Press Shift-F to create the frequency table
  • Navigate down two rows, to the row for CA

At which point, you should see something like this:

 STATE    count  #| percent  %| histogram                                                         
             9840 |     13.40 | ************************************************…                 
 TX          7309 |      9.95 | ************************************************…                 
 CA      ‖    5270 |      7.18 | ******************************************       ‖                 
 FL          4902 |      6.67 | ***************************************                           
 CO          3523 |      4.80 | ****************************                                      
 NY          3434 |      4.68 | ***************************                                       
 IL          3020 |      4.11 | ************************                                          
 PA          2475 |      3.37 | ********************                                              
 TN          2116 |      2.88 | *****************                                                 
 NJ          2085 |      2.84 | ****************                                                  
 OH          1914 |      2.61 | ***************                                                   
 MO          1633 |      2.22 | *************                                                     
 NC          1620 |      2.21 | *************                                                     
 KY          1548 |      2.11 | ************                                                      
 MI          1532 |      2.09 | ************                                                      
 GA          1247 |      1.70 | **********                                                        
 UT          1225 |      1.67 | *********                                                         
 LA          1182 |      1.61 | *********                                                         
 IN          1174 |      1.60 | *********                                                         
 WA          1089 |      1.48 | ********                                                          
 HI          1052 |      1.43 | ********                                                          
 MA           990 |      1.35 | ********                                                          
faa-wildlife-strikes_STATE_freq|                                                   j        63 bins 

From there, pressing Enter should create the filtered sheet we wanted:

<STATE   | AIRPORT            | PHASE_OF_FLT   | HEIGHT   | SPEED   | SPECIES            | BIRDS_S> 
 CA      | NORMAN Y. MINETA S…|                |          |         | Gulls              | 1        
 CA      | METRO OAKLAND INTL |                |          |         | Unknown bird       | 1        
 CA      | LOS ANGELES INTL   | CLIMB          |          |         | Unknown bird       | 1        
 CA      | LONG BEACH-DAUGH F…| LANDING ROLL   | 0        |         | Unknown bird       | 1        
 CA      | NAPA COUNTY ARPT   | LANDING ROLL   | 0        |         | Unknown bird       | 1        
 CA      | HAYWARD EXECUTIVE …| Climb          |          |         | Unknown bird - sma…| 1        
 CA      | ONTARIO INTL ARPT  |                |          |         | Owls               | 1        
 CA      | LOS ANGELES INTL   | APPROACH       |          |         | Unknown bird       | 1        
 CA      | LIVERMORE MUNICIPA…| TAKE-OFF RUN   | 0        |         | Unknown bird       | 2-10     
 CA      | WHITEMAN AIRPORT   | LANDING ROLL   | 0        |         | Rock pigeon        | 1        
 CA      | LIVERMORE MUNICIPA…| APPROACH       |          |         | Unknown bird       | 1        
 CA      | JOHN WAYNE-ORANGE …| APPROACH       | 1700     |         | Unknown bird - med…| 1        
 CA      | CHINO AIRPORT      |                |          |         | Unknown bird       | 1        
 CA      | SAN FRANCISCO INTL…| LANDING ROLL   | 0        |         | Unknown bird       | 1        
 CA      | SACRAMENTO INTL    | APPROACH       |          |         | Unknown bird       | 1        
 CA      | SANTA MONICA MUNIC…| APPROACH       | 60       |         | Unknown bird       | 1        
 CA      | SACRAMENTO INTL    |                |          |         | Unknown bird       | 1        
 CA      | SACRAMENTO INTL    | Climb          | 1500     | 210     | Northern pintail   | 2-10     
 CA      | LOS ANGELES INTL   | Approach       | 900      | 135     | Unknown bird - med…| 1        
 CA      | SAN FRANCISCO INTL…| Landing Roll   | 0        | 90      | Black-tailed jackr…| 1        
 CA      | SACRAMENTO INTL    | Approach       | 1000     | 135     | Northern pintail   | 1        
 CA      | SANTA BARBARA MUNI…| Approach       |          |         | Unknown bird - med…| 1        
faa-wildlife-strikes_CA|                                                          ^J      5270 rows 

Using frequency tables to select (and filter) for multiple values

The approach above is great if you want to drill down on rows where a field equals one particular value. But what if you want to include a few different values? You can do this through a combination of the techniques above. Specifically: Select the rows of the frequency table you want to include, move back to the main data sheet, and then filter with ".

Here’s a practical example, using the FAA dataset. Let’s say you want to filter for wildlife strikes at the five airports with the most reported incidents. To acheive that, you could take these steps:

  • For a fresh start, go back to the main data sheet, and then type gu to make sure that all rows are unselected. Then, navigate to the AIRPORT column, and press Shift-F to create a frequency table:
 AIRPORT             count  #| percent  %| histogram                                              
 UNKNOWN            ‖    8424 |     11.47 | ************************************************…‖      
 DENVER INTL AIRPORT    2756 |      3.75 | **************************                             
 DALLAS/FORT WORTH …    2392 |      3.26 | **********************                                 
 CHICAGO O'HARE INT…    1583 |      2.16 | ***************                                        
 JOHN F KENNEDY INTL    1302 |      1.77 | ************                                           
 MEMPHIS INTL           1217 |      1.66 | ***********                                            
 SALT LAKE CITY INTL    1179 |      1.61 | ***********                                            
 PHILADELPHIA INTL      1131 |      1.54 | **********                                             
 ORLANDO INTL           1026 |      1.40 | *********                                              
 SACRAMENTO INTL        1021 |      1.39 | *********                                              
 LA GUARDIA ARPT         974 |      1.33 | *********                                              
 CHARLOTTE/DOUGLAS …     960 |      1.31 | *********                                              
 NEWARK LIBERTY INT…     917 |      1.25 | ********                                               
 LOUISVILLE INTL AR…     841 |      1.15 | *******                                                
 AUSTIN-BERGSTROM I…     817 |      1.11 | *******                                                
 LOUIE ARMSTRONG NE…     809 |      1.10 | *******                                                
 KANSAS CITY INTL        807 |      1.10 | *******                                                
 HARTSFIELD - JACKS…     775 |      1.06 | *******                                                
 GEORGE BUSH INTERC…     746 |      1.02 | *******                                                
 DETROIT METRO WAYN…     734 |      1.00 | ******                                                 
 BALTIMORE/WASH INT…     691 |      0.94 | ******                                                 
 GENERAL EDWARD LAW…     686 |      0.93 | ******                                                 
faa-wildlife-strikes_AIRPORT_freq|                                                 F      1512 bins 
  • Then, select the top five entries (skipping UNKNOWN):
 AIRPORT             count  #| percent  %| histogram                                              
 UNKNOWN                8424 |     11.47 | ************************************************…      
 DENVER INTL AIRPORT‖    2756 |      3.75 | **************************                             
 DALLAS/FORT WORTH …‖    2392 |      3.26 | **********************                                 
 CHICAGO O'HARE INT…‖    1583 |      2.16 | ***************                                        
 JOHN F KENNEDY INTL‖    1302 |      1.77 | ************                                           
 MEMPHIS INTL       ‖    1217 |      1.66 | ***********                                            
 SALT LAKE CITY INTL‖    1179 |      1.61 | ***********                                      ‖      
 PHILADELPHIA INTL      1131 |      1.54 | **********                                             
 ORLANDO INTL           1026 |      1.40 | *********                                              
 SACRAMENTO INTL        1021 |      1.39 | *********                                              
 LA GUARDIA ARPT         974 |      1.33 | *********                                              
 CHARLOTTE/DOUGLAS …     960 |      1.31 | *********                                              
 NEWARK LIBERTY INT…     917 |      1.25 | ********                                               
 LOUISVILLE INTL AR…     841 |      1.15 | *******                                                
 AUSTIN-BERGSTROM I…     817 |      1.11 | *******                                                
 LOUIE ARMSTRONG NE…     809 |      1.10 | *******                                                
 KANSAS CITY INTL        807 |      1.10 | *******                                                
 HARTSFIELD - JACKS…     775 |      1.06 | *******                                                
 GEORGE BUSH INTERC…     746 |      1.02 | *******                                                
 DETROIT METRO WAYN…     734 |      1.00 | ******                                                 
 BALTIMORE/WASH INT…     691 |      0.94 | ******                                                 
 GENERAL EDWARD LAW…     686 |      0.93 | ******                                                 
faa-wildlife-strikes_AIRPORT_freq| selected 1217 more rows | selected 1 more rows  s      1512 bins 
  • Next, press q to leave the frequency table and return to the previous sheet, our main data sheet. You should see the matching rows selected:
<STATE   | AIRPORT            | PHASE_OF_FLT   | HEIGHT   | SPEED   | SPECIES            | BIRDS_S> 
 FL      | VERO BEACH MUNICIP…| APPROACH       |          |         | Unknown bird       | 1        
 AK      | KENAI MUNICIPAL AR…| APPROACH       |          |         | Unknown bird       | 1        
 TX      | DAVID WAYNE HOOKS …|                |          |         | Unknown bird       | 1        
 MO      | LAMBERT-ST LOUIS I…| APPROACH       |          |         | Unknown bird       | 1        
 FL      | POMPANO BEACH AIRP…| LANDING ROLL   | 0        |         | Unknown bird       | 1        
 VI      | HENRY E ROHLSEN AR…|                |          |         | Unknown bird       | 1        
 TX      | SAN ANTONIO INTL   | APPROACH       |          |         | Unknown bird       | 1        
 TX      | LONE STAR EXECUTIV…| DEPARTURE      |          |         | Unknown bird       | 1        
 FL      | TAMPA INTL         | APPROACH       | 6000     |         | Unknown bird       | 1        
 MO      | LAMBERT-ST LOUIS I…| APPROACH       |          |         | Owls               | 1        
 FL      | OPA-LOCKA EXECUTIV…| APPROACH       |          |         | Hawks              | 1        
 CA      | NORMAN Y. MINETA S…|                |          |         | Gulls              | 1        
 FL      | FORT LAUDERDALE/HO…| APPROACH       | 1500     |         | Unknown bird - sma…| 1        
 AR      | FORT SMITH REGIONA…| CLIMB          |          |         | Unknown bird - sma…| 1        
 AR      | BILL AND  HILLARY …| LANDING ROLL   | 0        |         | Unknown bird - sma…| 1        
         | UNKNOWN            | En Route       |          |         | Unknown bird       | 1        
 CA      | METRO OAKLAND INTL |                |          |         | Unknown bird       | 1        
 UT      | SALT LAKE CITY INTL|                |          |         | Unknown bird       | 1        
 TX      | GEORGE BUSH INTERC…| CLIMB          |          |         | Unknown bird       | 1        
 FL      | ORLANDO SANFORD IN…| APPROACH       |          |         | Unknown bird       | 1        
 IL      | CHICAGO O'HARE INT…| CLIMB          | 12000    |         | Unknown bird       | 1        
 AL      | BIRMINGHAM-SHUTTLE…| LANDING ROLL   | 0        |         | Unknown bird - sma…| 1        
faa-wildlife-strikes|                                                              q     73448 rows 
  • Finally, press " to create a new, filtered sheet with just the selected rows.
<STATE   | AIRPORT            | PHASE_OF_FLT   | HEIGHT   | SPEED   | SPECIES            | BIRDS_S> 
 IL      | CHICAGO O'HARE INT…| CLIMB          | 12000    |         | Unknown bird       | 1        
 CO      | DENVER INTL AIRPORT| LANDING ROLL   | 0        |         | Unknown bird       | 1        
 TX      | DALLAS/FORT WORTH …| LANDING ROLL   | 0        |         | Unknown bird       | 1        
 TX      | DALLAS/FORT WORTH …| LANDING ROLL   | 0        |         | Unknown bird       | 1        
 IL      | CHICAGO O'HARE INT…|                |          |         | Unknown bird       | 1        
 TN      | MEMPHIS INTL       | APPROACH       |          |         | Unknown bird       | 1        
 TX      | DALLAS/FORT WORTH …| APPROACH       |          |         | Unknown bird       | 1        
 IL      | CHICAGO O'HARE INT…| LANDING ROLL   | 0        |         | Unknown bird       | 1        
 CO      | DENVER INTL AIRPORT| Approach       |          |         | Unknown bird       | 1        
 CO      | DENVER INTL AIRPORT| LANDING ROLL   | 0        |         | Unknown bird       | 1        
 CO      | DENVER INTL AIRPORT| DEPARTURE      |          |         | Unknown bird - sma…| 1        
 CO      | DENVER INTL AIRPORT| Landing Roll   | 0        |         | Ferruginous hawk   | 1        
 TN      | MEMPHIS INTL       | APPROACH       | 600      |         | Unknown bird       | 1        
 CO      | DENVER INTL AIRPORT| DEPARTURE      |          |         | Unknown bird - sma…| 1        
 CO      | DENVER INTL AIRPORT| LANDING ROLL   | 0        |         | Unknown bird - sma…| 2-10     
 CO      | DENVER INTL AIRPORT| APPROACH       |          |         | Unknown bird       | 1        
 TX      | DALLAS/FORT WORTH …|                |          |         | Mourning dove      | 1        
 IL      | CHICAGO O'HARE INT…| Landing Roll   | 0        |         | Rock pigeon        | 2-10     
 NY      | JOHN F KENNEDY INTL| Approach       |          |         | Wilson's snipe     | 1        
 TX      | DALLAS/FORT WORTH …|                |          |         | Ring-billed gull   | 11-100   
 TX      | DALLAS/FORT WORTH …|                |          |         | Cedar waxwing      | 1        
 NY      | JOHN F KENNEDY INTL| Take-off run   | 0        |         | Unknown bird - med…| 1        
faa-wildlife-strikes_selectedref| selected 9250 rows                               "      9250 rows