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  •0 

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  •0 

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  •0 

… 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_coun g_  resize-cols-max       641 grouped rows  •0 

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  •0 

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_OF_FLT> 
 BUSINESS           | PA-28        ║ 05/22/15 00:00:00 | FL    | VERO BEACH MUNICIP…| APPROACH     ║
 BUSINESS           | BE-1900       06/18/15 00:00:00 | AK    | KENAI MUNICIPAL AR…| APPROACH      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…| APPROACH      BUSINESS           | BE-90 KING    12/17/15 00:00:00 | FL    | POMPANO BEACH AIRP…| LANDING ROLL  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   | APPROACH      BUSINESS           | C-414         08/03/15 00:00:00 | TX    | LONE STAR EXECUTIV…| DEPARTURE     ALLEGIANT AIR      | MD-80         09/02/15 00:00:00 | FL    | TAMPA INTL         | APPROACH      TRANS STATES AIRLI…| EMB-145       09/07/15 00:00:00 | MO    | LAMBERT-ST LOUIS I…| APPROACH      BUSINESS           | C-172         11/28/15 00:00:00 | FL    | OPA-LOCKA EXECUTIV…| APPROACH      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…| APPROACH      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 …| LANDING ROLL  BUSINESS           | HELICOPTER    05/06/15 00:00:00 |       | UNKNOWN            | En Route      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…| APPROACH      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…| LANDING ROLL 1› faa-wildlife-strikes|                                              !  key-col     73448 rows  •0 

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

 OPERATOR           | ATYPE         STATE | AIRPORT                                               
 BUSINESS           | PA-28        ║ FL    | VERO BEACH MUNICIP…║                                   
 BUSINESS           | BE-1900       AK    | KENAI MUNICIPAL AR…                                   
 BUSINESS           | PA-46 MALIBU  TX    | DAVID WAYNE HOOKS …                                   
 DELTA AIR LINES    | B-717-200     MO    | LAMBERT-ST LOUIS I…                                   
 BUSINESS           | BE-90 KING    FL    | POMPANO BEACH AIRP…                                   
 DELTA AIR LINES    | B-757         VI    | HENRY E ROHLSEN AR…                                   
 DELTA AIR LINES    | B-717-200     TX    | SAN ANTONIO INTL                                      
 BUSINESS           | C-414         TX    | LONE STAR EXECUTIV…                                   
 ALLEGIANT AIR      | MD-80         FL    | TAMPA INTL                                            
 TRANS STATES AIRLI…| EMB-145       MO    | LAMBERT-ST LOUIS I…                                   
 BUSINESS           | C-172         FL    | OPA-LOCKA EXECUTIV…                                   
 GOVERNMENT         | EC120         CA    | NORMAN Y. MINETA S…                                   
 AMERICAN AIRLINES  | A-321         FL    | FORT LAUDERDALE/HO…                                   
 EXPRESSJET AIRLINES| CRJ100/200    AR    | FORT SMITH REGIONA…                                   
 MESA AIRLINES      | CRJ900        AR    | BILL AND  HILLARY …                                   
 BUSINESS           | HELICOPTER          | UNKNOWN                                               
 DELTA AIR LINES    | A-320         CA    | METRO OAKLAND INTL                                    
 DELTA AIR LINES    | A-320         UT    | SALT LAKE CITY INTL                                   
 LUFTHANSA          | A-380         TX    | GEORGE BUSH INTERC…                                   
 BUSINESS           | C-172         FL    | ORLANDO SANFORD IN…                                   
 SPIRIT AIRLINES    | A-319         IL    | CHICAGO O'HARE INT…                                   
 EXPRESSJET AIRLINES| EMB-145       AL    | BIRMINGHAM-SHUTTLE…                                   
1› faa-wildlife-strikes|                                         gh  go-leftmost     73448 rows  •0 

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  •0 

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  •0 

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  •0 

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  •0 

Note

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