blf --> csv --> 리샘플 --> xlsx

시작하기 전에

• CAN 버스 데이터를 측정하여 blf 파일로 저장한다.
• TSMaster의 Log Converter를 이용하면 blf 파일을 csv 파일로 변환할 수 있다.
• csv 파일에는 타임스탬프 컬럼이 있다. 아래 그림의 A 컬럼이다. 모든 메시지들의 타임스탬프들이 있다. 빽빽하다.
• 각 신호가 열로 입력되어 있다. C 컬럼부터다. 듬성듬성하다. 신호의 타임스탬프는 정확히는 신호를 포함한 메시지의 타임스탬프이다.    

blf를 변환한 csv에는 빈 셀들이 많다. 메시지별로 타임스탬프가 조금씩 차이가 나기 때문이다.

• 빈 셀이 있으면 데이터 분석이 어렵다. 리샘플링을 하여 아래 그림과 같이 만들어야 분석이 편리하다.

리샘플을 한 후. 신호의 빈 칸들이 채워졌다.

• csv 파일을 리샘플링하여 xlsx 파일로 저장하는 방법을 설명한다.
• 이 방법은 TIO 측정 데이터 분석 - mat 파일을 데이터프레임으로 변환하기 (mdf2df) :: hsl's tsmaster 사용기 에서 설명한 방법과 개념 측면에서 동일하다. mat 파일 대신 csv 파일을 이용한다.

 

 

개요

• blf를 csv로 변환하기
• csv 파일 구조
  • 결측치
  • 대표값
• 신호 목록 파일
• resample_csv2xlsx.py: csv --> dataframe --> resample --> xlsx
• xlsx 파일 확인

 

 

blf를 csv로 변환하기

• 이 작업을 하기 전에 TSMaster 프로젝트에 dbc 파일을 등록해야 한다. blf에는 CAN 메시지들이 저장되어 있다. CAN 메시지는 신호 정의에 따라 동일한 데이터도 다르게 해석된다. 그래서 dbc 파일이 필요하다. dbc 파일을 TSMaster로 드래그&드롭하여 등록할 수 있다. 
• 메인 메뉴/ Analysis/ Log Converter를 클릭하여 Log Converter 창을 연다.

• Log Conveter 창에서 Source File의 파일 열기 버튼을 클릭하여 변환할 blf 파일을 선택한다. csv 버튼을 클릭하면 소스 파일과 동일한 이름의 csv 파일 이름이 Destination File에 표시된다. Convert 버튼을 클릭한다.

 

• 신호 선택창이 뜬다. Include All Signals를 체크하여 dbc에 정의된 모든 신호들을 csv 파일에 포함할 수 있다. dbc에 신호가 많은 경우, 사용하지 않을 신호들이 포함되어  파일 크기만 커지고 처리 속도만 늦어질 수도 있다. CAN, LIN 등 측정한 버스에 따라 해당 버튼을 클릭하여 신호들을 선택할 수 있다. CAN을 클릭한 경우 dbc 화면이 뜬다.  

• dbc 화면에서 필요한 신호들을 선택하면 된다. Ctrl 키 혹은 Shift 키를 누른 상태에서 마우스로 클릭하여 여러 신호들을 선택할 수 있다. 

• 선택된 신호들이 창에 표시된다. 이 텍스트를 복붙하여 파일로 저장했다가, 필요할 경우 이 창에 다시 복붙하는 방식으로 신호들을 선택할 수 있다.  

• OK 버튼을 클릭하면 변환이 시작된다. 변환이 종료되면 csv 파일이 저장된 디렉토리가 파일 탐색기에서 열린다.

 

 

csv 파일 구조

결측치

• 아래 그림은 csv 파일을 엑셀에서 연 모습이다. csv 파일은 기본적으로 표 형태이다. 
• 첫 컬럼의 이름은 Hardware Time으로 타임스탬프이다. 인터페이스 하드웨어에 각 메시지가 수신된 시각이다. 
• C 컬럼 이상의 컬럼들은 신호들이다. 신호 이름은 채널.메시지.신호 식으로 되어있다. 신호 이름은 CAN1.M0.S2 이런 식으로 표시된다.
  • CAN1: 채널
  • M0: 메시지 이름
  • S2: 시그널 이름
  • 1번 채널의 M0 메시지의 S2 신호를 의미한다.
• 아래 그림에서는 M0 메시지의 신호들만 표시가 되어있다. 그래서 모두 동일한 타임스탬프를 갖는다.

blf를 변환한 csv 파일을 엑셀에서 열면 그림처럼 보인다.

• 여러 메시지들을 함께 보면 csv 파일은 아래 그림처럼 보일 것이다.
• 예를 위해, 각메시지 별로 신호가 1개만 있다고 가정했다. 각 메시지별로 타임스탬프가 다르다. csv 파일에는 모든 타임스탬프들이 포함기 때문에 신호(열, 컬럼)별로 보면 빈 칸(결측히)들이 많다. 결측치들을 채워야 앞으로 할 리샘플링 후에 결측치가 안 생긴다. 
• 이번 메시지 전송 순간부터 다음 메시지 전송 순간까지 이번 메시지의 신호값을 가정하는 것이 타당하면 ffill (forward fill)을 할 수 있다. 이번 메시지 전송 순간까지만 이번 메시지의 신호값이 타당하고 그 직후부터 다음 메시지의 신호값을 가정하는 것이 타당하면 bfill (backward fill)을 할 수 있다. 보간(interpolation)을 할 수도 있다.   

csv에는 전체 메시지들의 타임스탬프들이 포함되기 때문에 신호별로 보면 결측치가 많다.

 

• 타임스탬프 사이의 간격을 보면 0.1msec에서 3.5msec이다. 히스토그램으로 보면 아래 그래프와 같다. 200 ~ 250usec 구간에 몰려있다. 500kbps로 통신한 CAN 데이터이다. 한 비트는 2usec이다. 100 ~ 125 비트 신호를 전송할 수 있는 시간이다. 대부분의 CAN 메시지 길이가 8 바이트이다. 데이터 부분이 64 비트, 나머지는 헤더, 트레일러, 남는 시간들을 채운 비트들이다.  

타임스탬프 간격 히스토그램

 

• 내차 베뉴에서 157.6초 동안 측정한 blf 파일의 크기는 20Mbyte이다.  blf에서 416개 신호들을 (신호가 더 많이 있을 텐데, 내가 갖고 있는 dbc에는 416개 신호만 정의되어 있다.) csv로 변환하였다. csv 파일의 크기는 193Mbyte이다. (거의 10배이다.) 393,434 행이다. 

대표값

• 결측치를 채운 후에 주기를 정해서 리샘플링을 한다고 가정하자. 모든 메시지들의 주기들을 구한 후 가장 짧은 주기를 리샘플링의 주기로 할 수 있다. 혹은 10msec나 100msec 같이 분석 목적에 따라 임의로 정할 수도 있다.
• 리샘플링 주기를 정했을 때, 샘플링 구간에 속하는 신호값들 중에 대표값을 정해야 한다. 구간의 시작값(first)이나 마지막(last)값을 대표값으로 할 수 있다. 평균(mean)으로 하거나 중간값(median)으로 할 수도 있다. 가중 평균 같은 연산을 할 수도 있다. 어쨌든 일관된 기준의 대표값이 필요하다.   

 

신호 목록 파일

• 아래 그림은 csv 파일의 첫 행을 복사하여 열로 붙여넣기 한 것이다. include 컬럼을 추가하였다. 1은 포함, 0은 미포함을 의미한다. 이를 xlsx 파일로 저장하였다. csv를 리샘플하여 xlsx로 변환할 때, 이 파일을 참조하도록 하려고 한다. 이 파일을 신호 목록 파일이라고 부르겠다.    

신호 목록 파일 구조

 

 

resample_csv2xlsx.py: csv --> dataframe --> resample --> xlsx

코드

resample_csv2xlsx_2.py

0.01MB

 

• 아래 기능을 하는 파이썬 프로그램을 만든다.
  • blf를 변환한 csv 파일을 리샘플하여 xlsx 파일로 저장한다.
  • 신호 목록 파일을 참조하여 xlsx에 포함할 신호들을 선택할 수 있도록 한다.
  • 결측치를 채우는 방법을 ffill과 bfill 중에서 선택할 수 있도록 한다.
  • 대표값을 선택하는 방법을 first, last, mean, median 중에서 선택할 수 있도록 한다. 
  • 사용자가 리샘플링 주기를 지정할 수 있도록 한다. 사용자가 지정하지 않는 경우, 가장 빠른 샘플링 주기로 리샘플한다.  

# resample_csv2xlsx.py
'''
- blf를 TSMaster를 이용하여 csv로 변환한 파일을 대상으로 한다.
- 이 csv 파일의 데이터를 forward fill 하고 resample 하고 dropna 해서 xlsx로 저장한다.
- xlsx에는 신호 목록 파일에서 선택한 컬럼들만 저장한다. 신호 목록 파일은 xlsx 형식이다.
'''


# import

import os                   # 운영체제 관련 모듈
from pathlib import Path    # 파일 경로를 다루기 위한 모듈
import pandas as pd         # 데이터 분석을 위한 모듈
import argparse             # 명령줄 인자 파싱을 위한 모듈
import logging              # 로깅 모듈


def get_signals_to_include(xlsx_signal_list):
    '''
    설정용 xlsx 파일을 읽어서 include가 1인 signal_name을 리스트로 반환한다.
    '''

    logging.info('Reading configuration from signals_to_include.xlsx...')

    # 설정용 xlsx 파일을 읽는다.
    df_signal_list = pd.read_excel(xlsx_signal_list, sheet_name='Sheet1', engine='openpyxl')
    df_signal_list.columns = ['signal_name', 'include']

    # include가 1인 signal_name을 리스트로 만든다.
    signals_to_include = df_signal_list[df_signal_list['include'] == 1]['signal_name'].tolist()

    # signals_to_include에 Raw Data가 있으면 제거한다.
    if 'Raw Data' in signals_to_include:
        signals_to_include.remove('Raw Data')

    # signals_to_include에 Global Time\Signal이 있으면 제거한다.
    if 'Global Time\\Signal' in signals_to_include:
        signals_to_include.remove('Global Time\\Signal')

    # signals_to_include에 Hardware Time이 없으면 맨 앞에 추가한다.
    if 'Hardware Time' not in signals_to_include:
        signals_to_include.insert(0, 'Hardware Time')

    return signals_to_include


def print_signals(signals, n_per_line=4):
    '''
    signals를 출력한다.
    n_per_line개씩 한 줄에 출력한다.
    '''

    logging.info(f"{len(signals)} signals to include")
    line = ""
    for i, signal in enumerate(signals, start=1):
        line += f'{i:>3}, {signal[:24]:<24} '
        if i % n_per_line == 0 and i != 0:
            logging.debug(line)
            line = ""
    if line:
        logging.debug(line)


def get_df_from_csv(csv, signals_to_include):
    '''
    데이터 csv 파일을 읽는다.
    '''    
    logging.info(f'Reading data from {csv}...')

    try:
        df = pd.read_csv(csv, encoding='utf-8')
    except Exception as e:
        logging.error(f"Error reading {csv}: {e}")
        return pd.DataFrame()  # 빈 DataFrame 반환

    # df에서 signals_to_include에 해당하는 열만 선택한다.
    df = df[signals_to_include]

    # Hardware Time 컬럼 이름을 ts로 변경한다.
    df.rename(columns={'Hardware Time': 'ts'}, inplace=True)

    return df


def get_tx_periods(df):
    '''
    각 컬럼별로 빈 칸이 아닌 행의 ts의 diff인 d_ts를 구한다.
    d_ts의 평균, 표준편차, 최대값, 최소값을 구한다.
    컬럼 이름, 평균, 표준편차, 최대값, 최소값을 출력한다.
    '''

    logging.info('Calculating transmission periods...')

    s1 = 'signal'
    s2 = 'mean'
    s3 = 'std'
    s4 = 'max'
    s5 = 'min'
    len_max_col_name = max([len(col) for col in df.columns if col != 'ts'])

    signals = []
    tx_periods = []
    logging.debug(f"{s1:{len_max_col_name}}: {s2:<7} {s3:<7} {s4:<7} {s5:<7}")
    for column in df.columns:
        if column != 'ts':
            d_ts_diff = df.loc[df[column].notna(), 'ts'].diff()
            signals.append(column)
            tx_periods.append(d_ts_diff.mean())
            logging.debug(f"{column:{len_max_col_name}}: {d_ts_diff.mean():.4f}, {d_ts_diff.std():.4f}, {d_ts_diff.max():.4f}, {d_ts_diff.min():.4f}")

    return tx_periods


if __name__ == "__main__":
    # 명령줄 인자 파싱
    parser = argparse.ArgumentParser(description="CSV resample to XLSX")
    parser.add_argument("-l", "--list", type=str, required=True, help="xlsx_signal_list의 파일 경로 (xlsx)")
    parser.add_argument("-i", "--input", type=str, required=True, help="데이터 csv 파일 경로 (csv)")
    parser.add_argument("-o", "--output", type=str, help="출력 xlsx 파일 경로 (지정하지 않으면 자동 생성)")
    parser.add_argument("-n", "--open", action="store_true", help="xlsx_out 파일 열기 (지정하지 않으면 열지 않음)")
    parser.add_argument("-f", "--fill", type=str, choices=['ffill', 'bfill'], default='bfill', help="결측치 처리 방법 (ffill: forward fill, bfill: backward fill, 기본값: bfill)")
    parser.add_argument("-r", "--repr", type=str, choices=['first', 'last', 'median', 'mean'], default='first', help="대표값 계산 방법 (first, last, median, interpolate, 기본값: first)")

    args = parser.parse_args()

    # 로깅 설정
    log_level = logging.DEBUG if args.verbose else logging.INFO
    logging.basicConfig(level=log_level, format='%(asctime)s %(levelname)s: %(message)s', datefmt='%Y-%m-%d %H:%M:%S')

    # xlsx_signal_list에서 signals_to_include를 읽는다.
    xlsx_signal_list = Path(args.list)
    signals_to_include = get_signals_to_include(xlsx_signal_list=xlsx_signal_list)
    print_signals(signals_to_include)

    # csv 파일을 읽어서 df를 만든다.
    csv = Path(args.input)
    df = get_df_from_csv(csv=csv, signals_to_include=signals_to_include)
    if df.empty:
        logging.error("DataFrame is empty. Exiting.")
        exit(1)

    # 각 컬럼별로 tx_period를 구한다. tx_period_min을 구한다.
    tx_periods = get_tx_periods(df)
    tx_period_min = round(min(tx_periods), 3)

    # 빈 칸을 이전 값으로 채운다. bfill = backward fill
    fill_method = args.fill
    logging.info(f'{fill_method.upper()} filling missing values...')
    if fill_method == 'ffill':
        df = df.ffill()
    elif fill_method == 'bfill':
        df = df.bfill()

    # resample 한다. period가 지정되지 않으면 tx_period_min을 사용한다.
    period = args.period if args.period else tx_period_min
    repr_method = args.repr
    logging.info(f'Resampling at {period:.3f} sec using {repr_method}...')
    df['ts_timedelta'] = pd.to_timedelta(df['ts'], unit='s')
    
    if repr_method == 'first':
        df = df.resample(f'{period * 1000:.0f}ms', on='ts_timedelta').first()
    elif repr_method == 'last':
        df = df.resample(f'{period * 1000:.0f}ms', on='ts_timedelta').last()
    elif repr_method == 'median':
        df = df.resample(f'{period * 1000:.0f}ms', on='ts_timedelta').median()
    elif repr_method == 'mean':
        df = df.resample(f'{period * 1000:.0f}ms', on='ts_timedelta').mean()

    # resample 과정에서 na가 생겼을 수 있다. na가 있는 행들을 삭제한다.
    logging.info('Dropping rows with missing values...')
    df = df.dropna()

    # 결과를 xlsx로 저장한다.
    xlsx_out = Path(args.output) if args.output else csv.with_suffix('.resampled.xlsx')
    logging.info(f'Saving to {xlsx_out}...')
    df.to_excel(xlsx_out, index=False)

    # xlsx_out 파일을 연다.
    if args.open:
        logging.info(f'Opening {xlsx_out}...')
        os.startfile(xlsx_out)

    logging.info('Done.')

 

사용법

• 아래 명령어로 프로그램을 실행한다.

resample_csv2xlsx_2.py [-h] -l LIST -i INPUT [-o OUTPUT] [-n] [-f {ffill,bfill}] [-r {first,last,median,mean}]

• -h, --help                                              show this help message and exit
• -l LIST, --list LIST                                xlsx_signal_list의 파일 경로 (xlsx)
• -i INPUT, --input INPUT                      데이터 csv 파일 경로 (csv)
• -o OUTPUT, --output OUTPUT           출력 xlsx 파일 경로 (지정하지 않으면 자동 생성)
• -n, --open                                            xlsx_out 파일 열기 (지정하지 않으면 열지 않음)
• -f {ffill,bfill}, --fill {ffill,bfill}                    결측치 처리 방법 (ffill: forward fill, bfill: backward fill, 기본값: bfill)
• -r {first,last,median,mean} --repr {first,last,median,mean}  대표값 계산 방법 (first, last, median, interpolate, 기본값: first)

        

설치

• resample_csv2xlsx_2.py는 아래 모듈들을 사용한다.
  • pandas, os, pathlib, argparse, logging
• os, pathlib, argparse, logging 모듈은 파이썬이 설치될 때 같이 설치된다.
• pandas는 아래 커맨드라인 명령으로 설치할 수 있다.

pip install -U pandas

 

• pandas가 openpyxl을 사용할 수 있다.

pip install -U openpyxl

  

 

 

xlsx 파일 확인

• 변환된 엑셀 파일은 '시작하기 전에' 부분의 그림처럼 빈 칸이 없이 데이터로 빼곡하게 차있다.
• 파일의 행은 15,546행이다. 393,434 행에서 1/20 이하로 줄었다.
• 전체 열을 선택한 것이 아니라 파일 크기를 그대로 비교할 수는 없지만, xlsx 파일 크기는 13Mbyte이다. csv 파일은 193Mbyte이다. 
• 파일 로딩 시간이 많이 단축되었다.
• 타임스탬프 사이의 간격은 10msec로 일정하다고 볼 수 있다. 

리샘플링 후 타임스탬프 간격 히스토그램

 

 

결론

• blf 파일을 변환한 csv 파일은 빈 칸(결측치)이 많아서 사용하기에 불편하다. 파일 크기도 커서 로딩에 시간이 많이 소요된다.
• 이 문제를 해결하고자, csv 파일에서 필요한 신호들만 선정하여 리샘플링하여 xlsx 파일로 변환하는 파이썬 코드를 개발하였다.
• 결측치를 채우는 방법을 ffill와 bfill 중에서 선택할 수 있도로 하였다. 대표값을 선택하는 방법을 first, last, mean, median 중에서 선택할 수 있도록 하였다.
• 최종적으로 변환된 xlsx 파일은 결측치가 없다. 측정 간격이 일정하다. 필요한 신호들만 있어서 파일 크기가 작아 로딩도 빠르다.

 

 

 

TIO 측정 데이터 분석 - mat 파일을 데이터프레임으로 변환하기 (mdf2df) :: hsl's tsmaster 사용기    

mat 파일을 데이터프레임으로 변환하고 feather 파일로 저장하기 :: hsl's tsmaster 사용기