Как очистить эти данные
Из этого:
+------+------+--------------------------+-----------------+
| code | type | name | final_component |
+------+------+--------------------------+-----------------+
| C001 | ACT | Exhaust Blower Drive | |
| C001 | AL | | |
| C001 | AL | | |
| C001 | SET | Exhaust Blower Drive | |
| C001 | AL | | |
| C001 | AL | | |
| C001 | AL | | |
| C002 | ACT | Spray Pump Motor 1 Pump | |
| C002 | SET | Spray Pump Motor 1 Pump | |
| C003 | ACT | Spray Pump Motor 2 Pump | |
| C003 | SET | Spray Pump Motor 2 Pump | |
| C004 | ACT | Spray Pump Motor 3 Pump | |
| C004 | SET | Spray Pump Motor 3 Pump | |
+------+------+--------------------------+-----------------+
Ожидается:
+------+------+--------------------------+--------------------------+
| code | type | name | final_component |
+------+------+--------------------------+--------------------------+
| C001 | ACT | Exhaust Blower Drive | Exhaust Blower Drive |
| C001 | AL | | Exhaust Blower Drive |
| C001 | AL | | Exhaust Blower Drive |
| C001 | SET | Exhaust Blower Drive | Exhaust Blower Drive |
| C001 | AL | | Exhaust Blower Drive |
| C001 | AL | | Exhaust Blower Drive |
| C001 | AL | | Exhaust Blower Drive |
| C002 | ACT | Spray Pump Motor 1 Pump | Spray Pump Motor 1 Pump |
| C002 | SET | Spray Pump Motor 1 Pump | Spray Pump Motor 1 Pump |
| C003 | ACT | Spray Pump Motor 2 Pump | Spray Pump Motor 2 Pump |
| C003 | SET | Spray Pump Motor 2 Pump | Spray Pump Motor 2 Pump |
| C004 | ACT | Spray Pump Motor 3 Pump | Spray Pump Motor 3 Pump |
| C004 | SET | Spray Pump Motor 3 Pump | Spray Pump Motor 3 Pump |
+------+------+--------------------------+--------------------------+
Мне нужно скопировать значение имени, тип которого 'SET', в final_component для всего того же кода, что и для C001, имя для типа 'SET' - Exhaust Blower Drive, я должен скопировать это в final_component для всех C001
for ind in dataframe.index:
if dataframe['final_component'][ind]!=None:
temp = dataframe['final_component'][ind]
temp_code = dataframe['code'][ind]
i = ind
while dataframe['code'][i] == temp_code:
dataframe['final_component'][ind] = temp
i+=1
я мог бы придумать это, но он застревает в цикле while
2 ответа
Решение 1.Когда данные сгруппированы по порядку
Если ваши данные в 'name'field уже имеет значения Null, тогда вы можете сделать что-нибудь простое, например, ffill(). Функция Pandas dataframe.ffill() используется для заполнения отсутствующего значения в кадре данных. "ffill" означает "заполнение вперед" и будет распространять последнее действительное наблюдение вперед. В этом случае он не принимает во внимание значения вcode. Если вы хотите это учитывать, посмотрите на решение 2.
import pandas as pd
import numpy as np
a = {'code':['C001']*7+['C002']*2+['C003']*2+['C004']*2,
'typ':['ACT','AL','AL','SET','AL','AL','AL','ACT','SET','ACT','SET','ACT','SET'],
'name':['Exhaust Blower Drive',None,None,'Exhaust Blower Drive',np.nan,np.nan,np.nan,
'Spray Pump Motor 1 Pump','Spray Pump Motor 1 Pump',
'Spray Pump Motor 2 Pump','Spray Pump Motor 2 Pump',
'Spray Pump Motor 3 Pump','Spray Pump Motor 3 Pump']}
df = pd.DataFrame(a)
#copy all the values from name to final_component' with ffill()
#it will fill the values where data does not exist
#this will work only if you think all values above are part of the same set
df['final_component'] = df['name'].ffill()
Решение 2.Когда данные должны основываться на другом значении столбца
Если вам необходимо заполнить на основе значения в коде, вы можете использовать решение ниже.
Вы можете выполнить поиск, а затем обновить значения. Попробуйте что-нибудь подобное.
import pandas as pd
import numpy as np
a = {'code':['C001']*7+['C002']*2+['C003']*2+['C004']*2,
'typ':['ACT','AL','AL','SET','AL','AL','AL','ACT','SET','ACT','SET','ACT','SET'],
'name':['Exhaust Blower Drive',np.nan,np.nan,'Exhaust Blower Drive',np.nan,np.nan,np.nan,
'Spray Pump Motor 1 Pump','Spray Pump Motor 1 Pump',
'Spray Pump Motor 2 Pump','Spray Pump Motor 2 Pump',
'Spray Pump Motor 3 Pump','Spray Pump Motor 3 Pump']}
df = pd.DataFrame(a)
#copy all the values from name to final_component' including nulls
df['final_component'] = df['name']
#create a sublist of items based on unique values in code
lookup = df[['code', 'final_component']].groupby('code').first()['final_component']
#identify all the null values that need to be replaced
noname=df['final_component'].isnull()
#replace all null values with correct value based on lookup
df['final_component'].loc[noname] = df.loc[noname].apply(lambda x: lookup[x['code']], axis=1)
print(df)
Результат будет выглядеть так:
code typ name final_component
0 C001 ACT Exhaust Blower Drive Exhaust Blower Drive
1 C001 AL NaN Exhaust Blower Drive
2 C001 AL NaN Exhaust Blower Drive
3 C001 SET Exhaust Blower Drive Exhaust Blower Drive
4 C001 AL NaN Exhaust Blower Drive
5 C001 AL NaN Exhaust Blower Drive
6 C001 AL NaN Exhaust Blower Drive
7 C002 ACT Spray Pump Motor 1 Pump Spray Pump Motor 1 Pump
8 C002 SET Spray Pump Motor 1 Pump Spray Pump Motor 1 Pump
9 C003 ACT Spray Pump Motor 2 Pump Spray Pump Motor 2 Pump
10 C003 SET Spray Pump Motor 2 Pump Spray Pump Motor 2 Pump
11 C004 ACT Spray Pump Motor 3 Pump Spray Pump Motor 3 Pump
12 C004 SET Spray Pump Motor 3 Pump Spray Pump Motor 3 Pump
Вот один из подходов. Сначала воссоздайте фрейм данных:
from io import StringIO
import pandas as pd
data = '''| code | type | name | final_component |
| C001 | ACT | Exhaust Blower Drive | |
| C001 | AL | | |
| C001 | AL | | |
| C001 | SET | Exhaust Blower Drive | |
| C001 | AL | | |
| C001 | AL | | |
| C001 | AL | | |
| C002 | ACT | Spray Pump Motor 1 Pump | |
| C002 | SET | Spray Pump Motor 1 Pump | |
| C003 | ACT | Spray Pump Motor 2 Pump | |
| C003 | SET | Spray Pump Motor 2 Pump | |
| C004 | ACT | Spray Pump Motor 3 Pump | |
| C004 | SET | Spray Pump Motor 3 Pump | |
'''
df = pd.read_csv(StringIO(data), sep='|',)
df = df.drop(columns=['Unnamed: 0', 'Unnamed: 5'])
Теперь удалите начальные и конечные пробелы:
# remove leading / trailing spaces
df.columns = [c.strip() for c in df.columns]
for col in df.columns:
if df[col].dtype == object:
df[col] = df[col].str.strip()
И заселить final_component:
# populate 'final component'
df['final_component'] = df['name']
Теперь замените пустые строки на None и использовать ffill()
# find final component that is empty string...
mask = df['final_component'] == ''
# ... and convert to None...
df.loc[mask, 'final_component'] = None
# ...so we can use ffill()
df['final_component'] = df['final_component'].ffill()
print(df)
code type name final_component
0 C001 ACT Exhaust Blower Drive Exhaust Blower Drive
1 C001 AL Exhaust Blower Drive
2 C001 AL Exhaust Blower Drive
3 C001 SET Exhaust Blower Drive Exhaust Blower Drive
4 C001 AL Exhaust Blower Drive
5 C001 AL Exhaust Blower Drive
6 C001 AL Exhaust Blower Drive
7 C002 ACT Spray Pump Motor 1 Pump Spray Pump Motor 1 Pump
8 C002 SET Spray Pump Motor 1 Pump Spray Pump Motor 1 Pump
9 C003 ACT Spray Pump Motor 2 Pump Spray Pump Motor 2 Pump
10 C003 SET Spray Pump Motor 2 Pump Spray Pump Motor 2 Pump
11 C004 ACT Spray Pump Motor 3 Pump Spray Pump Motor 3 Pump
12 C004 SET Spray Pump Motor 3 Pump Spray Pump Motor 3 Pump