csv 文件已发送给我/我无法重新分隔列
239845723,28374,2384234,AEVNE EFU 5 GN OR WNV,Owinv Vnwo Badvw 5 VIN,Ginq 2 jnwve wef evera wve 6 vwe as fgsb bfd bdfwd dsf (sdv seves 4-6), sebsbe sve(sevsev esvse 7-10) fsesef fesevsesv PaVvin (1 evesve vEV VEWee, 2 for WVEee VEWE. paper tuff as sWEFEWoon as VEWeew.).,2011-07-13 00:00:00,2011-07-13 00:00:00
我替换了字符串字母以覆盖敏感信息，但问题很明显。
这是我的 csv 中的“问题行”示例。应该按如下方式分为8列:
col1: 239845723
col2: 28374
col3: 2384234
col4: AEVNE EFU 5 GN OR WNV
col5: Owinv Vnwo Badvw 5 VIN
col6: Ginq 2 jnwve wef evera wve 6 vwe as fgsb bfd bdfwd dsf (sdv seves 4-6), sebsbe sve(sevsev esvse 7-10) fsesef fesevsesv PaVvin (1 evesve vEV VEWee, 2 for WVEee VEWE. paper tuff as sWEFEWoon as VEWeew.).
col7: 2011-07-13 00:00:00
col8: 2011-07-13 00:00:00
如您所见，第 6 列是出现问题的地方，因为字符串中的逗号会导致 pandas 错误地分隔和创建新列。我怎么解决这个问题？我在想正则表达式会有所帮助，也许使用以下设置。感谢您的帮助!
csvfile = open(filetrace)
reader = csv.reader(csvfile)
new_list=[]
for line in reader:
for i in line:
#not sure
请您参考如下方法:
我不使用正则表达式，而是读取带有分隔符“,”的 csv，您可以提取最后两个日期并将其存储在列表中。然后用 '' 填充日期，然后加入您想要的列并删除其余列。示例
如果你有一个 csv 文件:
239845723,28374,2384234,AEVNE EFU 5 GN OR WNV,Owinv Vnwo Badvw 5 VIN,Ginq 2 jnwve wef evera wve 6 vwe as fgsb bfd bdfwd dsf (sdv seves 4-6), sebsbe sve(sevsev esvse 7-10) fsesef fesevsesv PaVvin (1 evesve vEV VEWee, 2 for WVEee VEWE. paper tuff as sWEFEWoon as VEWeew.).,2011-07-13 00:00:00,2011-07-13 00:00:00 239845723,28374,2384234,AEVNE EFU 5 GN OR WNV,Owinv Vnwo Badvw 5 VIN,Ginq 2 jnwve wef evera wve 6 vwe as fgsb bfd bdfwd dsf (sdv seves 4-6), sebsbe sve(sevsev esvse 7-10) fsesef fesevsesv PaVvin (1 evesve vEV VEWee 2 for WVEee VEWE.).,2011-07-13 00:00:00,2011-07-13 00:00:00 239845723,28374,2384234,AEVNE EFU 5 GN OR WNV,Owinv Vnwo Badvw 5 VIN sebsbe sve(sevsev esvse 7-10) fsesef fesevsesv PaVvin (1 evesve vEV VEWee 2 for WVEee VEWE. paper tuff as sWEFEWoon as VEWeew.).,2011-07-13 00:00:00,2011-07-13 00:00:00
Then
df = pd.read_csv('good.txt',delimiter=',',header=None)
# Get the Dates from all the DataFrame
dates = [[item] for i in df.values for item in i if '2011-' in str(item)]
# Merge two Dates for each column
dates = pd.DataFrame([x+y for x,y in zip(dates[0::2], dates[1::2])])
# Remove the dates present
df = df.replace({'2011-': np.nan}, regex=True).replace(np.nan,'')
#Get the columns you want to merge
cols = df.columns[4:]
# Merge the columns
df[4] = df[cols].astype(str).apply(lambda x: ','.join(x), axis=1)
df[4] = df[4].replace('\,+$', '',regex=True)
#Drop the Columns
df = df.drop(df.columns[5:],axis=1)
#Concat the dates
df = pd.concat([df,dates],axis=1)
输出:打印(df)
0 1 2 3 \
0 239845723 28374 2384234 AEVNE EFU 5 GN OR WNV
1 239845723 28374 2384234 AEVNE EFU 5 GN OR WNV
2 239845723 28374 2384234 AEVNE EFU 5 GN OR WNV
4 0 \
0 Owinv Vnwo Badvw 5 VIN,Ginq 2 jnwve wef evera ... 2011-07-13 00:00:00
1 Owinv Vnwo Badvw 5 VIN,Ginq 2 jnwve wef evera ... 2011-07-13 00:00:00
2 Owinv Vnwo Badvw 5 VIN sebsbe sve(sevsev esvse... 2011-07-13 00:00:00
1
0 2011-07-13 00:00:00
1 2011-07-13 00:00:00
2 2011-07-13 00:00:00
Ouput of the 4th column :
['Owinv Vnwo Badvw 5 VIN,Ginq 2 jnwve wef evera wve 6 vwe as fgsb bfd bdfwd dsf (sdv seves 4-6), sebsbe sve(sevsev esvse 7-10) fsesef fesevsesv PaVvin (1 evesve vEV VEWee, 2 for WVEee VEWE. paper tuff as sWEFEWoon as VEWeew.).', 'Owinv Vnwo Badvw 5 VIN,Ginq 2 jnwve wef evera wve 6 vwe as fgsb bfd bdfwd dsf (sdv seves 4-6), sebsbe sve(sevsev esvse 7-10) fsesef fesevsesv PaVvin (1 evesve vEV VEWee 2 for WVEee VEWE.).', 'Owinv Vnwo Badvw 5 VIN sebsbe sve(sevsev esvse 7-10) fsesef fesevsesv PaVvin (1 evesve vEV VEWee 2 for WVEee VEWE. paper tuff as sWEFEWoon as VEWeew.).']
If you want to change column index
df.columns = [i for i in range(df.shape[1])]
希望对你有帮助
