1
$\begingroup$

I read my csv file as pandas dataframe. Originally it's a dict with multiple entries per keys. Its looks like this after reading as pandas dataframe:

aad,"[1,4,77,4,0,0,0,0,3]"
bchfg,"[4,1,7,8,0,0,0,1,0]"
cad,"[1,2,7,6,0,0,0,0,3,]"
mcfg,"[0,1,0,0,0,5,0,1,1]"

so I want to firstly remove the double quotes" symbol from the file and then want to create a new csv file from previous one with consecutive three entries in each row.

aad,1,4,77
aad,4,0,0
aad,0,0,3
bchfg,4,1,7
bchfg,8,0,0
bchfg,0,1,1
cad,1,2,7
cad,6,0,0
cad,0,0,3
mcfg,0,1,0
mcfg,0,0,5
mcfg,0,1,1
$\endgroup$
1
$\begingroup$
from pandas import read_csv, concat
from ast import literal_eval
df = read_csv('file.csv',header=None,names=['name','value'])
split = df.value.apply(literal_eval).apply(Series).set_index(df.name)
part1 = split.ix[:,:2]
part2 = split.ix[:,3:5]
part3 = split.ix[:,6:]
part2.columns=part3.columns=range(3)
stacked = concat([part1,part2,part3])

Note that this yields a different order than what you requested:

aad    1  4  77
bchfg  4  1   7
cad    1  2   7
mcfg   0  1   0
aad    4  0   0
bchfg  8  0   0
cad    6  0   0
mcfg   0  0   5
aad    0  0   3
bchfg  0  1   0
cad    0  0   3
mcfg   0  1   1
$\endgroup$
  • $\begingroup$ please check another question, as you have idea of doing it in small batch. you can suggest how to do it on big file .thanks in advane. $\endgroup$ – Hazel Jun 7 '18 at 9:12
  • $\begingroup$ If this question is answered, please "accept" it so other users don't waste time crafting answers. $\endgroup$ – Emre Jun 7 '18 at 16:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.