CODEWITHSUNDEEP
pythonpandasdataframe
Arjun
Arjun

Reputation: 1069

pandas dataframe reset index

I have a dataframe like this:

Attended Email JoinDate JoinTime JoinTime
JoinTimeFirst JoinTimeLast
Yes 009indrajeet 12/3/2022 12/3/2022 19:50 12/3/2022 21:47
Yes 09871143420.ms 12/18/2022 12/18/2022 20:41 12/18/2022 20:41
Yes 09s.bisht 12/17/2022 12/17/2022 19:51 12/17/2022 19:51

and I need to change column headers like this:

Attended Email JoinDate JoinTimeFirst JoinTimeLast
Yes 009indrajeet 12/3/2022 12/3/2022 19:50 12/3/2022 21:47
Yes 09871143420.ms 12/18/2022 12/18/2022 20:41 12/18/2022 20:41
Yes 09s.bisht 12/17/2022 12/17/2022 19:51 12/17/2022 19:51

I tried multiple ways but noting worked out, any help will be appreciated. To get to the first dataframe, this is what I did:

import pandas as pd
df = pd.DataFrame({"Attended":["Yes","Yes","Yes"]
                    ,"Email":["009indrajeet","09871143420.ms","09s.bisht"]
                    ,"JoinTime":["Dec 3, 2022 19:50:52","Dec 3, 2022 20:10:52","Dec 3, 2022 21:47:32"]})
#convert JoinTime to timestamp column
df['JoinTime'] = pd.to_datetime(df['JoinTime'],format='%b %d, %Y %H:%M:%S', errors='raise')
#extract date from timestamp column
df['JoinDate'] = df['JoinTime'].dt.date
#created grouper dataset
df_grp = df.groupby(["Attended","Email","JoinDate"])
#define aggregations
dict_agg = {'JoinTime':[('JoinTimeFirst','min'),('JoinTimeLast','max'),('JoinTimes',set)]}
#do grouping with aggregations
df = df_grp.agg(dict_agg).reset_index()

print(df)

print(df.columns)

MultiIndex([('Attended',              ''),
            (   'Email',              ''),
            ('JoinDate',              ''),
            ('JoinTime', 'JoinTimeFirst'),
            ('JoinTime',  'JoinTimeLast'),
            ('JoinTime',     'JoinTimes')],
           )

Upvotes: 0

Views: 218

Answers (3)

jezrael
jezrael

Reputation: 863611

Use named aggregations - pass dictionary with changed format - keys are new columns names, values are tuples - first value is processing column and second is aggregation function:

dict_agg = {'JoinTimeFirst':('JoinTime','min'),
            'JoinTimeLast':('JoinTime','min'),
            'JoinTimes':('JoinTime',set)}
#do grouping with aggregations
df = df_grp.agg(**dict_agg).reset_index() 
print (df)
  Attended           Email    JoinDate       JoinTimeFirst  \
0      Yes    009indrajeet  2022-12-03 2022-12-03 19:50:52   
1      Yes  09871143420.ms  2022-12-03 2022-12-03 20:10:52   
2      Yes       09s.bisht  2022-12-03 2022-12-03 21:47:32   

         JoinTimeLast              JoinTimes  
0 2022-12-03 19:50:52  {2022-12-03 19:50:52}  
1 2022-12-03 20:10:52  {2022-12-03 20:10:52}  
2 2022-12-03 21:47:32  {2022-12-03 21:47:32}

You can also pass named aggregation:

#do grouping with aggregations
df = df_grp.agg(JoinTimeFirst=('JoinTime','min'),
                JoinTimeLast=('JoinTime','min'),
                JoinTimes=('JoinTime',set)).reset_index() 
print (df)
  Attended           Email    JoinDate       JoinTimeFirst  \
0      Yes    009indrajeet  2022-12-03 2022-12-03 19:50:52   
1      Yes  09871143420.ms  2022-12-03 2022-12-03 20:10:52   
2      Yes       09s.bisht  2022-12-03 2022-12-03 21:47:32   

         JoinTimeLast              JoinTimes  
0 2022-12-03 19:50:52  {2022-12-03 19:50:52}  
1 2022-12-03 20:10:52  {2022-12-03 20:10:52}  
2 2022-12-03 21:47:32  {2022-12-03 21:47:32}

Upvotes: 2

Chaitanya Madduri
Chaitanya Madduri

Reputation: 36

Below approach is more generalized and basically it is designed if your first row has column names

# Setting the columns based on the column 1
import pandas as pd 
import numpy as np
# df = please load the dataframe to the df and assume that the empty values are read as null 
final_col = []
for key, val in dict(df.iloc[0].fillna(0)).items():
     if val == 0 :
        final_col.append(key)
     else:
         final_col.append(val)

df.columns  = final_col
df = df.loc[1:] # removing teh first column 
df.reset_index(drop=True, inplace=True) # Resetting the index to 0

Upvotes: 1

phœnix
phœnix

Reputation: 367

new_df=df.dropna(axis=1).rename(columns = {df.columns[3]:'JoinTimeFirst',df.columns[4]:'JoinTimeLast'})

Upvotes: 1

Related Questions