计算的三列数据的平均值,pandas存储

数据展示

import pandas as pd
import numpy as np
import json
from collections import Counter

data = pd.read_excel("work/Test.xlsx")
rows = data.shape[0] #行数
col = data.shape[1]  #列数
user_index = list(data.columns.values)#行标签
print(user_index, "\n 一共有：{}行，{}列".format(rows, col))
data.head(-3)

部分数据如下所示:

代码处理

查找特定列，某一个值出现过多少次

def maturity_count(data_col, val):
    """
    data_col : 输入data的某一列 如要输入pid这一列,则输入data.pid
    val : 特定data_col这一列索要查询的value值

    return  : 返回指定列，某一个值(val)出现多少次
    """

    data_pid_dict = data_col.value_counts()
    count = data_pid_dict[val]

    return count

count = maturity_count(data.pid, 7)
print("查找pid这一列中7出现的次数为:{}次".format(count))
count = maturity_count(data.pid, 8)
print("查找pid这一列中8出现的次数为:{}次".format(count))

输出为:
    查找pid这一列中7出现的次数为:3
    查找pid这一列中8出现的次数为:7

计算x,y,z坐标的平均值

def pd_to_list(data, line):
    """
    data : pd读入的数据，含有行列索引
    line : 按行读入

    输出 : pd格式的每行转为列表形式[1624865249825.0, 316.0, 351.0, 32.0, 107.0, 4.0, 0.9925, 0.3903, 1.7187, 0.2296, 2970.0, -1.0]
    """

    list_x = data.iloc[[line]]
    list_x = list_x.values.tolist()
    list_x = list(_flatten(list_x))

    return list_x

def get_id_conf_xyz(data_col, val):
    """
    data_col : 输入data的某一列 如要输入pid这一列,则输入data.pid
    val : 特定data_col这一列索要查询的value值

    return : 返回每行查找confidencey与xyz坐标矩阵
    [[0.9925, 0.3903, 1.7187, 0.2296],
    [0.9925, 0.4297, 1.8475, 0.1514],
    [0.9925, 0.3814, 1.6054, 0.2501],
    [0.9925, 0.4798, 2.2024, 0.4195],
    [0.9925, 0.4789, 1.856, 0.1646]]
    """

    conf_xyzList = []
    
    pidList = data_col.to_numpy()
    index = np.where(np.array(pidList) == val)
    index = list(index[0]) #取出为pid的所有索引
    
    for line in index:
        coordinates = pd_to_list(data, line)[3:6]
        conf_xyzList.append(coordinates)
    
    return conf_xyzList

def conf_xyz_average(conf_xyzList):

    """
    conf_xyzList : get_id_conf_xyz函数的返回值 存储了id相同的x,y,z的列表 

    return : 返回相同id的平均空间坐标值
    """

    conf_xyz_np = np.array(conf_xyzList)
    _mean = conf_xyz_np.mean(axis=0)
    
    x = _mean[0]
    y = _mean[1]
    z = _mean[2]
    
    return x, y, z

conf_xyzList = get_id_conf_xyz(data.pid, 8)
avg_x, avg_y, avg_z = conf_xyz_average(conf_xyzList)
print("x,y,z的数值分别为 :  \n ", conf_xyzList)
print("avg_x, avg_y, avg_z的平均值分别为 : ", avg_x, avg_y, avg_z)

输出为:
	    x,y,z的数值分别为 :  
  					[[0.4524, 2.0891, 0.2752], 
  					[0.4704, 2.1208, 0.1704], 
  					[0.4522, 1.9888, 0.3099],
  					[0.5596, 2.1749, 0.1986], 
  					[0.4374, 2.0295, 0.3439],
  					[0.4973, 1.9068, 0.2064], 
  					[0.4909, 2.0664, 0.2256]]
  					
avg_x, avg_y, avg_z的平均值分别为 :  0.4800285714285714 2.053757142857143 0.24714285714285714