# How to work with tables

_This page introduces tables in q and demonstrates how to create and manipulate them efficiently._

Tables are first-class objects in q, meaning they are data structures that live in memory just like lists or [dictionaries](dictionaries.md).

You can view a table from two perspectives:

1. **A list of rows/records**: where each record is a dictionary with identical keys.
1. **A list of columns**: where each column is a named list of equal length.

Internally, q implements a table as a flipped dictionary. Because operations on columns are vectorized, this structure runs a calculation across millions of records in a single pass over contiguous memory. A downside of this design is that row deletion is relatively expensive, as all the column lists must be compacted to close the resulting gap.

The column elements are called _items_ or _fields_.

## Table creation

There are three primary ways to initialize a table in memory.

### 1. From a list of dictionaries

q automatically interprets a list of conforming dictionaries (sharing the same keys) as a table.


**q**
```q
q)d1: ([name: `Alice; phone: "555-0101"; age: 23])
q)d2: ([name: `Bob; phone: "555-0723"; age: 32])
q)d3: ([name: `Mike; phone: "555-6666"; age: 22])
q)(d1; d2; d3)
name  phone      age
--------------------
Alice "555-0101" 23
Bob   "555-0723" 32
Mike  "555-6666" 22
```


**pandas**
```python
>>> d1 = {'name': 'Alice', 'phone': '555-0101', 'age': 23}
>>> d2 = {'name': 'Bob', 'phone': '555-0723', 'age': 32}
>>> d3 = {'name': 'Mike', 'phone': '555-6666', 'age': 22}
>>> pd.DataFrame([d1, d2, d3])
    name     phone  age
0  Alice  555-0101   23
1    Bob  555-0723   32
2   Mike  555-6666   22
```


### 2. By flipping a column dictionary

Since a table is a flipped dictionary, you can create one by applying [flip](../../../ref/flip.md) to a dictionary of lists of the same size:


**q**
```q
q)flip ([name: `Alice`Bob`Mike; phone: ("555-0101"; "555-0723"; "555-6666"); age: 23 32 22])
name  phone      age
--------------------
Alice "555-0101" 23
Bob   "555-0723" 32
Mike  "555-6666" 22
```


**pandas**
```python
>>> pd.DataFrame({
...     'name': ['Alice', 'Bob', 'Mike'],
...     'phone': ['555-0101', '555-0723', '555-6666'],
...     'age': [23, 32, 22]
... })
    name     phone  age
0  Alice  555-0101   23
1    Bob  555-0723   32
2   Mike  555-6666   22
```


### 3. Table definition syntax (recommended)

This is the most concise and idiomatic way to define a table. The `[]` brackets denote an unkeyed (simple) table.


**q**
```q
q)([] name: `Alice`Bob`Mike; age: 23 32 22; city:`Dublin)
name  age city
----------------
Alice 23  Dublin
Bob   32  Dublin
Mike  22  Dublin
```


**pandas**
```python
>>> pd.DataFrame({
...     'name': ['Alice', 'Bob', 'Mike'],
...     'age': [23, 32, 22],
...     'city': 'Dublin'
... })
    name  age    city
0  Alice   23  Dublin
1    Bob   32  Dublin
2   Mike   22  Dublin
```


> **Note**
>
> If a column is provided as an atom (like ```city:`Dublin``` above), q automatically extends it to match the length of the other columns.
>
### Implicit column names

Table definition also makes it convenient to create a table from variables.
```q
q)name: `Alice`Bob`Mike
q)phone: ("555-0101"; "555-0723"; "555-6666")
q)age: 23 32 22
q)([] name; phone; age)
name  phone      age
--------------------
Alice "555-0101" 23
Bob   "555-0723" 32
Mike  "555-6666" 22
```

q assigns default column names when you omit them.
```q
q)([] 1 2; `a`b)
x x1
----
1 a
2 b
```

### Empty tables

You can create empty tables from empty lists:
```q
q)([] name: `$(); phone: (); age: `long$())
name phone age
--------------
```

Specify the type of each column in an empty table. You can use the [each-left iterator](../../../ref/maps.md#each-left-and-each-right) and [type characters](../../../ref/index.md#datatypes) to avoid repeating empty list specifications for each column:


**q**
```q
q)flip `name`phone`age!"s*j"$\:()
name phone age
--------------
```


**pandas**
```python
>>> pd.DataFrame({
...     'name': pd.Series(dtype='str'),
...     'phone': pd.Series(dtype='object'),
...     'age': pd.Series(dtype='int64')
... })
Empty DataFrame
Columns: [name, phone, age]
Index: []
```


For wider tables, it is more convenient to define a schema dictionary:


**q**
```q
q)schema: ([name:"s"; phone:"*"; age: "j"])
q)flip schema$\:()
name phone age
--------------
```


**pandas**
```python
>>> schema = {'name': 'str', 'phone': 'object', 'age': 'int64'}
>>> pd.DataFrame({
...     col: pd.Series(dtype=dt)
...     for col, dt in schema.items()
... })
Empty DataFrame
Columns: [name, phone, age]
Index: []
```


## Table metadata

Understanding the structure of your data is critical for debugging and optimization.

- [cols](../../../ref/cols.md): Returns the column names as a list of symbols.
- [meta](../../../ref/meta.md): Returns a keyed table describing column types, foreign keys, and attributes.
- [tables](../../../ref/tables.md): Lists all tables defined in the namespace provided.


**q**
```q
q)t:([] name: `Alice`Bob`Mike; phone: ("555-0101"; "555-0723"; "555-6666"); age: 23 32 22)
q)meta t
c    | t f a
-----| -----
name | s
phone| C
age  | j
```


**pandas**
```python
>>> t = pd.DataFrame({'name': ['Alice', 'Bob', 'Mike'], 'phone': ['555-0101', '555-0723', '555-6666'], 'age': [23, 32, 22]})
>>> t.dtypes
name     object
phone    object
age       int64
dtype: object
```


| Column | Description |
| :--- | :--- |
| `c` | column names |
| `t` | data type character (lowercase for atoms, uppercase for nested lists) |
| `f` | foreign key link (if any) |
| `a` | applied [attributes](../../../ref/attr.md) (e.g., `s` for sorted, `u` for unique) |

### Renaming and reordering columns

Rename columns with [`xcol`](../../../ref/cols.md#xcol) and reorder them with [`xcols`](../../../ref/cols.md#xcols):


**q**
```q
q)([name: `nickname]) xcol t
nickname phone      age
-----------------------
Alice    "555-0101" 23
Bob      "555-0723" 32
Mike     "555-6666" 22
q)`name`age xcols t
name  age phone
--------------------
Alice 23  "555-0101"
Bob   32  "555-0723"
Mike  22  "555-6666"
```


**pandas**
```python
>>> t.rename(columns={'name': 'nickname'})
  nickname     phone  age
0    Alice  555-0101   23
1      Bob  555-0723   32
2     Mike  555-6666   22
>>> t[['name', 'age'] + [ c for c in t.columns if c not in ['name', 'age']]]
    name  age     phone
0  Alice   23  555-0101
1    Bob   32  555-0723
2   Mike   22  555-6666
```


## Appending records

Functions [insert](../../../ref/insert.md) and [upsert](../../../ref/upsert.md) append a single record or a batch of records to a table in place. `insert` throws an error if the table does not exist.


**q**
```q
q)t:([] name: `Alice`Bob`Mike; phone: ("555-0101"; "555-0723"; "555-6666"); age: 23 32 22)
q)`t insert (`George`David; ("555-4000"; "555-4001"); 18 19)  / in-place batch append
3 4
q)`t upsert ((`Phil;"555-2341"; 44); (`Ryan;"555-6267";55))
`t
q)t
name   phone      age
---------------------
Alice  "555-0101" 23
Bob    "555-0723" 32
Mike   "555-6666" 22
George "555-4000" 18
David  "555-4001" 19
Phil   "555-2341" 44
Ryan   "555-6267" 55
```


**pandas**
```python
>>> t = pd.DataFrame({'name': ['Alice', 'Bob', 'Mike'], 'phone': ['555-0101', '555-0723', '555-6666'], 'age': [23, 32, 22]})
>>> t = pd.concat([t, pd.DataFrame(dict(zip(t.columns, [['George', 'David'], ['555-4000', '555-4001'], [18, 19]])))], ignore_index=True)

>>> t = pd.concat([t, pd.DataFrame([['Phil', '555-2341', 44], ['Ryan', '555-6267', 55]], columns=t.columns)], ignore_index=True)

>>> t
     name     phone  age
0   Alice  555-0101   23
1     Bob  555-0723   32
2    Mike  555-6666   22
3  George  555-4000   18
4   David  555-4001   19
5    Phil  555-2341   44
6    Ryan  555-6267   55
```


Because tables are lists, you can append data with list concatenation ([`,`](../../../ref/join.md)), the most basic form of a [join](../../../ref/joins.md). Combined with variable assignment, concatenation also lets you [omit the keys](../../../ref/join.md#append):


**q**
```q
q)t,:(`John; "555-5382"; 40)  / append a single row
```


**pandas**
```python
>>> t = pd.concat([t, pd.DataFrame([['John', '555-5382', 40]], columns=t.columns)], ignore_index=True)
```


## List operations

Since tables are lists, most list operations apply to tables. For a full reference, see [list operations](lists.md#operations); this page lists only the most frequently used operations.

### Number of rows: `count`

The [count](../../../ref/count.md) function returns the number of rows in a table:


**q**
```q
q)t:([] name: `Alice`Bob`Mike; phone: ("555-0101"; "555-0723"; "555-6666"); age: 23 32 22)
q)count t
3
```


**pandas**
```python
>>> t = pd.DataFrame({'name': ['Alice', 'Bob', 'Mike'], 'phone': ['555-0101', '555-0723', '555-6666'], 'age': [23, 32, 22]})
>>> len(t)
3
```


### Indexing

Tables support both positional and label-based indexing:


**q**
```q
q)t:([] name: `Alice`Bob`Mike; phone: ("555-0101"; "555-0723"; "555-6666"); age: 23 32 22)
q)t 2         / get the third record
name | `Mike
phone| "555-6666"
age  | 22

q)t `name     / get the 'name' column
`Alice`Bob`Mike


q)t[2; `name]   / get the 'name' value of the third row
`Mike
q)t . (2;`name) / index at depth by a list
`Mike
q)t 2 0         / index by list
name  phone      age
--------------------
Mike  "555-6666" 22
Alice "555-0101" 23
q)first t
name | `Alice
phone| "555-0101"
age  | 23
```


**pandas**
```python
>>> t = pd.DataFrame({'name': ['Alice', 'Bob', 'Mike'], 'phone': ['555-0101', '555-0723', '555-6666'], 'age': [23, 32, 22]})
>>> t.iloc[2]
name         Mike
phone    555-6666
age            22
Name: 2, dtype: object
>>> t['name']
0    Alice
1      Bob
2     Mike
Name: name, dtype: object
>>> t.at[2, 'name']
'Mike'


>>> t.iloc[[2, 0]]
    name     phone  age
2   Mike  555-6666   22
0  Alice  555-0101   23

>>> t.iloc[0]
name     Alice
phone    555-0101
age            23
Name: 0, dtype: object
```


### Indexed assignment

You can overwrite any item by indexing:


**q**
```q
q)t:([] name: `Alice`Bob`Mike; phone: ("555-0101"; "555-0723"; "555-6666"); age: 23 32 22)
q)t[1]: (`John; "555-5382"; 40)
q)t
name  phone      age
--------------------
Alice "555-0101" 23
John  "555-5382" 40
Mike  "555-6666" 22
```


**pandas**
```python
>>> t = pd.DataFrame({'name': ['Alice', 'Bob', 'Mike'], 'phone': ['555-0101', '555-0723', '555-6666'], 'age': [23, 32, 22]})
>>> t.iloc[1] = ['John', '555-5382', 40]
>>> t
    name     phone  age
0  Alice  555-0101   23
1   John  555-5382   40
2   Mike  555-6666   22
```


or by using [general amend at](../../../ref/amend.md):
```q
q)@[t;2;:;(`Phil;"555-2341"; 44)]
name  phone      age
--------------------
Alice "555-0101" 23
John  "555-5382" 40
Phil  "555-2341" 44
```

Typically, you use an [update statement](../../../ref/update.md) to modify a table or get a new copy of it. Combining an update with a `where` clause provides a convenient and readable solution.

### Subtables

You can extract subtables using the [sublist](../../../ref/sublist.md) function and the [take](../../../ref/take.md) operator. The inverse operator is [`_` Drop](../../../ref/drop.md):


**q**
```q
q)t:([] name: `Alice`Bob`Mike; phone: ("555-0101"; "555-0723"; "555-6666"); age: 23 32 22)
q)2 sublist t       / subtable
name  phone      age
--------------------
Alice "555-0101" 23
Bob   "555-0723" 32
q)-2 sublist t      / last two records
name phone      age
-------------------
Bob  "555-0723" 32
Mike "555-6666" 22
q)2 _ t             / delete first two records
name phone      age
-------------------
Mike "555-6666" 22
q)`name`age cut t   / delete columns
phone
----------
"555-0101"
"555-0723"
"555-6666"
q)t _ 1             / delete records at index 1
name  phone      age
--------------------
Alice "555-0101" 23
Mike  "555-6666" 22
```


**pandas**
```python
>>> t = pd.DataFrame({'name': ['Alice', 'Bob', 'Mike'], 'phone': ['555-0101', '555-0723', '555-6666'], 'age': [23, 32, 22]})
>>> t.head(2)
    name     phone  age
0  Alice  555-0101   23
1    Bob  555-0723   32

>>> t.tail(2)
   name     phone  age
1   Bob  555-0723   32
2  Mike  555-6666   22

>>> t.iloc[2:]
   name     phone  age
2  Mike  555-6666   22

>>> t.drop(columns=['name', 'age'])
      phone
0  555-0101
1  555-0723
2  555-6666

>>> t.drop(index=1)
    name     phone  age
0  Alice  555-0101   23
2   Mike  555-6666   22
```


[Delete statements](../../../ref/delete.md) are another convenient way to delete rows from a table.

The [take](../../../ref/take.md) function is similar to `sublist`, but wraps around if the requested count exceeds the table length:
```q
q)5#t
name  phone      age
--------------------
Alice "555-0101" 23
Bob   "555-0723" 32
Mike  "555-6666" 22
Alice "555-0101" 23
Bob   "555-0723" 32
```

You can also take selected columns:


**q**
```q
q)`name`age#t
name  age
---------
Alice 23
Bob   32
Mike  22
```


**pandas**
```python
>>> t[['name', 'age']]
    name  age
0  Alice   23
1    Bob   32
2   Mike   22
```


### Arithmetic operations

All arithmetic operations apply to tables containing numeric columns. q applies operators element-wise.


**q**
```q
q)show t1: flip `a`b!3 cut til 6
a b
---
0 3
1 4
2 5
q)2*t1
a b
----
0 6
2 8
4 10
q)show t2: flip `a`b!3 cut 6?20
a  b
-----
12 1
8  9
10 11
q)t1 + t2
a  b
-----
12 4
9  13
12 16
q)neg t1
a  b
-----
0  -3
-1 -4
-2 -5
```


**pandas**
```python
>>> t1 = pd.DataFrame(np.arange(6).reshape(2, 3).T, columns=['a', 'b'])
>>> t1
   a  b
0  0  3
1  1  4
2  2  5
>>> 2 * t1
   a   b
0  0   6
1  2   8
2  4  10

>>> t2 = pd.DataFrame(np.random.randint(0, 20, size=(2, 3)).T, columns=['a', 'b'])
>>> t2
   a   b
0 12   1
1  8   9
2 10  11
>>> t1 + t2
    a   b
0  12   4
1   9  13
2  12  16

>>> -t1
   a  b
0  0 -3
1 -1 -4
2 -2 -5
```


q runs aggregations column-wise and result in ordinary dictionaries:


**q**
```q
q)avg t1
a| 1
b| 4
```


**pandas**
```python
>>> t1.mean()
a    1.0
b    4.0
dtype: float64
```


### Iteration

A table is a list of records, so [`each`](../../../ref/each.md) hands your function one _row_ at a time, as a dictionary:


**q**
```q
q)t:([] name: `Alice`Bob`Mike; phone: ("555-0101"; "555-0723"; "555-6666"); age: 23 32 22)
q)first t                   / a row is a dictionary
name | `Alice
phone| "555-0101"
age  | 23
q){show x`name} each t;     / one row at a time
`Alice
`Bob
`Mike
```


**pandas**
```python
>>> t = pd.DataFrame({'name': ['Alice', 'Bob', 'Mike'], 'phone': ['555-0101', '555-0723', '555-6666'], 'age': [23, 32, 22]})
>>> t.iloc[0]
name        Alice
phone    555-0101
age            23
Name: 0, dtype: object
>>> for _, row in t.iterrows():
...     print(row['name'])
Alice
Bob
Mike
```


> **Warning — Row-by-row iteration is the slow path**
>
>
> A table stores each column as a contiguous list, so work expressed over columns runs vectorized while work expressed over rows does not. Reach for a column expression or a [qSQL query](#querying) first. The same advice applies to row-wise iteration in pandas.
>
Because a table is a flipped dictionary, [`flip`](../../../ref/flip.md) turns it back into a column dictionary. Iterating that is then just [dictionary iteration](dictionaries.md#iteration), one whole column at a time:
```q
q)type each flip t
name | 11
phone| 0
age  | 7
```

To walk two columns in step, index them out and apply a binary function with the [each-both](../../../ref/maps.md#each) iterator (`'`):


**q**
```q
q){x, " is ", string y}'[string t`name; t`age]
"Alice is 23"
"Bob is 32"
"Mike is 22"
```


**pandas**
```python
>>> [f"{n} is {a}" for n, a in zip(t['name'], t['age'])]
['Alice is 23', 'Bob is 32', 'Mike is 22']
```


Most of the time, though, the iteration you want is a [query](../../../how_to/querying/select.md).

### Null replacement with `coalesce` (`^`)

The [coalesce](../../../ref/coalesce.md) operator `^` is related to `,` in that it employs upsert semantics to merge two tables with right prevailing over left. The difference is that null values in the right operand do not prevail over those in the left.


**q**
```q
q)show t:([] a: 1 0N; b: 0N 4)
a b
---
1
  4
q)0^t
a b
---
1 0
0 4
q)([] a: 10 2; b: 3 10) ^ t
a b
---
1 3
2 4
```


**pandas**
```python
>>> t = pd.DataFrame({'a': [1, None], 'b': [None, 4]}).astype('Int64')
>>> t
      a     b
0     1  <NA>
1  <NA>     4
>>> t.fillna(0)
   a  b
0  1  0
1  0  4

>>> t.combine_first(pd.DataFrame({'a': [10, 2], 'b': [3, 10]}))
   a  b
0  1  3
1  2  4
```


### Reordering

Tables are lists, and there are multiple ways to reorder a list:


**q**
```q
q)t:([] name: `Alice`Bob`Mike; phone: ("555-0101"; "555-0723"; "555-6666"); age: 23 32 22)
q)reverse t
name  phone      age
--------------------
Mike  "555-6666" 22
Bob   "555-0723" 32
Alice "555-0101" 23
q)0N?t    / random permutation
name  phone      age
--------------------
Bob   "555-0723" 32
Alice "555-0101" 23
Mike  "555-6666" 22
```


**pandas**
```python
>>> t = pd.DataFrame({'name': ['Alice', 'Bob', 'Mike'], 'phone': ['555-0101', '555-0723', '555-6666'], 'age': [23, 32, 22]})
>>> t.iloc[::-1].reset_index(drop=True)
    name     phone  age
0   Mike  555-6666   22
1    Bob  555-0723   32
2  Alice  555-0101   23

>>> t.sample(frac=1).reset_index(drop=True)
    name     phone  age
0    Bob  555-0723   32
1  Alice  555-0101   23
2   Mike  555-6666   22
```


Functions [asc](../../../ref/asc.md) and [desc](../../../ref/desc.md) sort lexicographically by all columns:


**q**
```q
q)asc ([]a:4 3 4; b:`s`a`d)
a b
---
3 a
4 d
4 s
```


**pandas**
```python
>>> pd.DataFrame({'a': [4, 3, 4], 'b': ['s', 'a', 'd']}).sort_values(by=['a', 'b'])
   a  b
1  3  a
2  4  d
0  4  s
```


To sort by selected columns, use [xasc](../../../ref/asc.md#xasc) and [xdesc](../../../ref/desc.md#xdesc):


**q**
```q
q)`a xasc ([]a:4 3 4; b:`s`a`d)
a b
---
3 a
4 s
4 d
```


**pandas**
```python
>>> pd.DataFrame({'a': [4, 3, 4], 'b': ['s', 'a', 'd']}).sort_values(by='a', kind='stable')
   a  b
1  3  a
0  4  s
2  4  d
```


The sort in q is stable: it preserves the relative order of equal elements.

Because the sort is stable, you can sort by several columns in different directions by applying the sorts one after another, starting with the minor column. For example, to sort by `a` in ascending order and, for equal values of `a`, by `b` in descending order:


**q**
```q
q)`a xasc `b xdesc ([]a:4 3 4 3; b:`d`a`s`c)
a b
---
3 c
3 a
4 s
4 d
```


**pandas**
```python
>>> pd.DataFrame({'a': [4, 3, 4, 3], 'b': ['d', 'a', 's', 'c']}).sort_values(by=['a', 'b'], ascending=[True, False])
   a  b
3  3  c
1  3  a
2  4  s
0  4  d
```


### Grouping and set operations

Grouping by a field or multiple fields is a basic operation in data analysis:


**q**
```q
q)t:([] name: `Alice`Bob`Mike; kids: (1#`Bill; (); `Maggie`Jack`Ellie))
q)show nt: ungroup t     / convert to normal table, "flatten" list fields
name  kids
------------
Alice Bill
Mike  Maggie
Mike  Jack
Mike  Ellie
q)`name xgroup nt       / group by name
name | kids
-----| ------------------
Alice| ,`Bill
Mike | `Maggie`Jack`Ellie
```


**pandas**
```python
>>> t = pd.DataFrame({'name': ['Alice', 'Bob', 'Mike'], 'kids': [['Bill'], [], ['Maggie', 'Jack', 'Ellie']]})
>>> nt = t.explode('kids').dropna(subset=['kids'])
>>> nt
    name    kids
0  Alice    Bill
2   Mike  Maggie
2   Mike    Jack
2   Mike   Ellie
>>> nt.groupby('name').agg(list)
                        kids
name
Alice                 [Bill]
Mike   [Maggie, Jack, Ellie]
```


Set operations like [inter](../../../ref/inter.md), [union](../../../ref/union.md), [except](../../../ref/except.md), and [distinct](../../../ref/distinct.md) handle tables as expected — the set items are table records. Furthermore, there are multiple ways to [join tables](../../../ref/joins.md).

## Querying

q supports an SQL-like syntax for querying tables. See section **Guides** → [**Querying**](../../../how_to/querying/select.md) for examples and more details.

## Next steps

- Learn how to create [keyed tables](keyed-tables.md) from two tables.
- To load a CSV as a table, refer to [Load CSV](../../../ref/file-text.md#load-csv).
- Move your in-memory data to the filesystem — see the [database overview](../../../how_to/interact_with_databases/overview.md).
- Improve the performance of table operations by applying [attributes](../../../ref/attr.md).
