Reshaping Data

VisiData provides several ways to reshape your data:

  • Creating pivot tables
  • Melting datasets
  • Transposing columns and rows

Creating pivot tables

VisiData’s pivot tables are similar to pivot tables you might have created in various spreadsheet programs. Pivot tables create a cross-tabulation of two or more columns in a dataset.

In VisiData, creating a pivot table involves the following steps:

  • Use ! to designate the column(s) you want to serve as the pivot table’s rows.
  • Optional: Use + to specify additional metrics you want the pivot table to calculate. (By default, the pivot table’s sole metric will be the overall count for each grouping.)
  • Navigate to the column you want to serve as the pivot table’s columns.
  • Press Shift-W

Let’s say we want to cross-tabulate species by whether their remains were collected. First, let’s designate the SPECIES column as a key column:

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

Then, navigate to the REMAINS_COLLECTED column, either by tapping l or the right-arrow until we get there, or by typing c followed by REMAINS and then Enter:

 SPECIES            ║<EFFECT             | DAMAGE | COST_REPAIRS | PERSON | REMAINS_COLLECTED | RE… 
 Unknown bird       ║ NONE               | M      |              | Tower  | 0                 | N9… 
 Unknown bird        NONE               | M      |              | Tower  | 0                 | ON… 
 Unknown bird        NONE               | M      |              | Tower  | 0                 | N9… 
 Unknown bird        NONE               | M      |              | Tower  | 0                 | NO… 
 Unknown bird                           | M      |              | Tower  | 0                 | ME… 
 Unknown bird        Other              | M?     |              | Tower  | 0                 | DA… 
 Unknown bird        NONE               | M?     |              | Tower  | 0                 | DA… 
 Unknown bird        PRECAUTIONARY LAND…| M?     |              | Tower  | 0                 | N9… 
 Unknown bird        NONE               | M?     |              | Tower  | 0                 | AA… 
 Owls                NONE               | M?     |              | Tower  | 0                 | LO… 
 Hawks               NONE               | M?     |              | Tower  | 0                 | N5… 
 Gulls               NONE               | M?     |              | Tower  | 0                 | PO… 
 Unknown bird - sma… NONE               | N      |              | Tower  | 0                 | PI… 
 Unknown bird - sma… NONE               | N      |              | Tower  | 0                 | AS… 
 Unknown bird - sma… NONE               | N      |              | Tower  | 0                 | AS… 
 Unknown bird        PRECAUTIONARY LAND…| N      |              | Tower  | 0                 | CM… 
 Unknown bird        NONE               | N      |              | Tower  | 0                 | DA… 
 Unknown bird        NONE               | N      |              | Tower  | 0                 | De… 
 Unknown bird        NONE               | N      |              | Tower  | 0                 | DL… 
 Unknown bird        NONE               | N      |              | Tower  | 0                 | AI… 
 Unknown bird        PRECAUTIONARY LAND…| N      |              | Tower  | 0                 | Ai… 
 Unknown bird - sma… NONE               | N      |              | Tower  | 0                 | HI… 
1› faa-wildlife-strikes| "REMAINS"                                  c  go-col-regex     73448 rows  

Now, press Shift+W to create the pivot table:

 SPECIES             0  #| 1 #                                                                    
 Unknown bird       ║ 6677| 393║                                                                    
 Owls                 36 | 23                                                                     
 Hawks               202 | 106                                                                    
 Gulls               559 | 525                                                                    
 Unknown bird - sma… 125…| 15…                                                                    
 Sparrows            346 | 542                                                                    
 Sandhill crane       15 | 26                                                                     
 Unknown bird - med… 3585| 322                                                                    
 Unknown bird - lar… 769 | 58                                                                     
 Barn swallow        246 | 28…                                                                    
 Rock pigeon         149 | 10…                                                                    
 Barn owl             22 | 517                                                                    
 Bank swallow         14 | 267                                                                    
 Swallows            152 | 302                                                                    
 Horned lark          56 | 26…                                                                    
 Northern pintail      0 | 93                                                                     
 American robin       32 | 644                                                                    
 American kestrel     81 | 25…                                                                    
 Microbats            43 | 443                                                                    
 Mourning dove       200 | 39…                                                                    
 Free-tailed bats      1 | 50                                                                     
 Red-tailed hawk     103 | 10…                                                                    
2› faa-wildlife-strikes_pivot_REMAINS_COLLECTED_count|                  W         641 grouped rows  

… and g_ to auto-adjust the column widths:

 SPECIES                0    #| 1   #                                                             
 Unknown bird          ║  6677 |  393 ║                                                             
 Owls                      36 |   23                                                              
 Hawks                    202 |  106                                                              
 Gulls                    559 |  525                                                              
 Unknown bird - small   12544 | 1523                                                              
 Sparrows                 346 |  542                                                              
 Sandhill crane            15 |   26                                                              
 Unknown bird - medium   3585 |  322                                                              
 Unknown bird - large     769 |   58                                                              
 Barn swallow             246 | 2838                                                              
 Rock pigeon              149 | 1008                                                              
 Barn owl                  22 |  517                                                              
 Bank swallow              14 |  267                                                              
 Swallows                 152 |  302                                                              
 Horned lark               56 | 2634                                                              
 Northern pintail           0 |   93                                                              
 American robin            32 |  644                                                              
 American kestrel          81 | 2506                                                              
 Microbats                 43 |  443                                                              
 Mourning dove            200 | 3909                                                              
 Free-tailed bats           1 |   50                                                              
 Red-tailed hawk          103 | 1085                                                              
2› faa-wildlife-strikes_pivot_REMAINS_COLLECTED_count|  g_  resize-cols-max       641 grouped rows  

The rows of the pivot table represent each species, while the columns represent the number of rows for each species that fall into each REMAINS_COLLECTED category.

This is a simple pivot table, since REMAINS_COLLECTED can only be either 0 or 1, but pivot tables on more complex columns can end up much wider.

Note

The order of the columns in a pivot table is based on the order the relevant values appear in the source sheet. If you want them to appear, instead, in alphabetical order, sort the source sheet’s relevant column(s) first.

Melting datasets

To “melt” a dataset is to reshape it from a “wide” format to a “long” one, specifically by converting each value in each column into its own row. If that concept is unfamiliar, the example below should help clarify.

Melting a dataset in VisiData involves the following steps:

  • Optional: Use ! to designate the column(s) you want to keep unmelted.
  • Optional: Use - to hide the columns you don’t want to appear, at all, in the melted sheet.
  • Press Shift+M

If you skip the optional steps, pressing Shift-M on the original faa-wildlife-strikes.csv dataset creates this melted sheet:

 Variable          | Value                                                                          
 OPERATOR          | BUSINESS                                                                       
 ATYPE             | PA-28                                                                          
 INCIDENT_DATE     | 05/22/15 00:00:00                                                              
 STATE             | FL                                                                             
 AIRPORT           | VERO BEACH MUNICIPAL                                                           
 PHASE_OF_FLT      | APPROACH                                                                       
 HEIGHT            |                                                                                
 SPEED             |                                                                                
 SPECIES           | Unknown bird                                                                   
 BIRDS_STRUCK      | 1                                                                              
 EFFECT            | NONE                                                                           
 DAMAGE            | M                                                                              
 COST_REPAIRS      |                                                                                
 PERSON            | Tower                                                                          
 REMAINS_COLLECTED | 0                                                                              
 REMARKS           | N9240F was right base to final on Runway 4, and he reported a bird strike to … 
 OPERATOR          | BUSINESS                                                                       
 ATYPE             | BE-1900                                                                        
 INCIDENT_DATE     | 06/18/15 00:00:00                                                              
 STATE             | AK                                                                             
 AIRPORT           | KENAI MUNICIPAL ARPT                                                           
 PHASE_OF_FLT      | APPROACH                                                                       
3› faa-wildlife-strikes_melted|                                        M     1175168 melted values  

Now let’s examine how the optional steps affect melting. Press q to return to the source sheet, and press ! on each of the first two columns (OPERATOR and ATYPE):

 OPERATOR           | ATYPE               INCIDENT_DATE     | STATE | AIRPORT            | PHASE_> 
 BUSINESS           | PA-28              ║ 05/22/15 00:00:00 | FL    | VERO BEACH MUNICIP…| APPROA… 
 BUSINESS           | BE-1900             06/18/15 00:00:00 | AK    | KENAI MUNICIPAL AR…| APPROA… 
 BUSINESS           | PA-46 MALIBU        09/20/15 00:00:00 | TX    | DAVID WAYNE HOOKS …|         
 DELTA AIR LINES    | B-717-200           11/07/15 00:00:00 | MO    | LAMBERT-ST LOUIS I…| APPROA… 
 BUSINESS           | BE-90 KING          12/17/15 00:00:00 | FL    | POMPANO BEACH AIRP…| LANDIN… 
 DELTA AIR LINES    | B-757               07/17/15 00:00:00 | VI    | HENRY E ROHLSEN AR…|         
 DELTA AIR LINES    | B-717-200           08/02/15 00:00:00 | TX    | SAN ANTONIO INTL   | APPROA… 
 BUSINESS           | C-414               08/03/15 00:00:00 | TX    | LONE STAR EXECUTIV…| DEPART… 
 ALLEGIANT AIR      | MD-80               09/02/15 00:00:00 | FL    | TAMPA INTL         | APPROA… 
 TRANS STATES AIRLI…| EMB-145             09/07/15 00:00:00 | MO    | LAMBERT-ST LOUIS I…| APPROA… 
 BUSINESS           | C-172               11/28/15 00:00:00 | FL    | OPA-LOCKA EXECUTIV…| APPROA… 
 GOVERNMENT         | EC120               12/08/15 00:00:00 | CA    | NORMAN Y. MINETA S…|         
 AMERICAN AIRLINES  | A-321               05/06/15 00:00:00 | FL    | FORT LAUDERDALE/HO…| APPROA… 
 EXPRESSJET AIRLINES| CRJ100/200          05/06/15 00:00:00 | AR    | FORT SMITH REGIONA…| CLIMB   
 MESA AIRLINES      | CRJ900              05/08/15 00:00:00 | AR    | BILL AND  HILLARY …| LANDIN… 
 BUSINESS           | HELICOPTER          05/06/15 00:00:00 |       | UNKNOWN            | En Rou… 
 DELTA AIR LINES    | A-320               05/07/15 00:00:00 | CA    | METRO OAKLAND INTL |         
 DELTA AIR LINES    | A-320               05/08/15 00:00:00 | UT    | SALT LAKE CITY INTL|         
 LUFTHANSA          | A-380               05/10/15 00:00:00 | TX    | GEORGE BUSH INTERC…| CLIMB   
 BUSINESS           | C-172               05/08/15 00:00:00 | FL    | ORLANDO SANFORD IN…| APPROA… 
 SPIRIT AIRLINES    | A-319               05/10/15 00:00:00 | IL    | CHICAGO O'HARE INT…| CLIMB   
 EXPRESSJET AIRLINES| EMB-145             05/11/15 00:00:00 | AL    | BIRMINGHAM-SHUTTLE…| LANDIN… 
1› faa-wildlife-strikes|                                                 !  key-col     73448 rows  

Then use - (or the Columns Sheet) to hide all the other columns except for STATE and AIRPORT:

 OPERATOR           | ATYPE              ║<AIRPORT                                                 
 BUSINESS           | PA-28               VERO BEACH MUNICIP…                                     
 BUSINESS           | BE-1900             KENAI MUNICIPAL AR…                                     
 BUSINESS           | PA-46 MALIBU        DAVID WAYNE HOOKS …                                     
 DELTA AIR LINES    | B-717-200           LAMBERT-ST LOUIS I…                                     
 BUSINESS           | BE-90 KING          POMPANO BEACH AIRP…                                     
 DELTA AIR LINES    | B-757               HENRY E ROHLSEN AR…                                     
 DELTA AIR LINES    | B-717-200           SAN ANTONIO INTL                                        
 BUSINESS           | C-414               LONE STAR EXECUTIV…                                     
 ALLEGIANT AIR      | MD-80               TAMPA INTL                                              
 TRANS STATES AIRLI…| EMB-145             LAMBERT-ST LOUIS I…                                     
 BUSINESS           | C-172               OPA-LOCKA EXECUTIV…                                     
 GOVERNMENT         | EC120               NORMAN Y. MINETA S…                                     
 AMERICAN AIRLINES  | A-321               FORT LAUDERDALE/HO…                                     
 EXPRESSJET AIRLINES| CRJ100/200          FORT SMITH REGIONA…                                     
 MESA AIRLINES      | CRJ900              BILL AND  HILLARY …                                     
 BUSINESS           | HELICOPTER          UNKNOWN                                                 
 DELTA AIR LINES    | A-320               METRO OAKLAND INTL                                      
 DELTA AIR LINES    | A-320               SALT LAKE CITY called to report a birdstrike on DAL2384… 
 LUFTHANSA          | A-380               DLH441 reported receiving bird strikes climbing through… 
 BUSINESS           | C-172               AIRCRAFT ENCOUNTERED BIRD STRIKE OUTSIDE 5 MILES OF SFB… 
 SPIRIT AIRLINES    | A-319               Aircraft reported bird strike climbing out of 12000ft a… 
 EXPRESSJET AIRLINES| EMB-145             HIT A SMALL BIRD IN THE FLARE NO DAMAGE.                 
1› faa-wildlife-strikes|                                                -  hide-col     73448 rows  

Now press Shift-M. In the resulting melted sheet, OPERATOR and ATYPE (the columns you keyed with !) are preserved as standard columns while STATE and AIRPORT have been converted to Variable-Value pairs:

 OPERATOR           | ATYPE               Variable | Value                                        
 BUSINESS           | PA-28              ║ STATE    | FL                              ║             
 BUSINESS           | PA-28               AIRPORT  | VERO BEACH MUNICIPAL                         
 BUSINESS           | BE-1900             STATE    | AK                                           
 BUSINESS           | BE-1900             AIRPORT  | KENAI MUNICIPAL ARPT                         
 BUSINESS           | PA-46 MALIBU        STATE    | TX                                           
 BUSINESS           | PA-46 MALIBU        AIRPORT  | DAVID WAYNE HOOKS MEMORIAL ARPT              
 DELTA AIR LINES    | B-717-200           STATE    | MO                                           
 DELTA AIR LINES    | B-717-200           AIRPORT  | LAMBERT-ST LOUIS INTL                        
 BUSINESS           | BE-90 KING          STATE    | FL                                           
 BUSINESS           | BE-90 KING          AIRPORT  | POMPANO BEACH AIRPARK                        
 DELTA AIR LINES    | B-757               STATE    | VI                                           
 DELTA AIR LINES    | B-757               AIRPORT  | HENRY E ROHLSEN ARPT                         
 DELTA AIR LINES    | B-717-200           STATE    | TX                                           
 DELTA AIR LINES    | B-717-200           AIRPORT  | SAN ANTONIO INTL                             
 BUSINESS           | C-414               STATE    | TX                                           
 BUSINESS           | C-414               AIRPORT  | LONE STAR EXECUTIVE ARPT                     
 ALLEGIANT AIR      | MD-80               STATE    | FL                                           
 ALLEGIANT AIR      | MD-80               AIRPORT  | TAMPA INTL                                   
 TRANS STATES AIRLI…| EMB-145             STATE    | MO                                           
 TRANS STATES AIRLI…| EMB-145             AIRPORT  | LAMBERT-ST LOUIS INTL                        
 BUSINESS           | C-172               STATE    | FL                                           
 BUSINESS           | C-172               AIRPORT  | OPA-LOCKA EXECUTIVE ARPT                     
4› faa-wildlife-strikes_melted|                                        M      146896 melted values  

Transposing columns and rows

In VisiData, you can press Shift-T to “transpose” any given sheet, essentially rotating the struture 90 degrees, so that the rows are represented as columns (and vice versa).

Pressing Shift-T on the original faa-wildlife-strikes.csv dataset should give you this result:

                                       |                    |                    |               > 
 OPERATOR          ║ BUSINESS           | BUSINESS           | BUSINESS           | DELTA AIR LINES 
 ATYPE              PA-28              | BE-1900            | PA-46 MALIBU       | B-717-200       
 INCIDENT_DATE      05/22/15 00:00:00  | 06/18/15 00:00:00  | 09/20/15 00:00:00  | 11/07/15 00:00… 
 STATE              FL                 | AK                 | TX                 | MO              
 AIRPORT            VERO BEACH MUNICIP…| KENAI MUNICIPAL AR…| DAVID WAYNE HOOKS …| LAMBERT-ST LOU… 
 PHASE_OF_FLT       APPROACH           | APPROACH           |                    | APPROACH        
 HEIGHT                                |                    |                    |                 
 SPEED                                 |                    |                    |                 
 SPECIES            Unknown bird       | Unknown bird       | Unknown bird       | Unknown bird    
 BIRDS_STRUCK       1                  | 1                  | 1                  | 1               
 EFFECT             NONE               | NONE               | NONE               | NONE            
 DAMAGE             M                  | M                  | M                  | M               
 COST_REPAIRS                          |                    |                    |                 
 PERSON             Tower              | Tower              | Tower              | Tower           
 REMAINS_COLLECTED  0                  | 0                  | 0                  | 0               
 REMARKS            N9240F was right b…| ON FINAL, PILOT RE…| N952G, P46T/G, bir…| NO EMERGENCY: … 
                                                                                                    
                                                                                                    
                                                                                                    
                                                                                                    
                                                                                                    
                                                                                                    
5› faa-wildlife-strikes_T|                                                      T          16 rows  

If your source sheet has a key column, the values in that column will become the headers for the transposed sheet. For instance, here’s the frequency table (with Shift-F) for the dataset’s OPERATOR column:

 OPERATOR           ↓count♯| percent%| histogram                                         ~        
 UNKNOWN            ║ 23076 |   31.42 | ************************************************** ║        
 SOUTHWEST AIRLINES   7752 |   10.55 | ****************                                           
 BUSINESS             5868 |    7.99 | ************                                               
 AMERICAN AIRLINES    4337 |    5.90 | *********                                                  
 DELTA AIR LINES      2817 |    3.84 | ******                                                     
 FEDEX EXPRESS        2709 |    3.69 | *****                                                      
 UNITED AIRLINES      2194 |    2.99 | ****                                                       
 US AIRWAYS           1885 |    2.57 | ****                                                       
 UPS AIRLINES         1773 |    2.41 | ***                                                        
 SKYWEST AIRLINES     1769 |    2.41 | ***                                                        
 JETBLUE AIRWAYS      1740 |    2.37 | ***                                                        
 EXPRESSJET AIRLINES  1347 |    1.83 | **                                                         
 AMERICAN EAGLE AIR…  1041 |    1.42 | **                                                         
 ENVOY AIR             883 |    1.20 | *                                                          
 ALASKA AIRLINES       835 |    1.14 | *                                                          
 REPUBLIC AIRLINES     804 |    1.09 | *                                                          
 MESA AIRLINES         693 |    0.94 | *                                                          
 AIR WISCONSIN AIRL…   623 |    0.85 | *                                                          
 PSA AIRLINES          577 |    0.79 | *                                                          
 PRIVATELY OWNED       516 |    0.70 | *                                                          
 PHI INC               491 |    0.67 | *                                                          
 SHUTTLE AMERICA       467 |    0.64 | *                                                          
6› faa-wildlife-strikes_OPERATOR_freq|                                          F         282 bins  

It has OPERATOR as its key column, so transposing this sheet should result in something like this:

 OPERATOR   UNKNOWN            | SOUTHWEST AIRLINES | BUSINESS          | AMERICAN AIRLINES | DEL> 
 count     ║ [23076]            | [7752]             | [5868]            | [4337]            | [28… 
 percent    31.41814617144102 %| 10.554405838143992%| 7.989325781505283%| 5.904857858621066%| 3.8… 
 histogram  ******************…| ****************   | ************      | *********         | ***… 
                                                                                                    
                                                                                                    
                                                                                                    
                                                                                                    
                                                                                                    
                                                                                                    
                                                                                                    
                                                                                                    
                                                                                                    
                                                                                                    
                                                                                                    
                                                                                                    
                                                                                                    
                                                                                                    
                                                                                                    
                                                                                                    
                                                                                                    
                                                                                                    
                                                                                                    
7› faa-wildlife-strikes_OPERATOR_freq_T|                                        T           3 rows  

Note

If your source sheet has multiple key columns, VisiData will join together the columns’ values with the _ character to create the header names.