Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
999 views
in Technique[技术] by (71.8m points)

python - pandas: unstack rows into new columns

i have a df that looks like this:

     a        date    c
0  ABC  2020-06-01  0.1
1  ABC  2020-05-01  0.2
2  DEF  2020-07-01  0.3
3  DEF  2020-01-01  0.4
4  DEF  2020-02-01  0.5
5  DEF  2020-07-01  0.6

i would like to "unstack" column 'a' so my new df looks like this

     a       date1   c1        date2   c2        date3   c3        date4   c4
0  ABC  2020-06-01  0.1   2020-05-01  0.2          nan  nan          nan  nan
1  DEF  2020-07-01  0.3   2020-01-01  0.4   2020-02-01  0.5   2020-07-01  0.6

how can i do this?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

Use GroupBy.cumcount for helper counter for MultiIndex and reshape by DataFrame.unstack, then for correct order is used DataFrame.sort_index with map for flatten MultiIndex:

df = (df.set_index(['a',df.groupby('a').cumcount().add(1)])
        .unstack()
        .sort_index(axis=1, level=[1, 0], ascending=[True, False]))
df.columns = df.columns.map(lambda x: f'{x[0]}{x[1]}')
df = df.reset_index()
print (df)
     a       date1   c1       date2   c2       date3   c3       date4   c4
0  ABC  2020-06-01  0.1  2020-05-01  0.2         NaN  NaN         NaN  NaN
1  DEF  2020-07-01  0.3  2020-01-01  0.4  2020-02-01  0.5  2020-07-01  0.6

Or if sorting is not possible because different columns names one idea is use DataFrame.reindex:

df1 = df.set_index(['a',df.groupby('a').cumcount().add(1)])
mux = pd.MultiIndex.from_product([df1.index.levels[1], ['date','c']])
df = df1.unstack().swaplevel(1,0, axis=1).reindex(mux, axis=1)
df.columns = df.columns.map(lambda x: f'{x[1]}{x[0]}')
df = df.reset_index()
print (df)
     a       date1   c1       date2   c2       date3   c3       date4   c4
0  ABC  2020-06-01  0.1  2020-05-01  0.2         NaN  NaN         NaN  NaN
1  DEF  2020-07-01  0.3  2020-01-01  0.4  2020-02-01  0.5  2020-07-01  0.6

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

1.4m articles

1.4m replys

5 comments

57.0k users

...