# Opening the same CSV file in two different ways in order to transform data in one column and update original CSV using Python

I have the following functions and accompanying script. The purpose of these is is to extract XML data from one CSV column (one XML document for each row), and then transform it into a dictionary (where the tags are the keys and the text are the values). Then I update the headers in the original CSV with the keys and their respective values. This must occur iteratively in order to maintain order and ensure that the data is intact for each record in the original CSV.

I have added comments in order to help the reader. I am seeking feedback on how to create functions for the latter two portions of the code. I am curious to learn new ways of creating, perhaps, a decorator function or a function within another function. To me the latter two scripts are rather clumsy and I would like to improve my code in order to make it more Pythonic.

My specs:

• Python 3.4.3
• Windows 7
• My IDE is Jupyter Notebooks

Sample Data

A,B,C,D,E,F,G,H,I,J,
"3","8","1","<Request TransactionID="3" RequestType="FOO"><InstitutionISO /><CallID>23</CallID><MemberID>12</MemberID><MemberPassword /><RequestData><AccountNumber>2</AccountNumber><AccountSuffix>85</AccountSuffix><AccountType>S</AccountType><MPIAcctType>Checking</MPIAcctType><TransactionCount>10</TransactionCount></RequestData></Request>","<Response TransactionID="2" RequestType="HoldInquiry"><PulledLoans>True</PulledLoans><PulledClosedLoans>False</PulledClosedLoans><PulledInvestments>False</PulledInvestments><PulledClosedInvestments>False</PulledClosedInvestments><PulledCards>False</PulledCards><ShareList>0000',0001,0070,</ShareList></Response>","1967-12-25 22:18:13.471000","2005-12-25 22:18:13.768000","2","70","0"
"5","6","7","<Request TransactionID="10" RequestType="BAR"><InstitutionISO /><CallID>10</CallID><MemberID>4</MemberID><MemberPassword /><RequestData><AccountNumber>6</AccountNumber><AccountSuffix>20</AccountSuffix><AccountType>500</AccountType><MPIAcctType>Checking</MPIAcctType><TransactionCount>10</TransactionCount></RequestData></Request>","<Response TransactionID="2" RequestType="MO"><CallID>21</CallID><ShareList>0000</ShareList></Response>","1978-12-25 22:18:13.471000","2010-12-25 22:18:13.768000","2","70","0"


Helper functions:

import sys
import os
import logging
import xml.etree.cElementTree as ElementTree
from xml.etree.ElementTree import XMLParser
import csv

def flatten_list(aList, prefix=''):

for i, element in enumerate(aList, 1):
eprefix = "{}{}".format(prefix, i)
if element:
# treat like dict
if len(element) == 1 or element[0].tag != element[1].tag:
yield from flatten_dict(element, eprefix)
# treat like list
elif element[0].tag == element[1].tag:
yield from flatten_list(element, eprefix)
elif element.text:
text = element.text.strip()
if text:
yield eprefix[:].rstrip('.'), element.text

def flatten_dict(parent_element, prefix=''):

prefix = prefix + parent_element.tag
if parent_element.items():
for k, v in parent_element.items():
yield prefix + k, v
for element in parent_element:
eprefix = element.tag
if element:
# treat like dict - we assume that if the first two tags
# in a series are different, then they are all different.
if len(element) == 1 or element[0].tag != element[1].tag:
yield from flatten_dict(element, prefix=prefix)
# treat like list - we assume that if the first two tags
# in a series are the same, then the rest are the same.
else:
# here, we put the list in dictionary; the key is the
# tag name the list elements all share in common, and
# the value is the list itself
yield from flatten_list(element, prefix=eprefix)
# if the tag has attributes, add those to the dict
if element.items():
for k, v in element.items():
yield eprefix+k
# this assumes that if you've got an attribute in a tag,
# you won't be having any text. This may or may not be a
# good idea -- time will tell. It works for the way we are
# currently doing XML configuration files...
elif element.items():
for k, v in element.items():
yield eprefix+k
# finally, if there are no child tags and no attributes, extract
# the text
else:
yield eprefix, element.text


Opening CSV file in order to avoid csv reader as it treats double-quotes literally. This affects how the XML data during my attempt to serialize the data. Single quotes within tags and other escape characters cause broken tags and, hence, parsing errors. Unfortunately, I have no control over how the CSV is written.

sample ='sample.csv'

data_list = []

##Step 1 - parse data from input source and append to global list (data_list)

lists = [[str(i) for i in line.strip().split('","')] for line in open(sample).readlines()]
for i in lists[0]:
new_headers = i.split(',') ##split string with comma in order to set us up for mapping headers to the data. This will further stablish order
for i in lists[1:]:
mapped_data = dict(zip(new_headers, i))
data_list.append(mapped_data)

#####Step 2 ####

headers = set()
rows = []

for dictionary in data_list:
#     print(dictionary)
xml_strings = dictionary['E']
root = ElementTree.XML(xml_strings)
xml_dat = dict(flatten_dict(root))


At this point, the two XML records (from the CSV data's column E, above) - excluding the headers A,B,C,D,E,F,G,H,I,J would have the dictionary output as follows):

{'ResponseTransactionID': '2', 'ShareList': "0000',0001,0070,", 'PulledLoans': 'True', 'ResponseRequestType': 'HoldInquiry', 'PulledCards': 'False', 'PulledClosedLoans': 'False', 'PulledInvestments': 'False', 'PulledClosedInvestments': 'False'}
{'ResponseRequestType': 'MO', 'CallID': '21', 'ShareList': '0000', 'ResponseTransactionID': '2'}


Update Data in the same CSV file (continuing the code from above)

    dictionary.update(xml_dat)
headers.update(mapped_data.keys()) ###mapped data headers
headers.update(xml_dat.keys()) ### new keys from xml data
rows.append(dictionary) # save row as its own entity

with open('test.csv', "wt", newline='') as output_file:
wr = csv.writer(output_file)
csv_headers = list(headers)
wr.writerow(csv_headers)
for row in rows:
values = []
for field in csv_headers:
value = row.get(field, None)
values.append(value)
wr.writerow(values)


Final output has headers (original and the transformed tags in the XML) and their values like this and accompanying value:

## Output:

G,H,E,B,ShareList,ResponseRequestType,D,PulledClosedLoans,A,CallID,J,PulledLoans,F,ResponseTransactionID,I,PulledCards,C,PulledInvestments,PulledClosedInvestments
2005-12-25 22:18:13.768000,2,"<Response TransactionID=""2"" RequestType=""HoldInquiry""><PulledLoans>True</PulledLoans><PulledClosedLoans>False</PulledClosedLoans><PulledInvestments>False</PulledInvestments><PulledClosedInvestments>False</PulledClosedInvestments><PulledCards>False</PulledCards><ShareList>0000',0001,0070,</ShareList></Response>",8,"0000',0001,0070,",HoldInquiry,"<Request TransactionID=""3"" RequestType=""FOO""><InstitutionISO /><CallID>23</CallID><MemberID>12</MemberID><MemberPassword /><RequestData><AccountNumber>2</AccountNumber><AccountSuffix>85</AccountSuffix><AccountType>S</AccountType><MPIAcctType>Checking</MPIAcctType><TransactionCount>10</TransactionCount></RequestData></Request>",False,"""3",,"0""",True,1967-12-25 22:18:13.471000,2,70,False,1,False,False
2010-12-25 22:18:13.768000,2,"<Response TransactionID=""2"" RequestType=""MO""><CallID>21</CallID><ShareList>0000</ShareList></Response>",6,0000,MO,"<Request TransactionID=""10"" RequestType=""BAR""><InstitutionISO /><CallID>10</CallID><MemberID>4</MemberID><MemberPassword /><RequestData><AccountNumber>6</AccountNumber><AccountSuffix>20</AccountSuffix><AccountType>500</AccountType><MPIAcctType>Checking</MPIAcctType><TransactionCount>10</TransactionCount></RequestData></Request>",,"""5",21,"0""",,1978-12-25 22:18:13.471000,2,70,,7,,

• @JoeWallis The code is all in one file. I added breaks to explain what is happening and what the ouputs are at each step. I added indentation. "dictionary" is only defined in the for loop in Step 2. I encourage you to test my code. I am only seeking advice on how to better write Python. – ahlusar1989 Jan 11 '16 at 18:23
• @JoeWallis I am not sure how you pasted my code - do you mind sending me a Gist? The order of the headers will be different due to the fact that you are writing the headers from a set (hence these will be unordered). I could have sorted them at the write stage but this is trivial. – ahlusar1989 Jan 11 '16 at 19:06
• @JoeWallis That's exactly correct - the output, however, will be different. Everything looks perfect. – ahlusar1989 Jan 11 '16 at 19:27
• You could learn much more if you tell us what is the purpose of the output. It is rather uncommon to have XML inside of CSV, thus I do not want to take time to transform strange data into another strange form. – Sascha Gottfried Jan 12 '16 at 10:12
• @SaschaGottfried 1) I am not at liberty to discuss the purpose, except that it is part of a process in data ingestion and 2) "I do not want to take time to transform strange data" is not a constructive contribution to the original post. – ahlusar1989 Jan 12 '16 at 12:01