时间戳

首页 > 解决方案 > Pandas：组合两个数据框并动态扩展行

python - Pandas：组合两个数据框并动态扩展行

问题描述

我有以下数据框设置

一种

[ClaimantId], [ClaimId], [LenderId], [IsWorked] 1 1 1 1 1 2 4 0 1 3 3 1 2 6 1 1

乙

[ClaimantId], [Forename], [Surname]
1             Bruce       Wayne
2             Peter       Parker

我想要的输出是

[ClaimantId], [Forename], [Surname], [C1], [C2], [C3], [L1], [L2], [L3], [W1], [W2], [W3]
1             Bruce       Wayne      1      2     3    1     4     3      1    0     1
2             Peter       Parker     6      Nan   Nan  1     Nan   Nan    1    Nan   Nan

我不确定我可以应用什么，C/L/W 列的数量上限为 20，并且永远不会超过。

我真的很感激任何帮助。

谢谢，

标签： pythonpandasdataframe

解决方案

采用：

d = {'ClaimId':'C', 'LenderId':'L','IsWorked':'W'}
df = (A.rename(columns=d)
       .set_index(['ClaimantId',A.groupby('ClaimantId').cumcount()])
       .unstack())
df.columns = [f'{i}{j+1}' for i, j in df.columns]
print (df)
               C1   C2   C3   L1   L2   L3   W1   W2   W3
ClaimantId                                             
1             1.0  2.0  3.0  1.0  4.0  3.0  1.0  0.0  1.0
2             6.0  NaN  NaN  1.0  NaN  NaN  1.0  NaN  NaN

df1 = B.join(df, on='ClaimantId')
print (df1)
    ClaimantId    Forename   Surname   C1   C2   C3   L1   L2   L3   W1   W2  \
0             1      Bruce     Wayne  1.0  2.0  3.0  1.0  4.0  3.0  1.0  0.0   
1             2      Peter    Parker  6.0  NaN  NaN  1.0  NaN  NaN  1.0  NaN   

    W3  
0  1.0  
1  NaN

说明：

  1. dict的第一rename列
  2. 然后由创建的set_index计数器Seriescumcount
  3. 重塑unstack
  4. list comprehension用f-strings展平 MultiIndex 列
  5. 最后join一秒DataFrame

编辑：

如果需要相同长度的所有列reindex由 newMultiIndex创建的range：

d = {'ClaimId':'C', 'LenderId':'L','IsWorked':'W'}
df = (A.rename(columns=d)
       .set_index(['ClaimantId',A.groupby('ClaimantId').cumcount()])
       .unstack())

mux = pd.MultiIndex.from_product([df.columns.get_level_values(0).unique(), range(5)])
df = df.reindex(columns=mux, fill_value=0)
df.columns = [f'{i}{j+1}' for i, j in df.columns]
print (df)
             C1   C2   C3  C4  C5   L1   L2   L3  L4  L5   W1   W2   W3  W4  \
ClaimantId                                                                    
1           1.0  2.0  3.0   0   0  1.0  4.0  3.0   0   0  1.0  0.0  1.0   0   
2           6.0  NaN  NaN   0   0  1.0  NaN  NaN   0   0  1.0  NaN  NaN   0   

            W5  
ClaimantId      
1            0  
2            0

推荐阅读