Data science: Profile a table

The first thing to do with a new table is to profile it: its shape, and each column's type, missing values and number of distinct values.

What to build

A program that prints a profile of a table.

Requirements

  • Print shape: R rows x C columns.
  • For each column print NAME: TYPE, missing M, distinct D, where TYPE is int if every non-missing value is a whole number (optional minus sign, then digits), float if not but every one converts with float(), str otherwise, and empty if every value is missing; D counts the different non-missing values.
  • Finally print complete rows: K, the number of rows with no missing value.

Input

Line 1: the header, separated by commas. Line 2: n. Then n rows separated by commas, where an empty field is a missing value.

Output

C + 2 lines.

Constraints

1 ≤ n ≤ 1000; 1 to 10 columns; no quoting.

Example

Input:

id,name,age,city
4
1,Asha,34,Dhaka
2,Ben,,Dhaka
3,,29,Sylhet
4,Dina,29,

Output:

shape: 4 rows x 4 columns
id: int, missing 0, distinct 4
name: str, missing 1, distinct 3
age: int, missing 1, distinct 2
city: str, missing 1, distinct 2
complete rows: 1

Why it works

A profile shows at a glance which columns need cleaning (missing values), which need converting (numbers stored as text) and which are identifiers or categories (many or few distinct values), before any analysis is attempted.

Examples

Example 1 · Input
id,name,age,city
4
1,Asha,34,Dhaka
2,Ben,,Dhaka
3,,29,Sylhet
4,Dina,29,
Expected output
shape: 4 rows x 4 columns
id: int, missing 0, distinct 4
name: str, missing 1, distinct 3
age: int, missing 1, distinct 2
city: str, missing 1, distinct 2
complete rows: 1
Example 2 · Input
x,y
2
1.5,
2,
Expected output
shape: 2 rows x 2 columns
x: float, missing 0, distinct 2
y: empty, missing 2, distinct 0
complete rows: 0

Prompts inside input() are ignored when checking, so input("n: ") is fine.

Hints

Hint 1

An empty field means a missing value; work out each column's type from its non-missing values only.

Hint 2

A row is complete when none of its fields is empty.

Solution

Show one correct solution (try it yourself first)
import re

header = input().split(",")
n = int(input())
rows = [input().split(",") for _ in range(n)]


def type_of(values):
    if not values:
        return "empty"
    if all(re.fullmatch(r"-?\d+", v) for v in values):
        return "int"
    try:
        for v in values:
            float(v)
        return "float"
    except ValueError:
        return "str"


print(f"shape: {n} rows x {len(header)} columns")
for i, name in enumerate(header):
    present = [row[i] for row in rows if row[i] != ""]
    print(f"{name}: {type_of(present)}, missing {n - len(present)}, distinct {len(set(present))}")
print("complete rows:", sum(1 for row in rows if all(v != "" for v in row)))

Lesson: Data types and tables →