Extracting values from dataframe columns and inserting them into rows

Question

I have the following dataframe:

DataFrame

I want the output to be:

df_output

I can do it looping over the original dataframe and adding data to a new dataframe but I have the impression there must be a simpler approach. I have tried pivot_table, grouping,query and dictionaries but with no results. Any ideas? Thanks in advance.

Welcome to StackOverflow! Unfortunately, this site has rules about how questions should be asked, and this one does not meet them. As a new user, you really should read How to Ask. Here, you have provided links to images. Both are frowned upon here, because links force readers to change page to understand the question and image prevent copy/paste. You should at least copy both dataframes as text (formatted as code) in the question itself. — Serge Ballesta
– Serge Ballesta, Commented Jun 26, 2020 at 21:06
Hi @KarlManz, welcome to SO. in general avoid pasting images because they cannot be use to reproduce your data. Your previous version was actually ok, where I formatted your table. Other users can take that and try. Any reason for switching to the images? — StupidWolf
– StupidWolf, Commented Jun 26, 2020 at 21:07

StupidWolf · Accepted Answer · 2020-06-26 21:04:46Z

1

You can set the 'Owner' to a category so that it will appear in the order after you pivot:

df = pd.DataFrame({'Owner':['John Doe','Woody Woodpecker','Bart Simpson','Ringo Star','Woody Woodpecker',
                           'John Doe','Donald Duck','Woody Woodpecker','John Doe','Bart Simpson'],
                   'Lot':['A%02d' % n for n in np.arange(1,11)]})
df['Owner'] = pd.Categorical(df['Owner'],categories=pd.unique(df['Owner']))

What you are missing is a column to enumerate the unique rows per group:

df['N'] = df.groupby('Owner').cumcount()+1

    Owner              Lot  N
0   John Doe           A01  1
1   Woody Woodpecker   A02  1
2   Bart Simpson       A03  1
3   Ringo Star         A04  1
4   Woody Woodpecker   A05  2
5   John Doe           A06  2
6   Donald Duck        A07  1
7   Woody Woodpecker   A08  3
8   John Doe           A09  3
9   Bart Simpson       A10  2

And changing this to the names you want, and you can pivot easily:

df['N'] = ['Lot'+str(i) for i in df['N']]
df.pivot(index='Owner',columns='N',values='Lot').reset_index()


N   Owner               Lot1 Lot2   Lot3
0   John Doe            A01  A06    A09
1   Woody Woodpecker    A02  A05    A08
2   Bart Simpson        A03  A10    NaN
3   Ringo Star          A04  NaN    NaN
4   Donald Duck         A07  NaN    NaN

answered Jun 26, 2020 at 21:04

StupidWolf

47.1k17 gold badges50 silver badges81 bronze badges

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

2 Comments

KarlManz Over a year ago

I apologize for the way I asked the question and its formatting and I thank the replies which I am going to test right now. Best regards.

KarlManz Over a year ago

Worked like a charm! Thanks a lot!

Collectives™ on Stack Overflow

Extracting values from dataframe columns and inserting them into rows

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