[python] 複雑なエクセルデータをpandasで処理する。

はじめに

pandasはデータ処理の時によく用いられるが、測定機械が古くて得られるデータが古い形式のままである場合がある。
私の会社でも測定機械が20年以上と古いものが多く、データ処理には一工夫が必要である。
複雑そうに見えるデータを簡単にまとめることを目標とする。

環境

python 3.7.8
window 10

データ

エクセルは写真のような扱いにくい配置でデータが書き込みされる
必要なデータはMA, MB, MC, MD, MEの下の行の値
それ以外は不必要であるが、データの数や順番は必要な情報である。
今回は５個のサンプルに対するデータがあるが、その数に決まりは存在しない。
もし測定サンプルが6個なら、それぞれのデータは1行ずつ、下にずれてしまう

条件

• エクセルのファイルによってデータ数の違いが存在する。
• データの数によって値のセルの配置が変動する。
• 必要な情報を集めて行と列を反転させる。

Code

データを読み込む

このデータは行が多いので、とりあえず２０行まで読み取る

import pandas as pd

file_excel = './excel.xlsx'
df = pd.read_excel(file_excel, header=None)
df = df[0:20]
print(df)

         0    1    2    3    4    5     6
0       NO  NaN  NaN  NaN  NaN  NaN   NaN
1        1  NaN  NaN  NaN  NaN  NaN   NaN
2        2  NaN  NaN  NaN  NaN  NaN   NaN
3        3  NaN  NaN  NaN  NaN  NaN   NaN
4        4  NaN  NaN  NaN  NaN  NaN   NaN
5        5  NaN  NaN  NaN  NaN  NaN   NaN
6   VALUES  NaN  NaN  NaN  NaN  NaN   NaN
7      NaN  NaN    A    B    C    D     E
8      NaN  NaN  NaN  NaN  NaN  NaN   NaN
9      NaN  NaN   MA   MB   MC   MD    ME
10       1    1  1.1  3.4  5.5    7    10
11     NaN  NaN   MA   MB   MC   MD    ME
12       2    2  1.2  3.2    5  7.5  10.2
13     NaN  NaN   MA   MB   MC   MD    ME
14       3    3  1.1  3.2  5.2  7.3  10.5
15     NaN  NaN   MA   MB   MC   MD    ME
16       4    4  1.4  3.2  5.1  7.1  10.8
17     NaN  NaN   MA   MB   MC   MD    ME
18       5    5  1.1  3.1  5.1  7.1    11
19     NaN  NaN  NaN  NaN  NaN  NaN   NaN

規則性を見つける

エクセルの中で１回しか登場しないキーワードを見つけて基準とする。
今回はA7のVALUESが基準の行として良いと思われる。
またサンプルの数はVALUESのすぐ上のエクセルから読み取ることができる。

row_standard = 0
samples = 0

for i, value in enumerate(df[0]):
    print(f'row:{i}, value: {value}')
    if value == 'VALUES':
        row_standard = i
        samples = df[0][i-1]
        break
    
print(f'init_row is {row_standard}')
print(f'the number of samples is {samples}')

row:0, value: NO
row:1, value: 1
row:2, value: 2
row:3, value: 3
row:4, value: 4
row:5, value: 5
row:6, value: VALUES
init_row is 6
the number of samples is 5

基準となる行が6であり、サンプル数の情報も得ることができた。

データ範囲を絞る

データが始まるのは基準となる列から４つ下にあるのでrow_init = row_standard + 4としておく

df_data = pd.DataFrame()
# 空のデータフレームをつくる

row_init = row_standard + 4
for i in range(samples):
    df_data = df_data.append(df.loc[row_init + 2*i])
# 空のデータに必要な行だけを追加する

df_data = df_data[[2,3,4,5,6]]
# 列の範囲を絞る
print(df_data)

      2    3    4    5     6
10  1.1  3.4  5.5  7.0  10.0
12  1.2  3.2  5.0  7.5  10.2
14  1.1  3.2  5.2  7.3  10.5
16  1.4  3.2  5.1  7.1  10.8
18  1.1  3.1  5.1  7.1  11.0

これでかなりデータらしくなった

行と列を反転させて行列の番号を整える

df_data = df_data.transpose()
# 行列を反転させる
df_data.reset_index(inplace=True, drop=True)
# 行の番号を初期化する
df_data = df_data.T.reset_index(drop=True).T
# 列の番号を初期化する
print(df_data)

      0     1     2     3     4
0   1.1   1.2   1.1   1.4   1.1
1   3.4   3.2   3.2   3.2   3.1
2   5.5   5.0   5.2   5.1   5.1
3   7.0   7.5   7.3   7.1   7.1
4  10.0  10.2  10.5  10.8  11.0

必要な情報を書き加える

unit = ['cm', 'm', 'min', 'kg', 'g']
method = ['MA', 'MB', 'MC', 'MD', 'ME']

df_data.insert(0, 'unit', unit)
df_data.insert(0, 'method', method)
print(df_data)

  method unit     0     1     2     3     4
0     MA   cm   1.1   1.2   1.1   1.4   1.1
1     MB    m   3.4   3.2   3.2   3.2   3.1
2     MC  min   5.5   5.0   5.2   5.1   5.1
3     MD   kg   7.0   7.5   7.3   7.1   7.1
4     ME    g  10.0  10.2  10.5  10.8  11.0

必要な情報として値の単位unitや手法methodなどを書き加えた
ここでは必要な情報を直接書いたが、場合に応じてはエクセルデータからもってくることもできる

データをエクセルに書きこむ

file_save = './data.xlsx'
df_save = pd.read_excel(file_save, index_col=0)
# 書き込み用のエクセルのデータフレームを取得する

df_input = pd.concat([df_save, df_data])
# ２つのデータフレームを１つにする
df_input.to_excel(file_save, index=True, header=True, startcol=0)
# 書き込む
print('done')

まとめ

プロセスとしては以下のようである
① データを読み込む
② 規則性を見つける
③ 範囲を絞る
④ 必要なデータを書き加える
⑤ エクセルに書きこむ