How to reproduce an Excel and ImportExcel chart as a learning project

Python Help
1

I’m trying to teach myself some Python by adapting an Excel csv file plus a ps1 file combo that services a chart. That csv file has 3 blocks of data:

Block 1 labeled “intro” has two lines for physical location data.

Block 2 labeled “temperature” has 337 lines with headers “time” and “temperature_2m (°F)”. Time data includes time of day, e.g, “2026-04-23T00:00” and temperature column is the temp at that hour. The entire block covers 14 days of hourly time and temperature.

Block 3 labeled “sunset” has 15 lines, with headers “time” and “sunset (iso8601)”. The time line contains a day, e.g., “2026-04-23”. The “sunset (iso8601)” column contains the time of sunset of that day.

The Excel version looks up the temperature in Block 2 for each day in Block 3.

My code, so far, is:

import pandas as pd
import matplotlib.pyplot as plt
import matplotlib.dates as mdates
import requests

data_url = "https://api.open-meteo.com/v1/forecast?latitude=39.49&longitude=-119.9&daily=sunset&hourly=temperature_2m&timezone=America%2FLos_Angeles&past_days=7&forecast_days=7&temperature_unit=fahrenheit&format=csv"
response = requests.get(data_url)
temp_download = "G:/workspace/apples/pythons/downloaded_data.csv"
with open(temp_download, "wb") as f:
    f.write(response.content)

df = pd.read_csv(temp_download)
df = df.replace(to_replace="sunset (iso8601)", value = "sunset")
intro = pd.read_csv(temp_download, skiprows=0, nrows=2)
# temp: time, temperature_2m (°F)
temperature = pd.read_csv(temp_download, skiprows=3, nrows=337)
# sunset: time, sunset (iso8601)
sunset = pd.read_csv(temp_download, skiprows=341, nrows=15)

result = pd.merge_asof(sunset, temperature, left_on=pd.to_datetime("sunset"), right_on=pd.to_datetime("time"))
# result = pd.merge_asof(temperature, sunset, on='time', direction='backward')
print(result)

This version (pd.merge_asof(sunset, temperature, left_on…) results in the error:

Unknown datetime string format, unable to parse: sunset

The other version (pd.merge_asof(temperature, sunset, on='time'…) results in the error:

…both sides must have numeric dtype

How do I avoid either of those errors?

2

I don’t know what’s wrong, but you may want to try asking this on stackoverflow.

What does df.info() look like?

3

If you ask on Stack Overflow, make sure to include an minimal reproducible example! That probably means adding an example table to your question as sample data.

4

And in the process of doing that, you may flush out your bug.

5

Does this change the column’s header, even for later read_csv? If not (my hunch), that may explain the absence of the ”sunset” column (even though the error message is somewhat cryptic).
(Cannot verify atm as I’m on my phone)

6

temperature has date+time, sunset just date, so you cannot join as-is (even if parsing would work, which the error message suggests it doesn’t)

7

Thanks for the reply. Makes sense.

The stumper here is that I want to use the column “sunset (iso8601)” in the sunset block to look through the “time” column of the temperature block. Those columns are date+time. So I guess the question is how to have sunset.sunset (iso8601) search against temperature.time.

Where might I see an example table2.column2 searching against table1.column1?

PS: How to include icon of someone who replies?

8

Use df.rename() to change the column headers,
pandas.DataFrame.rename — pandas 3.0.2 documentation

And if you do another read_csv, that will return a new DataFrame, so the columns will revert to what is in the CSV file.
pandas.read_csv — pandas 3.0.2 documentation

Returns:

DataFrame or TextFileReader

A comma-separated values (csv) file is returned as two-dimensional data structure with labeled axes.
9

Thanks to all who have helped with this.

Took a bit more juggling but I’ve gotten a result. The major hitch came in parsing block 2. I must have miscounted the number of rows in Block 2; it ended up with it’s last line being the labels of block 3.

So after that, and a bunch of renaming columns and formatting time columns before trying a merge I got a (hopefully) meaningful result. Out of the resultant six columns I want to chart only two. That’s a future adventure. Here’s the current code:

import pandas as pd
import matplotlib.pyplot as plt
import matplotlib.dates as mdates
import requests
data_url = "https://api.open-meteo.com/v1/forecast?latitude=39.49&longitude=-119.9&daily=sunset&hourly=temperature_2m&timezone=America%2FLos_Angeles&past_days=7&forecast_days=7&temperature_unit=fahrenheit&format=csv"
response = requests.get(data_url)
temp_download = "G:/workspace/apples/pythons/downloaded_data.csv"
with open(temp_download, "wb") as f:
    f.write(response.content)

df = pd.read_csv(temp_download)

temperature = pd.read_csv(temp_download, skiprows=3, nrows=336)
temperature = temperature.rename(columns={'time': 'temp_time', 'temperature_2m (°F)': 'temp'})
temperature["temp_time"] = pd.to_datetime(temperature['temp_time'])

sunset_times = pd.read_csv(temp_download, skiprows=341, nrows=15)
sunset_times = sunset_times.rename(columns={'sunset (iso8601)': 'sunset_time'})
sunset_times["sunset_time"] = pd.to_datetime(sunset_times['sunset_time'])

result = pd.merge_asof(sunset_times, temperature, left_on=sunset_times['sunset_time'], right_on=temperature['temp_time'])

print(result)