Python Excel, Move corresponding strings to new column

Question

I have a data set in excel like the following that will have a ~600 rows and varying number of rows that will be the same in Column A

Column A        Column B
row1.rack1      row1.rack1.x,X,B,C
row1.rack1      row1.rack1.x,B,D,A     
row1.rack2      row1.rack2.d,I,K,L
row1.rack2      row2.rack2.a,L,M,D

I want to able to be able to have only 1 of each string in Column A and for all the strings in Column B if they match Column A I want each one to be another column over. So in this case the output I'm looking for would be like...

Column A        Column B                Column C
row1.rack1      row1.rack1.x,X,B,C      row1.rack1.x,B,D,A
row2.rack2      row1.rack2.d,I,K,L      row2.rack2.a,L,M,D

I'm still pretty new to coding and wasn't sure where to start. Any help will be greatly appreciated!

U13-Forward · Accepted Answer · 2021-09-22 01:48:18Z

1

Use groupby with pivot:

(df.assign(idx=df.groupby('Column A').cumcount())
   .pivot(index='Column A',columns='idx')['Column B']
   .set_axis(['Column B', 'Column C'], axis=1)
   .reset_index())

     Column A            Column B            Column C
0  row1.rack1  row1.rack1.x,X,B,C  row1.rack1.x,B,D,A
1  row1.rack2  row1.rack2.d,I,K,L  row2.rack2.a,L,M,D

This code pivots the dataframe long to wide by the duplicates in Column A.

If there might be arbitrary length of duplicate values, try:

import string
(df.assign(idx=df.groupby('Column A').cumcount())
  .pivot(index='Column A',columns='idx')['Column B']
  .rename(columns=lambda x: f'Column {string.ascii_uppercase[x + 1]}')
  .reset_index()
  .rename_axis(columns=None))

edited Sep 22, 2021 at 1:48

answered Sep 22, 2021 at 1:43

U13-Forward

71.8k15 gold badges100 silver badges125 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nathaniel Over a year ago

Maybe I am doing something wrong? I defined the columns on mine so I edited Column A/Column B from your code to be the names I used and my output is coming out the same as my input. For reference I'm using the second one you gave as the number of duplicate values isn't set.

Nathaniel Over a year ago

Nevermind! I figured out what I did wrong. I didn't define the code block under a new dataframe

Collectives™ on Stack Overflow

Python Excel, Move corresponding strings to new column

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related