Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.python > #93043

To write headers once with different values in separate row in CSV

Show all headers | View raw

I have the following script for writing out to CSV two items in a list to a CSV in such a format, such that if we have:

L = [('A', 1), ('B', 2), ('C', 3), ('D', 4),('A', 5), ('B', 6), ('D', 8)]

we want

A B C D
1 2 3 4
5 6   8

And with this 

L = [('A', 1), ('B', 2), ('C', 3), ('D', 4),('D', 5), ('B', 6), ('A', 8)]

A B C D
1 2 3 4
8 6   5

I have this script: 

    def makerows(pairs):
        headers = []
        columns = {}
        for k, v in pairs:
            if k in columns:
                columns[k].extend((v,))
            else:
                headers.append(k)
                columns[k] = [k, v]
        m = max(len(c) for c in columns.values())
        for c in columns.values():
            c.extend('' for i in range(len(c), m))
        L = [columns[k] for k in headers]
        rows = list(zip(*L))
        return rows

//With this test//


    if __name__ == '__main__':
        
        lists = [
            [('A', 1), ('B', 2), ('C', 3), ('D', 4),('A', 5), ('B', 6), ('C', 7), ('D', 8)],
            [('A', 1), ('B', 2), ('C', 3), ('D', 4),('A', 5), ('B', 6), ('D', 8)],
            [('A', 1), ('B', 2), ('C', 3), ('D', 4),('D', 5), ('B', 6), ('A', 8)],
        ]
        from pprint import pprint
        for data in lists:
            print(data)
            pprint(makerows(data))


output:
    
    [('A', 1), ('B', 2), ('C', 3), ('D', 4), ('A', 5), ('B', 6), ('C', 7), ('D', 8)]
    [('A', 'B', 'C', 'D'), (1, 2, 3, 4), (5, 6, 7, 8)]
    [('A', 1), ('B', 2), ('C', 3), ('D', 4), ('A', 5), ('B', 6), ('D', 8)]
    [('A', 'B', 'C', 'D'), (1, 2, 3, 4), (5, 6, '', 8)]
    [('A', 1), ('B', 2), ('C', 3), ('D', 4), ('D', 5), ('B', 6), ('A', 8)]  

[('A', 'B', 'C', 'D'), (1, 2, 3, 4), (8, 6, '', 5)]

However, when I extrapolate this same logic with a list like:

('Response.MemberO.PMembers.PMembers.Member.CurrentEmployer.EmployerAddress.TimeAtPreviousAddress.', None), where the headers/columns are the first item (only to be written out once) with different values. I receive an output CSV with repeating headers and values all printed in one long string (when opened in an application like Excel). 

I use this script:

def makerows(pairs):

        headers = []
        columns = {}
        for k, v in pairs:
            if k in columns:
                columns[k].extend((v,))
            else:
                headers.append(k)
                columns[k] = [k, v]
        m = max(len(c) for c in columns.values())
        for c in columns.values():
            c.extend(' ' for i in range(len(c), m))
        L = [columns[k] for k in headers]
        rows = list(zip(*L))
        return rows
    
    
    def main():
        with open('sample.xml', 'r', encoding='utf-8') as f: 
            xml_string = f.read() 
        xml_string= xml_string.replace('�', '') #optional to remove ampersands. 
        root = ElementTree.XML(xml_string) 
        for item in root:
            print(root)
        writer = csv.writer(open("test_out.csv", 'wt'))
        writer.writerows(makerows(flatten_dict(root)))
        
    if __name__ == "__main__":
            main()


************FYI, flatten_dict() parses XML elements and their text in to key, value pairs and then converts them into a list. That is not the underlying issue. Any thoughts or suggestions would be very helpful. Thank you. 

Back to comp.lang.python | Previous | Next — Next in thread | Find similar | Unroll thread

Thread

To write headers once with different values in separate row in CSV Sahlusar <ahlusar.ahluwalia@gmail.com> - 2015-06-23 10:15 -0700
  Re: To write headers once with different values in separate row in CSV John Gordon <gordon@panix.com> - 2015-06-23 19:12 +0000
    Re: To write headers once with different values in separate row in CSV kbtyo <ahlusar.ahluwalia@gmail.com> - 2015-06-24 04:31 -0700
  Re: To write headers once with different values in separate row in CSV Steven D'Aprano <steve@pearwood.info> - 2015-06-24 11:50 +1000
    Re: To write headers once with different values in separate row in CSV kbtyo <ahlusar.ahluwalia@gmail.com> - 2015-06-24 04:37 -0700
      Re: To write headers once with different values in separate row in CSV Steven D'Aprano <steve@pearwood.info> - 2015-06-24 22:38 +1000
        Re: To write headers once with different values in separate row in CSV kbtyo <ahlusar.ahluwalia@gmail.com> - 2015-06-24 05:52 -0700
        Re: To write headers once with different values in separate row in CSV kbtyo <ahlusar.ahluwalia@gmail.com> - 2015-06-25 09:37 -0700

csiph-web