Unstack (pivot?) dataframe in Pandas

Question

I have a dataframe somewhat like this:

   ID | Relationship | First Name | Last Name |     DOB     |     Address   |    Phone
0 | 2 |     Self     |   Vegeta   |  Saiyan   |  01/01/1949 | Saiyan Planet | 123-456-7891
1 | 2 |     Spouse   |   Bulma    |  Saiyan   |  04/20/1969 | Saiyan Planet | 123-456-7891
2 | 3 |     Self     |   Krilin   |  Human    |  08/21/1992 | Planet Earth  | 789-456-4321
3 | 4 |     Self     |   Goku     |  Kakarot  |  05/04/1975 | Planet Earth  | 321-654-9870
4 | 4 |     Child    |   Gohan    |  Kakarot  |  04/02/2001 | Planet Earth  | 321-654-9870
5 | 5 |     Self     |   Freezer  |  Fridge   |  09/15/1955 |  Deep Space   | 456-788-9568

I'm looking to have the rows with same ID appended to the right of the first row with that ID.

Example:

   ID | Relationship | First Name | Last Name |     DOB     |     Address   |    Phone     |  Spouse_First Name |  Spouse_Last Name  |  Spouse_DOB  |  Child_First Name  |  Child_Last Name  |   Child_DOB   |
0 | 2 |     Self     |   Vegeta   |  Saiyan   |  01/01/1949 | Saiyan Planet | 123-456-7891 |      Bulma         |        Saiyan      |   04/20/1969 |                    |                   |
1 | 3 |     Self     |   Krilin   |  Human    |  08/21/1992 | Planet Earth  | 789-456-4321 |                    |                    |              |                    |                   |
2 | 4 |     Self     |   Goku     |  Kakarot  |  05/04/1975 | Planet Earth  | 321-654-9870 |                    |                    |              |        Gohan       |      Kakarot      |   04/02/2001  | 
3 | 5 |     Self     |   Freezer  |  Fridge   |  09/15/1955 |  Deep Space   | 456-788-9568 |                    |                    |              |                    |                   |

My real scenario dataframe has more columns, but they all have the same information when the two rows share the same ID, so no need to duplicate those in the other rows. I only need to add to the right the columns that I choose, which in this case would be First Name, Last Name, DOB with the identifier for the new column label depending on what's on the 'Relationship' column (I can rename them later if it's not possible to do in a straight way, just wanted to illustrate my point.

Now that I've said this, I want to add that I have tried different ways and seems like approaching with unstack or pivot is the way to go but I have not been successful in making it work.

Any help would be greatly appreciated.

Steele Farnsworth · Accepted Answer · 2021-10-25 23:23:27Z

1

This solution assumes that the DataFrame is indexed by the ID column.

not_self = (
    df.query("Relationship != 'Self'")
    .pivot(columns='Relationship')
    .swaplevel(axis=1)
    .reindex(
        pd.MultiIndex.from_product(
            (
                set(df['Relationship'].unique()) - {'Self'}, 
                df.columns.to_series().drop('Relationship')
            )
        ),
        axis=1
    )
)
not_self.columns = [' '.join((a, b)) for a, b in not_self.columns]
result = df.query("Relationship == 'Self'").join(not_self)

Please let me know if this is not what was wanted.

edited Oct 25, 2021 at 23:23

answered Oct 25, 2021 at 23:10

Steele Farnsworth

9131 gold badge7 silver badges17 bronze badges

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

10 Comments

rekeson21 Over a year ago

I have not been able to make this solution work. If I index by the ID, I get the duplicate index error. If I don't set the ID as index, the columns are created but everything is blank and all of the rows that have not Relationship == Self disappear

Steele Farnsworth Over a year ago

This shows how I was able to index by the ID column without error: pastebin.com/raw/ACzpQ3RY

rekeson21 Over a year ago

Yes, I'm able to set the ID as index, but to perform the whole operation with the ID as the index is what's causing the issues.

Steele Farnsworth Over a year ago

This shows my solution working for your provided data. Please let me know if it differs from what you expected. Otherwise, please provide the entire error message for the issue you are having. pastebin.com/raw/MEqv5rKn

rekeson21 Over a year ago

When I do the same as you, I get this ValueError: Index contains duplicate entries, cannot reshape

|

Collectives™ on Stack Overflow

Unstack (pivot?) dataframe in Pandas

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related