Pandas — Simplest way to change dataset rows from per-individual to per-action

user_12399409 0
Viewed 78

I have a dataset that's currently one row per applicant that I need to be one row per application. There are three columns, app_0, app_1, and app_2, which contain the names of the programs to which they are applying. If they've applied to one program, there's a string of the program name in app_0, and app_1 & app_2 are missing; if they've applied to two programs, there are strings of program names in app_0 & app_1 and app_2 is missing; and if they've applied to three programs all three columns contain strings. The three corresponding accepted columns work the same way. Here's an example dataframe:

import pandas as pd
import numpy as np

pd.DataFrame({
    'id': [0, 1, 2, 3, 4],
    'test_score': [350, 450, 500, 325, 433],
    'gender': ['F', 'F', 'M', 'M', 'F'],
    'app_0': ['A', 'B', 'D', 'C', 'B'],
    'app_1': ['B', np.nan, np.nan, 'B', np.nan],
    'app_2': [np.nan, np.nan, np.nan, 'A', np.nan],
    'accepted_0': [True, False, False, True, False],
    'accepted_1': [False, np.nan, np.nan, True, np.nan],
    'accepted_2': [np.nan, np.nan, np.nan, False, np.nan]
})

In the dataframe with one row per application, I would want one column with the string of the program applied to, and one column indictating if that application were accepted or denied. The rest of the columns I would want copied in cases where an applicant submitted more than one application. The final dataframe should look like this (row order doesn't matter):

pd.DataFrame({
    'id': [0, 1, 2, 3, 4, 0, 3, 3],
    'test_score': [350, 450, 500, 325, 433, 350, 325, 325],
    'gender': ['F', 'F', 'M', 'M', 'F', 'F', 'M', 'M'],
    'app': ['A', 'B', 'D', 'C', 'B', 'B', 'B', 'A'],
    'accepted': [True, False, False, True, False, False, True, False]
})

I'm looking for the simplest and most computationally efficient (i.e., probably not Iterrows) way to do this in Pandas. Would it be a pivot dataframe, and then a second command to fill in copied values from the old dataframe? Would unstack() be useful here? Thanks!

pandas numpy python
3 Answers

you can use wide_to_long with some reset_index and dropna

res = (pd.wide_to_long(df, ['app', 'accepted'], sep='_', i='id', j='_')
         .dropna(subset=['app', 'accepted'])
         .reset_index(level='id')
         .reset_index(drop=True)
      )
print(res)
   id  test_score gender app accepted
0   0         350      F   A     True
1   1         450      F   B    False
2   2         500      M   D    False
3   3         325      M   C     True
4   4         433      F   B    False
5   0         350      F   B    False
6   3         325      M   B     True
7   3         325      M   A    False

or use set_index and stack with concat each app and accepted columns seprately

df_ = df.set_index(['id','test_score','gender'])
res = pd.concat([df_.filter(like='app').stack().reset_index(level=-1, drop=True), 
                 df_.filter(like='accepted').stack().reset_index(level=-1, drop=True)], 
                axis=1, keys=['app','accepted']).reset_index()

The final order is not the same.

user_92747320

If I'm understanding your requirement correctly, you just need to reshape your dataframe, we can do this with a mix of stack, cumcount and unstack

since the levels will be mismatched, we can use a mix of droplevel and reset_index to create a tabular model.

s = df.set_index(['id','test_score','gender']).stack().reset_index(-1)
s['level_3'] = s['level_3'].str.split('_',expand=True)[0]

s1 = s.set_index([s.groupby('level_3').cumcount(),'level_3'],append=True).unstack([-1])\
      .reset_index(-1,drop=True).droplevel(0,1).reset_index()

print(s1)

level_3  id  test_score gender accepted app
0         0         350      F     True   A
1         0         350      F    False   B
2         1         450      F    False   B
3         2         500      M    False   D
4         3         325      M     True   C
5         3         325      M     True   B
6         3         325      M    False   A
7         4         433      F    False   B
user_93751020

You can concat the 3 columns into a list and then explode them using pd.Series.explode()

Here's how to do it:

import pandas as pd
import numpy as np
df = pd.DataFrame({
    'id': [0, 1, 2, 3, 4],
    'test_score': [350, 450, 500, 325, 433],
    'gender': ['F', 'F', 'M', 'M', 'F'],
    'app_0': ['A', 'B', 'D', 'C', 'B'],
    'app_1': ['B', np.nan, np.nan, 'B', np.nan],
    'app_2': [np.nan, np.nan, np.nan, 'A', np.nan],
    'accepted_0': [True, False, False, True, False],
    'accepted_1': [False, np.nan, np.nan, True, np.nan],
    'accepted_2': [np.nan, np.nan, np.nan, False, np.nan]
})
print (df)


#convert the three columns into a list
df['app'] = df[['app_0', 'app_1','app_2']].values.tolist()
df['accepted'] = df[['accepted_0', 'accepted_1','accepted_2']].values.tolist()

#then use pd.Series.explode to create the required rows
df = df.apply(pd.Series.explode).reset_index(drop=True)

#delete the unwanted columns
df = df.drop(columns = ['app_0', 'app_1','app_2','accepted_0', 'accepted_1','accepted_2'])

#Since you want to remove the NaNs of app & accepted, do a dropna()
df = df.dropna(subset=['app', 'accepted'], how='all').reset_index(drop=True)

print (df)

This will give you the desired results:

   id  test_score gender app accepted
0   0         350      F   A     True
1   0         350      F   B    False
2   1         450      F   B    False
3   2         500      M   D    False
4   3         325      M   C     True
5   3         325      M   B     True
6   3         325      M   A    False
7   4         433      F   B    False

Note that here we have the order repeat on id. Your answer had the three rows at the end. However, both of them have the same data.

user_138739800
Related