python之使用 "long-data"数据格式合并数据透视表_编程设计

更新!

请注意将 df 和数据透视表中的年份更改为 int(在取消堆叠之后)。这给我带来了一些麻烦:)

值的数据:

d = {'ID':[1,1,1,2,2,2],'Date':['01-01-2013','01-02-2013','01-03-2013','01- 
01-2008','01-02-2008','01-03-2008'],'CUSIP': 
['X1','X1','X1','X2','X2','X2'],'X':['bla','bla','bla','bla','bla','bla']} 
df = pd.DataFrame(data=d)

我有一个数据框:

   Identifier CUSIP    X       Date 
0           1    X1  bla 2013-01-01 
1           1    X1  bla 2013-01-02 
2           1    X1  bla 2013-01-03 
3           2    X2  bla 2008-01-01 
4           2    X2  bla 2008-01-02 
5           2    X2  bla 2008-01-03

还有一个数据透视表:

       2008  2009  2010  2011  2012  2013 
CUSIP                                     
X1        1     1     1     1     1     1 
X2        2     2     2     2     2     2

我想实现这样的布局:

   Identifier CUSIP    X       Date Values 
0           1    X1  bla 2013-01-01 1 
1           1    X1  bla 2013-01-02 1 
2           1    X1  bla 2013-01-03 1 
3           2    X2  bla 2008-01-01 2 
4           2    X2  bla 2008-01-02 2 
5           2    X2  bla 2008-01-03 2

请您参考如下方法:

您可以使用 stack用 join reshape df2左连接:

#if necessary 
df['Date'] = pd.to_datetime(df['Date']) 
df['year'] = df.Date.dt.year 
 
df1 = df.join(df1.stack().rename('val'), on=['CUSIP', 'year']) 
print (df1) 
   Identifier       Date CUSIP    X  year  val 
0           1 2013-01-01    X1  bla  2013    1 
1           1 2013-01-02    X1  bla  2013    1 
2           1 2013-04-03    X1  bla  2013    1 
3           2 2008-01-01    X2  bla  2008    2 
4           2 2008-01-02    X2  bla  2008    2 
5           2 2008-03-03    X2  bla  2008    2

替代方案:

df1 = df.join(df1.stack().rename('val'), on=[df['CUSIP'], df['Date'].dt.year]) 
print (df1) 
   Identifier       Date CUSIP    X  val 
0           1 2013-01-01    X1  bla    1 
1           1 2013-01-02    X1  bla    1 
2           1 2013-04-03    X1  bla    1 
3           2 2008-01-01    X2  bla    2 
4           2 2008-01-02    X2  bla    2 
5           2 2008-03-03    X2  bla    2

我相信你可以使用 transform通过 year具有 size、mean、sum 等函数:

df['Date'] = pd.to_datetime(df['Date']) 
 
df['Vals'] = df.groupby(['CUSIP', df['Date'].dt.year])['X'].transform('size') 
print (df) 
   Identifier       Date CUSIP    X  Vals 
0           1 2013-01-01    X1  bla     5 
1           1 2013-01-02    X1  bla     5 
2           1 2013-04-03    X1  bla     5 
3           1 2013-04-04    X1  bla     5 
4           1 2013-05-05    X1  bla     5 
5           2 2008-01-01    X2  bla     4 
6           2 2008-01-02    X2  bla     4 
7           2 2008-03-03    X2  bla     4 
8           2 2008-03-04    X2  bla     4