1

I have a question handling dataframe in pandas.

I really don't know what to do.

Could you check this problem?

[df1]

enter image description here

This is first dataframe and I want to get second dataframe. Like thisenter image description here

I got a index value DATE(Week), DATE(Month) using resample method in pandas. but I don't know merge the table like second table. so please check this question. Thank you so much.

bonbon
  • 121
  • 8

1 Answers1

1

What I have understood from your question is that you want to diversify DATE column to its nearest week and month, so if that is the case you need not have to create two separate DataFrame, there is an easier way to do it using DateOffsets

#taking sample from your data
import pandas as pd
from pandas.tseries.offsets import *

>>d = {'DATE': ['2019-01-14', '2019-01-16', '2019-02-19'], 'TX_COST': [156800, 157000, 150000]}
>>df = pd.DataFrame(data=d)
>>df
         DATE  TX_COST
0  2019-01-14   156800
1  2019-01-16   157000
2  2019-02-19   150000

#convert Date column to datetime format

df['DATE'] = pd.to_datetime(df['DATE'])


#as per your requirement set weekday=6 that is sunday as the week ending date

>>> df['WEEK'] = df['DATE'] + Week(weekday=6)
>>> df
        DATE  TX_COST       WEEK
0 2019-01-14   156800 2019-01-20
1 2019-01-16   157000 2019-01-20
2 2019-02-19   150000 2019-02-24

#use month offset to round the date to nearest month end

>>> df['MONTH'] = df['DATE'] + pd.offsets.MonthEnd()
>>> df
        DATE  TX_COST       WEEK      MONTH
0 2019-01-14   156800 2019-01-20 2019-01-31
1 2019-01-16   157000 2019-01-20 2019-01-31
2 2019-02-19   150000 2019-02-24 2019-02-28

This will create the DataFrame which you require

think-maths
  • 917
  • 2
  • 10
  • 28
  • Thank you so much, but I wondering about Week(weekday=6) meaning..Could you check this code? – bonbon Jan 07 '21 at 07:46
  • So when you do ```offsets.Week()``` it will add 7 days from the given date and will give you that date as week. But when you do ```Week(weekday=6)``` then ```weekday=0``` is ```Monday``` and ```weekday=6``` is ```Sunday``` so this ```Week(weekday=6)``` will round the week to the nearest ```sunday``` which is your requirement. You can go through the doc link which I have shared that will clear all your doubts – think-maths Jan 07 '21 at 07:53
  • I want df['WEEK'] = df['DATE'] + Week(weekday=6) this point. I'm really thank your comments. – bonbon Jan 07 '21 at 08:01
  • Great I have edited my answer just for your requirement – think-maths Jan 07 '21 at 08:02
  • Thank you, but Week(weekday=6) code dosen't work. – bonbon Jan 07 '21 at 08:21
  • It does work for me what is the error you are getting, have imported this ```from pandas.tseries.offsets import *``` in your code – think-maths Jan 07 '21 at 08:24
  • Oh, yes. Thank you so much! – bonbon Jan 07 '21 at 08:29