我是 Python/JSON 的新手，所以请多多包涵。我可以在 R 中执行此操作，但我们需要使用 Python 才能将其转换为 Python/Spark/MongoDB。另外，我只是发布了一个最小的子集——我有更多的文件类型，所以如果有人能帮助我，我可以在此基础上集成更多的文件和文件类型:
回到我的问题:
我有两个 tsv 输入文件需要合并并转换为 JSON。这两个文件都有基因和样本列以及一些额外的列。但是，gene 和 sample 可能会重叠，也可能不会像我展示的那样重叠 - f2.tsv 具有 f1.tsv 中的所有基因，但还有一个额外的基因 g3 。同样，两个文件在 sample 列中都有重叠和不重叠的值。
# f1.tsv – has gene, sample and additional column other1
$ cat f1.tsv
gene sample other1
g1 s1 a1
g1 s2 b1
g1 s3a c1
g2 s4 d1
# f2.tsv – has gene, sample and additional columns other21, other22
$ cat f2.tsv
gene sample other21 other22
g1 s1 a21 a22
g1 s2 b21 b22
g1 s3b c21 c22
g2 s4 d21 d22
g3 s5 f21 f22
基因构成顶层，每个基因有多个样本构成第二层，额外的列构成第三层的extras。extras是分为两个，因为一个文件有 other1，第二个文件有 other21 和 other22。我稍后将包含的其他文件将具有其他字段，如 other31 和 other32 等等，但它们仍将具有基因和样本列。
# expected output – JSON by combining both tsv files.
$ cat output.json
[{
"gene":"g1",
"samples":[
{
"sample":"s2",
"extras":[
{
"other1":"b1"
},
{
"other21":"b21",
"other22":"b22"
}
]
},
{
"sample":"s1",
"extras":[
{
"other1":"a1"
},
{
"other21":"a21",
"other22":"a22"
}
]
},
{
"sample":"s3b",
"extras":[
{
"other21":"c21",
"other22":"c22"
}
]
},
{
"sample":"s3a",
"extras":[
{
"other1":"c1"
}
]
}
]
},{
"gene":"g2",
"samples":[
{
"sample":"s4",
"extras":[
{
"other1":"d1"
},
{
"other21":"d21",
"other22":"d22"
}
]
}
]
},{
"gene":"g3",
"samples":[
{
"sample":"s5",
"extras":[
{
"other21":"f21",
"other22":"f22"
}
]
}
]
}]
如何将两个csv文件转换为基于两个公共(public)列的单层-多层JSON？
如果我能在这方面得到任何帮助，我将不胜感激。
谢谢!
请您参考如下方法:
这是另一种选择。当您开始添加更多文件时，我试图使其易于管理。您可以在命令行上运行并提供参数，每个参数对应您要添加的每个文件。基因/样本名称存储在字典中以提高效率。您所需的 JSON 对象的格式是在每个类的 format() 方法中完成的。希望这会有所帮助。
import csv, json, sys
class Sample(object):
def __init__(self, name, extras):
self.name = name
self.extras = [extras]
def format(self):
map = {}
map['sample'] = self.name
map['extras'] = self.extras
return map
def add_extras(self, extras):
#edit 8/20
#always just add the new extras to the list
for extra in extras:
self.extras.append(extra)
class Gene(object):
def __init__(self, name, samples):
self.name = name
self.samples = samples
def format(self):
map = {}
map ['gene'] = self.name
map['samples'] = sorted([self.samples[sample_key].format() for sample_key in self.samples], key=lambda sample: sample['sample'])
return map
def create_or_add_samples(self, new_samples):
# loop through new samples, seeing if they already exist in the gene object
for sample_name in new_samples:
sample = new_samples[sample_name]
if sample.name in self.samples:
self.samples[sample.name].add_extras(sample.extras)
else:
self.samples[sample.name] = sample
class Genes(object):
def __init__(self):
self.genes = {}
def format(self):
return sorted([self.genes[gene_name].format() for gene_name in self.genes], key=lambda gene: gene['gene'])
def create_or_add_gene(self, gene):
if not gene.name in self.genes:
self.genes[gene.name] = gene
else:
self.genes[gene.name].create_or_add_samples(gene.samples)
def row_to_gene(headers, row):
gene_name = ""
sample_name = ""
extras = {}
for value in enumerate(row):
if headers[value[0]] == "gene":
gene_name = value[1]
elif headers[value[0]] == "sample":
sample_name = value[1]
else:
extras[headers[value[0]]] = value[1]
sample_dict = {}
sample_dict[sample_name] = Sample(sample_name, extras)
return Gene(gene_name, sample_dict)
if __name__ == '__main__':
delim = "\t"
genes = Genes()
files = sys.argv[1:]
for file in files:
print("Reading " + str(file))
with open(file,'r') as f1:
reader = csv.reader(f1, delimiter=delim)
headers = []
for row in reader:
if len(headers) == 0:
headers = row
else:
genes.create_or_add_gene(row_to_gene(headers, row))
result = json.dumps(genes.format(), indent=4)
print(result)
with open('json_output.txt', 'w') as output:
output.write(result)
