pandas: Fill gaps column by column

Model fillna and its relatives, choosing a different rule for each column.

What to build

A program that fills missing values per column.

Requirements

  • For each column the rules line gives one of: value X (fill with X), mean (fill with the mean of the present values, two decimals), ffill (the last present value above), bfill (the next present value below), keep (leave the gaps).
  • Gaps that cannot be filled are printed as NaN. Values that were present are printed unchanged. Print the header and then each row, values joined by commas.

Input

Line 1: the header. Line 2: one rule per column, separated by ;. Line 3: n. Then n rows; an empty value is missing.

Output

n + 1 lines.

Constraints

1 ≤ n ≤ 100; a mean column has at least one present value and only numbers.

Example

Input:

name,age,price
value unknown;mean;ffill
4
Ada,30,
,,5
Mei,41,
Omar,,8

Output:

name,age,price
Ada,30,NaN
unknown,35.50,5
Mei,41,5
Omar,35.50,8

Why it works

Each rule answers "what is the best guess for this gap?" differently, which is why pandas lets fillna take a value per column and offers ffill and bfill for data that runs in order.

Examples

Example 1 · Input
name,age,price
value unknown;mean;ffill
4
Ada,30,
,,5
Mei,41,
Omar,,8
Expected output
name,age,price
Ada,30,NaN
unknown,35.50,5
Mei,41,5
Omar,35.50,8
Example 2 · Input
a,b
bfill;keep
3
,1
2,
,3
Expected output
a,b
2,1
2,NaN
NaN,3

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

Hints

Hint 1

Handle each column with its own rule: a constant, the column's mean, or the previous or next present value.

Hint 2

ffill cannot fill a gap at the very start, and bfill cannot fill one at the very end: those stay NaN.

Solution

Show one correct solution (try it yourself first)
header = input().split(",")
rules = input().split(";")
n = int(input())
rows = [input().split(",") for _ in range(n)]
columns = [[row[i] for row in rows] for i in range(len(header))]
for i, rule in enumerate(rules):
    col = columns[i]
    if rule.startswith("value "):
        columns[i] = [c if c else rule[6:] for c in col]
    elif rule == "mean":
        present = [float(c) for c in col if c]
        mean = f"{sum(present) / len(present):.2f}"
        columns[i] = [c if c else mean for c in col]
    elif rule in ("ffill", "bfill"):
        order = range(n) if rule == "ffill" else range(n - 1, -1, -1)
        last = ""
        filled = list(col)
        for j in order:
            if col[j]:
                last = col[j]
            else:
                filled[j] = last
        columns[i] = filled
columns = [[c if c else "NaN" for c in col] for col in columns]
print(",".join(header))
for j in range(n):
    print(",".join(col[j] for col in columns))

Lesson: Missing data: finding, dropping and filling →