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

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

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

问题

  1. 我想要以下代码将所有的输出保存在一个Excel文件中，但每次我尝试运行代码时，只保存最后一次迭代的结果。但是，当我在空闲状态下运行它时，得到第二个结果。
  3. ```python
  4. import urllib.request
  5. import datetime as dt
  6. from datetime import datetime, timezone, tzinfo
  7. import cdflib
  8. import numpy as np
  9. import pandas as pd
  10. from cdflib.epochs import CDFepoch
  11. import pathlib
  12. from xlsxwriter import Workbook
  13. from openpyxl import Workbook
  15. result = []
  16. months = ['01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12']
  17. days = ['01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12']
  19. for year in range(2014, 2019, 1):
  20.     for month in range(len(months)):
  21.         for day in range(len(days)):
  22.             def get_day_data(station: str, year, month, day):
  23.                 is_pg = station.startswith("pg")
  24.                 if is_pg:
  25.                     url = f"http://mist.nianet.org/CDFdata/{station}/{year}/thg_l2_mag_{station}_{year}{month}{day}_v01.cdf"
  26.                 else:
  27.                     url = f"https://ftp.space.dtu.dk/data/Ground_magnetometers/Adjusted/{station.upper()}/{year}/{month}/{station.upper()}_{year}{month}{day}.cdf"
  29.                 file_name = url.split("/")[-1]
  30.                 if not pathlib.Path(file_name).exists():
  31.                     try:
  32.                         urllib.request.urlretrieve(url, "tmp.cdf")
  33.                         pathlib.Path("tmp.cdf").rename(file_name)
  34.                     except:
  35.                         raise Exception(f"Failed to download {url}")
  37.                 cdf = cdflib.CDF(file_name)
  39.                 if is_pg:
  40.                     epoch = cdf.varget(f"thg_mag_{station}_time")
  41.                     index = pd.to_datetime(epoch, utc=False, unit="s")
  42.                     #index = index.round("1s")
  43.                     variable = f"thg_mag_{station}"
  44.                 else:
  45.                     epoch = cdf.varget("time")
  46.                     epoch = CDFepoch().to_datetime(epoch, to_np=True)
  47.                     index = pd.to_datetime(epoch)
  48.                     variable = "HEZ"
  50.                 values = cdf.varget(variable)
  51.                 if "FILLVAL" in cdf.varattsget(variable):
  52.                     values[values == cdf.attget("FILLVAL", variable)["Data"]] = np.nan
  53.                 if "VALIDMIN" in cdf.varattsget(variable):
  54.                     values[values < cdf.attget("VALIDMIN", variable)["Data"]] = np.nan
  55.                 if "VALIDMAX" in cdf.varattsget(variable):
  56.                     values[values > cdf.attget("VALIDMAX", variable)["Data"]] = np.nan
  58.                 return pd.DataFrame({"epoch": index, "X": values[:, 0], "Y": values[:, 1], "Z": values[:, 2]})
  60.             year += 1
  61.             output = pd.DataFrame(np.array(get_day_data('pg0', year, months[month], days[day])))
  62.             result.append(output)
  63.             print(result)

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

  2. 请注意，代码中有一些缩进错误，如果要运行它，需要将其修复。此外，代码中的结果将追加到`result`列表中，因此`result`中将包含多个数据帧。
  3. <details>
  4. <summary>英文:</summary>
  5. 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. 
  6. import urllib.request
  7. import datetime as dt
  8. from datetime import datetime, timezone, tzinfo
  9. import cdflib
  10. import numpy as np
  11. import pandas as pd
  12. from cdflib.epochs import CDFepoch
  13. import pathlib
  14. from xlsxwriter import Workbook
  15. from openpyxl import Workbook
  16. result = []
  17. 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;]
  18. 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;]
  19. for year in range(2014,2019,1):
  20. for month in range(len(months)):
  21. for day in range(len(days)):
  22. def get_day_data(station: str, year, month, day):
  23. is_pg = station.startswith(&quot;pg&quot;) 
  24. if is_pg:
  25. url = f&quot;http://mist.nianet.org/CDFdata/{station}/{year}/thg_l2_mag_{station}_{year}{month}{day}_v01.cdf&quot;
  26. else:
  27. url = f&quot;https://ftp.space.dtu.dk/data/Ground_magnetometers/Adjusted/{station.upper()}/{year}/{month}/{station.upper()}_{year}{month}{day}.cdf&quot;
  28. file_name = url.split(&quot;/&quot;)[-1]
  29. if not pathlib.Path(file_name).exists():
  30. try:
  31. urllib.request.urlretrieve(url, &quot;tmp.cdf&quot;)
  32. pathlib.Path(&quot;tmp.cdf&quot;).rename(file_name)
  33. except:
  34. raise Exception(f&quot;Failed to download {url}&quot;)
  35. cdf = cdflib.CDF(file_name)
  36. if is_pg:
  37. epoch = cdf.varget(f&quot;thg_mag_{station}_time&quot;)
  38. index = pd.to_datetime(epoch, utc=False, unit=&quot;s&quot;)
  39. #index = index.round(&quot;1s&quot;)
  40. variable = f&quot;thg_mag_{station}&quot;
  41. else:
  42. epoch = cdf.varget(&quot;time&quot;)
  43. epoch = CDFepoch().to_datetime(epoch, to_np=True)
  44. index = pd.to_datetime(epoch)
  45. variable = &quot;HEZ&quot;
  46. values = cdf.varget(variable)
  47. if &quot;FILLVAL&quot; in cdf.varattsget(variable):
  48. values[values == cdf.attget(&quot;FILLVAL&quot;, variable)[&quot;Data&quot;]] = np.nan
  49. if &quot;VALIDMIN&quot; in cdf.varattsget(variable):
  50. values[values &lt; cdf.attget(&quot;VALIDMIN&quot;, variable)[&quot;Data&quot;]] = np.nan
  51. if &quot;VALIDMAX&quot; in cdf.varattsget(variable):
  52. values[values &gt; cdf.attget(&quot;VALIDMAX&quot;, variable)[&quot;Data&quot;]] = np.nan
  53. return pd.DataFrame({&quot;epoch&quot;: index,
  54. &quot;X&quot;: values[:, 0],
  55. &quot;Y&quot;: values[:, 1],
  56. &quot;Z&quot;: values[:, 2]})
  57. year += 1
  58. output = pd.DataFrame(np.array(get_day_data(&#39;pg0&#39;, year, months[month], days[day])))
  59. result.append(output)
  60. print(result)
  61. [                                  0             1          2             3
  62. 0     2015-01-01 00:00:00.000999936  15152.530273 -79.599998 -53694.988281
  63. 1     2015-01-01 00:00:01.001000192  15152.589844 -79.599998 -53695.050781
  64. 2     2015-01-01 00:00:02.000999680  15152.589844 -79.599998 -53695.050781
  65. 3     2015-01-01 00:00:03.000999936  15152.650391 -79.599998 -53695.050781
  66. 4     2015-01-01 00:00:04.001000192  15152.650391 -79.599998 -53695.109375
  67. ...                             ...           ...        ...           ...
  68. 86395 2015-01-01 23:59:55.000999936  15206.650391     -16.33 -53680.210938
  69. 86396 2015-01-01 23:59:56.000999680  15206.709961 -16.389999 -53680.210938
  70. 86397 2015-01-01 23:59:57.000999936  15206.769531 -16.389999 -53680.210938
  71. 86398 2015-01-01 23:59:58.001000192  15206.830078 -16.389999 -53680.210938
  72. 86399 2015-01-01 23:59:59.000999680  15206.830078 -16.389999 -53680.210938
  73. [86400 rows x 4 columns]]
  74. [                                  0             1          2             3
  75. 0     2015-01-01 00:00:00.000999936  15152.530273 -79.599998 -53694.988281
  76. 1     2015-01-01 00:00:01.001000192  15152.589844 -79.599998 -53695.050781
  77. 2     2015-01-01 00:00:02.000999680  15152.589844 -79.599998 -53695.050781
  78. 3     2015-01-01 00:00:03.000999936  15152.650391 -79.599998 -53695.050781
  79. 4     2015-01-01 00:00:04.001000192  15152.650391 -79.599998 -53695.109375
  80. ...                             ...           ...        ...           ...
  81. 86395 2015-01-01 23:59:55.000999936  15206.650391     -16.33 -53680.210938
  82. 86396 2015-01-01 23:59:56.000999680  15206.709961 -16.389999 -53680.210938
  83. 86397 2015-01-01 23:59:57.000999936  15206.769531 -16.389999 -53680.210938
  84. 86398 2015-01-01 23:59:58.001000192  15206.830078 -16.389999 -53680.210938
  85. 86399 2015-01-01 23:59:59.000999680  15206.830078 -16.389999 -53680.210938
  86. [86400 rows x 4 columns],                                   0             1           2             3
  87. 0     2016-01-02 00:00:00.000999936  15220.959961  -82.709999 -53625.089844
  88. 1     2016-01-02 00:00:01.001000192  15220.959961  -82.709999 -53625.089844
  89. 2     2016-01-02 00:00:02.000999680  15220.959961  -82.769997 -53625.199219
  90. 3     2016-01-02 00:00:03.000999936  15220.959961  -82.769997 -53625.320312
  91. 4     2016-01-02 00:00:04.001000192  15220.959961  -82.769997 -53625.441406
  92. ...                             ...           ...         ...           ...
  93. 86395 2016-01-02 23:59:55.000999936   15125.44043 -124.110001 -53659.800781
  94. 86396 2016-01-02 23:59:56.000999680       15125.5 -124.110001 -53659.738281
  95. 86397 2016-01-02 23:59:57.000999936   15125.55957 -124.110001 -53659.738281
  96. 86398 2016-01-02 23:59:58.001000192   15125.55957 -124.110001 -53659.691406
  97. 86399 2016-01-02 23:59:59.000999680  15125.610352 -124.110001 -53659.691406
  98. [86400 rows x 4 columns]]
  99. [                                  0             1          2             3
  100. 0     2015-01-01 00:00:00.000999936  15152.530273 -79.599998 -53694.988281
  101. 1     2015-01-01 00:00:01.001000192  15152.589844 -79.599998 -53695.050781
  102. 2     2015-01-01 00:00:02.000999680  15152.589844 -79.599998 -53695.050781
  103. 3     2015-01-01 00:00:03.000999936  15152.650391 -79.599998 -53695.050781
  104. 4     2015-01-01 00:00:04.001000192  15152.650391 -79.599998 -53695.109375
  105. ...                             ...           ...        ...           ...
  106. 86395 2015-01-01 23:59:55.000999936  15206.650391     -16.33 -53680.210938
  107. 86396 2015-01-01 23:59:56.000999680  15206.709961 -16.389999 -53680.210938
  108. 86397 2015-01-01 23:59:57.000999936  15206.769531 -16.389999 -53680.210938
  109. 86398 2015-01-01 23:59:58.001000192  15206.830078 -16.389999 -53680.210938
  110. 86399 2015-01-01 23:59:59.000999680  15206.830078 -16.389999 -53680.210938
  111. [86400 rows x 4 columns],    
  112. </details>
  113. # 答案1
  114. **得分**: 0
  115. 以下是您要翻译的代码部分：
  116. ```python
  117. import urllib.request
  118. import datetime as dt
  119. from datetime import datetime, timezone, tzinfo
  120. import cdflib
  121. import numpy as np
  122. import pandas as pd
  123. from cdflib.epochs import CDFepoch
  124. import pathlib
  125. from xlsxwriter import Workbook
  126. from openpyxl import Workbook
  127. def get_day_data(station, year, month, day):
  128. is_pg = station.startswith("pg") 
  129. if is_pg:
  130. url = f"http://mist.nianet.org/CDFdata/{station}/{year}/thg_l2_mag_{station}_{year}{month}{day}_v01.cdf"
  131. else:
  132. url = f"https://ftp.space.dtu.dk/data/Ground_magnetometers/Adjusted/{station.upper()}/{year}/{month}/{station.upper()}_{year}{month}{day}.cdf"
  133. file_name = url.split("/")[-1]
  134. if not pathlib.Path(file_name).exists():
  135. try:
  136. urllib.request.urlretrieve(url, "tmp.cdf")
  137. pathlib.Path("tmp.cdf").rename(file_name)
  138. except:
  139. #raise Exception(f"Failed to download {url}")
  140. return None
  141. cdf = cdflib.CDF(file_name)
  142. if is_pg:
  143. epoch = cdf.varget(f"thg_mag_{station}_time")
  144. index = pd.to_datetime(epoch, utc=False, unit="s")
  145. #index = index.round("1s")
  146. variable = f"thg_mag_{station}"
  147. else:
  148. epoch = cdf.varget("time")
  149. epoch = CDFepoch().to_datetime(epoch, to_np=True)
  150. index = pd.to_datetime(epoch)
  151. variable = "HEZ"
  152. values = cdf.varget(variable)
  153. if "FILLVAL" in cdf.varattsget(variable):
  154. values[values == cdf.attget("FILLVAL", variable)["Data"]] = np.nan
  155. if "VALIDMIN" in cdf.varattsget(variable):
  156. values[values < cdf.attget("VALIDMIN", variable)["Data"]] = np.nan
  157. if "VALIDMAX" in cdf.varattsget(variable):
  158. values[values > cdf.attget("VALIDMAX", variable)["Data"]] = np.nan
  159. return pd.DataFrame({"epoch": index,
  160. "X": values[:, 0],
  161. "Y": values[:, 1],
  162. "Z": values[:, 2]})
  163. result = pd.DataFrame()
  164. months = ['01','02','03','04','05','06','07','08','09','10','11','12']
  165. days = ['01','02','03','04','05','06','07','08','09','10','11','12']
  166. for year in range(2015,2019,1):
  167. for month in range(len(months)):
  168. for day in range(len(days)):
  169. #output = pd.DataFrame(np.array(get_day_data('pg0', year, months[month], days[day])))
  170. output = get_day_data('pg0', str(year), months[month], days[day])
  171. if output is not None:
  172. result = pd.concat([result, output])
  173. 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.

  1. import urllib.request
  2. import datetime as dt
  3. from datetime import datetime, timezone, tzinfo
  4. import cdflib
  5. import numpy as np
  6. import pandas as pd
  7. from cdflib.epochs import CDFepoch
  8. import pathlib
  9. from xlsxwriter import Workbook
  10. from openpyxl import Workbook
  11. def get_day_data(station, year, month, day):
  12. is_pg = station.startswith(&quot;pg&quot;) 
  13. if is_pg:
  14. url = f&quot;http://mist.nianet.org/CDFdata/{station}/{year}/thg_l2_mag_{station}_{year}{month}{day}_v01.cdf&quot;
  15. else:
  16. url = f&quot;https://ftp.space.dtu.dk/data/Ground_magnetometers/Adjusted/{station.upper()}/{year}/{month}/{station.upper()}_{year}{month}{day}.cdf&quot;
  17. file_name = url.split(&quot;/&quot;)[-1]
  18. if not pathlib.Path(file_name).exists():
  19. try:
  20. urllib.request.urlretrieve(url, &quot;tmp.cdf&quot;)
  21. pathlib.Path(&quot;tmp.cdf&quot;).rename(file_name)
  22. except:
  23. #raise Exception(f&quot;Failed to download {url}&quot;)
  24. return None
  25. cdf = cdflib.CDF(file_name)
  26. if is_pg:
  27. epoch = cdf.varget(f&quot;thg_mag_{station}_time&quot;)
  28. index = pd.to_datetime(epoch, utc=False, unit=&quot;s&quot;)
  29. #index = index.round(&quot;1s&quot;)
  30. variable = f&quot;thg_mag_{station}&quot;
  31. else:
  32. epoch = cdf.varget(&quot;time&quot;)
  33. epoch = CDFepoch().to_datetime(epoch, to_np=True)
  34. index = pd.to_datetime(epoch)
  35. variable = &quot;HEZ&quot;
  36. values = cdf.varget(variable)
  37. if &quot;FILLVAL&quot; in cdf.varattsget(variable):
  38. values[values == cdf.attget(&quot;FILLVAL&quot;, variable)[&quot;Data&quot;]] = np.nan
  39. if &quot;VALIDMIN&quot; in cdf.varattsget(variable):
  40. values[values &lt; cdf.attget(&quot;VALIDMIN&quot;, variable)[&quot;Data&quot;]] = np.nan
  41. if &quot;VALIDMAX&quot; in cdf.varattsget(variable):
  42. values[values &gt; cdf.attget(&quot;VALIDMAX&quot;, variable)[&quot;Data&quot;]] = np.nan
  43. return pd.DataFrame({&quot;epoch&quot;: index,
  44. &quot;X&quot;: values[:, 0],
  45. &quot;Y&quot;: values[:, 1],
  46. &quot;Z&quot;: values[:, 2]})
  47. result = pd.DataFrame()
  48. 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;]
  49. 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;]
  50. for year in range(2015,2019,1):
  51. for month in range(len(months)):
  52. for day in range(len(days)):
  53. #output = pd.DataFrame(np.array(get_day_data(&#39;pg0&#39;, year, months[month], days[day])))
  54. output = get_day_data(&#39;pg0&#39;, str(year), months[month], days[day])
  55. if output is not None:
  56. result = pd.concat([result, output])
  57. print(result)
  58. #Output
  59. #                              epoch             X           Y             Z
  60. #0     2015-01-01 00:00:00.000999936  15152.530273  -79.599998 -53694.988281
  61. #1     2015-01-01 00:00:01.001000192  15152.589844  -79.599998 -53695.050781
  62. #2     2015-01-01 00:00:02.000999680  15152.589844  -79.599998 -53695.050781
  63. #3     2015-01-01 00:00:03.000999936  15152.650391  -79.599998 -53695.050781
  64. #4     2015-01-01 00:00:04.001000192  15152.650391  -79.599998 -53695.109375
  65. #...                             ...           ...         ...           ...
  66. #86395 2018-12-12 23:59:55.000999936  14926.240234 -115.320000 -53617.519531
  67. #86396 2018-12-12 23:59:56.000999680  14926.299805 -115.320000 -53617.519531
  68. #86397 2018-12-12 23:59:57.000999936  14926.299805 -115.320000 -53617.519531
  69. #86398 2018-12-12 23:59:58.001000192  14926.299805 -115.320000 -53617.519531
  70. #86399 2018-12-12 23:59:59.000999680  14926.299805 -115.320000 -53617.519531
  71. #[32745600 rows x 4 columns]

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

发表评论

匿名网友

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

确定