python csv文件与字典，列表等之间的转换小结

Posted on | In

整理了其中的一些对于csv文件的读写操作和常用的Python’数据结构’（如字典和列表）之间的转换 转载

csv文件与列表之间的转换

将列表转换为csv文件

1
2
3
4
def list2csv(list, file):
    wr = csv.writer(open(file, 'wb'), quoting=csv.QUOTE_ALL)
    for word in list:
        wr.writerow([word])

将嵌套字典的列表转换为csv文件

1
2
3
4
5
6
7
my_list = [{'players.vis_name': 'Khazri', 'players.role': 'Midfielder', 'players.country': 'Tunisia',
            'players.last_name': 'Khazri', 'players.player_id': '989', 'players.first_name': 'Wahbi',
            'players.date_of_birth': '08/02/1991', 'players.team': 'Bordeaux'},
           {'players.vis_name': 'Khazri', 'players.role': 'Midfielder', 'players.country': 'Tunisia',
            'players.last_name': 'Khazri', 'players.player_id': '989', 'players.first_name': 'Wahbi',
            'players.date_of_birth': '08/02/1991', 'players.team': 'Sunderland'}
            ]
1
2
3
4
5
6
7
def nestedlist2csv(list, out_file):
    with open(out_file, 'wb') as f:
        w = csv.writer(f)
        fieldnames=list[0].keys()  # solve the problem to automatically write the header
        w.writerow(fieldnames)
        for row in list:
            w.writerow(row.values())

csv文件与字典之间的转换

csv文件转换为字典

1
2
3
4
5
6
7
8
9
def csv2dict(in_file,key,value):
    new_dict = {}
    with open(in_file, 'rb') as f:
        reader = csv.reader(f, delimiter=',')
        fieldnames = next(reader)
        reader = csv.DictReader(f, fieldnames=fieldnames, delimiter=',')
        for row in reader:
            new_dict[row[key]] = row[value]
    return new_dict

针对每一行均为键值对的特殊情形

1
2
3
4
5
6
7
def row_csv2dict(csv_file):
    dict_club={}
    with open(csv_file)as f:
        reader=csv.reader(f,delimiter=',')
        for row in reader:
            dict_club[row[0]]=row[1]
    return dict_club

csv文件转换为二级字典

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
def build_level2_dict(source_file):
    new_dict = {}
    with open(source_file, 'rb')as csv_file:
        data = csv.DictReader(csv_file, delimiter=",")
        for row in data:
            item = new_dict.get(row['country'], dict())
            item[row['name']] = {k: row[k] for k in ('id','age')}
            new_dict[row['country']] = item
    return new_dict
# second method
def build_level2_dict2(source_file,outer_key,inner_key,inner_value):
    new_dict = {}
    with open(source_file, 'rb')as csv_file:
        data = csv.DictReader(csv_file, delimiter=",")
        for row in data:
            item = new_dict.get(row[outer_key], dict())
            item[row[inner_key]] = row[inner_value]
            new_dict[row[outer_key]] = item
    return new_dict

用列表保存值域

1
2
3
4
5
6
7
8
9
10
11
12
13
def build_level2_dict4(source_file,outer_key,lst_inner_value):
    new_dict = {}
    with open(source_file, 'rb')as csv_file:
        data = csv.DictReader(csv_file, delimiter=",")
        for row in data:
            # print row
            item = new_dict.get(row[outer_key], dict())
            # item.setdefault('move from',[]).append(row['move from'])
            # item.setdefault('move to', []).append(row['move to'])
            for element in lst_inner_value:
                item.setdefault(element, []).append(row[element])
            new_dict[row[outer_key]] = item
    return new_dict

构造三级字典

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
def build_level3_dict(source_file,outer_key,inner_key1,inner_key2):
    new_dict = {}
    with open(source_file, 'rb')as csv_file:
        reader = csv.reader(csv_file, delimiter=',')
        fieldnames = next(reader)
        inner_keyset=fieldnames
        inner_keyset.remove(outer_key)
        inner_keyset.remove(inner_key1)
        inner_keyset.remove(inner_key2)
        csv_file.seek(0)
        data = csv.DictReader(csv_file, delimiter=",")
        for row in data:
            item = new_dict.get(row[outer_key], dict())
            sub_item = item.get(row[inner_key1], dict())
            sub_item[row[inner_key2]] = {k: row[k] for k in inner_keyset}
            item[row[inner_key1]] = sub_item
            new_dict[row[outer_key]] = item
    return new_dict
# build specific nested dict from csv files
# a dict like {outer_key:{inner_key1:{inner_key2:inner_value}}}
# the params are extract from the csv column name as you like
def build_level3_dict2(source_file,outer_key,inner_key1,inner_key2,inner_value):
    new_dict = {}
    with open(source_file, 'rb')as csv_file:
        data = csv.DictReader(csv_file, delimiter=",")
        for row in data:
            item = new_dict.get(row[outer_key], dict())
            sub_item = item.get(row[inner_key1], dict())
            sub_item[row[inner_key2]] = row[inner_value]
            item[row[inner_key1]] = sub_item
            new_dict[row[outer_key]] = item
    return new_dict

字典转换为csv文件

1
2
3
4
5
6
7
8
9
10
11
12
def dict2csv(dict,file):
    with open(file,'wb') as f:
        w=csv.writer(f)
        # write each key/value pair on a separate row
        w.writerows(dict.items())
# second method
def dict2csv(dict,file):
    with open(file,'wb') as f:
        w=csv.writer(f)
        # write all keys on one row and all values on the next
        w.writerow(dict.keys())
        w.writerow(dict.values())

输出列表字典

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
import csv
import pandas as pd
from collections import OrderedDict
dct=OrderedDict()
dct['a']=[1,2,3,4]
dct['b']=[5,6,7,8]
dct['c']=[9,10,11,12]
header = dct.keys()
rows=pd.DataFrame(dct).to_dict('records')
with open('outTest.csv', 'wb') as f:
    f.write(','.join(header))
    f.write('\n')
    for data in rows:
        f.write(",".join(str(data[h]) for h in header))
        f.write('\n')

特殊的csv文件的读取

这个主要是针对那种分隔符比较特殊的csv文件，一般情形下csv文件统一用一种分隔符是关系不大的（向上述操作基本都是针对分隔符统一用,的情形），而下面这种第一行属性分隔符是,而后续值的分隔符均为;的读取时略有不同，一般可逐行转换为字典在进行操作，

1
2
3
4
5
6
7
8
9
def func(id_list,input_file,output_file):
    with open(input_file, 'rb') as f:
        # if the delimiter for header is ',' while ';' for rows
        reader = csv.reader(f, delimiter=',')
        fieldnames = next(reader)
        reader = csv.DictReader(f, fieldnames=fieldnames, delimiter=';')        
        rows = [row for row in reader if row['players.player_id'] in set(id_list)]
        # operation on rows...

json数据转换csv格式

转载

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
import csv
import json
import sys
def trans(path):
    jsonData=open(path+'.json')
    #csvfile = open(path+'.csv', 'w')#此处这样写会导致写出来的文件会有空行
    #csvfile = open(path+'.csv', 'wb')#python2下
    csvfile = open(path+'.csv', 'w',newline='')#python3下
    for line in jsonData:#获取属性列表
        dic=json.loads(line[0:-2])
        keys=dic.keys()
        break
    writer = csv.writer(csvfile)
    writer.writerow(keys)#将属性列表写入csv中
    for dic in jsonData:#读取json数据的每一行，将values数据一次一行的写入csv中
        dic=json.loads(dic[0:-2])
        writer.writerow(dic.values())
    jsonData.close()
    csvfile.close()
if __name__ == '__main__':
    path=str(sys.argv[1])#获取path参数
    print (path)
    trans(path)
# 如果需要对json文件中每个字典的key字段进行修改
import csv
import json
import sys
def trans(path):
    jsonData=open(path+'.json')
    #csvfile = open(path+'.csv', 'w')#此处这样写会导致写出来的文件会有空行
    #csvfile = open(path+'.csv', 'wb')#python2下
    csvfile = open(path+'.csv', 'w',newline='')#python3下
    keys=['id','name','category','price','count','type','address','link','x','y']
    writer = csv.writer(csvfile)
    writer.writerow(keys)
    i=1
    for dic in jsonData:
        dic=json.loads(dic[0:-2])
        x=dic['coordinates'][0]
        y=dic['coordinates'][1]
        writer.writerow([str(i),dic['name'],dic['category'],dic['price'],dic['count'],dic['type'],dic['address'],dic['link'],x,y])
        i+=1
    jsonData.close()
    csvfile.close()
if __name__ == '__main__':
    path=str(sys.argv[1])
    print (path)
    trans(path)