In this post we are going to cover cleaning data with pandas: converting text columns safely, handling every kind of null, keeping the latest duplicate, parsing mixed date formats, copy-on-write in pandas 3, and cleaning string join keys. These are 7 real Python interview questions for data engineers, asked the way they come up on a real project, and the last one is a scenario where you walk through your cleaning steps for a new source file before it reaches the warehouse.
Cleaning is the part of a pipeline that decides whether bad data is caught or quietly loaded. Interviewers ask about it because the wrong answer usually still runs: a coerce that hides bad values, a fill that changes an average, a date that parses to the wrong day. They want to hear how you find what failed and count it.
Every Python step below runs on the sample data, and the output under each step is the real result.
Part 3 of the Real Time Python for Data Engineers Interview Questions and Answers series on TechBrothersIT.
- The short answer
- Let's create some sample data
- Question 1: The amount column loads as text and the sum fails. How do you find bad values and convert the column safely?
- Question 2: Nulls show up as NaN, None and empty strings in the same column. How do you handle missing values?
- Question 3: The customer table has duplicate rows for the same customer. How do you keep only the latest one?
- Question 4: Dates arrive in two formats in the same file. How do you parse them without silent mistakes?
- Question 5: A teammate's code changes a filtered DataFrame and nothing happens. What changed with copy-on-write in pandas 3?
- Question 6: Names have extra spaces and mixed case, and joins miss matches. How do you clean string columns?
- Scenario 7: A new source file must be cleaned before it reaches the warehouse. Walk me through your cleaning steps.
- The full script
The short answer
Convert types explicitly (to_numeric, to_datetime with a format) and count what fails instead of hiding it. Normalize every kind of null before you fill, keep the latest row with sort_values + drop_duplicates, and remember that in pandas 3 copy-on-write is the only mode: chained assignment never works, so use .loc.
Keep that in mind. Every question below is one piece of it.
Let's create some sample data
The script builds one small piece of data per question: an orders file with a few bad amounts, customer records with nulls written four different ways, a customer extract with repeats, dates in two formats, open invoices, names typed differently in two systems, and a messy new source file for the scenario.
from pathlib import Path
import pandas as pd
pd.set_option('display.width', 60)
# Q1: an orders file whose amount column has a few bad values
Path('orders.csv').write_text(
'order_id,amount\n'
'1001,120.50\n'
'1002,"1,250.00"\n'
'1003,\n'
'1004,75\n'
'1005,TBD\n'
'1006,12..5\n', encoding='utf-8')
# Q2: customer records from an API, with nulls written four different ways
customers = pd.DataFrame([
{'cust_id': 1, 'city': 'Dallas', 'credit': 500.0},
{'cust_id': 2, 'city': None, 'credit': None},
{'cust_id': 3, 'city': '', 'credit': 250.0},
{'cust_id': 4, 'city': ' ', 'credit': float('nan')},
{'cust_id': 5, 'city': 'NULL', 'credit': 300.0},
])
# Q3: a customer extract with several rows per customer
cust_raw = pd.DataFrame({
'customer_id': [101, 102, 101, 103, 102, 101],
'email': ['ali@old.com', 'sara@a.com', 'ali@new.com',
'john@a.com', 'sara@b.com', 'ali@work.com'],
'updated_at': pd.to_datetime([
'2026-10-01 09:00', '2026-10-01 09:05', '2026-10-03 14:30',
'2026-10-02 11:00', '2026-10-04 08:15', '2026-10-05 16:45'],
format='%Y-%m-%d %H:%M'),
})
# Q4: order dates from one file, in two formats, plus one impossible date
order_dates = pd.Series(['2026-10-05', '10/07/2026', '2026-10-09',
'11/02/2026', '2026-02-30'])
# Q5: open invoices and how many days late they are
invoices = pd.DataFrame({'invoice_id': [1, 2, 3, 4],
'days_late': [5, 45, 0, 60],
'status': ['Open', 'Open', 'Open', 'Open']})
# Q6: the same people, typed differently in two systems
crm = pd.DataFrame({'name': [' Ali Khan', 'SARA LEE ', 'john smith',
'Maria Lopez']})
sales = pd.DataFrame({'name': ['Ali Khan', 'Sara Lee', 'John Smith',
'Maria Lopez'],
'total': [1200, 800, 450, 300]})
# Q7: a new source file: messy headers, a bad amount, a bad date, a repeat
Path('new_source.csv').write_text(
' Order ID,Customer , Amount,Order Date\n'
'5001, ali khan ,120.50,2026-10-20\n'
'5002,SARA LEE,TBD,2026-10-20\n'
'5003,John Smith,75.00,2026-10-21\n'
'5003,John Smith,80.00,2026-10-21\n'
'5004,maria lopez,99.90,21/10/2026\n'
'5005,Omar Ali,210.00,2026-10-22\n', encoding='utf-8')Question 1: The amount column loads as text and the sum fails. How do you find bad values and convert the column safely?
A column loads as text when even one value isn't a number. Our file has a thousands comma, a TBD, and a typo with two dots.
Let's read it, try the sum, and try a cast to float.
orders = pd.read_csv('orders.csv')
print(orders.dtypes)
print(repr(orders['amount'].sum()))
orders['amount'].astype('float64')order_id int64
amount str
dtype: object
'120.501,250.0075TBD12..5'
ValueError: could not convert string to float: '1,250.00'pandas 3 gives amount the str dtype. And the sum doesn't even fail: on text, it glues the values together into '120.501,250.0075TBD12..5'. The cast does fail, on the first value with a comma: could not convert string to float: '1,250.00'.
The safe way: remove the thousands commas, then call pd.to_numeric with errors='coerce'. Anything that still isn't a number becomes NaN. A bad row is one that is NaN now but was not missing before.
no_commas = orders['amount'].str.replace(',', '')
amount = pd.to_numeric(no_commas, errors='coerce')
is_bad = amount.isna() & orders['amount'].notna()
print(orders[is_bad])
valid = orders[~is_bad].assign(amount=amount)
print(valid['amount'].dtype, valid['amount'].sum()) order_id amount
4 1005 TBD
5 1006 12..5
float64 1445.5The bad rows are order 1005 with TBD and order 1006 with 12..5. Keep the good rows, and the column is float64, with a sum of 1445.5. The empty amount for order 1003 stays a real null.
errors='coerce' on its own: bad values become nulls without a trace. Count them, send them to a reject file, and fail the load when there are too many.
Question 2: Nulls show up as NaN, None and empty strings in the same column. How do you handle missing values?
First, make them one thing. In pandas 3, None and NaN in a text column both become NaN, so isna catches them. But an empty string, spaces, or the word NULL are real text, and isna misses them.
print(customers['city'].tolist())
print(customers.isna().sum())['Dallas', nan, '', ' ', 'NULL']
cust_id 0
city 1
credit 2
dtype: int64The city list is ['Dallas', nan, '', ' ', 'NULL'], and isna counts only 1 missing city.
So let's normalize first: strip the spaces, then mask the empty strings and the NULL text.
city = customers['city'].str.strip()
customers['city'] = city.mask(city.isin(['', 'NULL']))
print(customers['city'].tolist())
print(customers['city'].isna().sum(), 'missing cities')['Dallas', nan, nan, nan, nan]
4 missing citiesNow 4 missing cities, which is the truth.
Then decide per column. That is a business rule, not a default. Let's see what filling does to an average.
credit = customers['credit']
print('mean, nulls skipped:', credit.mean())
print('mean, nulls as zero:', credit.fillna(0).mean())
print(customers['city'].fillna('Unknown').tolist())mean, nulls skipped: 350.0
mean, nulls as zero: 210.0
['Dallas', 'Unknown', 'Unknown', 'Unknown', 'Unknown']The average credit, skipping nulls, is 350.0. Fill the nulls with zero, and it drops to 210.0. A missing amount is not a zero amount. For a text attribute in a report, Unknown is a fine fill. For whole numbers, use the nullable Int64 type, so one null doesn't turn the column into floats.
read_csv already treats empty fields and NULL as missing. na_values adds the source's own markers, like na_values=['-', 'unknown'].
Question 3: The customer table has duplicate rows for the same customer. How do you keep only the latest one?
Sort, then drop duplicates with keep last. But first, let's look at the duplicates. duplicated with keep=False marks every row in a duplicate group, not just the extra copies.
print(cust_raw)
dup = cust_raw.duplicated('customer_id', keep=False)
print(dup.sum(), 'rows are in duplicate groups') customer_id email updated_at
0 101 ali@old.com 2026-10-01 09:00:00
1 102 sara@a.com 2026-10-01 09:05:00
2 101 ali@new.com 2026-10-03 14:30:00
3 103 john@a.com 2026-10-02 11:00:00
4 102 sara@b.com 2026-10-04 08:15:00
5 101 ali@work.com 2026-10-05 16:45:00
5 rows are in duplicate groupsCustomer 101 has three rows and customer 102 has two, so 5 of the six rows are in duplicate groups.
To keep the latest, sort by the customer ID and the update time, then drop duplicates on the customer ID with keep='last'.
latest = (cust_raw
.sort_values(['customer_id', 'updated_at'])
.drop_duplicates('customer_id', keep='last'))
print(latest)
print(latest['customer_id'].is_unique) customer_id email updated_at
5 101 ali@work.com 2026-10-05 16:45:00
4 102 sara@b.com 2026-10-04 08:15:00
3 103 john@a.com 2026-10-02 11:00:00
TrueThree rows, one per customer, each with its newest email: ali@work.com, sara@b.com and john@a.com. And is_unique is True.
Another common way is groupby, then idxmax on the update time, which returns the label of the newest row in each group. Select those rows with .loc.
by_cust = cust_raw.groupby('customer_id')['updated_at']
newest = cust_raw.loc[by_cust.idxmax()]
print(newest.equals(latest))TrueTrue: exactly the same rows.
In a real project, add a tie breaker to the sort, like the load time or the file row number. And null timestamps sort last, so keep last would pick them: handle them first.
The customer ID must be unique. That one check catches a broken dedup before the warehouse does.
Question 4: Dates arrive in two formats in the same file. How do you parse them without silent mistakes?
Parse one explicit format at a time, and check what is left. Our file mixes year-month-day dates with US month/day/year dates, and one value, February 30, isn't a real date.
Let's start with to_datetime and no format.
print(order_dates.tolist())
pd.to_datetime(order_dates)['2026-10-05', '10/07/2026', '2026-10-09', '11/02/2026', '2026-02-30']
ValueError: time data "10/07/2026" doesn't match format "%Y-%m-%d". You might want to try:
- passing `format` if your strings have a consistent format;
- passing `format='ISO8601'` if your strings are all ISO8601 but not necessarily in exactly the same format;
- passing `format='mixed'`, and the format will be inferred for each element individually. You might want to use `dayfirst` alongside this.It infers the format from the first value, %Y-%m-%d, and fails on the first value that doesn't match, "10/07/2026". A loud failure is the good case.
The silent case is format='mixed', which guesses each value on its own. The pandas docs call it risky. Let's add dayfirst=True, the way a teammate might for a European file.
guess = pd.to_datetime(order_dates, format='mixed',
dayfirst=True, errors='coerce')
print(guess)0 2026-05-10
1 2026-07-10
2 2026-09-10
3 2026-02-11
4 NaT
dtype: datetime64[us]October 5 became 2026-05-10, May 10. Every real date is wrong, with no error.
The safe way: parse each known format explicitly, with errors='coerce', and combine them with fillna. Then check what is left.
iso = pd.to_datetime(order_dates, format='%Y-%m-%d',
errors='coerce')
us = pd.to_datetime(order_dates, format='%m/%d/%Y',
errors='coerce')
parsed = iso.fillna(us)
print(parsed)
print('unparsed:', order_dates[parsed.isna()].tolist())0 2026-10-05
1 2026-10-07
2 2026-10-09
3 2026-11-02
4 NaT
dtype: datetime64[us]
unparsed: ['2026-02-30']All four real dates are right: 2026-10-05, 2026-10-07, 2026-10-09 and 2026-11-02. The value that parsed in neither format goes to the rejects: unparsed: ['2026-02-30'].
The result is datetime64[us], microsecond resolution, the pandas 3 default when it parses strings.
Agree on ISO dates with the source, and treat a new format as a contract change, not something to guess.
Question 5: A teammate's code changes a filtered DataFrame and nothing happens. What changed with copy-on-write in pandas 3?
In pandas 3, copy-on-write is the only mode, and the result of any indexing behaves as a copy.
Let's run the teammate's line: filter the late invoices, then set the status.
import warnings
late = invoices['days_late'] > 30
with warnings.catch_warnings(record=True) as caught:
warnings.simplefilter('always')
invoices[late]['status'] = 'Overdue' # chained
print(caught[0].category.__name__)
print(invoices)ChainedAssignmentError
invoice_id days_late status
0 1 5 Open
1 2 45 Open
2 3 0 Open
3 4 60 OpenThe line sets the status on a temporary copy and throws it away. pandas emits a ChainedAssignmentError, which, despite the name, is only a warning. All four invoices are still Open.
In pandas 2, chained assignment sometimes worked, depending on view or copy, with a SettingWithCopyWarning. That warning is gone in pandas 3. Chained assignment simply never works.
The fix is one step with .loc: the row filter and the column in the same call.
invoices.loc[late, 'status'] = 'Overdue'
print(invoices) invoice_id days_late status
0 1 5 Open
1 2 45 Overdue
2 3 0 Open
3 4 60 OverdueNow invoices 2 and 4 are Overdue.
And the other side of it: when you filter into a new DataFrame and change it, only that DataFrame changes.
overdue = invoices[late]
overdue['status'] = 'Escalated'
print(overdue['status'].tolist())
print(invoices['status'].tolist())['Escalated', 'Escalated']
['Open', 'Overdue', 'Open', 'Overdue']The filtered frame shows ['Escalated', 'Escalated'], and the original still shows ['Open', 'Overdue', 'Open', 'Overdue']. The original never changes, so you don't need defensive copy calls anymore.
Test on pandas 2.3 first, with its copy-on-write warn mode on. And drop inplace=True: pandas 3.1 deprecates it on drop, rename, sort_values and other methods that never worked in place.
Question 6: Names have extra spaces and mixed case, and joins miss matches. How do you clean string columns?
Joins compare strings exactly. A leading space, a double space or a capital letter makes a different key.
Let's join the CRM names to the sales names as they are.
matched = crm.merge(sales, on='name')
print(len(matched), 'of', len(crm), 'matched')
print(crm['name'].tolist())1 of 4 matched
[' Ali Khan', 'SARA LEE ', 'john smith', 'Maria Lopez']They look the same to a person, but the merge matches only 1 of 4. The CRM side has ' Ali Khan', 'SARA LEE ' and 'john smith'.
Let's clean the key with the string accessor: strip the ends, replace any run of whitespace with one space, and casefold, which is lower case made for comparisons. We put it in one small function and use it on both sides.
def name_key(s):
return (s.str.strip()
.str.replace(r'\s+', ' ', regex=True)
.str.casefold())
crm['key'] = name_key(crm['name'])
sales['key'] = name_key(sales['name'])
matched = crm.merge(sales[['key', 'total']], on='key')
print(len(matched), 'of', len(crm), 'matched')
print(matched[['key', 'total']])4 of 4 matched
key total
0 ali khan 1200
1 sara lee 800
2 john smith 450
3 maria lopez 300Now 4 of 4 match, with totals 1200, 800, 450 and 300. Join on the clean key column, and keep the original name for display, or title case it.
One pandas 3 trap: text columns now have the str dtype, not object. So a check like dtype == object is wrong for strings. Use pd.api.types.is_string_dtype() instead.
of the pipeline, so every downstream join uses the same rule.
Scenario 7: A new source file must be cleaned before it reaches the warehouse. Walk me through your cleaning steps.
The order matters:
- Read as text:
dtype=str, so nothing is guessed. - Clean the headers.
- Trim strings, fix case.
- Convert types explicitly.
- Drop duplicates: keep the latest.
- Validate: good rows vs rejects.
Step one: let's read with dtype=str and turn the headers into snake case.
raw = pd.read_csv('new_source.csv', dtype=str)
raw.columns = (raw.columns.str.strip().str.lower()
.str.replace(' ', '_'))
print(raw.columns.tolist())['order_id', 'customer', 'amount', 'order_date']The file had spaces and capitals in its headers, like ' Order ID', and now the names are stable: ['order_id', 'customer', 'amount', 'order_date'].
Then one clean function. It trims and title cases the customer, converts the amount and the date with errors='coerce', keeps the last row the source sent for each order, and flags rows where a required value didn't convert.
def clean(df):
out = df.assign(
customer=df['customer'].str.strip().str.title(),
amount=pd.to_numeric(df['amount'], errors='coerce'),
order_date=pd.to_datetime(df['order_date'],
format='%Y-%m-%d',
errors='coerce'),
).drop_duplicates('order_id', keep='last')
bad = out[['amount', 'order_date']].isna().any(axis=1)
return out[~bad], df.loc[out.index[bad]] # raw rejectsThis step prints nothing; it prepares the data for the next step.
Let's run it.
good, rejects = clean(raw)
rejects.to_csv('rejects.csv', index=False)
dups = raw.duplicated('order_id').sum()
print('read', len(raw), '| duplicates', dups,
'| rejected', len(rejects), '| loaded', len(good))
print(good['order_id'].is_unique)
print(good)
print(rejects[['order_id', 'amount', 'order_date']])read 6 | duplicates 1 | rejected 2 | loaded 3
True
order_id customer amount order_date
0 5001 Ali Khan 120.5 2026-10-20
3 5003 John Smith 80.0 2026-10-21
5 5005 Omar Ali 210.0 2026-10-22
order_id amount order_date
1 5002 TBD 2026-10-20
4 5004 99.90 21/10/2026Six rows read, one duplicate, two rejected, three loaded, and is_unique is True. Order 5003 kept its last row, 80.0. The rejects keep their raw text: order 5002 with TBD, and order 5004 with 21/10/2026. They go to a rejects file.
In a real project, I do three more things:
- Log the counts on every run: read, rejected, loaded.
- Fail the load when rejects pass a threshold.
- Keep the raw file untouched, so you can reprocess it.
Nothing should disappear silently. Count the duplicates and the rejects, and keep every reject with its raw value.
Key points to remember
- to_numeric(errors="coerce"), then count and reject what failed
- Normalize NaN, None, empty and NULL text before you fill
- Latest row: sort_values + drop_duplicates(keep="last")
- Dates: one explicit format at a time, never a silent guess
- pandas 3: copy-on-write, use .loc; clean join keys on both sides
The full script
Here is everything in one script: the sample data and every step from this post, in order. Run it top to bottom in an empty folder.
Show the full Python script (sample data and every step)
# ========================================================================
# Python Interview Q&A #3 - Cleaning data with pandas
# TechBrothersIT. Sample data is invented for teaching.
# Every step below was run with Python 3.14.6 and pandas 3.1.0; the outputs in the video and the blog
# post are the real outputs. Run it in an empty folder: it writes small sample files.
# ========================================================================
# Step 0: sample data
from pathlib import Path
import pandas as pd
pd.set_option('display.width', 60)
# Q1: an orders file whose amount column has a few bad values
Path('orders.csv').write_text(
'order_id,amount\n'
'1001,120.50\n'
'1002,"1,250.00"\n'
'1003,\n'
'1004,75\n'
'1005,TBD\n'
'1006,12..5\n', encoding='utf-8')
# Q2: customer records from an API, with nulls written four different ways
customers = pd.DataFrame([
{'cust_id': 1, 'city': 'Dallas', 'credit': 500.0},
{'cust_id': 2, 'city': None, 'credit': None},
{'cust_id': 3, 'city': '', 'credit': 250.0},
{'cust_id': 4, 'city': ' ', 'credit': float('nan')},
{'cust_id': 5, 'city': 'NULL', 'credit': 300.0},
])
# Q3: a customer extract with several rows per customer
cust_raw = pd.DataFrame({
'customer_id': [101, 102, 101, 103, 102, 101],
'email': ['ali@old.com', 'sara@a.com', 'ali@new.com',
'john@a.com', 'sara@b.com', 'ali@work.com'],
'updated_at': pd.to_datetime([
'2026-10-01 09:00', '2026-10-01 09:05', '2026-10-03 14:30',
'2026-10-02 11:00', '2026-10-04 08:15', '2026-10-05 16:45'],
format='%Y-%m-%d %H:%M'),
})
# Q4: order dates from one file, in two formats, plus one impossible date
order_dates = pd.Series(['2026-10-05', '10/07/2026', '2026-10-09',
'11/02/2026', '2026-02-30'])
# Q5: open invoices and how many days late they are
invoices = pd.DataFrame({'invoice_id': [1, 2, 3, 4],
'days_late': [5, 45, 0, 60],
'status': ['Open', 'Open', 'Open', 'Open']})
# Q6: the same people, typed differently in two systems
crm = pd.DataFrame({'name': [' Ali Khan', 'SARA LEE ', 'john smith',
'Maria Lopez']})
sales = pd.DataFrame({'name': ['Ali Khan', 'Sara Lee', 'John Smith',
'Maria Lopez'],
'total': [1200, 800, 450, 300]})
# Q7: a new source file: messy headers, a bad amount, a bad date, a repeat
Path('new_source.csv').write_text(
' Order ID,Customer , Amount,Order Date\n'
'5001, ali khan ,120.50,2026-10-20\n'
'5002,SARA LEE,TBD,2026-10-20\n'
'5003,John Smith,75.00,2026-10-21\n'
'5003,John Smith,80.00,2026-10-21\n'
'5004,maria lopez,99.90,21/10/2026\n'
'5005,Omar Ali,210.00,2026-10-22\n', encoding='utf-8')
# Step 1: The amount column is text: sum and astype
# This step is EXPECTED TO FAIL: it shows the error the video explains.
try:
orders = pd.read_csv('orders.csv')
print(orders.dtypes)
print(repr(orders['amount'].sum()))
orders['amount'].astype('float64')
except Exception as e:
print(type(e).__name__ + ":", e)
# Step 2: Find the bad values, then convert safely
no_commas = orders['amount'].str.replace(',', '')
amount = pd.to_numeric(no_commas, errors='coerce')
is_bad = amount.isna() & orders['amount'].notna()
print(orders[is_bad])
valid = orders[~is_bad].assign(amount=amount)
print(valid['amount'].dtype, valid['amount'].sum())
# Step 3: isna sees only real nulls
print(customers['city'].tolist())
print(customers.isna().sum())
# Step 4: Normalize empty strings and NULL text to real nulls
city = customers['city'].str.strip()
customers['city'] = city.mask(city.isin(['', 'NULL']))
print(customers['city'].tolist())
print(customers['city'].isna().sum(), 'missing cities')
# Step 5: Decide per column: what filling does to an average
credit = customers['credit']
print('mean, nulls skipped:', credit.mean())
print('mean, nulls as zero:', credit.fillna(0).mean())
print(customers['city'].fillna('Unknown').tolist())
# Step 6: Look at the duplicate groups
print(cust_raw)
dup = cust_raw.duplicated('customer_id', keep=False)
print(dup.sum(), 'rows are in duplicate groups')
# Step 7: Keep the latest row per customer
latest = (cust_raw
.sort_values(['customer_id', 'updated_at'])
.drop_duplicates('customer_id', keep='last'))
print(latest)
print(latest['customer_id'].is_unique)
# Step 8: The same result with groupby and idxmax
by_cust = cust_raw.groupby('customer_id')['updated_at']
newest = cust_raw.loc[by_cust.idxmax()]
print(newest.equals(latest))
# Step 9: to_datetime with no format fails on the second format
# This step is EXPECTED TO FAIL: it shows the error the video explains.
try:
print(order_dates.tolist())
pd.to_datetime(order_dates)
except Exception as e:
print(type(e).__name__ + ":", e)
# Step 10: format='mixed' with dayfirst: silently wrong dates
guess = pd.to_datetime(order_dates, format='mixed',
dayfirst=True, errors='coerce')
print(guess)
# Step 11: Parse each known format explicitly, then check what is left
iso = pd.to_datetime(order_dates, format='%Y-%m-%d',
errors='coerce')
us = pd.to_datetime(order_dates, format='%m/%d/%Y',
errors='coerce')
parsed = iso.fillna(us)
print(parsed)
print('unparsed:', order_dates[parsed.isna()].tolist())
# Step 12: Chained assignment changes nothing in pandas 3
import warnings
late = invoices['days_late'] > 30
with warnings.catch_warnings(record=True) as caught:
warnings.simplefilter('always')
invoices[late]['status'] = 'Overdue' # chained
print(caught[0].category.__name__)
print(invoices)
# Step 13: One step with .loc works
invoices.loc[late, 'status'] = 'Overdue'
print(invoices)
# Step 14: Changing a filtered DataFrame never changes the original
overdue = invoices[late]
overdue['status'] = 'Escalated'
print(overdue['status'].tolist())
print(invoices['status'].tolist())
# Step 15: Join on the raw names
matched = crm.merge(sales, on='name')
print(len(matched), 'of', len(crm), 'matched')
print(crm['name'].tolist())
# Step 16: Clean the join key on both sides
def name_key(s):
return (s.str.strip()
.str.replace(r'\s+', ' ', regex=True)
.str.casefold())
crm['key'] = name_key(crm['name'])
sales['key'] = name_key(sales['name'])
matched = crm.merge(sales[['key', 'total']], on='key')
print(len(matched), 'of', len(crm), 'matched')
print(matched[['key', 'total']])
# Step 17: Read everything as text and clean the headers
raw = pd.read_csv('new_source.csv', dtype=str)
raw.columns = (raw.columns.str.strip().str.lower()
.str.replace(' ', '_'))
print(raw.columns.tolist())
# Step 18: One clean function: strings, types, duplicates, rules
def clean(df):
out = df.assign(
customer=df['customer'].str.strip().str.title(),
amount=pd.to_numeric(df['amount'], errors='coerce'),
order_date=pd.to_datetime(df['order_date'],
format='%Y-%m-%d',
errors='coerce'),
).drop_duplicates('order_id', keep='last')
bad = out[['amount', 'order_date']].isna().any(axis=1)
return out[~bad], df.loc[out.index[bad]] # raw rejects
# Step 19: Run it: counts, rejects file and the clean rows
good, rejects = clean(raw)
rejects.to_csv('rejects.csv', index=False)
dups = raw.duplicated('order_id').sum()
print('read', len(raw), '| duplicates', dups,
'| rejected', len(rejects), '| loaded', len(good))
print(good['order_id'].is_unique)
print(good)
print(rejects[['order_id', 'amount', 'order_date']])
Part 4: Joins, Groupby and Reshaping in pandas
The sample data on this page is invented for teaching. Every output is the real output of the code above it, run with Python 3.14.6 and pandas 3.1.0. Part of the Real Time Python for Data Engineers Interview Questions and Answers series on TechBrothersIT. Features, limits and their status (GA or preview) are current as of October 2026; check the official Python and library documentation for anything you quote in an interview.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.