3
$\begingroup$

I have a dataframe containing several features of form:

Id, Acol,                   Bcol,   Ccol,           Dcol,
1,  X:0232,Y:10332,Z:23891, E:1222, F:12912,G:1292, V:1281
2,  X:432,W:2932            R:2392, T:292,U:29203   Q:2392
3,  Y:29320,W:2392          R:2932, G:239,T:2392    Q:2391

...about 10,000 Id's
  • where 1,2,3 are the Id's.
  • Acol, Bcol, Ccol, and Dcol are the feature columns,
  • X, Y, Z, W are sub-features of feature "Acol" and so on...

How can I extract the sub-features/features from this sort of dataframe?

$\endgroup$
  • $\begingroup$ Not sure I understand the question. If you want say to access all values in Bcol, and you loaded the dataframe into a pandas df, then simply do df['Bcol']. Was that all? $\endgroup$ – famargar Jun 5 '17 at 12:15
2
$\begingroup$

You were not specific where you wanted to end up with the data in this frame, so I will simply show how to break out the features and sub features into a format that can be pivoted into a form as needed:

Code:

Most important element is taking a feature column and breaking out the sub features. That can be done like:

def get_sub_features(feature_col):
    # split on commas and then colons
    feature_df = feature_col.str.split(',').apply(
        lambda feature: pd.Series(
            dict([sub_feature.split(':') for sub_feature in feature]),
            name=feature_col.name), 1)

    # add a feature name column to use as an index
    feature_df['feature'] = feature_col.name

    # name the columns as sub-feature for later stacking
    feature_df.columns.names = ['sub-feature']

    # return dataframe with id/feature_name index
    new_index = [feature_df.index.name, 'feature']
    return feature_df.reset_index().set_index(new_index)

Test Code:

df = pd.read_fwf(StringIO(u"""
    Id  Acol                    Bcol    Ccol            Dcol
    1   X:0232,Y:10332,Z:23891  E:1222  F:12912,G:1292  V:1281
    2   X:432,W:2932            R:2392  T:292,U:29203   Q:2392
    3   Y:29320,W:2392          R:2932  G:239,T:2392    Q:2391"""
                          ), header=1).set_index(['Id'])
print(df)

feature_cols = ['Acol', 'Bcol', 'Ccol', 'Dcol']
stacked = pd.concat(get_sub_features(df[f]).stack() for f in feature_cols)
print(stacked)

Results:

                      Acol    Bcol            Ccol    Dcol
Id                                                        
1   X:0232,Y:10332,Z:23891  E:1222  F:12912,G:1292  V:1281
2             X:432,W:2932  R:2392   T:292,U:29203  Q:2392
3           Y:29320,W:2392  R:2932    G:239,T:2392  Q:2391

Id  feature  sub-feature
1   Acol     X               0232
             Y              10332
             Z              23891
2   Acol     W               2932
             X                432
3   Acol     W               2392
             Y              29320
1   Bcol     E               1222
2   Bcol     R               2392
3   Bcol     R               2932
1   Ccol     F              12912
             G               1292
2   Ccol     T                292
             U              29203
3   Ccol     G                239
             T               2392
1   Dcol     V               1281
2   Dcol     Q               2392
3   Dcol     Q               2391
dtype: object

Accessing Data:

Some examples:

print(stacked.xs('T', level=2))
print(stacked.iloc[stacked.index.get_level_values('sub-feature') == 'T'])

Results:

               0
Id feature      
2  Ccol      292
3  Ccol     2392

                           0
Id feature sub-feature      
2  Ccol    T             292
3  Ccol    T            2392    
| improve this answer | |
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.