CODEWITHSUNDEEP
pythonpandas
Amir Rahimpour
Amir Rahimpour

Reputation: 65

Pandas Duplicating Data but pivoting columns

I want to convert this DF

Location Date F1_ID F1_Name F1_Height F1_Status F2_ID F2_Name F2_Height F2_Status
USA 12/31/19 1 Jon 67 W 2 Anthony 68 L

To this DF by duplicating the rows but switching the data around.

Location Date F1_ID F1_Name F1_Height F1_Status F2_ID F2_Name F2_Height F2_Status
USA 12/31/19 1 Jon 67 W 2 Anthony 68 L
USA 12/31/19 2 Anthony 68 L 1 Jon 67 W

How can I acheive this in Pandas. I tried creating a copy of the df and renaming the columns but would get an error because of unique indexing

Upvotes: 0

Views: 35

Answers (1)

Henry Ecker
Henry Ecker

Reputation: 35646

Lets try a concat and sort_index:

import re

import pandas as pd

df = pd.DataFrame(
    {'Location': {0: 'USA'}, 'Date': {0: '12/31/19'},
     'F1_ID': {0: 1}, 'F1_Name': {0: 'Jon'}, 'F1_Height': {0: 67},
     'F1_Status': {0: 'W'}, 'F2_ID': {0: 2},
     'F2_Name': {0: 'Anthony'}, 'F2_Height': {0: 68},
     'F2_Status': {0: 'L'}})

# Columns Not To Swap
keep_columns = ['Location', 'Date']
# Get F1 and F2 Column Names
f1_columns = list(filter(re.compile(r'F1_').search, df.columns))
f2_columns = list(filter(re.compile(r'F2_').search, df.columns))
# Create Inverse DataFrame
inverse_df = df[[*keep_columns, *f2_columns, *f1_columns]]
# Set Columns so they match df (prevents concat from un-inverting)
inverse_df.columns = df.columns

# Concat and sort index
new_df = pd.concat((df, inverse_df)).sort_index().reset_index(drop=True)
print(new_df.to_string())

Src:

  Location      Date  F1_ID F1_Name  F1_Height F1_Status  F2_ID  F2_Name  F2_Height F2_Status
0      USA  12/31/19      1     Jon         67         W      2  Anthony         68         L

Output:

  Location      Date  F1_ID  F1_Name  F1_Height F1_Status  F2_ID  F2_Name  F2_Height F2_Status
0      USA  12/31/19      1      Jon         67         W      2  Anthony         68         L
1      USA  12/31/19      2  Anthony         68         L      1      Jon         67         W

Upvotes: 1

Related Questions