Match readings with latest same-city humidity
Company: Two Sigma
Role: Data Scientist
Category: Coding & Algorithms
Difficulty: easy
Interview Round: Technical Screen
## Problem
You are given two time-sorted lists of sensor readings:
- **Temperature** records: `(time, city, reading)`
- **Humidity** records: `(time, city, reading)`
Example:
**Temperature**
```text
[(1, 'NYC', 12),
(5, 'SFO', 20),
(7, 'NYC', 23),
(16, 'SFO', 10)]
```
**Humidity**
```text
[(0, 'SFO', 12),
(3, 'SFO', 2),
(6, 'NYC', 2),
(19, 'SFO', 9)]
```
### Task
For each temperature record, match it with the **most recent humidity record from the same city** whose `time <= temperature_time`.
- If there is no earlier humidity record for that city, return `null` (or `None`) for the humidity reading.
### Output
Return a list aligned to the temperature records, where each element includes the temperature record and the matched humidity reading (or `null`).
### Constraints
- Both input lists are individually sorted by time ascending.
- Cities may appear in any order within the global time ordering.
Overview: This question evaluates the ability to align and join time-ordered sensor data by key, testing skills in time-series merging, per-key state management, and handling absent matches.
Community answers
Answer by Paramartha Sengupta
SQL Solution:
select t_time, city, t_reading,h_reading
from (select t_time,city, t_reading, h_reading,
row_number() over (partition by city order by time_diff asc) as rnk
from (select a.time as t_time, a.city as city, a.reading as t_reading, h.time as h_time, h.reading as h_reading,
t_time-h_time as time_diff
from temparature as t
left join humidity as h
on t.time>=h.time and t.city = h.city) as a) as a
where rnk=1
Pandas Solution:
import pandas as pd
import numpy as np
temp_df=pd.DataFrame(temperature,columns=['time','city','reading'])
hum_df=pd.DataFrame(humidity,columns=['time','city','reading'])
comb_df = temp_df.merge(hum_df[['time','city','reading']],how='left',on=['city'])
comb_df.columns = ['t_time','city','t_reading','h_time','h_reading']
comb_df ['time_diff']= comb_df['t_time'] - comb_df['h_time']
comb_df ['time_diff_pos']= np.where(comb_df['t_time'] - comb_df['h_time']>0,1,0)
comb_df['Group_Rank'] = comb_df.groupby(['t_time','city','time_diff_pos'])['time_diff'].rank(method='dense', ascending=True)
comb_df = comb_df[comb_df['Group_Rank']==1]
comb_df['h_adj_rating'] = np.where(comb_df['time_diff_pos']==1,comb_df['h_reading'],None)
comb_df = comb_df[['t_time','city','t_reading','h_adj_rating']]
comb_df.head()
Answer by Paramartha Sengupta
import pandas as pd
Sample Data
temperature = [(1, "NYC", 12), (5, "SFO", 20), (7, "NYC", 23), (16, "SFO", 10)]
humidity = [(0, "SFO", 12), (3, "SFO", 2), (6, "NYC", 2), (19, "SFO", 9)]
temp_df = pd.DataFrame(temperature, columns=["time", "city", "temp_reading"])
hum_df = pd.DataFrame(humidity, columns=["time", "city", "hum_reading"])
Perform standard as-of merge
result_df = pd.merge_asof(
temp_df,
hum_df,
on="time",
by="city",
direction="backward", # Match humidity with time <= temp time
)
print(result_df)