CODEWITHSUNDEEP
pandasdataframereplace
Dhruv Ghulati
Dhruv Ghulati

Reputation: 3026

Iterate through pandas data frame and replace some strings with numbers

I have a dataframe sample_df that looks like:

     bar   foo
0    rejected unidentified
1    clear    caution
2    caution    NaN

Note this is just a random made up df, there are lot of other columns lets say with different data types than just text. bar and foo might also have lots of empty cells/values which are NaNs.

The actual df looks like this, the above is just a sample btw:

|      |   Unnamed: 0 | user_id                          | result   | face_comparison_result   | created_at          | facial_image_integrity_result   | visual_authenticity_result   | properties      | attempt_id                       |
|-----:|-------------:|:---------------------------------|:---------|:-------------------------|:--------------------|:--------------------------------|:-----------------------------|:----------------|:---------------------------------|
|    0 |           58 | ecee468d4a124a8eafeec61271cd0da1 | clear    | clear                    | 2017-06-20 17:50:43 | clear                           | clear                        | {}              | 9e4277fc1ddf4a059da3dd2db35f6c76 |
|    1 |           76 | 1895d2b1782740bb8503b9bf3edf1ead | clear    | clear                    | 2017-06-20 13:28:00 | clear                           | clear                        | {}              | ab259d3cb33b4711b0a5174e4de1d72c |
|    2 |          217 | e71b27ea145249878b10f5b3f1fb4317 | clear    | clear                    | 2017-06-18 21:18:31 | clear                           | clear                        | {}              | 2b7f1c6f3fc5416286d9f1c97b15e8f9 |
|    3 |          221 | f512dc74bd1b4c109d9bd2981518a9f8 | clear    | clear                    | 2017-06-18 22:17:29 | clear                           | clear                        | {}              | ab5989375b514968b2ff2b21095ed1ef |
|    4 |          251 | 0685c7945d1349b7a954e1a0869bae4b | clear    | clear                    | 2017-06-18 19:54:21 | caution                           | clear                        | {}              | dd1b0b2dbe234f4cb747cc054de2fdd3 |
|    5 |          253 | 1a1a994f540147ab913fcd61b7a859d9 | clear    | clear                    | 2017-06-18 20:05:05 | clear                           | clear                        | {}              | 1475037353a848318a32324539a6947e |
|    6 |          334 | 26e89e4a60f1451285e70ca8dc5bc90e | clear    | clear                    | 2017-06-17 20:21:54 | suspected                           | clear                        | {}              | 244fa3e7cfdb48afb44844f064134fec |
|    7 |          340 | 41afdea02a9c42098a15d94a05e8452b | NaN    | clear                    | 2017-06-17 20:42:53 | clear                           | clear                        | {}              | b066a4043122437bafae3ddcf6c2ab07 |
|    8 |          424 | 6cf6eb05a3cc4aabb69c19956a055eb9 | rejected    | NaN                    | 2017-06-16 20:00:26 |

I want to replace any strings I find with numbers, per the below mapping.

def no_strings(df):
  columns=list(df)
  for column in columns:
    df[column] = df[column].map(result_map)
#We will need a mapping of strings to numbers to be able to analyse later.
result_map = {'unidentified':0,"clear": 1, 'suspected': 2,"caution" : 3, 'rejected':4}

So the output might look like:

  bar  foo
0    4    0
1    1    3
2    3    NaN

For some reason, when I run no_strings(sample_df) I get errors.

What am I doing wrong?

Upvotes: 1

Views: 232

Answers (2)

adhg
adhg

Reputation: 10863

df['bar'] = df['bar'].map(result_map)
df['foo'] = df['foo'].map(result_map)

df

    bar foo
0   4   0
1   1   3
2   3   2

However, if you wish to be on the safe side (assuming a key/value is not in your result_map and you dont want to see a NaN) do this:

df['foo'] = df['foo'].map(lambda x: result_map.get(x, 'not found'))
df['bar'] = df['bar'].map(lambda x: result_map.get(x, 'not found'))

so an out put for this df

    bar           foo
0   rejected      unidentified
1   clear         caution
2   caution       suspected
3   sdgdg         0000

will result in:

   bar              foo
0   4                0
1   1                3
2   3                2
3   not found        not found

To be extra efficient:

cols = ['foo','bar','other_columns']
for c in cols:
   df[c] = df[c].map(lambda x: result_map.get(x, 'not found'))

Upvotes: 1

wwnde
wwnde

Reputation: 26676

Lets try stack, map the dict and then unstack

df.stack().to_frame()[0].map(result_map).unstack()



    bar  foo
0    4    0
1    1    3
2    3    2

Upvotes: 1

Related Questions