Python: Converting multiple columns to a single column with categorical data

Question

If I have this table:

City	State	Person 1	Person 2
Atlanta	GA	Bob	Fred

But, I want to convert it to:

City	State	Person#	Person Name
Atlanta	GA	1	Bob
Atlanta	GA	2	Fred

What is the most efficient way to accomplish this?

Corralien · Accepted Answer · 2021-07-27 15:29:13Z

4

Use melt:

out = df.melt(['City', 'State'], var_name='Person#', value_name='Person Name')
out['Person#'] = out['Person#'].str.extract('(\d+)')

>>> out
       City State Person# Person Name
0  Atlanta    GA        1        Bob 
1  Atlanta    GA        2        Fred

answered Jul 27, 2021 at 15:29

Corralien

121k8 gold badges44 silver badges69 bronze badges

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

1 Comment

Perfect! Thanks for your help.