R, Python 분석과 프로그래밍의 친구 (by R Friend)

  (1) 텍스트 파일을 열어 각 Line별로 읽어들인 후 문자열 메소드를 이용해 파싱(Parsing)

       --> pandas DataFrame 만들기

import pandas as pd

import os

# set file path

cwd = os.getcwd()

file_path = os.path.join(cwd, 'color_range.txt')

# read 'color_range.txt' file and parsing it by id and value

df = pd.DataFrame() # blank DataFrame to store results

# open file

f = open(file_path)

# parsing text line by line using for loop statement

for line in f.readlines():

    id_list = []

    color_list = []

    bin_list = []

    # remove white space

    line = line.strip()

    # delete '"'

    line = line.replace('"', '')

    # get ID and VALUE from a line

    id = line[:3]

    val = line[4:]

    # make a separator with comma(',')

    val = val.replace(' a', ',a')

    val = val.replace(' b', ',b')

    # split a line using separator ','

    val_split = val.split(sep=',')

    # get a 'ID', 'COLOR', 'BIN_END' values and append it to list

    for j in range(len(val_split)):

        id_list.append(id)

        color_list.append(val_split[j][:1])

        bin_list.append(val_split[j][2:])

    # make a temp DataFrame, having ID, COLOR, BIN_END values per each line

    # note: if a line has only one value(ie. Scalar), then it will erase 'index error'  :-(

    df_tmp = pd.DataFrame({'id': id_list, 

                           'color_cd': color_list, 

                           'bin_end': bin_list}

                         )

    # combine df and df_tmp one by one

    df = pd.concat([df, df_tmp], axis=0, ignore_index=True)

# let's check df DataFrame

df

[Out]:

  (2) ID를 기준으로 그룹별로 값을 한칸식 내려서(Lag) 새로운 칼럼을 만들기

# lag 1 group by 'id' and fill missing value with '0'

df['bin_start'] = df.groupby('id')['bin_end'].shift(1).fillna(0)

[Out]:

# mapping color using color_cd

color_map = {'a': 'red', 

             'b': 'blue'}

df['color'] = df['color_cd'].map(lambda x: color_map.get(x, x))

df

[Out]:

# change the sequence of columns

df = df[['id', 'color_cd', 'color', 'bin_start', 'bin_end']]

df

[Out]:

# make a 'Bin Range' column with include '[' and exclude ')' sign

df['bin_range'] = df['bin_start'].apply(lambda x: '[' + str(x) + ',') + \

    df['bin_end'].apply(lambda x: str(x + ')'))

df

[Out]: