CODEWITHSUNDEEP
pythoncsvpandas
Pete
Pete

Reputation: 15

writing pandas dataframe columns to csv rows staggered

I have a pandas dataframe with three columns, say : A,B,C and I would like to rearrange the data and ouput it in a CSV so that all values in C that have the same value in A share a row. So for example if my Code block is designed as follows (for example, not that I'd design it this way):'

check=pd.DataFrame(columns=['A','B', 'C'])

for i in range(8):
    check.loc[1]=[1,11,10]
    check.loc[2]=[1,21,23]
    check.loc[3]=[1,23,32]
    check.loc[4]=[2,21,41]
    check.loc[5]=[2,21,11]
    check.loc[6]=[3,21,29]
    check.loc[7]=[4,21,43]
    check.loc[8]=[4,21,52]

` I'd want the output to look like one of the following in the CSV: This:

1,,,

10,23,32,

2,,,

41,11,,

3,,,

29,,,

4,,,

43,52,,

OR:

1,10,23,32

2,41,11,

3,29,,

4,43,52,

OR:

10,23,32,

41,11,,

29,,,

43,52,,

Thank you in advance for any suggestions.

Upvotes: 0

Views: 723

Answers (1)

JD Long
JD Long

Reputation: 60746

Well... It's a little hard to grok what you're really doing. But it looks like you are not outputting the B column at all. The first step is to get your data arranged in an acceptable way. Which appears to be a row for each value of A. Then export.

One way to get your last example output is to create a list of lists where each list item is a desired row. I'd do that by grouping the data by A then iterating over the groups:

g = check.groupby('A')
bigList = []
for group in g:
    rowList = []
    for c in group[1].C.iteritems():
       rowList.append(c[1])
    bigList.append( rowList )

now bigList is a list of lists. So we can just convert that to a Pandas dataframe and then save to csv:

outData = pd.DataFrame(bigList)
outData.to_csv('myFile.csv', index=False)

You could take the above loop and modify it to do your other examples as well. This would do your second:

bigList = []
for group in g:
    rowList = []
    rowList.append(group[0])
    for c in group[1].C.iteritems():
        rowList.append(c[1])
    bigList.append( rowList )

Upvotes: 1

Related Questions