Convert dataframe index to string and substring?

Question

Wondering whats the easiest way to split the following dataframes index into substrings and set the second piece as a column in the new dataframe.

Input:
          Ask    Bid   Last Open_Int Vol
245.0P  11.36  11.15  10.41       37  30
225.0C  10.31  10.23   10.3       52   5
224.5C  10.78  10.67     12       72  72
223.5C  11.68  11.56  12.68       89  59
244.5P  10.83  10.64   8.65      118  22
244.0P  10.34  10.15   9.93      137  10

Output:
          Ask    Bid   Last Open_Int Vol Type
245.0P  11.36  11.15  10.41       37  30 P
225.0C  10.31  10.23   10.3       52   5 C
224.5C  10.78  10.67     12       72  72 C
223.5C  11.68  11.56  12.68       89  59 C
244.5P  10.83  10.64   8.65      118  22 P
244.0P  10.34  10.15   9.93      137  10 P

How would you split exactly? I mean how would you split 245.0P? — Peaceful
– Peaceful, Commented Apr 13, 2017 at 18:41
[245.0, P]. I dont know, substring, with the lengths of the string as the inputs to substring? And then df['Type'] = substring_out[1]. I can figure it out by iterating over the df, but theres the fancy lambda map stuff that can do this all in one line. Wondering if anyone can show me that trick again. — nicholas.reichel
– nicholas.reichel, Commented Apr 13, 2017 at 18:43

piRSquared · Accepted Answer · 2017-04-13 18:44:03Z

6

df.assign(Type=df.index.str[-1])

          Ask    Bid   Last  Open_Int  Vol Type
245.0P  11.36  11.15  10.41        37   30    P
225.0C  10.31  10.23  10.30        52    5    C
224.5C  10.78  10.67  12.00        72   72    C
223.5C  11.68  11.56  12.68        89   59    C
244.5P  10.83  10.64   8.65       118   22    P
244.0P  10.34  10.15   9.93       137   10    P

answered Apr 13, 2017 at 18:44

piRSquared

296k68 gold badges509 silver badges654 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Peaceful Over a year ago

You are so fast!

nicholas.reichel Over a year ago

I get error. Probably because my dataframe is a json doc thats read from disk and making some encoding issue? AttributeError: Can only use .str accessor with string values (i.e. inferred_type is 'string', 'unicode' or 'mixed')

piRSquared Over a year ago

@mobone use df.assign(Type=df.index.astype(str).str[-1])

nicholas.reichel Over a year ago

Was my mistake. I had df and option_df, used the wrong dataframe. Thanks!

Alexey Trofimov · Accepted Answer · 2017-04-13 18:46:02Z

2

For your very example this is the solution:

df['type'] = df.index.str[-1]

answered Apr 13, 2017 at 18:46

Alexey Trofimov

5,0672 gold badges24 silver badges30 bronze badges

Comments

Quentin · Accepted Answer · 2017-04-13 18:46:13Z

0

If your index is consistent, here's a straightforward approach to this problem df['Type'] = [str(_)[-1] for _ in df.index]

answered Apr 13, 2017 at 18:46

Quentin

7004 silver badges10 bronze badges

Collectives™ on Stack Overflow

Convert dataframe index to string and substring?

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related