如何在Python中使用while循环迭代的所有值创建一个Excel文件?

huangapple go评论68阅读模式
英文:

How to create an excel file with all values of an while loop iteration in python?

问题

我想要以下代码将所有的输出保存在一个Excel文件中但每次我尝试运行代码时只保存最后一次迭代的结果但是当我在空闲状态下运行它时得到第二个结果

```python
import urllib.request
import datetime as dt
from datetime import datetime, timezone, tzinfo
import cdflib
import numpy as np
import pandas as pd
from cdflib.epochs import CDFepoch
import pathlib
from xlsxwriter import Workbook
from openpyxl import Workbook

result = []
months = ['01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12']
days = ['01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12']

for year in range(2014, 2019, 1):
    for month in range(len(months)):
        for day in range(len(days)):
            def get_day_data(station: str, year, month, day):
                is_pg = station.startswith("pg")
                if is_pg:
                    url = f"http://mist.nianet.org/CDFdata/{station}/{year}/thg_l2_mag_{station}_{year}{month}{day}_v01.cdf"
                else:
                    url = f"https://ftp.space.dtu.dk/data/Ground_magnetometers/Adjusted/{station.upper()}/{year}/{month}/{station.upper()}_{year}{month}{day}.cdf"

                file_name = url.split("/")[-1]
                if not pathlib.Path(file_name).exists():
                    try:
                        urllib.request.urlretrieve(url, "tmp.cdf")
                        pathlib.Path("tmp.cdf").rename(file_name)
                    except:
                        raise Exception(f"Failed to download {url}")

                cdf = cdflib.CDF(file_name)

                if is_pg:
                    epoch = cdf.varget(f"thg_mag_{station}_time")
                    index = pd.to_datetime(epoch, utc=False, unit="s")
                    #index = index.round("1s")
                    variable = f"thg_mag_{station}"
                else:
                    epoch = cdf.varget("time")
                    epoch = CDFepoch().to_datetime(epoch, to_np=True)
                    index = pd.to_datetime(epoch)
                    variable = "HEZ"

                values = cdf.varget(variable)
                if "FILLVAL" in cdf.varattsget(variable):
                    values[values == cdf.attget("FILLVAL", variable)["Data"]] = np.nan
                if "VALIDMIN" in cdf.varattsget(variable):
                    values[values < cdf.attget("VALIDMIN", variable)["Data"]] = np.nan
                if "VALIDMAX" in cdf.varattsget(variable):
                    values[values > cdf.attget("VALIDMAX", variable)["Data"]] = np.nan

                return pd.DataFrame({"epoch": index, "X": values[:, 0], "Y": values[:, 1], "Z": values[:, 2]})

            year += 1
            output = pd.DataFrame(np.array(get_day_data('pg0', year, months[month], days[day])))
            result.append(output)
            print(result)

[包含结果的数据帧]
[包含结果的数据帧]
[包含结果的数据帧]


请注意,代码中有一些缩进错误,如果要运行它,需要将其修复。此外,代码中的结果将追加到`result`列表中,因此`result`中将包含多个数据帧。
<details>
<summary>英文:</summary>
I want the following code to save all the outputs in an excel file but every time I try the code all I get the result of the last iteration saved. Yet, when I run it in idle, I get the second result. 
import urllib.request
import datetime as dt
from datetime import datetime, timezone, tzinfo
import cdflib
import numpy as np
import pandas as pd
from cdflib.epochs import CDFepoch
import pathlib
from xlsxwriter import Workbook
from openpyxl import Workbook
result = []
months = [&#39;01&#39;,&#39;02&#39;,&#39;03&#39;,&#39;04&#39;,&#39;05&#39;,&#39;06&#39;,&#39;07&#39;,&#39;08&#39;,&#39;09&#39;,&#39;10&#39;,&#39;11&#39;,&#39;12&#39;]
days = [&#39;01&#39;,&#39;02&#39;,&#39;03&#39;,&#39;04&#39;,&#39;05&#39;,&#39;06&#39;,&#39;07&#39;,&#39;08&#39;,&#39;09&#39;,&#39;10&#39;,&#39;11&#39;,&#39;12&#39;]
for year in range(2014,2019,1):
for month in range(len(months)):
for day in range(len(days)):
def get_day_data(station: str, year, month, day):
is_pg = station.startswith(&quot;pg&quot;) 
if is_pg:
url = f&quot;http://mist.nianet.org/CDFdata/{station}/{year}/thg_l2_mag_{station}_{year}{month}{day}_v01.cdf&quot;
else:
url = f&quot;https://ftp.space.dtu.dk/data/Ground_magnetometers/Adjusted/{station.upper()}/{year}/{month}/{station.upper()}_{year}{month}{day}.cdf&quot;
file_name = url.split(&quot;/&quot;)[-1]
if not pathlib.Path(file_name).exists():
try:
urllib.request.urlretrieve(url, &quot;tmp.cdf&quot;)
pathlib.Path(&quot;tmp.cdf&quot;).rename(file_name)
except:
raise Exception(f&quot;Failed to download {url}&quot;)
cdf = cdflib.CDF(file_name)
if is_pg:
epoch = cdf.varget(f&quot;thg_mag_{station}_time&quot;)
index = pd.to_datetime(epoch, utc=False, unit=&quot;s&quot;)
#index = index.round(&quot;1s&quot;)
variable = f&quot;thg_mag_{station}&quot;
else:
epoch = cdf.varget(&quot;time&quot;)
epoch = CDFepoch().to_datetime(epoch, to_np=True)
index = pd.to_datetime(epoch)
variable = &quot;HEZ&quot;
values = cdf.varget(variable)
if &quot;FILLVAL&quot; in cdf.varattsget(variable):
values[values == cdf.attget(&quot;FILLVAL&quot;, variable)[&quot;Data&quot;]] = np.nan
if &quot;VALIDMIN&quot; in cdf.varattsget(variable):
values[values &lt; cdf.attget(&quot;VALIDMIN&quot;, variable)[&quot;Data&quot;]] = np.nan
if &quot;VALIDMAX&quot; in cdf.varattsget(variable):
values[values &gt; cdf.attget(&quot;VALIDMAX&quot;, variable)[&quot;Data&quot;]] = np.nan
return pd.DataFrame({&quot;epoch&quot;: index,
&quot;X&quot;: values[:, 0],
&quot;Y&quot;: values[:, 1],
&quot;Z&quot;: values[:, 2]})
year += 1
output = pd.DataFrame(np.array(get_day_data(&#39;pg0&#39;, year, months[month], days[day])))
result.append(output)
print(result)
[                                  0             1          2             3
0     2015-01-01 00:00:00.000999936  15152.530273 -79.599998 -53694.988281
1     2015-01-01 00:00:01.001000192  15152.589844 -79.599998 -53695.050781
2     2015-01-01 00:00:02.000999680  15152.589844 -79.599998 -53695.050781
3     2015-01-01 00:00:03.000999936  15152.650391 -79.599998 -53695.050781
4     2015-01-01 00:00:04.001000192  15152.650391 -79.599998 -53695.109375
...                             ...           ...        ...           ...
86395 2015-01-01 23:59:55.000999936  15206.650391     -16.33 -53680.210938
86396 2015-01-01 23:59:56.000999680  15206.709961 -16.389999 -53680.210938
86397 2015-01-01 23:59:57.000999936  15206.769531 -16.389999 -53680.210938
86398 2015-01-01 23:59:58.001000192  15206.830078 -16.389999 -53680.210938
86399 2015-01-01 23:59:59.000999680  15206.830078 -16.389999 -53680.210938
[86400 rows x 4 columns]]
[                                  0             1          2             3
0     2015-01-01 00:00:00.000999936  15152.530273 -79.599998 -53694.988281
1     2015-01-01 00:00:01.001000192  15152.589844 -79.599998 -53695.050781
2     2015-01-01 00:00:02.000999680  15152.589844 -79.599998 -53695.050781
3     2015-01-01 00:00:03.000999936  15152.650391 -79.599998 -53695.050781
4     2015-01-01 00:00:04.001000192  15152.650391 -79.599998 -53695.109375
...                             ...           ...        ...           ...
86395 2015-01-01 23:59:55.000999936  15206.650391     -16.33 -53680.210938
86396 2015-01-01 23:59:56.000999680  15206.709961 -16.389999 -53680.210938
86397 2015-01-01 23:59:57.000999936  15206.769531 -16.389999 -53680.210938
86398 2015-01-01 23:59:58.001000192  15206.830078 -16.389999 -53680.210938
86399 2015-01-01 23:59:59.000999680  15206.830078 -16.389999 -53680.210938
[86400 rows x 4 columns],                                   0             1           2             3
0     2016-01-02 00:00:00.000999936  15220.959961  -82.709999 -53625.089844
1     2016-01-02 00:00:01.001000192  15220.959961  -82.709999 -53625.089844
2     2016-01-02 00:00:02.000999680  15220.959961  -82.769997 -53625.199219
3     2016-01-02 00:00:03.000999936  15220.959961  -82.769997 -53625.320312
4     2016-01-02 00:00:04.001000192  15220.959961  -82.769997 -53625.441406
...                             ...           ...         ...           ...
86395 2016-01-02 23:59:55.000999936   15125.44043 -124.110001 -53659.800781
86396 2016-01-02 23:59:56.000999680       15125.5 -124.110001 -53659.738281
86397 2016-01-02 23:59:57.000999936   15125.55957 -124.110001 -53659.738281
86398 2016-01-02 23:59:58.001000192   15125.55957 -124.110001 -53659.691406
86399 2016-01-02 23:59:59.000999680  15125.610352 -124.110001 -53659.691406
[86400 rows x 4 columns]]
[                                  0             1          2             3
0     2015-01-01 00:00:00.000999936  15152.530273 -79.599998 -53694.988281
1     2015-01-01 00:00:01.001000192  15152.589844 -79.599998 -53695.050781
2     2015-01-01 00:00:02.000999680  15152.589844 -79.599998 -53695.050781
3     2015-01-01 00:00:03.000999936  15152.650391 -79.599998 -53695.050781
4     2015-01-01 00:00:04.001000192  15152.650391 -79.599998 -53695.109375
...                             ...           ...        ...           ...
86395 2015-01-01 23:59:55.000999936  15206.650391     -16.33 -53680.210938
86396 2015-01-01 23:59:56.000999680  15206.709961 -16.389999 -53680.210938
86397 2015-01-01 23:59:57.000999936  15206.769531 -16.389999 -53680.210938
86398 2015-01-01 23:59:58.001000192  15206.830078 -16.389999 -53680.210938
86399 2015-01-01 23:59:59.000999680  15206.830078 -16.389999 -53680.210938
[86400 rows x 4 columns],    
</details>
# 答案1
**得分**: 0
以下是您要翻译的代码部分:
```python
import urllib.request
import datetime as dt
from datetime import datetime, timezone, tzinfo
import cdflib
import numpy as np
import pandas as pd
from cdflib.epochs import CDFepoch
import pathlib
from xlsxwriter import Workbook
from openpyxl import Workbook
def get_day_data(station, year, month, day):
is_pg = station.startswith("pg") 
if is_pg:
url = f"http://mist.nianet.org/CDFdata/{station}/{year}/thg_l2_mag_{station}_{year}{month}{day}_v01.cdf"
else:
url = f"https://ftp.space.dtu.dk/data/Ground_magnetometers/Adjusted/{station.upper()}/{year}/{month}/{station.upper()}_{year}{month}{day}.cdf"
file_name = url.split("/")[-1]
if not pathlib.Path(file_name).exists():
try:
urllib.request.urlretrieve(url, "tmp.cdf")
pathlib.Path("tmp.cdf").rename(file_name)
except:
#raise Exception(f"Failed to download {url}")
return None
cdf = cdflib.CDF(file_name)
if is_pg:
epoch = cdf.varget(f"thg_mag_{station}_time")
index = pd.to_datetime(epoch, utc=False, unit="s")
#index = index.round("1s")
variable = f"thg_mag_{station}"
else:
epoch = cdf.varget("time")
epoch = CDFepoch().to_datetime(epoch, to_np=True)
index = pd.to_datetime(epoch)
variable = "HEZ"
values = cdf.varget(variable)
if "FILLVAL" in cdf.varattsget(variable):
values[values == cdf.attget("FILLVAL", variable)["Data"]] = np.nan
if "VALIDMIN" in cdf.varattsget(variable):
values[values < cdf.attget("VALIDMIN", variable)["Data"]] = np.nan
if "VALIDMAX" in cdf.varattsget(variable):
values[values > cdf.attget("VALIDMAX", variable)["Data"]] = np.nan
return pd.DataFrame({"epoch": index,
"X": values[:, 0],
"Y": values[:, 1],
"Z": values[:, 2]})
result = pd.DataFrame()
months = ['01','02','03','04','05','06','07','08','09','10','11','12']
days = ['01','02','03','04','05','06','07','08','09','10','11','12']
for year in range(2015,2019,1):
for month in range(len(months)):
for day in range(len(days)):
#output = pd.DataFrame(np.array(get_day_data('pg0', year, months[month], days[day])))
output = get_day_data('pg0', str(year), months[month], days[day])
if output is not None:
result = pd.concat([result, output])
print(result)
英文:

I tweaked it a little bit to test your code. It works after a few changes. Check if this is the output you expect.

import urllib.request
import datetime as dt
from datetime import datetime, timezone, tzinfo
import cdflib
import numpy as np
import pandas as pd
from cdflib.epochs import CDFepoch
import pathlib
from xlsxwriter import Workbook
from openpyxl import Workbook
def get_day_data(station, year, month, day):
is_pg = station.startswith(&quot;pg&quot;) 
if is_pg:
url = f&quot;http://mist.nianet.org/CDFdata/{station}/{year}/thg_l2_mag_{station}_{year}{month}{day}_v01.cdf&quot;
else:
url = f&quot;https://ftp.space.dtu.dk/data/Ground_magnetometers/Adjusted/{station.upper()}/{year}/{month}/{station.upper()}_{year}{month}{day}.cdf&quot;
file_name = url.split(&quot;/&quot;)[-1]
if not pathlib.Path(file_name).exists():
try:
urllib.request.urlretrieve(url, &quot;tmp.cdf&quot;)
pathlib.Path(&quot;tmp.cdf&quot;).rename(file_name)
except:
#raise Exception(f&quot;Failed to download {url}&quot;)
return None
cdf = cdflib.CDF(file_name)
if is_pg:
epoch = cdf.varget(f&quot;thg_mag_{station}_time&quot;)
index = pd.to_datetime(epoch, utc=False, unit=&quot;s&quot;)
#index = index.round(&quot;1s&quot;)
variable = f&quot;thg_mag_{station}&quot;
else:
epoch = cdf.varget(&quot;time&quot;)
epoch = CDFepoch().to_datetime(epoch, to_np=True)
index = pd.to_datetime(epoch)
variable = &quot;HEZ&quot;
values = cdf.varget(variable)
if &quot;FILLVAL&quot; in cdf.varattsget(variable):
values[values == cdf.attget(&quot;FILLVAL&quot;, variable)[&quot;Data&quot;]] = np.nan
if &quot;VALIDMIN&quot; in cdf.varattsget(variable):
values[values &lt; cdf.attget(&quot;VALIDMIN&quot;, variable)[&quot;Data&quot;]] = np.nan
if &quot;VALIDMAX&quot; in cdf.varattsget(variable):
values[values &gt; cdf.attget(&quot;VALIDMAX&quot;, variable)[&quot;Data&quot;]] = np.nan
return pd.DataFrame({&quot;epoch&quot;: index,
&quot;X&quot;: values[:, 0],
&quot;Y&quot;: values[:, 1],
&quot;Z&quot;: values[:, 2]})
result = pd.DataFrame()
months = [&#39;01&#39;,&#39;02&#39;,&#39;03&#39;,&#39;04&#39;,&#39;05&#39;,&#39;06&#39;,&#39;07&#39;,&#39;08&#39;,&#39;09&#39;,&#39;10&#39;,&#39;11&#39;,&#39;12&#39;]
days = [&#39;01&#39;,&#39;02&#39;,&#39;03&#39;,&#39;04&#39;,&#39;05&#39;,&#39;06&#39;,&#39;07&#39;,&#39;08&#39;,&#39;09&#39;,&#39;10&#39;,&#39;11&#39;,&#39;12&#39;]
for year in range(2015,2019,1):
for month in range(len(months)):
for day in range(len(days)):
#output = pd.DataFrame(np.array(get_day_data(&#39;pg0&#39;, year, months[month], days[day])))
output = get_day_data(&#39;pg0&#39;, str(year), months[month], days[day])
if output is not None:
result = pd.concat([result, output])
print(result)
#Output
#                              epoch             X           Y             Z
#0     2015-01-01 00:00:00.000999936  15152.530273  -79.599998 -53694.988281
#1     2015-01-01 00:00:01.001000192  15152.589844  -79.599998 -53695.050781
#2     2015-01-01 00:00:02.000999680  15152.589844  -79.599998 -53695.050781
#3     2015-01-01 00:00:03.000999936  15152.650391  -79.599998 -53695.050781
#4     2015-01-01 00:00:04.001000192  15152.650391  -79.599998 -53695.109375
#...                             ...           ...         ...           ...
#86395 2018-12-12 23:59:55.000999936  14926.240234 -115.320000 -53617.519531
#86396 2018-12-12 23:59:56.000999680  14926.299805 -115.320000 -53617.519531
#86397 2018-12-12 23:59:57.000999936  14926.299805 -115.320000 -53617.519531
#86398 2018-12-12 23:59:58.001000192  14926.299805 -115.320000 -53617.519531
#86399 2018-12-12 23:59:59.000999680  14926.299805 -115.320000 -53617.519531
#[32745600 rows x 4 columns]

huangapple
  • 本文由 发表于 2023年2月19日 11:03:26
  • 转载请务必保留本文链接:https://go.coder-hub.com/75497747.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定