Python：将CSV格式转化为字典

一、场景一，每行数据带表头

现在有如下数据：

student_Id,score,rating,class_id
1,60,4,1
1,67,1.5,2
2,45,4,1
2,47,4,2
2,50,4,3
3,100,4,1
3,90,3,2
3,61,4,3
3,85,3,4

现在需求让每一行的数据都带有表头字段信息，代码如下：

import json
import pprint
from csv import DictReader
import os

def csv_to_dict(filename):
    try:
        with open('student.csv', 'r') as read_obj:
            dict_reader = DictReader(read_obj)
            list_of_dict = list(dict_reader)
            result = json.dumps(list_of_dict, indent=2)
        return result
    except IOError as err:
        print("I/O error({0})".format(err))

if __name__ == "__main__":
    filename = os.path.join(os.getcwd(), 'student.csv')
    result = csv_to_dict(filename)
    print(result)

结果：

D:\Users\Administrator\Anaconda3\python.exe F:/testproject/appiumdedao/test04.py
[
  {
    "student_Id": "1",
    "score": "60",
    "rating": "4",
    "class_id": "1"
  },
  {
    "student_Id": "1",
    "score": "67",
    "rating": "1.5",
    "class_id": "2"
  },
  {
    "student_Id": "2",
    "score": "45",
    "rating": "4",
    "class_id": "1"
  },
  {
    "student_Id": "2",
    "score": "47",
    "rating": "4",
    "class_id": "2"
  },
  {
    "student_Id": "2",
    "score": "50",
    "rating": "4",
    "class_id": "3"
  },
  {
    "student_Id": "3",
    "score": "100",
    "rating": "4",
    "class_id": "1"
  },
  {
    "student_Id": "3",
    "score": "90",
    "rating": "3",
    "class_id": "2"
  },
  {
    "student_Id": "3",
    "score": "61",
    "rating": "4",
    "class_id": "3"
  },
  {
    "student_Id": "3",
    "score": "85",
    "rating": "3",
    "class_id": "4"
  }
]

二、场景二，输出行号及表头

现在有如下数据：

student_Id,score,rating,class_id
1,60,4,1
1,67,1.5,2
2,45,4,1
2,47,4,2
2,50,4,3
3,100,4,1
3,90,3,2
3,61,4,3
3,85,3,4

显示每行行号，并带表头信息，代码如下：

import json
import os
import pprint


def csv_to_dict(filename):
    try:
        with open(filename, 'r') as file:
            header, *lines = file.readlines()  # 读取文件数据（包含第一行列名）
            header = header.split(",")  # 第一行列名
            header = [i.strip() for i in header]  # 格式化
            lines = [i.strip() for i in lines]
            result = {}
            for counter, line in enumerate(lines):
                line_dict = {}
                for idx, item in enumerate(line.split(",")):
                    line_dict[header[idx]] = item
                result[str(counter)] = line_dict
            return result
    except IOError as err:
        print("I/O error({0})".format(err))


if __name__ == "__main__":
    filename = os.path.join(os.getcwd(), 'student.csv')
    result = csv_to_dict(filename)
    # pprint.pprint(result)
    print(json.dumps(result,indent=2))

结果：

{
  "0": {
    "student_Id": "1",
    "score": "60",
    "rating": "4",
    "class_id": "1"
  },
  "1": {
    "student_Id": "1",
    "score": "67",
    "rating": "1.5",
    "class_id": "2"
  },
  "2": {
    "student_Id": "2",
    "score": "45",
    "rating": "4",
    "class_id": "1"
  },
  "3": {
    "student_Id": "2",
    "score": "47",
    "rating": "4",
    "class_id": "2"
  },
  "4": {
    "student_Id": "2",
    "score": "50",
    "rating": "4",
    "class_id": "3"
  },
  "5": {
    "student_Id": "3",
    "score": "100",
    "rating": "4",
    "class_id": "1"
  },
  "6": {
    "student_Id": "3",
    "score": "90",
    "rating": "3",
    "class_id": "2"
  },
  "7": {
    "student_Id": "3",
    "score": "61",
    "rating": "4",
    "class_id": "3"
  },
  "8": {
    "student_Id": "3",
    "score": "85",
    "rating": "3",
    "class_id": "4"
  }
}

三、场景三，按某列表头规类

现在有如下数据：

userId,movieId,rating
1,16,4
1,24,1.5
2,32,4
2,47,4
2,50,4
3,110,4
3,150,3
3,161,4
3,165,3

现在需求获得每个用户下，观看的电影及其评分，代码如下：

import csv

class AutoVivification(dict):
    def __getitem__(self, item):
        try:
            return dict.__getitem__(self, item)
        except KeyError:
            value = self[item] = type(self)()
            return value

def main():
    d = AutoVivification()
    filename = 'student.csv'
    with open(filename, 'r') as f:
        reader = csv.reader(f, delimiter=',')
        next(reader)
        for row in reader:
            d[row[0]][row[1]] = row[2]

    print(d)


if __name__ == '__main__':
    main()

结果：

{'1': {'16': '4', '24': '1.5'}, '2': {'32': '4', '47': '4', '50': '4'}, '3': {'110': '4', '150': '3', '161': '4', '165': '3'}}

四、场景四，按列规类

现在有如下数据：

student_Id,score,rating,class_id
1,60,4,1
1,67,1.5,2
2,45,4,1
2,47,4,2
2,50,4,3
3,100,4,1
3,90,3,2
3,61,4,3
3,85,3,4

现在需求获取分数列下的数据，代码如下：

import json
import pprint
import pandas as pd

data = pd.read_csv("student.csv")
# data_dict = {col: list(data[col]) for col in data.columns}

#与字典生成式功能一致
data_dict = {}
for col in data.columns:
    data_dict[col] = list(data[col])

# print(json.dumps(data_dict))
pprint.pprint(data_dict)

结果：

{'class_id': [1, 2, 1, 2, 3, 1, 2, 3, 4],
 'rating': [4.0, 1.5, 4.0, 4.0, 4.0, 4.0, 3.0, 4.0, 3.0],
 'score': [60, 67, 45, 47, 50, 100, 90, 61, 85],
 'student_Id': [1, 1, 2, 2, 2, 3, 3, 3, 3]}