Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Convert whole dataframe from lower case to upper case with Pandas

I have a dataframe like the one displayed below:

# Create an example dataframe about a fictional army
raw_data = {'regiment': ['Nighthawks', 'Nighthawks', 'Nighthawks', 'Nighthawks'],
            'company': ['1st', '1st', '2nd', '2nd'],
            'deaths': ['kkk', 52, '25', 616],
            'battles': [5, '42', 2, 2],
            'size': ['l', 'll', 'l', 'm']}
df = pd.DataFrame(raw_data, columns = ['regiment', 'company', 'deaths', 'battles', 'size'])

enter image description here

My goal is to transform every single string inside of the dataframe to upper case so that it looks like this:

enter image description here

Notice: all data types are objects and must not be changed; the output must contain all objects. I want to avoid to convert every single column one by one... I would like to do it generally over the whole dataframe possibly.

What I tried so far is to do this but without success

df.str.upper()
like image 511
Federico Gentile Avatar asked Sep 15 '16 13:09

Federico Gentile


People also ask

How do I convert a column to a DataFrame uppercase?

We can assign a column to the DataFrame by making it uppercase using the upper() method.

How do I lowercase a whole data frame?

Method 1: Using apply() function In the first method, I will use the pandas apply() method to convert the entire dataframe columns to lowercase. Here you also have to pass the lambda function that will take all the string values of the dataframe and convert them into lower cases.

How do I convert all columns to lowercase in pandas?

We can convert the names into lower case using Pandas' str. lower() function. We first take the column names and convert it to lower case. And then rename the Pandas columns using the lowercase names.


4 Answers

astype() will cast each series to the dtype object (string) and then call the str() method on the converted series to get the string literally and call the function upper() on it. Note that after this, the dtype of all columns changes to object.

In [17]: df
Out[17]: 
     regiment company deaths battles size
0  Nighthawks     1st    kkk       5    l
1  Nighthawks     1st     52      42   ll
2  Nighthawks     2nd     25       2    l
3  Nighthawks     2nd    616       2    m

In [18]: df.apply(lambda x: x.astype(str).str.upper())
Out[18]: 
     regiment company deaths battles size
0  NIGHTHAWKS     1ST    KKK       5    L
1  NIGHTHAWKS     1ST     52      42   LL
2  NIGHTHAWKS     2ND     25       2    L
3  NIGHTHAWKS     2ND    616       2    M

You can later convert the 'battles' column to numeric again, using to_numeric():

In [42]: df2 = df.apply(lambda x: x.astype(str).str.upper())

In [43]: df2['battles'] = pd.to_numeric(df2['battles'])

In [44]: df2
Out[44]: 
     regiment company deaths  battles size
0  NIGHTHAWKS     1ST    KKK        5    L
1  NIGHTHAWKS     1ST     52       42   LL
2  NIGHTHAWKS     2ND     25        2    L
3  NIGHTHAWKS     2ND    616        2    M

In [45]: df2.dtypes
Out[45]: 
regiment    object
company     object
deaths      object
battles      int64
size        object
dtype: object
like image 132
Nehal J Wani Avatar answered Oct 20 '22 09:10

Nehal J Wani


This can be solved by the following applymap method:

df = df.applymap(lambda s: s.lower() if type(s) == str else s)
like image 29
VincentQT Avatar answered Oct 20 '22 10:10

VincentQT


Loops are very slow instead of using apply function to each and cell in a row, try to get columns names in a list and then loop over list of columns to convert each column text to lowercase.

Code below is the vector operation which is faster than apply function.

for columns in dataset.columns:
    dataset[columns] = dataset[columns].str.lower() 
like image 13
Shaz Avatar answered Oct 20 '22 11:10

Shaz


Since str only works for series, you can apply it to each column individually then concatenate:

In [6]: pd.concat([df[col].astype(str).str.upper() for col in df.columns], axis=1)
Out[6]: 
     regiment company deaths battles size
0  NIGHTHAWKS     1ST    KKK       5    L
1  NIGHTHAWKS     1ST     52      42   LL
2  NIGHTHAWKS     2ND     25       2    L
3  NIGHTHAWKS     2ND    616       2    M

Edit: performance comparison

In [10]: %timeit df.apply(lambda x: x.astype(str).str.upper())
100 loops, best of 3: 3.32 ms per loop

In [11]: %timeit pd.concat([df[col].astype(str).str.upper() for col in df.columns], axis=1)
100 loops, best of 3: 3.32 ms per loop

Both answers perform equally on a small dataframe.

In [15]: df = pd.concat(10000 * [df])

In [16]: %timeit pd.concat([df[col].astype(str).str.upper() for col in df.columns], axis=1)
10 loops, best of 3: 104 ms per loop

In [17]: %timeit df.apply(lambda x: x.astype(str).str.upper())
10 loops, best of 3: 130 ms per loop

On a large dataframe my answer is slightly faster.

like image 9
IanS Avatar answered Oct 20 '22 09:10

IanS