How to transfer data from a column to multiple columns using Python?-CodePudding

How can I use python to transfer data from the "weekday" column and multiple columns (Monday, Tuesday, Wednesday...) and vice versa

       buyer    weekday    
0      A        Saturday
1      A        Friday
2      B        Monday
3      B        Tuesday
4      B        Thursday
5      C        Monday

Desired Outcome:

       buyer   Monday Tuesday Wednesday Thursday Friday Saturday Sunday
0      A                                           Y       Y       
1      B         Y       Y                 Y                            
2      C         Y

CodePudding user response：

df = pd.DataFrame({'buyer': ['A', 'A', 'B', 'B', 'B', 'C'],
                   'weekday': ['Saturday', 'Friday', 'Monday', 'Tuesday', 'Thursday', 'Monday']})
w_days = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday']
df = pd.crosstab(df['buyer'], df['weekday']).replace({0: '', 1: 'Y'})
df = df.assign(**dict.fromkeys(set(w_days).difference(df.columns), ''))[w_days].reset_index().rename_axis(columns={'weekday': ''})
print(df)

  buyer Monday Tuesday Wednesday Thursday Friday Saturday Sunday
0     A                                        Y        Y       
1     B      Y       Y                  Y                       
2     C      Y

CodePudding user response：

Option #1

Introduce a dummy column for values and use pivot():

import calendar
weekdays = list(calendar.day_name)

df = pd.DataFrame({'buyer': ['A', 'A', 'B', 'B', 'B', 'C'],
                   'weekday': ['Saturday', 'Friday', 'Monday', 'Tuesday', 'Thursday', 'Monday']})

df["dummy"] = "Y"

df = df.pivot(index="buyer", columns="weekday", values="dummy").reindex(labels=weekdays, axis=1).fillna("").reset_index().rename_axis(columns={"weekday": ""})

[Out]:
  buyer Monday Tuesday Wednesday Thursday Friday Saturday Sunday
0     A                                        Y        Y       
1     B      Y       Y                  Y                       
2     C      Y

Option #2

Use a combination of groupby, size & unstack and finally replace values:

import calendar
weekdays = list(calendar.day_name)

df = pd.DataFrame({'buyer': ['A', 'A', 'B', 'B', 'B', 'C'],
                   'weekday': ['Saturday', 'Friday', 'Monday', 'Tuesday', 'Thursday', 'Monday']})

df = df = df.groupby(["buyer", "weekday"]).size().unstack(fill_value=0).replace({1:"Y",0:""}).reindex(labels=weekdays, axis=1).fillna("").reset_index().rename_axis(columns={"weekday": ""})

[Out]:
  buyer Monday Tuesday Wednesday Thursday Friday Saturday Sunday
0     A                                        Y        Y       
1     B      Y       Y                  Y                       
2     C      Y