Conversion of df one column values to multiple column values in pandas

Question

id	date	decision
1	2022-11-10	improve
1	2022-11-10	checked
2	2021-09-12	checked
3	2020-08-22	checked
4	2019-11-10	complete
4	2019-11-10	revise

Converting above dataframe as

id	date	CR	Principal
1	2022-11-10	checked	improve
2	2021-09-12	checked	NA
3	2020-08-22	checked	NA
4	2019-11-10	revise	complete

Id and date column should be unique, i.e., one row for each id. If there are two decisions for single id then convert the decisions into CR and Principal where CR is the last decision and Principal is the first decision. — Swetha, Nov 29 '22 at 06:27
Then it should create new column and respective value. Maximum is 4 decisions. — Swetha, Nov 29 '22 at 06:34
If 3 and 4 decisions are empty display null values for each id so that rename(columns={0:'CR',1:'Principal', 2:"NA",3:"NA"} — Swetha, Nov 29 '22 at 06:38
Rather not, because get duplicated columns names, so if select `print (df['NA'])` get both columns. — jezrael, Nov 29 '22 at 06:40
3 and 4 are not duplicates. 3 is final 4 is post final. They should display nan if not present in df. — Swetha, Nov 29 '22 at 06:42
fillna(0) and display final and post final columns as well in df while printing df — Swetha, Nov 29 '22 at 06:45
In your answer df has only 4 columns, but required final and postfinal columns with values as zero as well. — Swetha, Nov 29 '22 at 07:02

jezrael · Accepted Answer · 2022-11-29T07:05:39.027

Use GroupBy.cumcount with ascending=False for counter in descending order and pivoting by 4 columns, then use rename - add keys to dictionary for rename if 3 or 4 duplicated decisions:

df = (df.assign(g = df.groupby(['id','date']).cumcount(ascending=False))
        .pivot(['id','date'], 'g', 'decision')
        .reindex(columns=range(4))
        .fillna(0)
        .rename(columns={0:'CR',1:'Principal',2:'final',3:'post final'})
        .rename_axis(columns=None)
        .reset_index())
print (df)
   id        date       CR Principal  final  post final
0   1  2022-11-10  checked   improve    0.0         0.0
1   2  2021-09-12  checked         0    0.0         0.0
2   3  2020-08-22  checked         0    0.0         0.0
3   4  2019-11-10   revise  complete    0.0         0.0

Conversion of df one column values to multiple column values in pandas

1 Answers1