mix the information of all columns in a single column

user_14870964 0
Viewed 72

I have a dataset like this:

df = pd.DataFrame({'ID':["a"," b", "c","d", "e"],
               'P1': [10, 28, 34, 56, 78],
              'P2':[ 90, 54, 54, 32, 16],
              'P3':[14, 15, 24, 90,34]})

which contains the grades of students for each problem. I need to creat a new column named "Notes" which contains the grades of all problems in one column. the output must look like this: enter image description here

jupyter-notebook pandas python
3 Answers

Please Try to_dict, after isolating required columns using the iloc accessor.

df['notes']=df.iloc[:,1:].to_dict('records')

If you need totals included, we can use a column filter, assign total and notes as follows;

df=df.assign(Total=df.filter(regex='^P', axis=1).sum(1),notes=df.filter(regex='^P', axis=1).to_dict('records'))

In case the dict brackets are an issue. You can strip them off

df.notes.astype(str).str.strip('{}')
user_89869750

Use:

#Calculate Total
df['Total'] = df.filter(regex='P').sum(axis=1)
#Create Notes
df['Notes'] = df.filter(regex='P').astype(str)\
    .apply(lambda row: ','.join(row.index + ';' + row), axis=1)
print(df)


   ID  P1  P2  P3  Total              Notes
0   a  10  90  14    114  P1;10,P2;90,P3;14
1   b  28  54  15     97  P1;28,P2;54,P3;15
2   c  34  54  24    112  P1;34,P2;54,P3;24
3   d  56  32  90    178  P1;56,P2;32,P3;90
4   e  78  16  34    128  P1;78,P2;16,P3;34
user_118842370

Try using lambda and map

p_val = df.filter(like='P')

comb = lambda x: [f"{key}:{val}" for key,val in zip(p_val.columns, x)] 
df['Notes'] = pd.Series(list(map(comb,p_val.values.tolist()))).str.join(',')

df:

    ID  P1  P2  P3  Notes
0   a   10  90  14  P1:10,P2:90,P3:14
1   b   28  54  15  P1:28,P2:54,P3:15
2   c   34  54  24  P1:34,P2:54,P3:24
3   d   56  32  90  P1:56,P2:32,P3:90
4   e   78  16  34  P1:78,P2:16,P3:34
user_66603730
Related