Using Hex Notebook Agent For NYC Taxi Trip Data Analysis — First Impressions

TL;DR

I recently came across Hex and signed up for a 14-day trial. I am very impressed by how much work has been automated by Hex Notebook Agent and how easy Hex makes it for users. In this post, I am replicating the analysis from NYC Taxi Trip Data Analysis using Hex with the same NYC Taxi Trip dataset

Photo by Nick Fewings on Unsplash

Raw Data and Inspiration

I would assume that many people before me have used NYC Taxi Trip data in the past either for learning purposes or showcasing a new visualization tool. While I am late to the party, I am here now (^_~).

I can probably get the raw data from many sources (such as NYC Yellow Taxi Trip Data or TLC Trip Record Data and many more), but I am following this blog post: NYC Taxi Trip Data Analysis and essentially trying to achieve the same goals.

A link to the raw data is in that post and also provided here for quick reference (https://github.com/muharismrgn/NYC-Taxi-Trip-Data-Analysis/tree/main/data/raw).

Importing Data

Assuming a workspace in Hex has been created as the result of signing up for a free trial. Time to get the data in.

However, free trial plan does not provide an option to read the CSV file directly from a URL link to that file nor did I see AWS S3 as an option. No problem, we can download the file first. After that, dragging the file into the Hex notebook is pretty straightforward and after that Hex started doing its own magic.

Quality Check

From the chat window on the right I asked Hex to do a “quick quality check” and this is what it returned in the notebook

import pandas as pd
import numpy as np

df = NYC_TLC_Trip_Record

# Basic info
print(f"Total records: {len(df):,}")
print(f"Columns: {len(df.columns)}")
print(f"Date range: {df['lpep_pickup_datetime'].min()} to {df['lpep_pickup_datetime'].max()}")
print()

# Missing values
print("Missing values:")
missing = df.isnull().sum()
missing_pct = (missing / len(df) * 100).round(2)
missing_summary = pd.DataFrame({'count': missing[missing > 0], 'percent': missing_pct[missing > 0]})
if len(missing_summary) > 0:
    print(missing_summary)
else:
    print("No missing values detected")
print()

# Key metrics validation
print("Potential quality issues:")
issues = []

# Negative or zero fares
neg_fares = (df['fare_amount'] <= 0).sum()
if neg_fares > 0:
    issues.append(f"• {neg_fares:,} trips with fare ≤ $0 ({neg_fares/len(df)*100:.1f}%)")

# Zero distance trips
zero_dist = (df['trip_distance'] == 0).sum()
if zero_dist > 0:
    issues.append(f"• {zero_dist:,} trips with 0 distance ({zero_dist/len(df)*100:.1f}%)")

# Passenger count issues
invalid_pass = (df['passenger_count'] == 0).sum()
if invalid_pass > 0:
    issues.append(f"• {invalid_pass:,} trips with 0 passengers ({invalid_pass/len(df)*100:.1f}%)")

# Trips with same pickup/dropoff
same_location = (df['PULocationID'] == df['DOLocationID']).sum()
if same_location > 0:
    issues.append(f"• {same_location:,} trips with same pickup/dropoff ({same_location/len(df)*100:.1f}%)")

# Duration anomalies
df['duration_mins'] = (df['lpep_dropoff_datetime'] - df['lpep_pickup_datetime']).dt.total_seconds() / 60
short_trips = (df['duration_mins'] < 1).sum()
long_trips = (df['duration_mins'] > 180).sum()
if short_trips > 0:
    issues.append(f"• {short_trips:,} trips < 1 minute ({short_trips/len(df)*100:.1f}%)")
if long_trips > 0:
    issues.append(f"• {long_trips:,} trips > 3 hours ({long_trips/len(df)*100:.1f}%)")

if issues:
    for issue in issues:
        print(issue)
else:
    print("No obvious issues detected")

print()
print("Key statistics:")
print(f"Avg fare: ${df['fare_amount'].mean():.2f}")
print(f"Avg trip distance: {df['trip_distance'].mean():.2f} miles")
print(f"Avg passengers: {df['passenger_count'].mean():.2f}")
print(f"Avg trip duration: {df['duration_mins'].mean():.1f} minutes")

Looks great, isn’t it?

But we should double check ourselves.

I followed up with a ask to investigate and look for any duplicate records. (pardon the typo in the chat )

Ok, not a bad start. Let’s double check with what was being used by the blog post checking for duplicate. It was using the following columns

lpep_pickup_datetime
lpep_dropoff_datetime
RatecodeID
PULocationID
DOLocationID
passenger_count
trip_distance

Now it is uncovering bigger issue within the dataset and Hex was happily updating its answer to include both approaches.

df = NYC_TLC_Trip_Record

# 1. Exact duplicates (all columns identical)
exact_dupes = df.duplicated().sum()
print(f"Exact duplicates (all columns): {exact_dupes:,}")

if exact_dupes > 0:
    # Show some examples
    dupe_rows = df[df.duplicated(keep=False)].sort_values(['lpep_pickup_datetime', 'PULocationID'])
    print(f"\nExample duplicate records:")
    print(dupe_rows.head(10)[['lpep_pickup_datetime', 'PULocationID', 'DOLocationID', 'fare_amount', 'trip_distance']])

print("\n" + "="*60)

# 2. Logical duplicates (same key trip identifiers)
# Define key fields that should uniquely identify a trip
key_fields = ['lpep_pickup_datetime', 'lpep_dropoff_datetime', 'PULocationID', 'DOLocationID', 'fare_amount']

logical_dupes = df.duplicated(subset=key_fields).sum()
print(f"\nLogical duplicates (same trip details): {logical_dupes:,}")

if logical_dupes > 0:
    # Find groups with duplicates
    dupe_groups = df[df.duplicated(subset=key_fields, keep=False)].copy()
    dupe_groups = dupe_groups.sort_values(key_fields)

    print(f"Total records involved in duplicate groups: {len(dupe_groups):,}")
    print(f"\nSample duplicate groups:")
    print(dupe_groups.head(20)[key_fields + ['passenger_count', 'payment_type']])

    # Count of duplicate group sizes
    group_sizes = dupe_groups.groupby(key_fields).size()
    print(f"\nDuplicate group distribution:")
    print(group_sizes.value_counts().head(10))

print("\n" + "="*60)

# 3. Alternative key fields (trip characteristics)
key_fields_alt = ['lpep_pickup_datetime', 'lpep_dropoff_datetime', 'RatecodeID', 
                  'PULocationID', 'DOLocationID', 'passenger_count', 'trip_distance']

logical_dupes_alt = df.duplicated(subset=key_fields_alt).sum()
print(f"\nLogical duplicates (alternative definition - trip characteristics): {logical_dupes_alt:,}")

if logical_dupes_alt > 0:
    dupe_groups_alt = df[df.duplicated(subset=key_fields_alt, keep=False)].copy()
    dupe_groups_alt = dupe_groups_alt.sort_values(key_fields_alt)

    print(f"Total records involved in duplicate groups: {len(dupe_groups_alt):,}")
    print(f"\nSample duplicate groups:")
    print(dupe_groups_alt.head(20)[key_fields_alt + ['fare_amount', 'payment_type']])

    group_sizes_alt = dupe_groups_alt.groupby(key_fields_alt).size()
    print(f"\nDuplicate group distribution:")
    print(group_sizes_alt.value_counts().head(10))

print("\n" + "="*60)
print("\nRecommendation:")
if exact_dupes > 0:
    print(f"→ Remove {exact_dupes:,} exact duplicate rows")
elif logical_dupes_alt > logical_dupes:
    print(f"→ Alternative definition found {logical_dupes_alt:,} duplicates vs {logical_dupes:,} with original")
    print(f"→ Review these {logical_dupes_alt:,} records for potential duplicates")
elif logical_dupes > 0:
    print(f"→ Review {logical_dupes:,} logical duplicates - may be legitimate or data entry errors")
else:
    print("→ No duplicates found - data appears clean")

Exact duplicates (all columns): 0

============================================================

Logical duplicates (same trip details): 3
Total records involved in duplicate groups: 6

Sample duplicate groups:
      lpep_pickup_datetime lpep_dropoff_datetime  PULocationID  DOLocationID  \
30232  2023-01-16 03:39:38   2023-01-16 03:54:12           235            78   
30233  2023-01-16 03:39:38   2023-01-16 03:54:12           235            78   
30247  2023-01-16 04:11:30   2023-01-16 04:12:44            78            78   
30248  2023-01-16 04:11:30   2023-01-16 04:12:44            78            78   
36900  2023-01-19 13:49:39   2023-01-19 14:40:05            43            23   
36901  2023-01-19 13:49:39   2023-01-19 14:40:05            43            23   

       fare_amount  passenger_count  payment_type  
30232          0.0              1.0           2.0  
30233          0.0              1.0           2.0  
30247          0.0              1.0           2.0  
30248          0.0              1.0           2.0  
36900          0.0              1.0           2.0  
36901          0.0              1.0           2.0  

Duplicate group distribution:
2    3
Name: count, dtype: int64

============================================================

Logical duplicates (alternative definition - trip characteristics): 174
Total records involved in duplicate groups: 348

Sample duplicate groups:
     lpep_pickup_datetime lpep_dropoff_datetime  RatecodeID  PULocationID  \
662   2023-01-01 14:58:13   2023-01-01 14:58:39         1.0            75   
663   2023-01-01 14:58:13   2023-01-01 14:58:39         1.0            75   
898   2023-01-01 17:24:52   2023-01-01 17:25:12         1.0           193   
899   2023-01-01 17:24:52   2023-01-01 17:25:12         1.0           193   
1317  2023-01-02 02:05:08   2023-01-02 02:12:57         1.0           129   
1318  2023-01-02 02:05:08   2023-01-02 02:12:57         1.0           129   
1686  2023-01-02 12:56:53   2023-01-02 13:00:10         1.0            75   
1687  2023-01-02 12:56:53   2023-01-02 13:00:10         1.0            75   
1864  2023-01-02 13:51:33   2023-01-02 13:52:42         1.0            75   
1865  2023-01-02 13:51:33   2023-01-02 13:52:42         1.0            75   
1919  2023-01-02 14:03:10   2023-01-02 14:03:26         1.0            75   
1920  2023-01-02 14:03:10   2023-01-02 14:03:26         1.0            75   
2481  2023-01-02 19:03:48   2023-01-02 19:04:24         1.0           130   
2482  2023-01-02 19:03:48   2023-01-02 19:04:24         1.0           130   
3317  2023-01-03 10:18:45   2023-01-03 10:20:36         1.0           260   
3318  2023-01-03 10:18:45   2023-01-03 10:20:36         1.0           260   
3378  2023-01-03 11:30:37   2023-01-03 11:32:23         1.0           166   
3379  2023-01-03 11:30:37   2023-01-03 11:32:23         1.0           166   
3407  2023-01-03 11:41:06   2023-01-03 11:41:54         1.0           193   
3408  2023-01-03 11:41:06   2023-01-03 11:41:54         1.0           193   

      DOLocationID  passenger_count  trip_distance  fare_amount  payment_type  
662             74              2.0           0.17         -3.0           3.0  
663             74              2.0           0.17          3.0           3.0  
898            193              1.0           0.00         -3.0           4.0  
899            193              1.0           0.00          3.0           4.0  
1317            82              1.0           0.64         -8.6           3.0  
1318            82              1.0           0.64          8.6           3.0  
1686            75              1.0           0.77         -4.5           4.0  
1687            75              1.0           0.77          4.5           4.0  
1864            75              2.0           0.08         -3.7           3.0  
1865            75              2.0           0.08          3.7           3.0  
1919            75              1.0           0.00         -3.0           4.0  
1920            75              1.0           0.00          3.0           4.0  
2481           130              1.0           0.11         -3.0           3.0  
2482           130              1.0           0.11          3.0           3.0  
3317           260              4.0           0.00         -3.7           3.0  
3318           260              4.0           0.00          3.7           3.0  
3378           166              2.0           0.16         -3.7           3.0  
3379           166              2.0           0.16          3.7           3.0  
3407           193              1.0           0.00         -3.0           3.0  
3408           193              1.0           0.00          3.0           3.0  

Duplicate group distribution:
2    174
Name: count, dtype: int64

============================================================

Recommendation:
→ Alternative definition found 174 duplicates vs 3 with original
→ Review these 174 records for potential duplicates

Amazing!

Data Cleaning

Let’s do some clean up before moving forward using data.

Removing Duplicates

Based on our earlier discovery, we have identified which records are duplicate so I asked Hex to remove them.

df = NYC_TLC_Trip_Record.copy()

# Define the key fields for duplicate identification
key_fields = ['lpep_pickup_datetime', 'lpep_dropoff_datetime', 'RatecodeID', 
              'PULocationID', 'DOLocationID', 'passenger_count', 'trip_distance']

# Identify duplicate groups
duplicates_mask = df.duplicated(subset=key_fields, keep=False)
duplicate_records = df[duplicates_mask].copy()

print(f"Total records before cleaning: {len(df):,}")
print(f"Records in duplicate groups: {len(duplicate_records):,}")

# For duplicate groups, keep only records with positive fare_amount
# First, get indices to remove (negative fares in duplicate groups)
to_remove = duplicate_records[duplicate_records['fare_amount'] < 0].index

print(f"Records with negative fares to remove: {len(to_remove):,}")

# Create cleaned dataset
nyc_trips_clean = df.drop(to_remove)

print(f"Total records after cleaning: {len(nyc_trips_clean):,}")
print(f"Records removed: {len(df) - len(nyc_trips_clean):,}")

# Verify no negative fare duplicates remain
remaining_dupes = nyc_trips_clean.duplicated(subset=key_fields).sum()
print(f"\nRemaining duplicates after cleaning: {remaining_dupes}")

# Quick stats on removed records
print(f"\nRemoved records fare range: ${duplicate_records[duplicate_records['fare_amount'] < 0]['fare_amount'].min():.2f} to ${duplicate_records[duplicate_records['fare_amount'] < 0]['fare_amount'].max():.2f}")

Total records before cleaning: 68,211
Records in duplicate groups: 348
Records with negative fares to remove: 171
Total records after cleaning: 68,040
Records removed: 171

Remaining duplicates after cleaning: 3

Removed records fare range: $-70.00 to $-2.50

Negative Values

There are still 5 records which have negative fair amount value so we converted them to positive values.

# Convert any remaining negative fares to positive
negative_mask = nyc_trips_clean['fare_amount'] < 0
neg_count_before = negative_mask.sum()
if neg_count_before > 0:
    print(f"\nConverting {neg_count_before} remaining negative fares to positive values...")
    nyc_trips_clean.loc[negative_mask, 'fare_amount'] = nyc_trips_clean.loc[negative_mask, 'fare_amount'].abs()
    print(f"✓ All negative fares converted to positive")

Zero Values

There are records with zero passenger count which seem odd so we first calculated median value of passenger count from all other records and updated records without passenger count with this median value.

df = nyc_trips_clean.copy()

# Calculate median passenger count from valid (non-zero) records
valid_passenger_counts = df[df['passenger_count'] > 0]['passenger_count']
median_passengers = valid_passenger_counts.median()

print(f"Median passenger count (from valid records): {median_passengers}")

# Count records to update
zero_mask = df['passenger_count'] == 0
records_to_update = zero_mask.sum()

print(f"Records with 0 passenger_count: {records_to_update:,}")

# Update zero passenger counts to median
df.loc[zero_mask, 'passenger_count'] = median_passengers

# Verify update
remaining_zeros = (df['passenger_count'] == 0).sum()
print(f"Remaining zeros after update: {remaining_zeros}")
print(f"✓ Updated {records_to_update:,} records to passenger_count = {median_passengers}")

# Save as final cleaned dataset
nyc_trips_final = df

Median passenger count (from valid records): 1.0
Records with 0 passenger_count: 336
Remaining zeros after update: 0
✓ Updated 336 records to passenger_count = 1.0

Missing Values

For records with missing payment type, if tip amount is greater than zero, they are likely paid by credit card.

df = nyc_trips_final.copy()

# Identify records with missing payment_type and tip_amount > 0
missing_payment = df['payment_type'].isnull()
has_tip = df['tip_amount'] > 0
to_update = missing_payment & has_tip

records_to_update = to_update.sum()

print(f"Records with missing payment_type and tip_amount > 0: {records_to_update:,}")

# Update payment_type to 1 (credit card)
df.loc[to_update, 'payment_type'] = 1

# Verify update
remaining_missing = df['payment_type'].isnull().sum()
print(f"Remaining missing payment_type after update: {remaining_missing:,}")
print(f"✓ Updated {records_to_update:,} records to payment_type = 1 (credit card)")

# Save updated dataset
nyc_trips_updated = df

Records with missing payment_type and tip_amount > 0: 3,831
Remaining missing payment_type after update: 493
✓ Updated 3,831 records to payment_type = 1 (credit card)

The blog post also talked about fixing records with missing trip type based on RateCodeId but upon examination, I didn’t agree with the approach because none of the records have expected RateCodeId

df = nyc_trips_final

# Filter to records with missing trip_type
missing_trip_type = df[df['trip_type'].isnull()]

print(f"Total records with missing trip_type: {len(missing_trip_type):,}")
print()

# Get RatecodeID distribution
ratecode_dist = missing_trip_type['RatecodeID'].value_counts().sort_index()

print("RatecodeID distribution for missing trip_type:")
for ratecode, count in ratecode_dist.items():
    pct = count / len(missing_trip_type) * 100
    print(f"  RatecodeID {ratecode}: {count:,} ({pct:.1f}%)")

print()
print(f"Records with RatecodeID in [1,2,3]: {missing_trip_type['RatecodeID'].isin([1, 2, 3]).sum():,}")
print(f"Records with RatecodeID NOT in [1,2,3]: {(~missing_trip_type['RatecodeID'].isin([1, 2, 3])).sum():,}")

Total records with missing trip_type: 4,334

RatecodeID distribution for missing trip_type:
  RatecodeID 99.0: 10 (0.2%)

Records with RatecodeID in [1,2,3]: 0
Records with RatecodeID NOT in [1,2,3]: 4,334

Visualization

Now we have cleaned the records. Time to plot them on a graph.

Total Trip Distribution on January

First, we plot the daily trip count over the month of January 2023.

import matplotlib.pyplot as plt
import numpy as np
from matplotlib.collections import LineCollection
from matplotlib.colors import Normalize

df = nyc_trips_updated[
    (nyc_trips_updated['lpep_pickup_datetime'].dt.year == 2023) &
    (nyc_trips_updated['lpep_pickup_datetime'].dt.month == 1)
].copy()

# Count trips by date
df['date'] = df['lpep_pickup_datetime'].dt.date
trips_by_date = df.groupby('date').size().reset_index(name='trip_count')
trips_by_date['day_num'] = range(len(trips_by_date))

x = trips_by_date['day_num'].values
y = trips_by_date['trip_count'].values

# Create gradient line using LineCollection
fig, ax = plt.subplots(figsize=(14, 6))

points = np.array([x, y]).T.reshape(-1, 1, 2)
segments = np.concatenate([points[:-1], points[1:]], axis=1)

norm = Normalize(vmin=y.min(), vmax=y.max())
lc = LineCollection(segments, cmap='coolwarm', norm=norm, linewidth=4)
lc.set_array(y)
ax.add_collection(lc)

# Add scatter points
scatter = ax.scatter(x, y, c=y, cmap='coolwarm', s=70, zorder=3, 
                    edgecolors='white', linewidth=1.5)

# Formatting
ax.set_xlim(-0.5, len(x) - 0.5)
ax.set_ylim(0, y.max() * 1.1)
ax.set_xlabel('Day of January 2023', fontsize=12, fontweight='bold')
ax.set_ylabel('Trip Count', fontsize=12, fontweight='bold')
ax.set_title('Trip Distribution - January 2023', fontsize=14, fontweight='bold', pad=15)
ax.set_xticks(x[::2])
ax.set_xticklabels([f'{int(i)+1}' for i in x[::2]])
ax.grid(True, alpha=0.3)

# Add colorbar
cbar = fig.colorbar(scatter, ax=ax, label='Trip Count')

plt.tight_layout()
plt.show()

print(f"Peak day: {trips_by_date.loc[trips_by_date['trip_count'].idxmax(), 'date']} with {y.max():,} trips")
print(f"Lowest day: {trips_by_date.loc[trips_by_date['trip_count'].idxmin(), 'date']} with {y.min():,} trips")

Average Trip By Weekday

The average is calculated by:

1. Grouping trips by actual calendar date + day of week (e.g., Jan 2, Jan 9, Jan 16, Jan 23, Jan 30 are all Mondays)
2. Counting total trips for each specific date
3. Averaging those daily counts by day of week

import pandas as pd

df = nyc_trips_updated[
    (nyc_trips_updated['lpep_pickup_datetime'].dt.year == 2023) &
    (nyc_trips_updated['lpep_pickup_datetime'].dt.month == 1)
].copy()

df['day_of_week'] = df['lpep_pickup_datetime'].dt.day_name()
df['date'] = df['lpep_pickup_datetime'].dt.date

# Count trips per day, then average by day of week
daily_trips = df.groupby(['date', 'day_of_week']).size().reset_index(name='trip_count')
avg_by_dow = daily_trips.groupby('day_of_week')['trip_count'].mean().reset_index()
avg_by_dow.columns = ['day_of_week', 'avg_trips']

# Reorder to Mon-Sun
day_order = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday']
avg_by_dow['day_of_week'] = pd.Categorical(avg_by_dow['day_of_week'], categories=day_order, ordered=True)
avg_trips_jan = avg_by_dow.sort_values('day_of_week').reset_index(drop=True)

print("Average trips per day of week in January 2023:")
print(avg_trips_jan)

Average trips per day of week in January 2023:
  day_of_week  avg_trips
0      Monday    1992.80
1     Tuesday    2245.80
2   Wednesday    2464.25
3    Thursday    2545.75
4      Friday    2492.25
5    Saturday    2125.25
6      Sunday    1666.60

Total Trip By Hour

import pandas as pd

df = nyc_trips_updated[
    (nyc_trips_updated['lpep_pickup_datetime'].dt.year == 2023) &
    (nyc_trips_updated['lpep_pickup_datetime'].dt.month == 1)
].copy()

df['hour'] = df['lpep_pickup_datetime'].dt.hour
df['day_of_week'] = df['lpep_pickup_datetime'].dt.day_name()

# Count trips by hour and day of week
heatmap_data = df.groupby(['day_of_week', 'hour']).size().reset_index(name='trip_count')

# Pivot to wide format for heatmap
heatmap_pivot = heatmap_data.pivot(index='day_of_week', columns='hour', values='trip_count').fillna(0)

# Reorder rows to Mon-Sun
day_order = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday']
heatmap_pivot = heatmap_pivot.reindex(day_order)

print("Trip count heatmap (rows=day of week, columns=hour of day):")
print(heatmap_pivot)
print(f"\nShape: {heatmap_pivot.shape}")
print(f"Min trips: {heatmap_pivot.min().min():.0f}")
print(f"Max trips: {heatmap_pivot.max().max():.0f}")

Trip count heatmap (rows=day of week, columns=hour of day):
hour          0    1    2    3    4   5    6    7    8    9   ...   14   15  \
day_of_week                                                   ...             
Monday       127   87   62   81   65  62  144  413  478  553  ...  647  779   
Tuesday       92   58   48   46   23  65  206  585  730  719  ...  731  802   
Wednesday     97   72   35   30   20  49  187  453  570  584  ...  600  744   
Thursday     104   70   56   37   38  56  179  474  576  604  ...  656  730   
Friday       151   90   49   41   32  51  154  473  512  525  ...  681  775   
Saturday     238  201  148  128  106  54   55  135  212  279  ...  517  654   
Sunday       316  310  256  232  166  92   71  113  139  246  ...  565  609   

hour          16   17   18   19   20   21   22   23  
day_of_week                                          
Monday       776  774  778  608  414  344  250  162  
Tuesday      872  859  871  683  507  350  270  179  
Wednesday    788  819  800  667  461  314  236  169  
Thursday     765  849  861  607  491  345  262  195  
Friday       763  786  781  637  465  370  328  278  
Saturday     635  563  581  525  467  362  337  333  
Sunday       604  554  553  485  420  324  223  184  

[7 rows x 24 columns]

Shape: (7, 24)
Min trips: 20
Max trips: 872

import matplotlib.pyplot as plt
import seaborn as sns
import numpy as np

# Create figure with 2 subplots stacked vertically
fig, (ax1, ax2) = plt.subplots(2, 1, figsize=(16, 12), 
                                gridspec_kw={'height_ratios': [1, 1.5]})

# ===== TOP: Line graph =====
df_jan = nyc_trips_updated[
    (nyc_trips_updated['lpep_pickup_datetime'].dt.year == 2023) &
    (nyc_trips_updated['lpep_pickup_datetime'].dt.month == 1)
].copy()

df_jan['hour'] = df_jan['lpep_pickup_datetime'].dt.hour
trips_by_hour = df_jan.groupby('hour').size().reset_index(name='trip_count')

x = trips_by_hour['hour'].values
y = trips_by_hour['trip_count'].values

# Plot with gradient color effect
scatter = ax1.scatter(x, y, c=y, cmap='coolwarm', s=80, zorder=3, 
                     edgecolors='white', linewidth=2)
ax1.plot(x, y, linewidth=3, color='gray', alpha=0.5, zorder=2)

ax1.set_xlabel('Hour of Day', fontsize=12, fontweight='bold')
ax1.set_ylabel('Total Trip Count', fontsize=12, fontweight='bold')
ax1.set_title('Total Trip Count by Hour - January 2023', 
             fontsize=14, fontweight='bold', pad=15)
ax1.set_xticks(range(0, 24))
ax1.grid(True, alpha=0.3)
ax1.set_xlim(-0.5, 23.5)

cbar1 = fig.colorbar(scatter, ax=ax1, label='Trip Count')

# ===== BOTTOM: Heatmap =====
sns.heatmap(heatmap_pivot, 
            annot=True,
            fmt='.0f',
            cmap='RdBu_r',
            cbar_kws={'label': 'Trip Count'},
            linewidths=0.5,
            linecolor='white',
            ax=ax2)

ax2.set_xlabel('Hour of Day', fontsize=12, fontweight='bold')
ax2.set_ylabel('Day of Week', fontsize=12, fontweight='bold')
ax2.set_title('Trip Count by Hour and Day of Week - January 2023', 
             fontsize=14, fontweight='bold', pad=15)

plt.tight_layout()
plt.show()

Total Trips By Pickup Borough and Pickup Zone

import matplotlib.pyplot as plt
import numpy as np

df = nyc_trips_updated[
    (nyc_trips_updated['lpep_pickup_datetime'].dt.year == 2023) &
    (nyc_trips_updated['lpep_pickup_datetime'].dt.month == 1)
].copy()

# For now, I'll use Borough mapping based on common NYC taxi zone knowledge
# This is a simplified mapping - ideally would use official TLC zone lookup
borough_map = {
    # Manhattan (1-100 approx): Common zones
    **{i: 'Manhattan' for i in range(1, 101)},
    # Queens (101-200 approx): Common zones include EWR
    **{i: 'Queens' for i in range(101, 201)},
    # Bronx (201-260 approx)
    **{i: 'Bronx' for i in range(201, 261)},
    # Brooklyn (mainly in 200s range but mixed)
    **{i: 'Brooklyn' for i in [11, 12, 13, 14, 17, 21, 22, 25, 26, 29, 33, 34, 35, 36, 37, 39, 40, 49, 52, 54, 55, 61, 62, 63, 65, 66, 67, 71, 72, 76, 77, 80, 85, 89, 91, 94, 96, 97, 106, 108, 111, 112, 123, 133, 149, 150, 154, 155, 165, 177, 178, 181, 188, 189, 190, 195, 208, 217, 222, 225, 227, 228, 243, 244, 245, 250, 252, 256, 257]},
    # Staten Island
    **{i: 'Staten Island' for i in [121, 122, 123]},
}

# Apply borough mapping with fallback
df['borough'] = df['PULocationID'].map(lambda x: borough_map.get(int(x) if not np.isnan(x) else 0, 'Unknown'))

# Count trips by borough
borough_counts = df['borough'].value_counts().sort_values(ascending=False)

# Count trips by zone (top 15)
zone_counts = df['PULocationID'].value_counts().head(15).sort_values(ascending=True)

# Create color palette using coolwarm gradient (blue=low, red=high)
from matplotlib.colors import Normalize
from matplotlib.cm import ScalarMappable

# Normalize values for color mapping
norm_borough = Normalize(vmin=borough_counts.min(), vmax=borough_counts.max())
cmap = plt.cm.coolwarm
colors_borough = cmap(norm_borough(borough_counts.values))

# Colors for zones based on their trip counts
norm_zone = Normalize(vmin=zone_counts.min(), vmax=zone_counts.max())
zone_colors = cmap(norm_zone(zone_counts.values))

# Create side-by-side plots
fig, (ax1, ax2) = plt.subplots(1, 2, figsize=(18, 7))

# LEFT: Vertical bar chart - Borough
bars1 = ax1.bar(range(len(borough_counts)), borough_counts.values, color=colors_borough)
ax1.set_xlabel('Borough', fontsize=12, fontweight='bold')
ax1.set_ylabel('Total Trips', fontsize=12, fontweight='bold')
ax1.set_title('Total Trips by Pickup Borough - January 2023', fontsize=14, fontweight='bold', pad=15)
ax1.set_xticks(range(len(borough_counts)))
ax1.set_xticklabels(borough_counts.index, rotation=45, ha='right')
ax1.grid(axis='y', alpha=0.3)

# Add value labels on bars
for bar in bars1:
    height = bar.get_height()
    ax1.text(bar.get_x() + bar.get_width()/2., height,
             f'{int(height):,}',
             ha='center', va='bottom', fontsize=10, fontweight='bold')

# RIGHT: Horizontal bar chart - Top Zones
bars2 = ax2.barh(range(len(zone_counts)), zone_counts.values, color=zone_colors)
ax2.set_xlabel('Total Trips', fontsize=12, fontweight='bold')
ax2.set_ylabel('Pickup Zone (Location ID)', fontsize=12, fontweight='bold')
ax2.set_title('Total Trips by Top 15 Pickup Zones - January 2023', fontsize=14, fontweight='bold', pad=15)
ax2.set_yticks(range(len(zone_counts)))
ax2.set_yticklabels([f'Zone {int(x)}' for x in zone_counts.index])
ax2.grid(axis='x', alpha=0.3)

# Add value labels on bars
for i, bar in enumerate(bars2):
    width = bar.get_width()
    ax2.text(width, bar.get_y() + bar.get_height()/2.,
             f'{int(width):,}',
             ha='left', va='center', fontsize=9, fontweight='bold', 
             bbox=dict(boxstyle='round,pad=0.3', facecolor='white', alpha=0.7, edgecolor='none'))

plt.tight_layout()
plt.show()

print("Borough distribution:")
print(borough_counts)
print(f"\nTop pickup zone: Zone {zone_counts.index[-1]} with {zone_counts.values[-1]:,} trips")

Final Thoughts

I think it is clear by now that Hex is pretty good at both understanding the requirements as well as generating the expected solutions.

I am impressed by Hex because

• I never had to write any code.
• The code generated by hex works correctly almost the first time.
• Hex understood my intention correctly most of the time.
• Generated code is shown to the users, no holding back or hiding.
• I previously had used IBM’s Watson Studio not long before. While it is capable of generating beautiful visualization but it does not offer generated code that powers the graph. AI chatbot was not as powerful as what Hex provides. Granted that AI chatbot has improved significantly in the past couple years.

In conclusion, looking forward for opportunities leveraging Hex in the future.

Until next time.