CODEWITHSUNDEEP
pythonpandas
IcemanBerlin
IcemanBerlin

Reputation: 3437

Convert a Week format like 201729 in a dataframe to a month in a new column where the date is the first monday

I found this but cant get the syntax correct. 

time.asctime(time.strptime('2017 28 1', '%Y %W %w'))

I want to set a new column to show month in the format "201707" for July. It can be int64 or string doesnt have to be an actual readable date in the column.

My dataframe column ['Week'] is also in the format 201729 i.e. YYYYWW 

 dfAttrition_Billings_KPIs['Day_1'] = \
  time.asctime(time.strptime(dfAttrition_Billings_KPIs['Week'].str[:4] 
     + dfAttrition_Billings_KPIs['Month'].str[:-2] - 1 + 1', '%Y %W %w'))

So I want the output of the rows that have week 201729 to show in a new field month 201707. the output depends on what the row value is in 'Week'.

I have a million records so would like to avoid iterations of rows, lambdas and slow functions where possible :)

Upvotes: 0

Views: 558

Answers (1)

jezrael
jezrael

Reputation: 862691

Use to_datetime with parameter format with add 1 for Mondays, last for format YYYYMM use strftime

df = pd.DataFrame({'date':[201729,201730,201735]})

df['date1']=pd.to_datetime(df['date'].astype(str) + '1', format='%Y%W%w')
df['date2']=pd.to_datetime(df['date'].astype(str) + '1', format='%Y%W%w').dt.strftime('%Y%m')
print (df)
     date      date1   date2
0  201729 2017-07-17  201707
1  201730 2017-07-24  201707
2  201735 2017-08-28  201708

If need convert from datetime to weeks custom format:

 df = pd.DataFrame({'date':pd.date_range('2017-01-01', periods=10)})

df['date3'] = df['date'].dt.strftime('%Y %W %w')
print (df)
        date      date3
0 2017-01-01  2017 00 0
1 2017-01-02  2017 01 1
2 2017-01-03  2017 01 2
3 2017-01-04  2017 01 3
4 2017-01-05  2017 01 4
5 2017-01-06  2017 01 5
6 2017-01-07  2017 01 6
7 2017-01-08  2017 01 0
8 2017-01-09  2017 02 1
9 2017-01-10  2017 02 2

Upvotes: 1

Related Questions