如何将带有NUL（'\ x00'）行的csv读取到熊猫中？
ChemEnger
来自的数据
我有一组csv文件，其中日期和时间作为前两列（文件中没有标题）。这些文件可以在Excel中正常打开，但是当我尝试使用Pandas read_csv将它们读入Python时，无论是否尝试类型转换，都只返回第一个Date。
当我在记事本中打开时，它不是简单地用逗号分隔，而是在第1行之后的每一行之前都有空间；我试图skipinitialspace = True无济于事
我也尝试了各种类型转换，但是没有用。我目前正在使用parse_dates = [['Date','Time']], infer_datetime_format = True, dayfirst = True
输出示例（不进行转换）：
0 1 2 3 4 ... 12 13 14 15 16
0 02/03/20 15:13:39 5.5 5.8 42.84 ... 30.0 79.0 0.0 0.0 0.0
1 NaN 15:13:49 5.5 5.8 42.84 ... 30.0 79.0 0.0 0.0 0.0
2 NaN 15:13:59 5.5 5.7 34.26 ... 30.0 79.0 0.0 0.0 0.0
3 NaN 15:14:09 5.5 5.7 34.26 ... 30.0 79.0 0.0 0.0 0.0
4 NaN 15:14:19 5.5 5.4 17.10 ... 30.0 79.0 0.0 0.0 0.0
... ... ... ... ... ... ... ... ... ... ... ...
39451 NaN 01:14:27 5.5 8.4 60.00 ... 30.0 68.0 0.0 0.0 0.0
39452 NaN 01:14:37 5.5 8.4 60.00 ... 30.0 68.0 0.0 0.0 0.0
39453 NaN 01:14:47 5.5 8.4 60.00 ... 30.0 68.0 0.0 0.0 0.0
39454 NaN 01:14:57 5.5 8.4 60.00 ... 30.0 68.0 0.0 0.0 0.0
39455 NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN
以及parse_dates等：
Date_Time pH1 SP pH Ph1 PV pH ... 1 2 3
0 02/03/20 15:13:39 5.5 5.8 ... 0.0 0.0 0.0
1 nan 15:13:49 5.5 5.8 ... 0.0 0.0 0.0
2 nan 15:13:59 5.5 5.7 ... 0.0 0.0 0.0
3 nan 15:14:09 5.5 5.7 ... 0.0 0.0 0.0
4 nan 15:14:19 5.5 5.4 ... 0.0 0.0 0.0
... ... ... ... ... ... ... ...
39451 nan 01:14:27 5.5 8.4 ... 0.0 0.0 0.0
39452 nan 01:14:37 5.5 8.4 ... 0.0 0.0 0.0
39453 nan 01:14:47 5.5 8.4 ... 0.0 0.0 0.0
39454 nan 01:14:57 5.5 8.4 ... 0.0 0.0 0.0
39455 nan nan NaN NaN ... NaN NaN NaN
从记事本复制的数据（实际上每行前面有更多的空格，但在这里行不通）：
来自的数据 67.csv
02/03/20,15:13:39,5.5,5.8,42.84,7.2,6.8,10.63,60.0,0.0,300,1,30,79,0.0,0.0, 0.0
02/03/20,15:13:49,5.5,5.8,42.84,7.2,6.8,10.63,60.0,0.0,300,1,30,79,0.0,0.0, 0.0
02/03/20,15:13:59,5.5,5.7,34.26,7.2,6.8,10.63,60.0,22.3,300,1,30,79,0.0,0.0, 0.0
02/03/20,15:14:09,5.5,5.7,34.26,7.2,6.8,10.63,60.0,15.3,300,45,30,79,0.0,0.0, 0.0
02/03/20,15:14:19,5.5,5.4,17.10,7.2,6.8,10.63,60.0,50.2,300,86,30,79,0.0,0.0, 0.0
在Excel中（所以我知道信息在那里并且可读）：
码
import sys
import numpy as np
import pandas as pd
from datetime import datetime
from tkinter import filedialog
from tkinter import *
def import_file(filename):
print('\nOpening ' + filename + ":")
##Read the data in the file
df = pd.read_csv(filename, header = None, low_memory = False)
print(df)
df['Date_Time'] = pd.to_datetime(df[0] + ' ' + df[1])
df.drop(columns=[0, 1], inplace=True)
print(df)
filenames=[]
print('Select files to read, Ctrl or Shift for Multiples')
TkWindow = Tk()
TkWindow.withdraw() # we don't want a full GUI, so keep the root window from appearing
## Show an "Open" dialog box and return the path to the selected file
filenames = filedialog.askopenfilename(title='Open data file', filetypes=(("Comma delimited", "*.csv"),), multiple=True)
TkWindow.destroy()
if len(filenames) == 0:
print('No files selected - Exiting program.')
sys.exit()
else:
print('\n'.join(filenames))
##Read the data from the specified file/s
print('\nReading data file/s')
dfs=[]
for filename in filenames:
dfs.append(import_file(filename))
if len(dfs) > 1:
print('\nCombining data files.')
特伦顿·麦金尼
- 该文件填充有
NUL，'\x00'需要删除。 - 清除行后，用于
pandas.DataFrame从中加载数据d。
import pandas as pd
import string # to make column names
# the issue is the the file is filled with NUL not whitespace
def import_file(filename):
# open the file and clean it
with open(filename) as f:
d = list(f.readlines())
# replace NUL, strip whitespace from the end of the strings, split each string into a list
d = [v.replace('\x00', '').strip().split(',') for v in d]
# remove some empty rows
d = [v for v in d if len(v) > 2]
# load the file with pandas
df = pd.DataFrame(d)
# convert column 0 and 1 to a datetime
df['datetime'] = pd.to_datetime(df[0] + ' ' + df[1])
# drop column 0 and 1
df.drop(columns=[0, 1], inplace=True)
# set datetime as the index
df.set_index('datetime', inplace=True)
# convert data in columns to floats
df = df.astype('float')
# give character column names
df.columns = list(string.ascii_uppercase)[:len(df.columns)]
# reset the index
df.reset_index(inplace=True)
return df.copy()
# call the function
dfs = list()
filenames = ['67.csv']
for filename in filenames:
dfs.append(import_file(filename))
display(df)
A B C D E F G H I J K L M N O
datetime
2020-02-03 15:13:39 5.5 5.8 42.84 7.2 6.8 10.63 60.0 0.0 300.0 1.0 30.0 79.0 0.0 0.0 0.0
2020-02-03 15:13:49 5.5 5.8 42.84 7.2 6.8 10.63 60.0 0.0 300.0 1.0 30.0 79.0 0.0 0.0 0.0
2020-02-03 15:13:59 5.5 5.7 34.26 7.2 6.8 10.63 60.0 22.3 300.0 1.0 30.0 79.0 0.0 0.0 0.0
2020-02-03 15:14:09 5.5 5.7 34.26 7.2 6.8 10.63 60.0 15.3 300.0 45.0 30.0 79.0 0.0 0.0 0.0
2020-02-03 15:14:19 5.5 5.4 17.10 7.2 6.8 10.63 60.0 50.2 300.0 86.0 30.0 79.0 0.0 0.0 0.0
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
编辑于
相关文章
Related 相关文章
- 1
如何从列表中删除“ \ x00”
- 2
我如何将像 b'\x08\x00\x00\x00' 这样的 Python/socket 读取的雷达数据解码为数字
- 3
如何在Python3中修复此字节对象的编码以仅保留实际文本并删除'\ x00 \ x05 * \ x00 \ x00 \ x0e \ x00bjbj'？
- 4
如何删除那些“ \ x00 \ x00”
- 5
如何在Erlang中的字符串中替换NUL字符（\ x00）？
- 6
如何从列表中删除 '\x' =[ '\xbb', '\x02', '\x00', '\x11', '\xbe']
- 7
我可以将NUL（\ x00）放在xml中吗
- 8
如何在C中输入/ x00内存地址？
- 9
如何摆脱字节数组中的\ x00？
- 10
如何解决int（）无法将'\ x002 \ x002 \ x001 \ x000 \ x00'转换为整数的python中的转换问题？
- 11
删除列表中数字前面的\ x00
- 12
javascript中是否存在Python的“ \ x00”？
- 13
在Django模板中处理u'\ x00'
- 14
如何处理其数据中包含\ x00（或\ u0000）的API响应（JSON），并使用Django模型将数据存储在Postgres中？
- 15
在Python 3中将'\ x00'转换为ASCII
- 16
在python中读取SQL查询时出现问题。每个字符后添加“ \ x00”
- 17
如何将带有选中复选框的表行值从jsp获取到servlet
- 18
将带有列的大熊猫DF列到文件中，如何将其读回？
- 19
如何将带有很多注释行的数据文本文件加载到熊猫中？
- 20
如何将带有嵌套字典的列表写到csv文件中？
- 21
如何将带有tinestamp等的CSV文件输入到mahout中以实现相似功能等？
- 22
如何将带有嵌套字典的列表写到csv文件中？
- 23
如何将带有多个对象的JSON数据中的值插入MySQL中的行？
- 24
如何将带有awk的行合并到具有相同键的一行
- 25
如何将带有分隔值的行分成单独的行？
- 26
bash脚本如何将带有密钥的日志转换为csv
- 27
如何将带有链接的 php 行回显到 iframe src
- 28
如何将带有时间（日期）类型的文件加载到Hive表中？
- 29
如何将带有空格的字符串传递到PowerShell中？
我来说两句