1

I'm able to fetch the data, but I'm getting issues with the transformation process. Here's my code:

import requests
import pandas as pd
api_url = "https://api.open-meteo.com/v1/forecast?latitude=51.4325&longitude=6.7652&hourly=shortwave_radiation,direct_radiation,diffuse_radiation,direct_normal_irradiance,terrestrial_radiation" 
response = requests.get(api_url)

if response.status_code == 200:
        api_data = response.json()  # Die API-Daten im JSON-Format
else:
        print("Fehler beim Abrufen der API-Daten")
        api_data = None
   
df = pd.DataFrame(api_data)
df['hourly']=df['hourly'].replace("[","", regex=False)
df_new = df['hourly'].str.split(',',expand = True)

#df[['0','1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23']] = df['hourly'].str.split(',',expand=True)
df['hourly']
df.shape
dfP = df['hourly']
print(df)

I'm trying to transform the 'hourly' data into the structure where it splits into columns labeled from 0 to 24. If anyone could guide me on how to achieve this, I'd greatly appreciate it. Thank you!

Henry Ecker
  • 34,399
  • 18
  • 41
  • 57
Tobias Riepe
  • 19
  • 1
  • 1
    Your question needs a minimal reproducible example consisting of sample input, expected output, actual output, so that we can reproduce the problem. See [How to make good reproducible pandas examples](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) for best practices related to Pandas questions. – itprorh66 Aug 18 '23 at 21:25
  • Welcome to Stack Overflow! Check out the [tour], and [ask] if you want more tips (one of which is making a [mre]). – wjandrea Aug 18 '23 at 21:54

1 Answers1

0

The forecast API call returns by default predictions for 7 days (168 hours) (https://open-meteo.com/en/docs)

df = pd.DataFrame(api_data)

df
# Out: 
#                            latitude  longitude  generationtime_ms  ...  
# elevation hourly_units                                             hourly
# time                      51.440002       6.76           0.753999  ...       36.0      iso8601  [2023-08-18T00:00, 2023-08-18T01:00, 2023-08-1...
# shortwave_radiation       51.440002       6.76           0.753999  ...       36.0         W/m²  [0.0, 0.0, 0.0, 0.0, 0.0, 4.0, 53.0, 101.0, 18...
# direct_radiation          51.440002       6.76           0.753999  ...       36.0         W/m²  [0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 6.0, 9.0, 35.0,...
# diffuse_radiation         51.440002       6.76           0.753999  ...       36.0         W/m²  [0.0, 0.0, 0.0, 0.0, 0.0, 4.0, 47.0, 92.0, 148...
# direct_normal_irradiance  51.440002       6.76           0.753999  ...       36.0         W/m²  [0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 37.5, 28.4, 75....
# terrestrial_radiation     51.440002       6.76           0.753999  ...       36.0         W/m²  [0.0, 0.0, 0.0, 0.0, 0.0, 43.8, 213.3, 422.9, ...

# [6 rows x 9 columns]

Reset index

df.reset_index(inplace=True)

Split column of lists into dataframe columns

hours = pd.DataFrame(df.hourly.iloc[1:].tolist(), columns=df['hourly'].iloc[0])

# Out: 
#    2023-08-18T00:00  2023-08-18T01:00  2023-08-18T02:00  2023-08-18T03:00  ...  2023-08-24T20:00  2023-08-24T21:00  2023-08-24T22:00  2023-08-24T23:00
# 0               0.0               0.0               0.0               0.0  ...               0.0               0.0               0.0               0.0
# 1               0.0               0.0               0.0               0.0  ...               0.0               0.0               0.0               0.0
# 2               0.0               0.0               0.0               0.0  ...               0.0               0.0               0.0               0.0
# 3               0.0               0.0               0.0               0.0  ...               0.0               0.0               0.0               0.0
# 4               0.0               0.0               0.0               0.0  ...               0.0               0.0               0.0               0.0

[5 rows x 168 columns]

And finally add the new columns to the original dataframe:

pd.concat([df, hourly], axis=1)
# Out: 
#                       index   latitude  longitude  generationtime_ms  utc_offset_seconds  ... 2023-08-24T19:00 2023-08-24T20:00  2023-08-24T21:00 2023-08-24T22:00 2023-08-24T23:00
# 0                      time  51.440002       6.76           0.753999                   0  ...              9.0              0.0               0.0              0.0              0.0
# 1       shortwave_radiation  51.440002       6.76           0.753999                   0  ...              0.0              0.0               0.0              0.0              0.0
# 2          direct_radiation  51.440002       6.76           0.753999                   0  ...              9.0              0.0               0.0              0.0              0.0
# 3         diffuse_radiation  51.440002       6.76           0.753999                   0  ...              0.0              0.0               0.0              0.0              0.0
# 4  direct_normal_irradiance  51.440002       6.76           0.753999                   0  ...             45.6              0.0               0.0              0.0              0.0
# 5     terrestrial_radiation  51.440002       6.76           0.753999                   0  ...              NaN              NaN               NaN              NaN              NaN

[6 rows x 178 columns]
user2314737
  • 27,088
  • 20
  • 102
  • 114