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_REPAIRScolumn - 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:
File Edit View Column Row Data Plot System Help | VisiData 3.0.2 | Alt+H for help men <HEIGHT │ SPEED │ SPECIES │ BIRDS_STRUCK │ EFFECT │ DAMAGE │↓COST_REPAIRS#│> 0 │ 143 │ Red-tailed hawk │ 1 │ Engine Shut Down │ S │ 6500000 │A 25 │ │ Gadwall │ 2-10 │ Precautionary Land…│ S │ 5818528 │A │ │ Mallard │ 1 │ None │ S │ 5548924 │A 0 │ │ Gray partridge │ 2-10 │ Precautionary Land…│ S │ 5427992 │A 5000 │ │ American white pel…│ 2-10 │ Engine Shut Down │ S │ 5111000 │ 250 │ 150 │ American coot │ 1 │ Engine Shut Down │ S │ 5000000 │A 972 │ 180 │ Northern pintail │ 2-10 │ Engine Shut Down │ S │ 5000000 │A 0 │ 96 │ Red-tailed hawk │ 2-10 │ Engine Shut Down │ S │ 4600279 │A 10 │ 160 │ Bald eagle │ 1 │ Precautionary Land…│ S │ 4500000 │ 400 │ 170 │ Canada goose │ 2-10 │ Precautionary Land…│ S │ 3909837 │A 10 │ 130 │ Rock pigeon │ 11-100 │ Precautionary Land…│ S │ 3250000 │P 2000 │ 230 │ Greater white-fron…│ 2-10 │ Precautionary Land…│ S │ 3228053 │A 90 │ 141 │ Mallard │ 2-10 │ Precautionary Land…│ S │ 3000000 │A 0 │ 120 │ Bald eagle │ 1 │ Engine Shut Down │ S │ 2900000 │A 8500 │ │ Snow goose │ 11-100 │ Precautionary Land…│ S │ 2737462 │A 0 │ │ Red-tailed hawk │ 1 │ Precautionary Land…│ S │ 2300000 │A 1200 │ 190 │ Turkey vulture │ 2-10 │ Precautionary Land…│ S │ 2000000 │A 400 │ 160 │ Unknown bird - med…│ 1 │ Engine Shut Down │ S │ 2000000 │P 2000 │ 220 │ Black vulture │ 1 │ Engine Shut Down │ S │ 2000000 │A │ │ Brown thrasher │ 1 │ None │ M │ 2000000 │A 1000 │ 140 │ Great black-backed…│ 1 │ │ S │ 1529013 │A 1› faa-wildlife-strikes| ] sort-desc 73448 rows
Tip
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 + g + Enter
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
SPECIEScolumn - Press | to select by searching, then type
hawk, and then press Enter
At this point, 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 │ SPEED │ SPECIES │ BIRDS_STRUCK │ > FL │ VERO BEACH MUNICIP…│ APPROACH │ │ │ Unknown bird │ 1 │ N AK │ KENAI MUNICIPAL AR…│ APPROACH │ │ │ Unknown bird │ 1 │ N TX │ DAVID WAYNE HOOKS …│ │ │ │ Unknown bird │ 1 │ N MO │ LAMBERT-ST LOUIS I…│ APPROACH │ │ │ Unknown bird │ 1 │ N FL │ POMPANO BEACH AIRP…│ LANDING ROLL │ 0 │ │ Unknown bird │ 1 │ VI │ HENRY E ROHLSEN AR…│ │ │ │ Unknown bird │ 1 │ O TX │ SAN ANTONIO INTL │ APPROACH │ │ │ Unknown bird │ 1 │ N TX │ LONE STAR EXECUTIV…│ DEPARTURE │ │ │ Unknown bird │ 1 │ P FL │ TAMPA INTL │ APPROACH │ 6000 │ │ Unknown bird │ 1 │ N MO │ LAMBERT-ST LOUIS I…│ APPROACH │ │ │ Owls │ 1 │ N •FL │ OPA-LOCKA EXECUTIV…│ APPROACH │ │ │ Hawks │ 1 │ N CA │ NORMAN Y. MINETA S…│ │ │ │ Gulls │ 1 │ N FL │ FORT LAUDERDALE/HO…│ APPROACH │ 1500 │ │ Unknown bird - sma…│ 1 │ N AR │ FORT SMITH REGIONA…│ CLIMB │ │ │ Unknown bird - sma…│ 1 │ N AR │ BILL AND HILLARY …│ LANDING ROLL │ 0 │ │ Unknown bird - sma…│ 1 │ N │ UNKNOWN │ En Route │ │ │ Unknown bird │ 1 │ P CA │ METRO OAKLAND INTL │ │ │ ┌─────────────────────────────| statuses |─┐ UT │ SALT LAKE CITY INTL│ │ │ │ search wrapped │ TX │ GEORGE BUSH INTERC…│ CLIMB │ │ │ 2164 matches for /hawk/ │ FL │ ORLANDO SANFORD IN…│ APPROACH │ │ │ selected 2164 rows │ IL │ CHICAGO O'HARE INT…│ CLIMB │ 12000 │ └──────────────────────────────────────────┘ 1› faa-wildlife-strikes| | select-col-regex 73448 rows •2164
Then, press ", which should give you 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 │ SPEED │ SPECIES │ BIRDS_STRUCK │ > FL │ OPA-LOCKA EXECUTIV…│ APPROACH │ │ │ Hawks │ 1 │ N 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 │ N GA │ DEKALB-PEACHTREE A…│ APPROACH │ │ │ Hawks │ 1 │ N OK │ RICHARD LLOYD JONE…│ APPROACH │ │ │ Hawks │ 1 │ N AR │ NW ARKANSAS REGION…│ DEPARTURE │ │ │ Hawks │ 1 │ N TX │ AUSTIN-BERGSTROM I…│ APPROACH │ │ │ Hawks │ 2-10 │ N IL │ QUAD CITY ARPT │ APPROACH │ │ │ Red-tailed hawk │ 1 │ N MO │ LAMBERT-ST LOUIS I…│ APPROACH │ 100 │ │ Hawks │ 1 │ N │ UNKNOWN │ En Route │ 500 │ 50 │ Hawks │ 1 │ P 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 │ N UT │ SALT LAKE CITY INTL│ Approach │ 75 │ 140 │ Rough-legged hawk │ 1 │ N BC │ VANCOUVER INTL │ Take-off run │ 0 │ │ Rough-legged hawk │ 1 │ N MD │ BALTIMORE/WASH INT…│ Climb │ 100 │ │ Red-tailed hawk │ 1 │ N FL │ GAINESVILLE REG AR…│ │ │ │ Common nighthawk │ 1 │ CA │ LONG BEACH-DAUGH F…│ Landing Roll │ 0 │ │ Red-tailed hawk │ 1 │ N MD │ BALTIMORE/WASH INT…│ Take-off run │ 0 │ 140 │ Hawks │ 1 │ N IL │ CHICAGO O'HARE INT…│ │ │ │ Red-tailed hawk │ 1 │ 2› faa-wildlife-strikes_selectedref| " 2164 rows
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
STATEcolumn - 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:
File Edit View Column Row Data Plot System Help | VisiData 3.0.2 | Alt+H for help men 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 │ ■■■■ ║ 3› faa-wildlife-strikes_STATE_freq| j go-down 63 bins
From there, pressing Enter should create the filtered sheet we wanted:
File Edit View Column Row Data Plot System Help | VisiData 3.0.2 | Alt+H for help men <STATE │ AIRPORT │ PHASE_OF_FLT │ HEIGHT │ SPEED │ SPECIES │ BIRDS_STRUCK │ > CA │ NORMAN Y. MINETA S…│ │ │ │ Gulls │ 1 │ N CA │ METRO OAKLAND INTL │ │ │ │ Unknown bird │ 1 │ N CA │ LOS ANGELES INTL │ CLIMB │ │ │ Unknown bird │ 1 │ P CA │ LONG BEACH-DAUGH F…│ LANDING ROLL │ 0 │ │ Unknown bird │ 1 │ N CA │ NAPA COUNTY ARPT │ LANDING ROLL │ 0 │ │ Unknown bird │ 1 │ N CA │ HAYWARD EXECUTIVE …│ Climb │ │ │ Unknown bird - sma…│ 1 │ P CA │ ONTARIO INTL ARPT │ │ │ │ Owls │ 1 │ N CA │ LOS ANGELES INTL │ APPROACH │ │ │ Unknown bird │ 1 │ N CA │ LIVERMORE MUNICIPA…│ TAKE-OFF RUN │ 0 │ │ Unknown bird │ 2-10 │ N CA │ WHITEMAN AIRPORT │ LANDING ROLL │ 0 │ │ Rock pigeon │ 1 │ N CA │ LIVERMORE MUNICIPA…│ APPROACH │ │ │ Unknown bird │ 1 │ N CA │ JOHN WAYNE-ORANGE …│ APPROACH │ 1700 │ │ Unknown bird - med…│ 1 │ N CA │ CHINO AIRPORT │ │ │ │ Unknown bird │ 1 │ N CA │ SAN FRANCISCO INTL…│ LANDING ROLL │ 0 │ │ Unknown bird │ 1 │ N CA │ SACRAMENTO INTL │ APPROACH │ │ │ Unknown bird │ 1 │ N CA │ SANTA MONICA MUNIC…│ APPROACH │ 60 │ │ Unknown bird │ 1 │ N CA │ SACRAMENTO INTL │ │ │ │ Unknown bird │ 1 │ N CA │ SACRAMENTO INTL │ Climb │ 1500 │ 210 │ Northern pintail │ 2-10 │ P CA │ LOS ANGELES INTL │ Approach │ 900 │ 135 │ Unknown bird - med…│ 1 │ N CA │ SAN FRANCISCO INTL…│ Landing Roll │ 0 │ 90 │ Black-tailed jackr…│ 1 │ N CA │ SACRAMENTO INTL │ Approach │ 1000 │ 135 │ Northern pintail │ 1 │ N 4› faa-wildlife-strikes_CA| Enter 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? Select the rows of the frequency table you want to include, then press g + Enter.
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 achieve that, you could take these steps:
- On the main data sheet navigate to the
AIRPORTcolumn, and press Shift-F to create a frequency table:
File Edit View Column Row Data Plot System Help | VisiData 3.0.2 | Alt+H for help men 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 │ ■■■ ║ 5› faa-wildlife-strikes_AIRPORT_freq| processing… Shift+F 1512 bins
- Then, select the top five entries (skipping
UNKNOWN) using s:
File Edit View Column Row Data Plot System Help | VisiData 3.0.2 | Alt+H for help men 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 │ ■■■ ┌─────────────────────────────| statuses |─┐ DETROIT METRO WAYN…║ 734 │ 1.00 │ ■■■ │ selected 1217 more rows │ BALTIMORE/WASH INT…║ 691 │ 0.94 │ ■■■ └──────────────────────────────────────────┘ 5› faa-wildlife-strikes_AIRPORT_freq| s select-row 1512 bins •5
- Next, press g + Enter. The result should look like this, with 9,250 rows (the total of those five airports):
File Edit View Column Row Data Plot System Help | VisiData 3.0.2 | Alt+H for help men <STATE │ AIRPORT │ PHASE_OF_FLT │ HEIGHT │ SPEED │ SPECIES │ BIRDS_STRUCK │ > CO │ DENVER INTL AIRPORT│ LANDING ROLL │ 0 │ │ Unknown bird │ 1 │ N CO │ DENVER INTL AIRPORT│ Approach │ │ │ Unknown bird │ 1 │ N CO │ DENVER INTL AIRPORT│ LANDING ROLL │ 0 │ │ Unknown bird │ 1 │ N CO │ DENVER INTL AIRPORT│ DEPARTURE │ │ │ Unknown bird - sma…│ 1 │ N CO │ DENVER INTL AIRPORT│ Landing Roll │ 0 │ │ Ferruginous hawk │ 1 │ CO │ DENVER INTL AIRPORT│ DEPARTURE │ │ │ Unknown bird - sma…│ 1 │ N CO │ DENVER INTL AIRPORT│ LANDING ROLL │ 0 │ │ Unknown bird - sma…│ 2-10 │ N CO │ DENVER INTL AIRPORT│ APPROACH │ │ │ Unknown bird │ 1 │ N CO │ DENVER INTL AIRPORT│ │ │ │ Northern harrier │ 1 │ CO │ DENVER INTL AIRPORT│ Take-off run │ 0 │ │ Unknown bird - med…│ 1 │ CO │ DENVER INTL AIRPORT│ │ │ │ Great horned owl │ 1 │ CO │ DENVER INTL AIRPORT│ │ │ │ Short-eared owl │ 1 │ CO │ DENVER INTL AIRPORT│ Approach │ │ │ Horned lark │ 1 │ CO │ DENVER INTL AIRPORT│ │ │ │ Horned lark │ 1 │ CO │ DENVER INTL AIRPORT│ Climb │ 500 │ 150 │ Unknown bird - sma…│ 1 │ N CO │ DENVER INTL AIRPORT│ │ │ │ Western meadowlark │ 1 │ CO │ DENVER INTL AIRPORT│ │ │ │ Western meadowlark │ 2-10 │ CO │ DENVER INTL AIRPORT│ Landing Roll │ 0 │ │ Mourning dove │ 1 │ CO │ DENVER INTL AIRPORT│ Take-off run │ 0 │ 145 │ Unknown bird - sma…│ 1 │ N CO │ DENVER INTL AIRPORT│ │ │ │ Red-tailed hawk │ 1 │ CO │ DENVER INTL AIRPORT│ │ │ │ Horned lark │ 1 │ 6› faa-wildlife-strikes_several| gEnter 9250 rows