从两个日期列中去除月份 [英] Strip out months from two date columns

问题描述

我有一个熊猫数据框，其中包含合同的开始和结束日期以及数量.我将如何去除各个月份，以便对其进行汇总和绘制图形.

I have a pandas dataframe that has contracts start and end date and a quantity. How would I strip out the individual months so they can be aggregated and graphed.

ex 
Start Date  End Date       Demanded     Customer
1/1/2017    3/31/2017        100            A
2/1/2017    3/31/2017         50            B

将月份减少到以下

Month       Demand    Customer
1/1/2017     100      A
2/1/2017     100      A
3/1/2017     100      A
2/1/2017      50      B
3/1/2017      50      B

最终结果是将其旋转，然后在x轴上绘制月份，在y轴上绘制总需求

End result is to pivot this and then graph with months on the x-axis and total demand on the y-axis

推荐答案

您可以先转换日期为 itertuples 和 date_range 频率为 MS (月初)，并带有 join 原始列所需数量和客户:

You can first convert columns with dates to_datetime. Then use itertuples and date_range with frequency MS (start of month) with concat for creating new expanding DataFrame. Last join original columns Quantity Demanded and Customer:

df['Start_Date'] = pd.to_datetime(df['Start Date'])
df['End_Date'] = pd.to_datetime(df['End Date'])

df1 = pd.concat([pd.Series(r.Index, 
                           pd.date_range(r.Start_Date, r.End_Date, freq='MS')) 
                           for r in df.itertuples()])
        .reset_index()
df1.columns = ['Month','idx']
print (df1)
       Month  idx
0 2017-01-01    0
1 2017-02-01    0
2 2017-03-01    0
3 2017-02-01    1
4 2017-03-01    1

df2 = df1.set_index('idx').join(df[['Quantity Demanded','Customer']]).reset_index(drop=True)
print (df2)
       Month  Quantity Demanded Customer
0 2017-01-01                100        A
1 2017-02-01                100        A
2 2017-03-01                100        A
3 2017-02-01                 50        B
4 2017-03-01                 50        B

这篇关于从两个日期列中去除月份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！