如何将数据集转换为将某些值从行转换为列？

Question

我有个数据：

id   year      value
a1   2020-K       A
a1   2020-1       B
a1   2020-2       A
a1   2021-K       A
a1   2021-7       C
a1   2022-K       C
a1   2022-1       C
a1   2022-2       A
b1   2020-K       D
b1   2020-1       C
b1   2020-2       A
b1   2021-K       D
b1   2021-1       D
b1   2021-2       C
b1   2022-K       C
b1   2022-1       B
b1   2022-2       A

我希望将“年份”列中的每个唯一值转换为列，并在其中放置“值”。期望的结果是：

id    2020-K   2020-1  2020-2  2020-7  2021-K   2021-1  2021-2  2021-7  2022-K   2022-1  2022-2  2022-7
a1    A         B        A       NaN     A      NaN     NaN      C       C        C         A     NaN

b1    D         C        A       NaN     D      D        C       NaN     C        B         A     NaN

我怎么能这么做？这种转位有什么功能吗？

Answer 1

output = df.pivot(index='id', columns='year', values='value')
output
###
year 2020-1 2020-2 2020-K 2021-1 2021-2 2021-7 2021-K 2022-1 2022-2 2022-K
id                                                                        
a1        B      A      A    NaN    NaN      C      A      C      A      C
b1        C      A      D      D      C    NaN      D      B      A      C

如果您需要20 If K 20 If 0 20 If 1 20 If 2 20 If 7订单

df2 = pd.DataFrame(np.array(np.meshgrid(['a1','b1'],['2020','2021','2022'],['0','1','2','7'])).T.reshape(-1,3))
df2.sort_values(by=[1,0,2],inplace=True)
df2[3] = df2[1].str.cat(df2[2],sep='-')
df2.drop(columns=[1,2],inplace=True)
df2.reset_index(drop=True,inplace=True)
df2.rename(columns={0:'id',3:'year'},inplace=True)
df2['year'] = df2['year'].str.replace(r'0$','K', regex=True)
df3 = df2.merge(df,on=['id','year'], how='left').pivot(index='id', columns='year', values='value')
df3 = df3[df2['year'].unique()]
df3
###

year 2020-K 2020-1 2020-2 2020-7 2021-K 2021-1 2021-2 2021-7 2022-K 2022-1  2022-2 2022-7
id                                                                           
a1        A      B      A    NaN      A    NaN    NaN      C      C      C       A    NaN  
b1        D      C      A    NaN      D      D      C    NaN      C      B       A    NaN