Create additional columns for duplicate dimensions

user_14777208 0
Viewed 41

I have the following dataframe:

p_id    c_id    region  account state
A0001   100     east    CA1000  VA
A0001   100     east    CA1001  GA
A0001   100     west    CA1002  CA
A0001   101     west    CA1004  TX
A0001   101     west    CA1007  GA
A0002   108     east    CA1013  MI
A0002   109     east    CA1018  WA
A0002   109     east    CA1020  AZ

I want to store each unique account & state in separate columns for the same p_id + c_id + region. So the output looks like:

p_id    c_id    region  account_1   state_1 account_2   state_2 account_3   state_3 account_4   state_4
A0001   100     east    CA1000      VA      CA1001      GA              
A0001   100     west    CA1002      CA                      
A0001   101     west    CA1004      TX      CA1007      GA              
A0002   108     east    CA1013      MI                      
A0002   109     east    CA1018      WA      CA1020      AZ

Note:

  1. I need 8 additional columns in the output (account_1 ~ 4 and state_1 ~ 4), even if there is less than 4 different account & state from every combination of p_id, c_id and region in the whole dataframe.
  2. Keep values blank in new columns if there is no values.
pandas python
1 Answers

You could use a combination of groupby, unstack and reindex:

# create and indicator column
df['dup'] = df.groupby(['p_id', 'c_id', 'region']).transform('cumcount') + 1
df['dup'] = df['dup'].astype(str)

# set index and then unstack in the indicator column
df = df.set_index(['p_id', 'c_id', 'region', 'dup']).unstack('dup')

# rename columns (flatten MultiIndex at the same time)
df.columns = ['_'.join(col) for col in df.columns.values]

# reindex columns
df = df.reindex(columns=[f'{col}_{i}' for i in range(1, 5) for col in ['account', 'state']]).reset_index()

print(df)

Output

    p_id  c_id region account_1  ... account_3 state_3 account_4  state_4
0  A0001   100   east    CA1000  ...       NaN     NaN       NaN      NaN
1  A0001   100   west    CA1002  ...       NaN     NaN       NaN      NaN
2  A0001   101   west    CA1004  ...       NaN     NaN       NaN      NaN
3  A0002   108   east    CA1013  ...       NaN     NaN       NaN      NaN
4  A0002   109   east    CA1018  ...       NaN     NaN       NaN      NaN

[5 rows x 11 columns]
user_40015920
Related