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
Improve this question
$\endgroup$

1 Answer 1

Reset to default
2
$\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
Improve this answer
$\endgroup$
2
  • $\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, 2018 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, 2018 at 16:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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