将属性相互匹配

3条回答

网友

1楼 · 编辑于 2024-09-25 00:25:41

只需使用dict映射到每个SSN的唯一id来记录所看到的SSN，您只需要对行进行一次传递，并使用csv module来解析将为您执行拆分的文件。如果您想要一个全新的文件：

import csv

cn = 10001

with open("test.txt") as f, open("out.txt","w") as tmp:
    r, wr = csv.reader(f, delimiter="|"), csv.writer(tmp, delimiter="|")
    head, d = next(r), {}
    wr.writerow(["ID"]+head)
    for row in r:
        v = row[4]
        # if we have already seen the SSN, use the id assigned
        if v in d:
            wr.writerow([d[v]] + row)
        else:
            # else create new id, add pairing to dict and write
            d[v] = cn
            wr.writerow([cn] + row)
            cn += 1

输出：

ID|RefID|FirstName|MiddleName|LastName|SSN|DOB|School Year|Age|District LEA|District Description|School LEA|Location Description|title|frng_amt
10001|1|JULIE|A|ADAMS|123456789|654321|20142015|47|0101000|DEWITTSCHOOLDISTRICT|P|014
10001|2|JULIE|A|ADAMS|123456789|654321|20132014|46|0101000|DEWITTSCHOOLDISTRICT|S|13100
10001|3|JULIE|A|ADAMS|123456789|654321|20122013|45|0101000|DEWITTSCHOOLDISTRICT|P|014
10001|4|JULIE|A|ADAMS|123456789|654321|20132014|46|0101000|DEWITTSCHOOLDISTRICT|P|014
10001|5|JULIE|A|ADAMS|123456789|654321|20142015|47|0101000|DEWITTSCHOOLDISTRICT|S|15000
10001|6|JULIE|A|ADAMS|123456789|654321|20122013|45|0101000|DEWITTSCHOOLDISTRICT|S|13100
10002|7|SHIRLEY||ADAMS|987654321|987890|20122013|49|0101000|DEWITTSCHOOLDISTRICT|S|13100
10002|8|SHIRLEY||ADAMS|987654321|987890|20092010|46|0101000|DEWITTSCHOOLDISTRICT|P|014
10002|9|SHIRLEY||ADAMS|987654321|987890|20102011|47|0101000|DEWITTSCHOOLDISTRICT|P|014
10002|10|SHIRLEY||ADAMS|987654321|987890|20132014|50|0101000|DEWITTSCHOOLDISTRICT|S|13100
10002|11|SHIRLEY||ADAMS|987654321|987890|20132014|50|0101000|DEWITTSCHOOLDISTRICT|P|014
10002|12|SHIRLEY||ADAMS|987654321|987890|20122013|49|0101000|DEWITTSCHOOLDISTRICT|P|014
10002|13|SHIRLEY||ADAMS|987654321|987890|20102011|47|0101000|DEWITTSCHOOLDISTRICT|A|13100
10002|14|SHIRLEY||ADAMS|987654321|987890|20142015|51|0101000|DEWITTSCHOOLDISTRICT|S|15000
10002|15|SHIRLEY||ADAMS|987654321|987890|20092010|46|0101000|DEWITTSCHOOLDISTRICT|A|13100
10002|16|SHIRLEY||ADAMS|987654321|987890|20142015|51|0101000|DEWITTSCHOOLDISTRICT|P|014

如果要更新原始文件，可以写入tempfile并执行shutil.move：

import csv
from shutil import move
from tempfile import NamedTemporaryFile
import os

cn = 100001
try:
    with open("test.txt") as f, NamedTemporaryFile("w", dir=".", delete=False) as tmp:
        r, wr = csv.reader(f, delimiter="|"), csv.writer(tmp, delimiter="|")
        head, d = next(r), {}
        wr.writerow(["ID"] + head)
        for row in r:
            v = row[4]
            if v in d:
                wr.writerow([d[v]] + row)
            else:
                d[v] = cn
                wr.writerow([cn] + row)
                cn += 1
    # replace original file
    move(tmp.name, "test.txt"))
finally:
    if os.path.isfile(tmp.name):
        os.unlink(tmp.name)

如果数据的顺序与输入的顺序相同，则可以groupby：

import csv
from itertools import groupby
from operator import itemgetter

cn = 10001
with open("test.txt") as f, open("out.txt", "w") as tmp:
    r, wr = csv.reader(f, delimiter="|"), csv.writer(tmp, delimiter="|")
    head, d = next(r), {}
    wr.writerow(["ID"] + head)
    for k, v in groupby(r, key=itemgetter(4)):
        wr.writerows([cn]+sub for sub in v)
        cn += 1

网友

2楼 · 编辑于 2024-09-25 00:25:41

你听说过^{}吗？它可以帮助你！你知道吗

import numpy as np
import pandas as pd

# Load data set
data = pd.read_csv(inputFile, delimiter='|')

# Tag
def func(ssn):
    if ssn == 123456789:
        return 10001
    if ssn == 987654321:
        return 10002

data['ID'] = data['SSN'].apply(func)

# Reorder columns
new_cols = np.concatenate((data.columns[-1:], data.columns[:-1]), axis=0)
data = data[new_cols]

# Save file
data.to_csv(outputFile, sep='|', index=False)

输出为：

ID|RefID|FirstName|MiddleName|LastName|SSN|DOB|School Year|Age|District LEA|District Description|School LEA|Location Description|title|frng_amt
10001|1|JULIE|A|ADAMS|123456789|654321|20142015|47|101000|DEWITTSCHOOLDISTRICT|P|14||
10001|2|JULIE|A|ADAMS|123456789|654321|20132014|46|101000|DEWITTSCHOOLDISTRICT|S|13100||
10001|3|JULIE|A|ADAMS|123456789|654321|20122013|45|101000|DEWITTSCHOOLDISTRICT|P|14||
10001|4|JULIE|A|ADAMS|123456789|654321|20132014|46|101000|DEWITTSCHOOLDISTRICT|P|14||
10001|5|JULIE|A|ADAMS|123456789|654321|20142015|47|101000|DEWITTSCHOOLDISTRICT|S|15000||
10001|6|JULIE|A|ADAMS|123456789|654321|20122013|45|101000|DEWITTSCHOOLDISTRICT|S|13100||
10002|7|SHIRLEY||ADAMS|987654321|987890|20122013|49|101000|DEWITTSCHOOLDISTRICT|S|13100||
10002|8|SHIRLEY||ADAMS|987654321|987890|20092010|46|101000|DEWITTSCHOOLDISTRICT|P|14||
10002|9|SHIRLEY||ADAMS|987654321|987890|20102011|47|101000|DEWITTSCHOOLDISTRICT|P|14||
10002|10|SHIRLEY||ADAMS|987654321|987890|20132014|50|101000|DEWITTSCHOOLDISTRICT|S|13100||
10002|11|SHIRLEY||ADAMS|987654321|987890|20132014|50|101000|DEWITTSCHOOLDISTRICT|P|14||
10002|12|SHIRLEY||ADAMS|987654321|987890|20122013|49|101000|DEWITTSCHOOLDISTRICT|P|14||
10002|13|SHIRLEY||ADAMS|987654321|987890|20102011|47|101000|DEWITTSCHOOLDISTRICT|A|13100||
10002|14|SHIRLEY||ADAMS|987654321|987890|20142015|51|101000|DEWITTSCHOOLDISTRICT|S|15000||
10002|15|SHIRLEY||ADAMS|987654321|987890|20092010|46|101000|DEWITTSCHOOLDISTRICT|A|13100||
10002|16|SHIRLEY||ADAMS|987654321|987890|20142015|51|101000|DEWITTSCHOOLDISTRICT|P|14||

更新

正如与Padraic Cunningham讨论的，OP可以有两个以上的SSN。在这种情况下，bes解决方案是：

import numpy as np
import pandas as pd

# Load data set
data = pd.read_csv(inputFile, delimiter='|')

# Tag
tag ={k:10001+k for i, k in enumerate(data['SSN'].unique())}
data['ID'] = data['SSN'].apply(lambda ssn: tag[ssn])

# Reorder columns
new_cols = np.concatenate((data.columns[-1:], data.columns[:-1]), axis=0)
data = data[new_cols]

# Save file
data.to_csv(outputFile, sep='|', index=False)

网友
3楼 · 编辑于 2024-09-25 00:25:41

好吧，你已经有一个唯一的号码了，它是SSN。你能做的就是创建一个SSN字典来定义唯一的代码。你知道吗

inputReader = open(inputFile,'r')
outputWriter = open(outputFile, 'w')
headers = inputReader.readline()
outputWriter.write("ID"+headers)

ssn_dict = {}
counter = 100000
for x in inputReader:
    ssn_counter = ssn_dict.get(x.split("|")[4]
    if ssn_count is not None:
        outputWriter.write(str(ssn_count) + "|" + x)
    else:
       ssn_count[x.split("|")[4] = counter
       counter =+ 1
       outputWriter.write(str(counter) + "|" + x)

相关问题更多 >

编程相关推荐

热门问题

热门文章

将属性相互匹配

相关问题 更多 >

编程相关推荐

热门问题

热门文章

相关问题更多 >