根据列名在pandas中转换数据帧
根据列名在pandas中转换数据帧
提问于 2018-12-13 02:24:01
我有一个熊猫数据框架，看起来像这样：
employeeId cumbId firstName lastName emailAddress \
0 E123456 102939485 Andrew Hoover hoovera@xyz.com
1 E123457 675849302 Curt Austin austinc1@xyz.com
2 E123458 354852739 Celeste Riddick riddickc@xyz.com
3 E123459 937463528 Hazel Tooley tooleyh@xyz.com
employeeIdTypeCode cumbIDTypeCode entityCode sourceCode roleCode
0 001 002 AE AWB EMPLR
1 001 002 AE AWB EMPLR
2 001 002 AE AWB EMPLR
3 001 002 AE AWB EMPLR 我希望对于pandas数据帧中的每个ID和IDtypecode，它看起来像这样：
idvalue IDTypeCode firstName lastName emailAddress entityCode sourceCode roleCode CodeName
E123456 001 Andrew Hoover hoovera@xyz.com AE AWB EMPLR 1
102939485 002 Andrew Hoover hoovera@xyz.com AE AWB EMPLR 1这可以通过pandas数据帧中的一些函数来实现吗？我还希望它是动态的，基于数据帧中的in数量。
我所说的动态是这样的，如果有3个Ids，那么它应该是这样的：
idvalue IDTypeCode firstName lastName emailAddress entityCode sourceCode roleCode CodeName
A123456 001 Andrew Hoover hoovera@xyz.com AE AWB EMPLR 1
102939485 002 Andrew Hoover hoovera@xyz.com AE AWB EMPLR 1
M1000 003 Andrew Hoover hoovera@xyz.com AE AWB EMPLR 1谢谢!
回答 1
Stack Overflow用户
回答已采纳
发布于 2018-12-13 05:53:27
我想这就是你要找的..。您可以在拆分数据帧的各个部分后使用concat：
# create a new df without the id columns
df2 = df.loc[:, ~df.columns.isin(['employeeId','employeeIdTypeCode'])]
# rename columns to match the df columns names that they "match" to
df2 = df2.rename(columns={'cumbId':'employeeId', 'cumbIDTypeCode':'employeeIdTypeCode'})
# concat you dataframes
pd.concat([df,df2], sort=False).drop(columns=['cumbId','cumbIDTypeCode']).sort_values('firstName')
# rename columns here if you want更新
# sample df
employeeId cumbId otherId1 firstName lastName emailAddress \
0 E123456 102939485 5 Andrew Hoover hoovera@xyz.com
1 E123457 675849302 5 Curt Austin austinc1@xyz.com
2 E123458 354852739 5 Celeste Riddick riddickc@xyz.com
3 E123459 937463528 5 Hazel Tooley tooleyh@xyz.com
employeeIdTypeCode cumbIDTypeCode otherIdTypeCode1 entityCode sourceCode \
0 1 2 6 AE AWB
1 1 2 6 AE AWB
2 1 2 6 AE AWB
3 1 2 6 AE AWB
roleCode
0 EMPLR
1 EMPLR
2 EMPLR
3 EMPLR 一定要有一些规则：
规则1.总是有两个“匹配列”规则2.所有匹配的ids都是相邻的.规则3.你知道Ids组的数量(要添加的行)
def myFunc(df, num_id): # num_id is the number of id groups
# find all columns that contain the string id
id_col = df.loc[:, df.columns.str.lower().str.contains('id')].columns
# rename columns to id_0 and id_1
df = df.rename(columns=dict(zip(df.loc[:, df.columns.str.lower().str.contains('id')].columns,
['id_'+str(i) for i in range(int(len(id_col)/num_id)) for x in range(num_id)])))
# groupby columns and values.tolist
new = df.groupby(df.columns.values, axis=1).agg(lambda x: x.values.tolist())
data = []
# for-loop to explode the lists
for n in range(len(new.loc[:, new.columns.str.lower().str.contains('id')].columns)):
s = new.loc[:, new.columns.str.lower().str.contains('id')]
i = np.arange(len(new)).repeat(s.iloc[:,n].str.len())
data.append(new.iloc[i, :-1].assign(**{'id_'+str(n): np.concatenate(s.iloc[:,n].values)}))
# remove the list from all cells
data0 = data[0].applymap(lambda x: x[0] if isinstance(x, list) else x).drop_duplicates()
data1 = data[1].applymap(lambda x: x[0] if isinstance(x, list) else x).drop_duplicates()
# update dataframes
data0.update(data1[['id_1']])
return data0
myFunc(df,3)
emailAddress entityCode firstName id_0 id_1 lastName roleCode
0 hoovera@xyz.com AE Andrew E123456 1 Hoover EMPLR
0 hoovera@xyz.com AE Andrew 102939485 2 Hoover EMPLR
0 hoovera@xyz.com AE Andrew 5 6 Hoover EMPLR
1 austinc1@xyz.com AE Curt E123457 1 Austin EMPLR
1 austinc1@xyz.com AE Curt 675849302 2 Austin EMPLR
1 austinc1@xyz.com AE Curt 5 6 Austin EMPLR
2 riddickc@xyz.com AE Celeste E123458 1 Riddick EMPLR
2 riddickc@xyz.com AE Celeste 354852739 2 Riddick EMPLR
2 riddickc@xyz.com AE Celeste 5 6 Riddick EMPLR
3 tooleyh@xyz.com AE Hazel E123459 1 Tooley EMPLR
3 tooleyh@xyz.com AE Hazel 937463528 2 Tooley EMPLR
3 tooleyh@xyz.com AE Hazel 5 6 Tooley EMPLR页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/53749155
复制相关文章
相似问题