python处理csv_to_excel

提取各csv文件不同频率的S参数tx

import os.path
import pandas as pd
import openpyxl
import glob
from os.path import basename
path1 = r'E:\PythonDemo\4T4R_DATA\4t4r\tx\ps\*.csv'
path2 = r'E:\PythonDemo\4T4R_DATA\4t4r\tx\tx_ref0.csv'
name_text = glob.glob(path2)+glob.glob(path1)
num_csv = len(name_text)
# print(name_text)
# print(num_csv)
workbook = openpyxl.Workbook()
# df1 = pd.DataFrame()
with pd.ExcelWriter('TXdata.xlsx') as writer:
    for i in range(0, num_csv):
        df = pd.read_csv(name_text[i], skiprows=[0, 1, 2, 3, 4, 5])  # 读出来的是11col
        df1 = pd.read_csv(name_text[i], skiprows=212)
        name = basename(name_text[i])
        name = os.path.splitext(name)[0]   # 分割，不带后缀名
        ch1_Freq = df.loc[0:200, 'Freq(Hz)']
        # print(len(ch1_Freq))
        Freq = {}
        for j in range(len(ch1_Freq)):
            ch1_freq = int(ch1_Freq[j]) / int(1000000000)
            s = pd.Series(ch1_freq)
            # print(list[s])
            Freq[j] = s
            print(type(Freq))     #dict
        #print(Freq.values())
        FreqGHz = pd.concat(list(Freq.values()), ignore_index=True)
        #print(FreqGHz)
        ch1_freqG_dic = {'Freq(GHz)': FreqGHz}
        ch1_freqGHz = pd.DataFrame(ch1_freqG_dic)
        print(ch1_freqG_dic)
        print(ch1_freqGHz)
        for k in df.columns[1:]:   # 去掉FREQ（HZ）命名的sheet
            ch1_freqGHz.to_excel(writer, sheet_name="ch1_" + k, startcol=0, index=False)
        for m in df1.columns[1:]:
            ch1_freqGHz.to_excel(writer, sheet_name="ch2_" + m, startcol=0, index=False)
        ch1_S11DB = df.loc[0:200, 'S11(DB)']  # 取2列的方法 s11DB = df.loc[0:200, ['Freq(Hz)', 'S11(DB)']]
        # print(ch1_S11DB)   #series
        ch1_S12DB = df.loc[0:200, 'S12(DB)']
        ch1_S12DEG = df.loc[0:200, 'S12(DEG)']
        ch1_S22DB = df.loc[0:200, 'S22(DB)']
        ch1_S21DB = df.loc[0:200, 'S21(DB)']
        ch1_S21DEG = df.loc[0:200, 'S21(DEG)']
        ch2_P1DB = df1.loc[0:200, 'CompOut21(DB)']
        ch1_s11DB_dic = {name: ch1_S11DB}    # 赋值
        # print(ch1_s11DB_dic)
        ch1_s12DB_dic = {name: ch1_S12DB}     # dict字典type
        ch1_s12DEG_dic = {name: ch1_S12DEG}
        ch1_s22DB_dic = {name: ch1_S22DB}
        ch1_s21DB_dic = {name: ch1_S21DB}
        ch1_s21DEG_dic = {name: ch1_S21DEG}
        ch2_p1DB_dic = {name: ch2_P1DB}

        ch1_s11DB = pd.DataFrame(ch1_s11DB_dic)    # ch1_s11DB = pd.DataFrame({name: ch1_S11DB})
        ch1_s12DB = pd.DataFrame(ch1_s12DB_dic)
        ch1_s12DEG = pd.DataFrame(ch1_s12DEG_dic)
        ch1_s22DB = pd.DataFrame(ch1_s22DB_dic)
        ch1_s21DB = pd.DataFrame(ch1_s21DB_dic)
        ch1_s21DEG = pd.DataFrame(ch1_s21DEG_dic)
        ch2_p1DB = pd.DataFrame(ch2_p1DB_dic)
        ch1_s11DB.to_excel(writer, sheet_name="ch1_S11(DB)", startcol=i+1, index=False)
        ch1_s12DB.to_excel(writer, sheet_name="ch1_S12(DB)", startcol=i+1, index=False)
        ch1_s22DB.to_excel(writer, sheet_name="ch1_S22(DB)", startcol=i+1, index=False)
        ch1_s21DB.to_excel(writer, sheet_name="ch1_S21(DB)", startcol=i+1, index=False)
        ch1_s12DEG.to_excel(writer, sheet_name="ch1_S12(DEG)", startcol=i+1, index=False)
        ch1_s21DEG.to_excel(writer, sheet_name="ch1_S21(DEG)", startcol=i+1, index=False)
        ch2_p1DB.to_excel(writer, sheet_name="ch2_CompOut21(DB)", startcol=i+1, index=False)
    workbook.save('TXdata.xlsx')

print("成功写入文件")

rx

import os.path
import pandas as pd
import openpyxl
import glob
from os.path import basename
path1 = "E:/PythonDemo/4T4R_DATA/4t4r/rx/att/*.csv"
path2 = r'E:\PythonDemo\4T4R_DATA\4t4r\rx\phasecal\*.csv'
path3 = r'E:\PythonDemo\4T4R_DATA\4t4r\rx\ps\*.csv'
name_text = glob.glob(path1)+glob.glob(path2)+glob.glob(path3)
num_csv = len(name_text)
print(num_csv)
workbook = openpyxl.Workbook()

with pd.ExcelWriter('RXdata.xlsx') as writer:
    for i in range(0, num_csv):
        df = pd.read_csv(name_text[i], skiprows=[0, 1, 2, 3, 4, 5])  # 读出来的是11col
        ch1_freq = df.loc[0:200, 'Freq(Hz)']
        name = basename(name_text[i])      # 输出最后文件名+后缀
        # print(name)
        name = os.path.splitext(name)[0]   # 分割，不带后缀名;[1]表示后缀名
        # print(name)
        ch1_S11DB = df.loc[0:200, 'S11(DB)']  # 取2列的方法 s11DB = df.loc[0:200, ['Freq(Hz)', 'S11(DB)']]
        ch1_S12DB = df.loc[0:200, 'S12(DB)']
        ch1_S12DEG = df.loc[0:200, 'S12(DEG)']
        ch1_S22DB = df.loc[0:200, 'S22(DB)']
        ch1_S21DB = df.loc[0:200, 'S21(DB)']
        ch1_S21DEG = df.loc[0:200, 'S12(DEG)']
        print(type(ch1_S21DEG))
        ch1_s11DB_dic = {name: ch1_S11DB}    # 赋值
        ch1_s12DB_dic = {name: ch1_S12DB}     # dict字典type
        ch1_s12DEG_dic = {name: ch1_S12DEG}
        ch1_s22DB_dic = {name: ch1_S22DB}
        ch1_s21DB_dic = {name: ch1_S21DB}
        ch1_s21DEG_dic = {name: ch1_S21DEG}
        ch1_s11DB = pd.DataFrame(ch1_s11DB_dic)
        ch1_s12DB = pd.DataFrame(ch1_s12DB_dic)
        ch1_s12DEG = pd.DataFrame(ch1_s12DEG_dic)
        ch1_s22DB = pd.DataFrame(ch1_s22DB_dic)
        ch1_s21DB = pd.DataFrame(ch1_s21DB_dic)
        ch1_s21DEG = pd.DataFrame(ch1_s21DEG_dic)
        print(ch1_s21DEG)
        ch1_s11DB.to_excel(writer, sheet_name="ch1_S11(DB)", startcol=i+1, index=False)      # to_excel必须是Dataframe格式
        ch1_s12DB.to_excel(writer, sheet_name="ch1_S12(DB)", startcol=i + 1, index=False)
        ch1_s22DB.to_excel(writer, sheet_name="ch1_S22(DB)", startcol=i + 1, index=False)
        ch1_s21DB.to_excel(writer, sheet_name="ch1_S21(DB)", startcol=i + 1, index=False)
        ch1_s12DEG.to_excel(writer, sheet_name="ch1_S12(DEG)", startcol=i + 1, index=False)
        ch1_s21DEG.to_excel(writer, sheet_name="ch1_S21(DEG)", startcol=i + 1, index=False)
        for k in df.columns[1:]:  # 去掉FREQ（HZ）命名的sheet
            ch1_freq.to_excel(writer, sheet_name="ch1_" + k, startcol=0, index=False)
    workbook.save('RXdata.xlsx')
print("成功写入文件")