在python中将序列重组为Dataframe矩阵
在python中将序列重组为Dataframe矩阵
提问于 2017-12-25 14:21:42
我有一个包含305个条目的系列，其中包含了Datatime索引。数据如下所示
1992-01-31 1.123077
1992-02-28 -2.174845
1992-03-31 -3.884848
1992-04-30 8.682919
1992-05-29 1.312976
1992-06-30 7.851080
1992-07-31 -3.192788
1992-08-31 -7.351976
1992-09-30 -6.782217
1992-10-30 -17.182738
1992-11-30 3.898782
1992-12-31 -26.190414
1993-01-29 2.233359
1993-02-26 6.709006
continues with monthly data till December 2017我想将数据重组为一个DataFrame，它有行的所有年份，列的月份，以及适当的需要填充的数据。
January February March etc >> December
2017 values values values values values
2016 values values values values values
2015 values values values values values
etc \\//
1992 values 我看了其他帖子，试着重新塑造和调整矩阵，但考虑到这是不均衡的系列，我不断地得到这个错误。
ValueError:新数组的总大小必须保持不变。
我真正想要做的是，如果矩阵是奇数形状的，那么为缺少的值插入NaN。因此，如果2017年没有11月份或12月份的数值，它们将是NaN。
如果有人能帮忙，请告诉我
回答 3
Stack Overflow用户
回答已采纳
发布于 2017-12-25 14:47:33
来源DF：
In [159]: df
Out[159]:
val
date
1992-01-31 1.123077
1992-02-28 -2.174845
1992-03-31 -3.884848
1992-04-30 8.682919
1992-05-29 1.312976
1992-06-30 7.851080
1992-07-31 -3.192788
1992-08-31 -7.351976
1992-09-30 -6.782217
1992-10-30 -17.182738
1992-11-30 3.898782
1992-12-31 -26.190414
1993-01-29 2.233359
1993-02-26 6.709006解决方案：
import calendar
In [158]: (df.assign(year=df.index.year, mon=df.index.month)
.pivot(index='year', columns='mon', values='val')
.rename(columns=dict(zip(range(13), calendar.month_name))))
Out[158]:
mon January February March April May June July August September October November December
year
1992 1.123077 -2.174845 -3.884848 8.682919 1.312976 7.85108 -3.192788 -7.351976 -6.782217 -17.182738 3.898782 -26.190414
1993 2.233359 6.709006 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN更新：或更好、更短的version from @COLDSPEED
In [164]: pd.pivot(df.index.year, df.index.month, df['val']) \
.rename(columns=calendar.month_name.__getitem__)
Out[164]:
date January February March April May June July August September October November December
date
1992 1.123077 -2.174845 -3.884848 8.682919 1.312976 7.85108 -3.192788 -7.351976 -6.782217 -17.182738 3.898782 -26.190414
1993 2.233359 6.709006 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaNStack Overflow用户
发布于 2017-12-25 14:57:15
s
1992-01-31 1.123077
1992-02-28 -2.174845
1992-03-31 -3.884848
1992-04-30 8.682919
1992-05-29 1.312976
1992-06-30 7.851080
1992-07-31 -3.192788
1992-08-31 -7.351976
1992-09-30 -6.782217
1992-10-30 -17.182738
1992-11-30 3.898782
1992-12-31 -26.190414
1993-01-29 2.233359
1993-02-26 6.709006
Name: 1, dtype: float64
type(s)
pandas.core.series.Series如果有必要，将索引转换为datetime -
s.index = pd.to_datetime(s.index, errors='coerce')
现在，使用pd.pivot -
x = pd.Series(s.index.strftime('%Y %B')).str.split()
y, m = x.str[0], x.str[1]
pd.pivot(y, m, s)
April August December February January July June \
1992 8.682919 -7.351976 -26.190414 -2.174845 1.123077 -3.192788 7.85108
1993 NaN NaN NaN 6.709006 2.233359 NaN NaN
March May November October September
1992 -3.884848 1.312976 3.898782 -17.182738 -6.782217
1993 NaN NaN NaN NaN NaN Stack Overflow用户
发布于 2017-12-25 14:48:10
试着做些像
#Give your series index a name so that we can reset index and have a new column
your_series.index = your_series.index.rename('Time')
df = your_series.toframe('Values').reset_index()
#Create variables for month and year
df['Month'] = df.Time.dt.month
df['Year'] = df.Time.dt.Year
#Assuming they are unique, create a pivot table
df.pivot('Year','Month','Values')月份将是数字的。如果你想要这个月的名字，你必须做
import datetime as dt
df['Month'] = df.Time.date.apply(lambda x: dt.datetime.strftime(x,'%B'))如果您的月份/年份对不是唯一的，那么请执行以下操作
df.groupby(['Year','Month']).Values.sum().unstack()页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/47969456
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号