Skip to content

Instantly share code, notes, and snippets.

@simonw
Created September 9, 2025 17:56
Show Gist options
  • Select an option

  • Save simonw/806e1aa0e6c29ad64834037f779e0dc0 to your computer and use it in GitHub Desktop.

Select an option

Save simonw/806e1aa0e6c29ad64834037f779e0dc0 to your computer and use it in GitHub Desktop.
Claude stuff
import pandas as pd
import matplotlib.pyplot as plt
import numpy as np
from datetime import datetime
import matplotlib.dates as mdates
# Load the data
df = pd.read_excel('/mnt/user-data/uploads/Employment-Size-Class-Sep-2025.xlsx', sheet_name='Response Estimates')
# Get AI adoption data (Question ID 7, Answer 'Yes')
ai_yes = df[(df['Question ID'] == 7.0) & (df['Answer'] == 'Yes')]
# Employment size mapping based on data dictionary
empsize_labels = {
'G': '250 or more employees',
'F': '100-249',
'E': '50-99',
'D': '20-49',
'C': '10-19',
'B': '5-9',
'A': '1-4'
}
# Colors to match the original chart (approximate)
colors = {
'G': '#8B1C62', # Dark purple for 250 or more
'F': '#FF6B35', # Orange for 100-249
'E': '#4CAF50', # Green for 50-99
'D': '#FFB74D', # Light orange for 20-49
'C': '#42A5F5', # Blue for 10-19
'B': '#AB47BC', # Purple for 5-9
'A': '#78909C' # Gray for 1-4
}
# Extract data for each employment size
data_dict = {}
date_columns = [col for col in ai_yes.columns if col.startswith('2023') or col.startswith('2024') or col.startswith('2025')]
date_columns.sort()
for empsize in ['A', 'B', 'C', 'D', 'E', 'F', 'G']:
row = ai_yes[ai_yes['Empsize'] == empsize]
if len(row) > 0:
values = []
for col in date_columns:
val = row[col].iloc[0]
if isinstance(val, str) and val.endswith('%'):
values.append(float(val[:-1]))
else:
values.append(np.nan)
data_dict[empsize] = values
# Convert date columns to proper dates
dates = []
for col in date_columns:
year = int(col[:4])
period = int(col[4:])
# Convert bi-weekly period to approximate month
month = max(1, min(12, round((period - 1) * 12 / 25) + 1))
dates.append(datetime(year, month, 1))
# Create the plot
plt.figure(figsize=(14, 8))
ax = plt.gca()
# Plot lines for each employment size category
for empsize in ['G', 'F', 'E', 'D', 'C', 'B', 'A']:
if empsize in data_dict:
plt.plot(dates, data_dict[empsize],
color=colors[empsize],
linewidth=2,
label=empsize_labels[empsize])
# Formatting to match the original
plt.title('AI adoption rates starting to decline for larger firms',
fontsize=16, fontweight='bold', pad=20)
# Add subtitle
plt.figtext(0.5, 0.85, 'AI adoption rate, by firm size',
ha='center', fontsize=14)
# Format axes
ax.set_ylabel('%', fontsize=12)
ax.set_ylim(0, 15)
ax.set_xlim(datetime(2023, 11, 1), datetime(2025, 9, 1))
# Format x-axis
ax.xaxis.set_major_locator(mdates.MonthLocator(interval=2))
ax.xaxis.set_major_formatter(mdates.DateFormatter('%b\n%Y'))
# Add grid
ax.grid(True, alpha=0.3, color='gray', linestyle='-', linewidth=0.5)
# Format legend to match original (horizontal layout)
legend = plt.legend(bbox_to_anchor=(0.5, 0.75), loc='center', ncol=4,
frameon=False, fontsize=10)
legend.get_frame().set_facecolor('none')
# Add percentage signs to y-axis
ax.set_yticks(range(0, 16, 2))
ax.set_yticklabels([f'{i}' for i in range(0, 16, 2)])
# Add a second y-axis on the right (matching original)
ax2 = ax.twinx()
ax2.set_ylabel('%', fontsize=12)
ax2.set_ylim(0, 15)
ax2.set_yticks(range(0, 16, 2))
ax2.set_yticklabels([f'{i}' for i in range(0, 16, 2)])
# Clean up appearance
ax.spines['top'].set_visible(False)
ax2.spines['top'].set_visible(False)
ax.tick_params(axis='x', rotation=0)
# Add "APOLLO" watermark in top right corner
plt.figtext(0.95, 0.95, 'APOLLO', ha='right', va='top',
fontsize=10, alpha=0.7, fontweight='bold')
plt.tight_layout()
plt.subplots_adjust(top=0.85)
plt.savefig('/mnt/user-data/outputs/ai_adoption_chart.png', dpi=300, bbox_inches='tight')
plt.show()
print("Chart created successfully!")
import pandas as pd
import matplotlib.pyplot as plt
import numpy as np
from datetime import datetime
import matplotlib.dates as mdates
# Load the data
df = pd.read_excel('/mnt/user-data/uploads/Employment-Size-Class-Sep-2025.xlsx', sheet_name='Response Estimates')
# Get AI adoption data (Question ID 7, Answer 'Yes')
ai_yes = df[(df['Question ID'] == 7.0) & (df['Answer'] == 'Yes')]
# Employment size mapping based on data dictionary (ordered by size)
empsize_labels = {
'G': '250 or more employees',
'F': '100-249',
'E': '50-99',
'D': '20-49',
'C': '10-19',
'B': '5-9',
'A': '1-4'
}
# Colors to closely match the original chart
colors = {
'G': '#7B1FA2', # Deep purple for largest firms
'F': '#FF7043', # Orange-red for 100-249
'E': '#388E3C', # Green for 50-99
'D': '#FFA726', # Orange for 20-49
'C': '#1976D2', # Blue for 10-19
'B': '#5E35B1', # Purple for 5-9
'A': '#757575' # Gray for smallest firms
}
# Extract data for each employment size
data_dict = {}
date_columns = [col for col in ai_yes.columns if col.startswith('2023') or col.startswith('2024') or col.startswith('2025')]
date_columns.sort()
for empsize in ['A', 'B', 'C', 'D', 'E', 'F', 'G']:
row = ai_yes[ai_yes['Empsize'] == empsize]
if len(row) > 0:
values = []
for col in date_columns:
val = row[col].iloc[0]
if isinstance(val, str) and val.endswith('%'):
values.append(float(val[:-1]))
else:
values.append(np.nan)
data_dict[empsize] = values
# Convert date columns to proper dates with better approximation
dates = []
for col in date_columns:
year = int(col[:4])
period = int(col[4:])
# More accurate conversion of bi-weekly periods to dates
# Assuming 26 periods per year, starting from January
week_of_year = (period - 1) * 2 + 1
month = min(12, max(1, int(week_of_year / 4.33) + 1))
dates.append(datetime(year, month, 1))
# Create the plot with styling to match original
plt.style.use('default') # Ensure clean styling
fig, ax = plt.subplots(figsize=(12, 7))
# Plot lines for each employment size category in order
line_width = 2.0
for empsize in ['G', 'F', 'E', 'D', 'C', 'B', 'A']:
if empsize in data_dict:
# Filter out NaN values for cleaner lines
x_vals = []
y_vals = []
for i, (date, val) in enumerate(zip(dates, data_dict[empsize])):
if not np.isnan(val):
x_vals.append(date)
y_vals.append(val)
ax.plot(x_vals, y_vals,
color=colors[empsize],
linewidth=line_width,
label=empsize_labels[empsize])
# Main title styling
plt.suptitle('AI adoption rates starting to decline for larger firms',
fontsize=18, fontweight='normal', y=0.95)
# Subtitle
ax.text(0.5, 0.92, 'AI adoption rate, by firm size',
transform=fig.transFigure, ha='center', fontsize=12, color='black')
# Y-axis formatting
ax.set_ylabel('%', fontsize=12, labelpad=15)
ax.set_ylim(2, 14)
ax.set_yticks(range(2, 15, 2))
ax.set_yticklabels([f'{i}' for i in range(2, 15, 2)])
# X-axis formatting
ax.set_xlim(datetime(2023, 10, 1), datetime(2025, 9, 1))
ax.xaxis.set_major_locator(mdates.MonthLocator(bymonth=[1, 4, 7, 10]))
ax.xaxis.set_major_formatter(mdates.DateFormatter('%b'))
# Add year labels
year_positions = [datetime(2023, 12, 1), datetime(2024, 6, 1), datetime(2025, 6, 1)]
for pos in year_positions:
ax.axvline(x=datetime(pos.year, 1, 1), color='gray', alpha=0.3, linestyle='-', linewidth=0.5)
# Manually add year labels
ax.text(datetime(2023, 11, 15), 1.5, '2023', ha='center', fontsize=11)
ax.text(datetime(2024, 6, 15), 1.5, '2024', ha='center', fontsize=11)
ax.text(datetime(2025, 6, 15), 1.5, '2025', ha='center', fontsize=11)
# Add right-side y-axis
ax2 = ax.twinx()
ax2.set_ylabel('%', fontsize=12, labelpad=15)
ax2.set_ylim(2, 14)
ax2.set_yticks(range(2, 15, 2))
ax2.set_yticklabels([f'{i}' for i in range(2, 15, 2)])
# Grid styling
ax.grid(True, alpha=0.2, color='gray', linestyle='-', linewidth=0.5)
# Legend styling - horizontal layout like original
legend_elements = []
for empsize in ['G', 'F', 'E', 'D', 'C', 'B', 'A']:
if empsize in data_dict:
legend_elements.append(plt.Line2D([0], [0], color=colors[empsize],
linewidth=2, label=empsize_labels[empsize]))
legend = ax.legend(handles=legend_elements,
bbox_to_anchor=(0.5, 0.82),
loc='center',
ncol=7, # All in one row
frameon=False,
fontsize=9,
columnspacing=1.0)
# Clean up spines
for spine in ax.spines.values():
spine.set_color('black')
spine.set_linewidth(0.5)
ax.spines['top'].set_visible(False)
ax2.spines['top'].set_visible(False)
# Add APOLLO watermark
ax.text(0.98, 0.98, 'APOLLO', transform=ax.transAxes,
ha='right', va='top', fontsize=10, alpha=0.6, fontweight='bold')
plt.tight_layout()
plt.subplots_adjust(top=0.88)
plt.savefig('/mnt/user-data/outputs/ai_adoption_chart_improved.png',
dpi=300, bbox_inches='tight', facecolor='white')
plt.show()
print("Improved chart created successfully!")
# Also save the data to CSV for reference
output_data = pd.DataFrame(data_dict, index=[d.strftime('%Y-%m') for d in dates])
output_data.to_csv('/mnt/user-data/outputs/ai_adoption_data.csv')
print("Data also saved to CSV file")
A B C D E F G
2023-09 4.3 3.0 2.6 2.6 2.4 2.5 3.1
2023-10 4.4 2.5 2.7 2.6 2.5 2.3 6.0
2023-10 4.2 3.3 3.3 2.7 3.0 3.8 3.8
2023-10 4.4 3.4 3.1 2.9 2.6 2.9 4.6
2023-11 4.8 3.6 3.9 3.4 4.3 4.7 7.3
2023-11 5.1 3.6 4.2 3.3 2.9 4.4 4.9
2023-12 5.5 4.1 4.0 3.3 4.3 4.4 6.7
2023-12 5.3 4.3 3.9 4.1 5.1 3.6 7.4
2024-01 5.1 3.6 4.0 4.1 3.9 3.5 6.5
2024-01 5.7 4.8 4.4 4.1 3.8 6.6 8.0
2024-02 5.3 4.9 4.1 3.7 4.5 5.9 8.0
2024-02 5.9 4.4 5.0 3.9 4.1 4.9 6.5
2024-03 4.8 3.3 3.6 3.5 2.4 4.5 6.2
2024-03 5.0 3.6 3.2 3.6 3.7 2.8 7.0
2024-04 4.6 3.8 3.4 3.4 3.2 3.8 5.3
2024-04 5.3 4.2 3.9 3.5 3.6 5.1 7.5
2024-04 5.1 4.1 4.2 3.5 3.8 5.4 6.1
2024-05 5.3 3.8 4.0 3.9 4.0 5.6 7.2
2024-05 5.1 3.9 3.6 3.3 3.6 4.6 5.3
2024-06 5.5 4.3 3.6 4.1 4.3 3.8 8.7
2024-06 5.2 4.2 4.0 3.7 3.6 4.1 6.1
2024-07 5.3 4.8 4.3 4.0 3.2 5.7 6.5
2024-07 5.6 4.0 4.6 4.1 4.3 4.5 9.7
2024-08 5.5 4.2 4.4 4.3 4.8 4.5 7.8
2024-08 6.1 4.2 4.1 4.3 4.4 6.3 8.3
2024-09 6.5 4.5 4.3 3.9 4.9 6.6 6.7
2024-09 6.6 4.8 4.0 4.4 6.1 5.6 9.2
2024-10 6.5 5.2 4.8 4.3 5.6 5.7 11.1
2024-10 6.6 4.6 5.2 4.9 4.9 5.6 9.7
2024-10 6.6 5.2 4.6 4.8 5.4 7.1 10.0
2024-11 6.6 5.2 5.0 5.0 5.5 5.6 9.4
2024-11 6.7 5.4 4.5 4.9 4.2 7.9 9.4
2024-12 6.7 5.3 4.6 4.5 6.1 8.2 10.5
2024-12 6.7 5.7 5.1 5.6 6.5 6.2 10.9
2025-01 6.5 4.6 4.8 5.2 6.2 5.7 9.8
2025-01 7.2 6.2 5.1 5.4 5.8 5.4 10.7
2025-02 7.3 6.1 5.4 5.4 6.3 5.8 12.4
2025-02 7.9 6.6 5.8 5.8 5.9 8.2 11.4
2025-03 8.1 6.6 5.7 5.2 8.5 8.6 12.2
2025-03 8.4 7.1 6.4 5.9 6.7 8.5 14.3
2025-04 8.6 6.6 7.0 7.0 6.4 7.7 11.9
2025-04 9.0 7.1 6.4 7.2 7.6 6.9 11.6
2025-04 9.5 7.4 6.5 7.3 7.6 9.0 14.2
2025-05 9.2 7.9 6.7 7.2 8.4 10.9 12.8
2025-05 9.7 8.7 7.6 7.4 9.4 9.9 14.9
2025-06 9.7 8.6 7.8 7.9 8.3 9.4 14.2
2025-06 9.9 7.7 8.3 8.1 8.9 9.8 12.5
2025-07 9.9 8.6 7.8 8.0 7.1 11.1 12.1
2025-07 10.4 7.6 7.5 7.0 7.0 9.5 12.0
2025-08 9.7 7.3 7.8 7.2 7.1 7.0 8.7
2025-08 10.8 8.5 7.0 7.8 7.3 8.7 11.9
View raw

(Sorry about that, but we can’t show files that are this big right now.)

View raw

(Sorry about that, but we can’t show files that are this big right now.)

View raw

(Sorry about that, but we can’t show files that are this big right now.)

View raw

(Sorry about that, but we can’t show files that are this big right now.)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment