In this code, some information are written in the csv file. Specifically, I want to ignore any delimited character (comma, space, ...) for the key
string. In other words, I want to see the key in one Excel cell. The rest is OK and should be delimited.
df = pd.read_csv('test.batch.csv')
print(df)
# Creating a dictionary
data = {'Value':[0,0,0]}
kernel_df = pd.DataFrame(data, index=['M1','M2','M3'])
my_dict = {'dummy':kernel_df}
# dummy -> Value
# M1 0
# M2 0
# M3 0
for name, df_group in df.groupby('Name'):
my_dict[name] = pd.concat(
[g.reset_index(drop=True) for _, g in df_group.groupby('ID')['Value']],
axis=1
)
print(my_dict)
with open('output.csv', 'w') as f:
for key in my_dict.keys():
f.write("%s\n"%(key)) <-- This should be written in one cell
df2 = my_dict[key]
df2.to_csv(f)
The output is
ID Name Metric Value
0 0 K1::foo(bar::z(x,u)) M1 10
1 0 K1::foo(bar::z(x,u)) M2 5
2 0 K1::foo(bar::z(x,u)) M3 10
3 1 K2::foo() M1 20
4 1 K2::foo() M2 10
5 1 K2::foo() M3 15
6 2 K1::foo(bar::z(x,u)) M1 2
7 2 K1::foo(bar::z(x,u)) M2 2
8 2 K1::foo(bar::z(x,u)) M3 2
{'dummy': Value
M1 0
M2 0
M3 0, 'K1::foo(bar::z(x,u))': Value Value
0 10 2
1 5 2
2 10 2, 'K2::foo()': Value
0 20
1 10
2 15}
And the CSV file in Excel looks like
As you can see the 6th row is split into two columns. The output file in plain text looks like
dummy
,Value
M1,0
M2,0
M3,0
K1::foo(bar::z(x,u))
,Value,Value
0,10,2
1,5,2
2,10,2
K2::foo()
,Value
0,20
1,10
2,15
How can I fix that?
CodePudding user response:
You could use the csv module to ensure that special characters in the key will be correctly quoted or escaped. As you use the default formattings, ie a comma delimiter and a double quote as string delimiter, you can use the defaults for the csv module. Your code would become:
import csv
...
with open('output.csv', 'w') as f:
wr = csv.writer(f)
for key in my_dict.keys():
wr.writerow([key]) # ensure proper CSV formatting of the key cell
df2 = my_dict[key]
df2.to_csv(f)
With you sample data the content of the csv file is:
dummy
,Value
M1,0
M2,0
M3,0
"K1::foo(bar::z(x,u))"
,Value,Value
0,10,2
1,5,2
2,10,2
K2::foo()
,Value
0,20
1,10
2,15
You can see the the csv writer has correctly quoted the second key because it contains a comma...