Set index in pandas

Question

I am working on the Olympics dataset related to this

This is what the dataframe looks like:

                Unnamed: 0  # Summer  01 !  02 !  03 !  Total  # Winter  \
0        Afghanistan (AFG)        13     0     0     2      2         0   
1            Algeria (ALG)        12     5     2     8     15         3   
2          Argentina (ARG)        23    18    24    28     70        18   
3            Armenia (ARM)         5     1     2     9     12         6   
4  Australasia (ANZ) [ANZ]         2     3     4     5     12         0

I want to do the following things:

Split country name and country code and add country name as data frame index
Remove extra unnecessary characters from country name.

For example the updated column should be:

                    Unnamed: 0  # Summer  01 !  02 !  03 !  Total  # Winter  \
0        Afghanistan         13     0     0     2      2         0   
1            Algeria         12     5     2     8     15         3   
2          Argentina         23    18    24    28     70        18   
3            Armenia          5     1     2     9     12         6   
4         Australasia         2     3     4     5     12         0

Please show me a proper way to achieve this.

Possible duplicate of [Pandas DataFrame, how do i split a column into two](https://stackoverflow.com/questions/14745022/pandas-dataframe-how-do-i-split-a-column-into-two) — ASGM, Sep 20 '17 at 13:34
What extra characters do you want to remove ? Can you show an example ? — Spandan Brahmbhatt, Sep 20 '17 at 13:42

Bharath M Shetty · Accepted Answer · 2017-09-20T13:52:04.523

3

You can use regex and replace to that i.e

df = df.replace('\(.+?\)|\[.+?\]\s*','',regex=True).rename(columns={'Unnamed: 0':'Country'}).set_index('Country')

Output:

               Summer  01 !  02 !  03 !  Total  Winter
Country                                               
Afghanistan        13     0     0     2      2       0
Algeria            12     5     2     8     15       3
Argentina          23    18    24    28     70      18
Armenia             5     1     2     9     12       6
Australasia         2     3     4     5     12       0

If you dont want to rename then .set_index('Unnamed: 0')

Or Thanks @Scott a much easier solution is to split by ( and select the first element i.e

df['Unnamed: 0'] = df['Unnamed: 0'].str.split('\(').str[0]

edited Sep 20 '17 at 13:52

answered Sep 20 '17 at 13:45

Bharath M Shetty

30,075
6
57
108

1

Thanks a lot. Had troubles on thinking how to split or use regex. Made it clear! – Akhilesh Chobey Sep 20 '17 at 13:47
1

`df['Unnamed: 0'] = df['Unnamed: 0'].str.split('\(').str[0]` – Scott Boston Sep 20 '17 at 13:48
@ScottBoston So sorry I dint see the `\(` there. So stubborn me. – Bharath M Shetty Sep 20 '17 at 13:51

score 1 · Answer 2 · answered Sep 20 '17 at 16:31

Splitting to get two columns, country and Country Code and setting country as index:

df2 = pd.DataFrame(df.Unnamed.str.split(' ',1).tolist(), columns = ['Country', 'countryCode']).set_index('Country')

You could also add country code as an additional info in your dataframe.

Removing the extra thing, as I suppose like: [ANZ], using regex (as mentioned in other answer)

df2 = df2.replace('\[.*?\]','', regex=True)

Set index in pandas

2 Answers2