how to create new column names from another column all values and agg by another column in pandas dataframe?

user_15502316 0
Viewed 68

I have tried pivot tables but did not work. I am wondering how can be done?

Input data frame:

df_test = pd.DataFrame({'users': ['user1','user1','user2','user2'],
 'day' : [2,2,2,2],
 'col':['a','b','c','d']})

input df

I want to get something like this:

output df

pandas dataframe list numpy python
3 Answers

For this particular exmaple you can use the following:

df_test = pd.DataFrame({'users': ['user1','user1','user2','user2'],
 'day' : [2,2,2,2],
 'col':['a','b','c','d']})

df_out = df_test.set_index(['users', 'day', df_test.groupby(['users', 'day']).cumcount()])[['col']].unstack([1,2])

df_out.columns = [f'{i}_day{j}_another{j}' if k != 0 else f'{i}_day{j}' for i, j, k in df_out.columns]
print(df_out.reset_index())

Output:

   users col_day2 col_day2_another2
0  user1        a                 b
1  user2        c                 d
user_63615310

You can build a label column (col_dayX_anotherY) before pivoting:

df_test['day_count'] = df_test.groupby(['users', 'day']).cumcount().add(1)

df_test['label'] = df_test.agg(lambda x: f'col_day{x.day}_another{x.day_count}', axis=1)
df_test['label'] = df_test.label.str.strip('_another1')

# users  day col  day_count              label
# user1    2   a          1           col_day2
# user1    2   b          2  col_day2_another2
# user2    2   c          1           col_day2
# user2    2   d          2  col_day2_another2

Then you can pivot():

df_test = df_test.pivot(index='users', columns='label', values='col')

#       col_day2 col_day2_another2
# users                           
# user1        a                 b
# user2        c                 d
user_131383640

This is a more general approach, generating columns for new days when needed. Note that some data elements may be missing (automatically filled up by NaN). (Multiple) repeated columns are extended by '_another'.

df_test = pd.DataFrame({'users': ['user1','user1','user1','user1',
                                  'user2','user2','user2','user3'],
    'day':[1,2,2,2,1,2,2,3],
    'col':['a','b','c','d','e','f','g','h']})
# Create empty dictionary
dd={}
# Loop across rows in dataframe
for i in range(len(df_test)):
    user=df_test.users[i]
    # add dictionary entry for each user (set dict with first col on new user)
    if not user in dd:dd[user]={'users':user}
    # set corresponding new field for col+day+daynr
    newField='col_day'+str(df_test.day[i])
    # if field exists already, extend the name
    while newField in dd[user]: newField+='_another'
    # now set the value
    dd[user][newField]=df_test.col[i]
# create new data frame
df_new=pd.DataFrame(dd.values())

df_new is resulting in this example as shown here:

   users col_day1 col_day2 col_day2_another col_day2_another_another col_day3
0  user1        a        b                c                        d      NaN
1  user2        e        f                g                      NaN      NaN
2  user3      NaN      NaN              NaN                      NaN        h
user_155010350
Related