Transforming annotated csv (influxdb) to normal csv file using python script

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

Transforming annotated csv (influxdb) to normal csv file using python script

问题

以下是您要翻译的内容：

  1. import csv
  3. # Specify the input and output file names
  4. input_file = 'influx.csv'
  5. output_file = 'output.csv'
  7. try:
  8.     # Open the input file for reading
  9.     with open(input_file, 'r') as csv_file:
  10.         # Create a CSV reader object
  11.         csv_reader = csv.reader(csv_file)
  13.         # Skip the first row (header)
  14.         next(csv_reader)
  16.         # Open the output file for writing
  17.         with open(output_file, 'w', newline='') as output_csv:
  18.             # Create a CSV writer object
  19.             csv_writer = csv.writer(output_csv)
  21.             # Write the header row
  22.             csv_writer.writerow(['_time', '_field', '_value'])
  24.             # Iterate over the input file and write the rows to the output file
  25.             for row in csv_reader:
  26.                 # Check if the row is not empty
  27.                 if row:
  28.                     # Split the fields
  29.                     fields = row[0].split(',')
  31.                     # Write the row to the output file
  32.                     csv_writer.writerow(fields)
  34.     print(f'{input_file} converted to {output_file} successfully!')
  36. except FileNotFoundError:
  37.     print(f'Error: File {input_file} not found.')
  39. except Exception as e:
  40.     print(f'Error: {e}')

如果您需要任何其他翻译，请告诉我。

英文:

I have a CSV file that was downloaded from InfluxDB UI. I want to extract useful data from the downloaded file. A snippet of the downloaded file is as follows:

  1. #group	FALSE	FALSE	TRUE	TRUE	FALSE	FALSE	TRUE	TRUE	TRUE	TRUE	TRUE
  2. #datatype	string	long	dateTime:RFC3339	dateTime:RFC3339	dateTime:RFC3339	double	string	string	string	string	string
  3. #default	mean										
  4. 	result	table	_start	_stop	_time	_value	_field	_measurement	smart_module	serial	type
  5. 		0	2023-03-31T08:12:40.697076925Z	2023-03-31T09:12:40.697076925Z	2023-03-31T08:20:00Z	0	sm_alarm	system_test	8	2.14301E+11	sm_extended
  6. 		0	2023-03-31T08:12:40.697076925Z	2023-03-31T09:12:40.697076925Z	2023-03-31T08:40:00Z	0	sm_alarm	system_test	8	2.14301E+11	sm_extended
  7. 		0	2023-03-31T08:12:40.697076925Z	2023-03-31T09:12:40.697076925Z	2023-03-31T09:00:00Z	0	sm_alarm	system_test	8	2.14301E+11	sm_extended
  8. 		0	2023-03-31T08:12:40.697076925Z	2023-03-31T09:12:40.697076925Z	2023-03-31T09:12:40.697076925Z	0	sm_alarm	system_test	8	2.14301E+11	sm_extended

I'd like to have the output CSV as follows:

  1. _time                   sm_alarm  next_column next_column ....... ...........
  2. 2023-03-29T08:41:15Z    0

Please note that sm_alarm is only one field among 9 others (that are under _filed).

I tried to do with the following script, but could not solve my problem.

  1. import csv
  3. # Specify the input and output file names
  4. input_file = 'influx.csv'
  5. output_file = 'output.csv'
  7. try:
  8.     # Open the input file for reading
  9.     with open(input_file, 'r') as csv_file:
  10.         # Create a CSV reader object
  11.         csv_reader = csv.reader(csv_file)
  13.         # Skip the first row (header)
  14.         next(csv_reader)
  16.         # Open the output file for writing
  17.         with open(output_file, 'w', newline='') as output_csv:
  18.             # Create a CSV writer object
  19.             csv_writer = csv.writer(output_csv)
  21.             # Write the header row
  22.             csv_writer.writerow(['_time', '_field', '_value'])
  24.             # Iterate over the input file and write the rows to the output file
  25.             for row in csv_reader:
  26.                 # Check if the row is not empty
  27.                 if row:
  28.                     # Split the fields
  29.                     fields = row[0].split(',')
  31.                     # Write the row to the output file
  32.                     csv_writer.writerow(fields)
  34.     print(f'{input_file} converted to {output_file} successfully!')
  36. except FileNotFoundError:
  37.     print(f'Error: File {input_file} not found.')
  39. except Exception as e:
  40.     print(f'Error: {e}')

Thank you.

答案1

得分: 1

以下是翻译好的部分：

  1. import pandas as pd
  3. with open("influx.csv", "r") as csv_file:
  4.     headers = csv_file.readlines()[3].strip().split()[1:]
  5.     
  6. df = pd.read_csv("influx.csv", header=None, skiprows=4, sep="\s+",
  7.                  engine="python", names=headers).iloc[:, 1:]
  9. #print(df)
  1. print(df)
  3.                                _start                           _stop                           _time  _value    _field _measurement  smart_module        serial         type
  4.     0  2023-03-31T08:12:40.697076925Z  2023-03-31T09:12:40.697076925Z            2023-03-31T08:20:00Z       0  sm_alarm  system_test             8  2.143010e+11  sm_extended
  5.     1  2023-03-31T08:12:40.697076925Z  2023-03-31T09:12:40.697076925Z            2023-03-31T08:40:00Z       0  sm_alarm  system_test             8  2.143010e+11  sm_extended
  6.     2  2023-03-31T08:12:40.697076925Z  2023-03-31T09:12:40.697076925Z            2023-03-31T09:00:00Z       0  sm_alarm  system_test             8  2.143010e+11  sm_extended
  7.     3  2023-03-31T08:12:40.697076925Z  2023-03-31T09:12:40.697076925Z  2023-03-31T09:12:40.697076925Z       0  sm_alarm  system_test             8  2.143010e+11  sm_extended
英文:

The format of your expected output is ambiguous and not fully clear.
But as a starting point, you can straighten your file with read_csv from [tag:pandas] this way :

  1. import pandas as pd
  2. ​
  3. with open("influx.csv", "r") as csv_file:
  4.     headers = csv_file.readlines()[3].strip().split()[1:]
  5.     
  6. df = pd.read_csv("influx.csv", header=None, skiprows=4, sep="\s+",
  7.                  engine="python", names=headers).iloc[:, 1:]
  9. #df.to_csv("output.csv", index=False, sep=",") # <- uncomment this line to make a real csv

Output :

  1. print(df)
  3.                            _start                           _stop                           _time  _value    _field _measurement  smart_module        serial         type
  4. 0  2023-03-31T08:12:40.697076925Z  2023-03-31T09:12:40.697076925Z            2023-03-31T08:20:00Z       0  sm_alarm  system_test             8  2.143010e+11  sm_extended
  5. 1  2023-03-31T08:12:40.697076925Z  2023-03-31T09:12:40.697076925Z            2023-03-31T08:40:00Z       0  sm_alarm  system_test             8  2.143010e+11  sm_extended
  6. 2  2023-03-31T08:12:40.697076925Z  2023-03-31T09:12:40.697076925Z            2023-03-31T09:00:00Z       0  sm_alarm  system_test             8  2.143010e+11  sm_extended
  7. 3  2023-03-31T08:12:40.697076925Z  2023-03-31T09:12:40.697076925Z  2023-03-31T09:12:40.697076925Z       0  sm_alarm  system_test             8  2.143010e+11  sm_extended

If you share a clear expected ouptut, I'll update my answer accordingly.

huangapple
  • 本文由 发表于 2023年3月31日 20:20:44
  • 转载请务必保留本文链接：https://go.coder-hub.com/75898467.html
  • csv
  • influxdb
  • influxdb-python
  • python
匿名

发表评论

匿名网友

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

确定