How to groupby date in pandas and create time column associated by the date

Question

I have this data as dataframe and I want to group dates and create columns of time :

        empIdn      date      time
36       517     2019-05-02  12:00:12
54       517    2019-05-02  12:32:05
223      517    2019-05-02  05:18:57
95       517    2019-05-02  05:23:02

and what I want to achieve is

      empIdn       date      timeA    timeB     timeC      timeD
36     517     2019-05-02  12:00:12    12:32:05  05:18:57   05:23:02

Here is what I've tried so far:

datas = pd.read_csv(datFile, sep="\t",usecols=[0, skipinitialspace=True, names=['empId', 'date_time'])
df = pd.DataFrame(data = datas)
new = df['date_time'].str.split(" ", n = 1, expand = True)

df['empIdn'] =  df['empId'].astype(int)
df['date'] = new[0]
df['time'] = new[1]
df['date'] = pd.to_datetime(df['date'])
df['time'] = pd.to_datetime(df['time'],format= '%H:%M:%S' ).dt.time

df.drop(columns=['date_time'], inplace = True)
df.drop(columns=['empId'], inplace = True)
df.sort_values('empIdn',inplace = True, ascending = True)

@WeNYoBen I have a difficulty on applying your suggestion sir. I am a beginner in using pandas — Carlo june Caimen, Aug 29 '19 at 03:21

moys · Accepted Answer · 2019-08-29T03:49:58.723

0

df3=df.groupby(['empIdn','date'])['time'].agg(list).reset_index(name='new')
df3 = df3.join(pd.DataFrame(df3.new.values.tolist(), df3.index).add_prefix('time_'))
df3.drop('new', axis=1, inplace = True)

edited Aug 29 '19 at 03:49

answered Aug 29 '19 at 02:54

moys

7,747
2
11
42

it is working fine for me. i have included the pic of the result also. please check the df names, columns names...etc properly in the code that you run – moys Aug 29 '19 at 03:25
can this be dynamic? df2[['timeA','timeB','timeC','timeD']] if data is only 3 then it will create 3 columns. – Carlo june Caimen Aug 29 '19 at 03:36
what do you mean? – moys Aug 29 '19 at 03:36
then you have to change the number of names in `df2[['timeA','timeB','timeC','timeD']]`, we are saying convert 4 values into 4 columns, if there are less values, there should be less columns – moys Aug 29 '19 at 03:40
in my example i have 4 rows, if i will delete the last one, the error "Columns must be same length as key" will show up because of df2[['timeA','timeB','timeC','timeD']] – Carlo june Caimen Aug 29 '19 at 03:41
can't this be done dynamically sir? im sorry, I am new in python – Carlo june Caimen Aug 29 '19 at 03:43
code updated, try. The names of new columns will be however, have suffix 0,1,2,3 instead of A,B,C....I don't know myself how to create names with A,B,C – moys Aug 29 '19 at 03:51
It is very great, Thank you sir. God bless – Carlo june Caimen Aug 29 '19 at 03:58
Great, don't forget to vote/accept as answer if this helped you :) – moys Aug 29 '19 at 03:59

How to groupby date in pandas and create time column associated by the date

1 Answers1