row to column csv python for multiple values under one heading

261 views Asked by At

I have the following text file. dummy.txt

Property,Value
status,ACTIVE
updated,2014-04-26T17:01:27Z
OS-EXT_STS:task-state,None
OS-EXT-ATTR:host,noe-001
key_name,dummy-host
image,1234d46g-354f-89fg-23fg-45fhty56fjd7
mine-internalnetwork,192.168.50.10,10.10.80.40
hostId,123g3124h4h32h3h432h4324h2jh423j4h32k4h32jh432j4hj2k342
Property,Value
status,ACTIVE
updated,2014-04-25T17:01:27Z
OS-EXT_STS:task-state,None
OS-EXT-ATTR:host,noe-002
key_name,dummy-host1
image,1234d46g-fh56-8h5g-23fg-4556fhtmggjg
mine-internalnetwork,192.168.49.10,10.10.80.39
hostId,436bx8fbby747ycb4y7535afggoirif947307ffh8tycfu34yxycf84

Now, I want to print result like below where value is getting changed for column 2 after property and value.

status,updated,OS-EXT_STS:task-state,OS-EXT-ATTR:host,key_name,image,mine-internalnetwork,hostId
ACTIVE,2014-04-26T17:01:27Z,None,noe-001,dummy-host,1234d46g-354f-89fg-23fg-45fhty56fjd7,192.168.50.10,10.10.80.40
ACTIVE,2014-04-25T17:01:27Z,None,noe-002,dummy-host1,1234d46g-fh56-8h5g-23fg-4556fhtmggjg,192.168.49.10,10.10.80.39

This is my code: import csv import subprocess import sys import os

from collections import defaultdict

columns = defaultdict(list)

with open('dummy.txt.txt') as f: reader = csv.DictReader(f) for row in reader: for (k,v) in row.items(): columns[k].append(v)

print(columns['Property']) print(columns['Value'])

And the output i am getting as below.

./testdb_update3.py ['status', 'updated', 'OS-EXT_STS:task-state', 'OS-EXT-ATTR:host', 'key_name', 'image', 'mine-internalnetwork', 'hostId'] ['ACTIVE', '2014-04-26T17:01:27Z', 'None', 'noe-001', 'dummy-host', '1234d46g-354f-89fg-23fg-45fhty56fjd7', '192.168.50.10', '123g3124h4h32h3h432h4324h2jh423j4h32k4h32jh432j4hj2k342']

dummy.txt file: (this file doesn't contain multiple values, which i tried later and post a question)

status,ACTIVE updated,2014-04-26T17:01:27Z OS-EXT_STS:task-state,None OS-EXT-ATTR:host,noe-001 key_name,dummy-host image,1234d46g-354f-89fg-23fg-45fhty56fjd7 mine-internalnetwork,192.168.50.10,10.10.80.40 hostId,123g3124h4h32h3h432h4324h2jh423j4h32k4h32jh432j4hj2k342

1

There are 1 answers

7
AudioBubble On

For data you should use Pandas. Try this:

import pandas as pd
from pandas import DataFrame as df
import numpy as np

f = open('dummy.txt','r')
text = f.read()

t = np.array([i.split(',')[1] for i in text.split('\n')])
t = t.reshape(len(t)/9,9)

A = df(t[:,1:], columns=['status', 'updated', 'OS-EXT_STS:task-state', 'OS-EXT-ATTR:host', 'key_name',
                'image', 'mine-internalnetwork', 'hostId', ])

for p in A.columns.values:
    print(p,end=', ')
for i in A.values:
    for j in i:
        print(j,end=', ')
    print('\n',end='')

Also if you want to import you information to csv (common task), use this command:

A.to_csv('out.csv',index=False)