python csv : Split column to columns and then to rows by delimiter

Question

我在 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", "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'

Split Column Delimiter : ':' ,新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)

打印结果:

row['Members'] ->

OK-1:name1:sunrmae2:ID No:id1233123:male:note12 OK-10:name2:sunrame2:Passport No:asda3243242:female:note2 OZ-1:nma3:surname3:Passport No:asd213131:other:note 56

print(member_tuple) ->

('OZ-1', 'nma3', 'surname3', 'Passport No', 'asd213131', 'other', 'note 56')

Answer 1

假设这个输入 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.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