使用python从csv转换为文本文件

Question

我试图从csvfile中编写两个txt文件（Test_8.txt和Test_9.txt）。 从COL4行我得到单引号和双引号以及“[”

我怎样才能摆脱它们

csvfie：

NR;COL1;COL2;COL3;COL4;COL5;COL6;COL7;REMARK

Test_9;96;0;4.26;4;5.25;-0.01;1;Test_9 tested, python

Test_9;96;0;4.26;4;11.75;2.35;1;Test_9 tested, python

Test_9;96;0;4.26;4;-3;-3;0.9;Test_9 tested, python

Test_8;95;0;4.25;3;4.75;-0.11;1;Test_8 tested, python

Test_8;95;0;4.25;3;-3;-3;0.9;Test_8 tested, python

Test_8;95;0;4.25;3;16.5;4.26;1;Test_8 tested, python

Test_8;95;0;4.25;3;12.751;2.861;1;Test_8 tested, python

预期输出：

TYPE    1.0
NR  Test_8

COL1    95

COL2    0
COL3    4.250

COL4    3  
-3.000  -3.000  0.900
4.750   -0.110  1.000
12.751  2.861   1.000
16.500  4.260   1.000

REMARK
Test_8 tested
with python

霉菌代码：

import os
import pandas as pd
pd.options.mode.chained_assignment = None 
df=pd.read_csv(r'C:\Users\Desktop\test_map\test\mycsv_v1.csv',sep=';',index_col='NR')

df['COL3'] = df['COL3'].map('{:,.3f}'.format)

df['COL5'] = df['COL5'].map('{:,.3f}'.format)
df['COL6'] = df['COL6'].map('{:,.3f}'.format)
df['COL7'] = df['COL7'].map('{:,.3f}'.format)

ans = [[x,pd.DataFrame(y)] for x, y in df.groupby(df.index, as_index=True)]
#print ans

for table in ans:
    line1=table[1].iloc[0]
    #print line1
    line1['TYPE']=1.0
    line1['NR']=table[0]

    col567=table[1][['COL5','COL6','COL7']].sort_values(by=['COL5'], ascending=True)
    print col567

    for row in range(len(col567)):
        #print row
        line1[str(col567.values[row])[1:-1]] = None

    line1['']=None

    col8=table[1]['REMARK'].str.split(',')[0]
    col8=table[1]['REMARK'].str.split(', ')[1]
    line1['REMARK']=str(col8.values[0])
    line1['REMARK']=str(col8.values[1])


    line1=line1[['TYPE', 'NR','','COL1','', 'COL2','', 'COL3', 'COL4', 
             str(col567.values[0:]), '', 'REMARK\n', col8.values[0],col8.values[1]]]


    line1.to_csv(table[0]+'.txt',sep='\t')

输出

TYPE    1.0
NR  Test_8

COL1    95

COL2    0

COL3    4.250
COL4    3
"[['-3.000' '-3.000' '0.900']
 ['12.751' '2.861' '1.000']
 ['16.500' '4.260' '1.000']
 ['4.750' '-0.110' '1.000']]"   

"REMARK
"   
Test_8 tested   
python  

Answer 1

如果希望文本不带[]和quota，则不要使用str()和defalt格式，而是创建自己的函数来格式化文本。您可以为此使用" ".join()和for-loop

示例代码

import numpy as np

data = np.array([['-3.000', '-3.000', '0.900'],
 ['12.751', '2.861', '1.000'],
 ['16.500', '4.260', '1.000'],
 ['4.750', '-0.110', '1.000']])

print(' - default format  -')
text = str(data)
print(text)

print(' - own format  -')
text = ''
for row in data:
    text += ' '.join(row) + '\n'
print(text)

结果:

 - default format  -
[['-3.000' '-3.000' '0.900']
 ['12.751' '2.861' '1.000']
 ['16.500' '4.260' '1.000']
 ['4.750' '-0.110' '1.000']]

 - own format  -
-3.000 -3.000 0.900
12.751 2.861 1.000
16.500 4.260 1.000
4.750 -0.110 1.000

---

顺便说一句：您需要转换col567.values[0:]

print(str(col567.values[0:]))

text = ''
for row in col567.values[0:]:
    text += " ".join(row) + '\n'
print(text)

并在

line1=line1[['TYPE', 'NR','','COL1','', 'COL2','', 'COL3', 'COL4', 
         text, '', 'REMARK\n', col8.values[0],col8.values[1]]]

我试图运行你的代码，但它有很多错误，它从来没有工作

---

使用字符串格式化的示例代码

我使用io.StringIO只是为了用数据模拟文件，但您使用pd.read_csv

顺便说一句：我必须更改一些元素，因为要获得正确排序的数据，它们必须是整数/浮点值，而不是字符串{:,.3f}

import os
import pandas as pd

pd.options.mode.chained_assignment = None 

#df=pd.read_csv(r'C:\Users\Desktop\test_map\test\mycsv_v1.csv',sep=';',index_col='NR')

text = u'''NR;COL1;COL2;COL3;COL4;COL5;COL6;COL7;REMARK
Test_9;96;0;4.26;4;5.25;-0.01;1;Test_9 tested, python
Test_9;96;0;4.26;4;11.75;2.35;1;Test_9 tested, python
Test_9;96;0;4.26;4;-3;-3;0.9;Test_9 tested, python
Test_8;95;0;4.25;3;4.75;-0.11;1;Test_8 tested, python
Test_8;95;0;4.25;3;-3;-3;0.9;Test_8 tested, python
Test_8;95;0;4.25;3;16.5;4.26;1;Test_8 tested, python
Test_8;95;0;4.25;3;12.751;2.861;1;Test_8 tested, python'''

import io
df = pd.read_csv(io.StringIO(text), sep=';', index_col='NR')

df['COL3'] = df['COL3'].map('{:,.3f}'.format)
#df['COL5'] = df['COL5'].map('{:,.3f}'.format)
#df['COL6'] = df['COL6'].map('{:,.3f}'.format)
#df['COL7'] = df['COL7'].map('{:,.3f}'.format)

ans = df.groupby(df.index, as_index=True)

for table in ans:
    line1 = table[1].iloc[0]

    col567 = table[1][['COL5','COL6','COL7']].sort_values(by=['COL5'], ascending=True)
    col567_text = '\n'.join(' '.join('{:,.3f}'.format(item) for item in row) for row in col567.values[0:])        

    col8 = table[1]['REMARK'][0].split(', ')

    text = '''TYPE    {type_}
NR  {nr}

COL1    {col1}

COL2    {col2}
COL3    {col3}

COL4    {col4}
{col567}

REMARK
{remark1}
{remark2}'''.format(
    type_ = 1.0,
    nr = table[0],
    col1 = table[1]['COL1'][0],
    col2 = table[1]['COL2'][0],
    col3 = table[1]['COL3'][0],
    col4 = table[1]['COL4'][0],
    col567 = col567_text,
    remark1 = col8[0],
    remark2 = col8[1],
)    


    print(text)

    with open(table[0]+'.txt', 'w') as f:
        f.write(text)

Answer 2

您正在打印numpy.array的numpy.array。 默认格式为列表列表

您可以使用列表理解和字符串join()添加自己的格式

col567_fmt = '\n'.join( [ '\t'.join(x) for x in col567.values[0:] ] )
line1=line1[['TYPE', 'NR','','COL1','', 'COL2','', 'COL3', 'COL4', 
         col567_fmt, '', 'REMARK\n', col8.values[0],col8.values[1]]]

此外，如果要使用to_csv()打印，则需要禁用引号。见对this question的答复