Python :将列拆分为列，然后按分隔符对行进行拆分

Question

我在csv文件中有一个列，它以这种格式包含person的详细信息：

+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  Team  |                                                                                                Members                                                                                                 |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Team 1 | OK-10:Jason:Jones:ID No:00000000:male:my notes                                                                                                                                                         |
| Team 2 | OK-10:Mike:James:ID No:00000001:male:my notes OZ-09:John:Rick:ID No:00000002:male:my notes                                                                                                             |
| Team 3 | OK-08:Michael:Knight:ID No:00000004:male:my notes2 OK-09:Helen:Rick:ID No:00000005:female:my notes3 OZ-10:Jane:James:ID No:00000034:female:my notes23 OK-09:Mary:Jane:ID No:00000023:female:my notes46 |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

实际csv格式：

"Team", "Members"                                                                                                 
 Team 1, OK-10:Jason:Jones:ID No:00000000:male:my notes                                                                                                                                                         
 Team 2, OK-10:Mike:James:ID No:00000001:male:my notes OZ-09:John:Rick:ID No:00000002:male:my notes                                                                                                             
 Team 3, OK-08:Michael:Knight:ID No:00000004:male:my notes2 OK-09:Helen:Rick:ID No:00000005:female:my notes3 OZ-10:Jane:James:ID No:00000034:female:my notes23 OK-09:Mary:Jane:ID No:00000023:female:my notes46

我想把它们分割成一个新的csv文件，如下所示：

+-------+-------------+-------------+----------------+------------------+---------------+---------------+--------------+
| Team  | Member_Rank | Member_Name | Member_Surname | Member_ID_Method | Member_ID_Num | Member_Gender | Member_Notes |
+-------+-------------+-------------+----------------+------------------+---------------+---------------+--------------+
| Team1 | OK-10       | Jason       | Jones          | ID No            |      00000000 | male          | my notes     |
| Team2 | OK-10       | Mike        | James          | ID No            |      00000001 | male          | my notes     |
| Team2 | OZ-09       | John        | Rick           | ID No            |      00000002 | male          | my notes     |
+-------+-------------+-------------+----------------+------------------+---------------+---------------+--------------+

分割细节：

分隔行划界器：' O&-'，其中&只能是'K'或'Z'

分隔列分隔符：':'，新csv文件中的列号是固定的

(一个团队可以包含多个成员，没有上限)

更新

通过使用@Adirio提供的代码，我只能从具有多个成员的字段中获得最后一个成员：

import csv
import re


members_split_regex = re.compile(r'(O[KZ]-\d+):([a-zA-Z0-9 ]+):([a-zA-Z0-9 ]+):([a-zA-Z0-9 ]+):([a-zA-Z0-9 ]+):([a-zA-Z0-9 ]+):([a-zA-Z0-9 ]+)(?= O[KZ]|$)')

with open('test.csv') as input_file, open('output_csv.csv', 'w', newline='') as output_file:
    csv_reader = csv.DictReader(input_file)
    fieldnames = csv_reader.fieldnames.copy()
    fieldnames.remove('Members')
    csv_writer = csv.DictWriter(output_file, extrasaction='ignore', fieldnames=fieldnames + ['Member_Rank', 'Member_Name', 'Member_Surname', 'Member_ID_Method', 'Member_ID_Num', 'Member_Gender', 'Member_Notes'])
    csv_writer.writeheader()
    for row in csv_reader:
        for member_tuple in members_split_regex.findall(row['Members']):
                member_dict = {}
                (
                    member_dict['Member_Rank'],
                    member_dict['Member_Name'],
                    member_dict['Member_Surname'],
                    member_dict['Member_ID_Method'],
                    member_dict['Member_ID_Num'],
                    member_dict['Member_Gender'],
                    member_dict['Member_Notes']
                ) = member_tuple
                print(row['Members'])
                print(member_tuple)
                member_dict.update(row)
                csv_writer.writerow(member_dict)

打印结果：

行‘’Members‘-> OK-1:name1:sunrmae2 2:ID 1233123:男:Note12 OK-10:name2:sunrame2:护照号:asda3243242:女:Note2OZ-1:nma3 3:surname3:护照编号:asd213131:其他:注56 打印(Member_tuple) -> (“OZ-1”、“nma3 3”、“suname3”、“Passport No”、“note 213131”、“other”、“note 56”)

Answer 1

基于@深度空间的答案，但有一个固定的regex和新的要求：

import csv
import re


members_split_regex = re.compile(r'(O[KZ]-\d+):([a-zA-Z0-9 ]+):([a-zA-Z0-9 ]+):([a-zA-Z0-9 ]+):([a-zA-Z0-9 ]+):([a-zA-Z0-9 ]+):([a-zA-Z0-9 ]+)(?= O[KZ]|$)')

with open('test.csv') as input_file, open('output_csv', 'w', newline='') as output_file:
    csv_reader = csv.DictReader(input_file)
    fieldnames = csv_reader.fieldnames.copy()
    fieldnames.remove('Members')
    csv_writer = csv.DictWriter(output_file, extrasaction='ignore', fieldnames=fieldnames + ['Member_Rank', 'Member_Name', 'Member_Surname', 'Member_ID_Method', 'Member_ID_Num', 'Member_Gender', 'Member_Notes'])
    csv_writer.writeheader()
    for row in csv_reader:
        for member_tuple in members_split_regex.findall(row['Members']):
            member_dict = {}
            (
                member_dict['Member_Rank'],
                member_dict['Member_Name'],
                member_dict['Member_Surname'],
                member_dict['Member_ID_Method'],
                member_dict['Member_ID_Num'],
                member_dict['Member_Gender'],
                member_dict['Member_Notes']
            ) = member_tuple
            member_dict.update(row)
            csv_writer.writerow(member_dict)

主要的区别是，我从字典中删除了该列，以便我们可以使用它来更新我们的新字典。这样，我们不仅复制了"Team“列，而且复制了其他不是”成员“的列。为此，还复制了读取器的字段名，删除了“成员”项，并将新的字段名添加到写者的字段名中。

使用的regex不对任何字段进行硬编码，允许名称和姓氏中的空格，注释中的大写Os，以及不只是8位数字的ID字段。

Answer 2

假设输入CSV

Team,Members
Team 1,OK-10:Jason:Jones:ID No:00000000:male:my notes
Team 2,OK-10:Mike:James:ID No:00000001:male:my notes OZ-09:John:Rick:ID No:00000002:male:my notes
Team 3,OK-08:Michael:Knight:ID No:00000004:male:my notes2 OK-09:Helen:Rick:ID No:00000005:female:my notes3 OZ-10:Jane:James:ID No:00000034:female:my notes23 OK-09:Mary:Jane:ID No:00000023:female:my notes46

这可以通过regex、csv.DictReader和csv.DictWriter来实现。

import csv
import re

output = []

members_split_regex = re.compile(r'(O[KZ]-\d+):([a-zA-Z0-9 ]+):([a-zA-Z0-9 ]+):([a-zA-Z0-9 ]+):([a-zA-Z0-9 ]+):([a-zA-Z0-9 ]+):([a-zA-Z0-9 ]+)(?= O[KZ]|$)')

with open('test.csv') as f:
    csv_reader = csv.DictReader(f)
    for row in csv_reader:
        team = row['Team']
        members = row['Members']
        split_members = members_split_regex.findall(members)
        for member in split_members:
                (member_rank, member_name, member_surname, member_id_method,
                 member_id_num, member_gender, member_notes) = member

                output.append({'Team': team, 'Member_Rank': member_rank, 'Member_Name': member_name,
                               'Member_Surname': member_surname, 'Member_ID_Method': member_id_method,
                               'Member_ID_Num': member_id_num, 'Member_Gender': member_gender,
                               'Member_Notes': member_notes})

with open('output_csv', 'w', newline='') as f:
    csv_writer = csv.DictWriter(f, fieldnames=['Team', 'Member_Rank', 'Member_Name', 'Member_Surname', 'Member_ID_Method', 'Member_ID_Num', 'Member_Gender', 'Member_Notes'])
    csv_writer.writeheader()
    csv_writer.writerows(output)

输出文件是

Team,Member_Rank,Member_Name,Member_Surname,Member_ID_Method,Member_ID_Num,Member_Gender,Member_Notes
Team 1,OK-10,Jason,Jones,ID No,00000000,male,my notes
Team 2,OK-10,Mike,James,ID No,00000001,male,my notes 
Team 2,OZ-09,John,Rick,ID No,00000002,male,my notes
Team 3,OK-08,Michael,Knight,ID No,00000004,male,my notes2 
Team 3,OK-09,Helen,Rick,ID No,00000005,female,my notes3 
Team 3,OZ-10,Jane,James,ID No,00000034,female,my notes23 
Team 3,OK-09,Mary,Jane,ID No,00000023,female,my notes46