Python學習筆記：實驗設計組合(一)

 本文的起源來自於iThelp中網友的提問：

python 資料處理

程式碼是我自已寫的。

import pandas as pd
import numpy as np
df1 = pd.read_excel('實驗設計試解.xlsx', sheet_name = '實驗設計')
df2 = pd.read_excel('實驗設計試解.xlsx', sheet_name = '水準表')

df = pd.DataFrame(np.full((6, 7), np.nan))
df.columns = list(df1.columns)

print('實驗設計組合：')
df1

實驗設計組合：

Out[1]:

	a	b	c	d	e	f	g
0	0	2	1	0	1	2	2
1	1	1	2	1	0	1	0
2	2	0	0	2	2	0	1
3	0	2	1	2	0	1	2
4	1	1	2	0	2	0	1
5	2	0	0	1	1	2	0

In [2]:

print('水準表：')
df2

水準表：

Out[2]:

	水準	a	b	c	d	e	f	g
0	0	24	1	32	Sigmoid	SGD	64	0.01
1	1	72	3	64	Tanh	RMSprop	128	0.05
2	2	144	6	128	ReLU	Adam	256	0.10

In [3]:

print('預期結果：')
df

預期結果：

Out[3]:

	a	b	c	d	e	f	g
0	NaN	NaN	NaN	NaN	NaN	NaN	NaN
1	NaN	NaN	NaN	NaN	NaN	NaN	NaN
2	NaN	NaN	NaN	NaN	NaN	NaN	NaN
3	NaN	NaN	NaN	NaN	NaN	NaN	NaN
4	NaN	NaN	NaN	NaN	NaN	NaN	NaN
5	NaN	NaN	NaN	NaN	NaN	NaN	NaN

In [4]:

print('這種寫法會有語法上的問題：')
for i in df.columns:
    for j in df.index:
        df[i][j] = df2[i][df1[i][j]]
df

這種寫法會有語法上的問題：

c:\users\superuser\miniconda3\lib\site-packages\ipykernel_launcher.py:4: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  after removing the cwd from sys.path.
c:\users\superuser\miniconda3\lib\site-packages\pandas\core\indexing.py:205: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  self._setitem_with_indexer(indexer, value)

Out[4]:

	a	b	c	d	e	f	g
0	24.0	6.0	64.0	Sigmoid	RMSprop	256.0	0.10
1	72.0	3.0	128.0	Tanh	SGD	128.0	0.01
2	144.0	1.0	32.0	ReLU	Adam	64.0	0.05
3	24.0	6.0	64.0	ReLU	SGD	128.0	0.10
4	72.0	3.0	128.0	Sigmoid	Adam	64.0	0.05
5	144.0	1.0	32.0	Tanh	RMSprop	256.0	0.01

In [5]:

print('另一種更有效率的寫法：')
df = pd.DataFrame(np.full((6, 7), np.nan))
df.columns = list(df1.columns)

for x in df.columns:
  for y in df2.index:
    df[x] = np.where(df1[x] == y, df2[x][y], df[x])
df

另一種更有效率的寫法：

Out[5]:

	a	b	c	d	e	f	g
0	24.0	6.0	64.0	Sigmoid	RMSprop	256.0	0.10
1	72.0	3.0	128.0	Tanh	SGD	128.0	0.01
2	144.0	1.0	32.0	ReLU	Adam	64.0	0.05
3	24.0	6.0	64.0	ReLU	SGD	128.0	0.10
4	72.0	3.0	128.0	Sigmoid	Adam	64.0	0.05
5	144.0	1.0	32.0	Tanh	RMSprop	256.0	0.01

In [6]:

print('改良自第一種，修正語法：')
df = df1

for x in df.columns:
  for y in df2.index:
    df.loc[df[x] == y, x] = df2[x][y]
df

改良自第一種，修正語法：

Out[6]:

	a	b	c	d	e	f	g
0	24	6	64	Sigmoid	RMSprop	256	0.10
1	72	3	128	Tanh	SGD	128	0.01
2	144	3	32	ReLU	Adam	64	0.05
3	24	6	64	ReLU	SGD	128	0.10
4	72	3	128	Sigmoid	Adam	64	0.05
5	144	3	32	Tanh	RMSprop	256	0.01