Pandas: how to change all the values of a column?

Question

I have a data frame with a column called "Date" and want all the values from this column to have the same value (the year only). Example:

City     Date
Paris    01/04/2004
Lisbon   01/09/2004
Madrid   2004
Pekin    31/2004

What I want is:

City     Date
Paris    2004
Lisbon   2004
Madrid   2004
Pekin    2004

Here is my code:

fr61_70xls = pd.ExcelFile('AMADEUS FRANCE 1961-1970.xlsx')

#Here we import the individual sheets and clean the sheets    
years=(['1961','1962','1963','1964','1965','1966','1967','1968','1969','1970'])

fr={}

header=(['City','Country','NACE','Cons','Last_year','Op_Rev_EUR_Last_avail_yr','BvD_Indep_Indic','GUO_Name','Legal_status','Date_of_incorporation','Legal_status_date'])

for year in years:
    # save every sheet in variable fr['1961'], fr['1962'] and so on
    fr[year]=fr61_70xls.parse(year,header=0,parse_cols=10)
    fr[year].columns=header
    # drop the entire Legal status date column
    fr[year]=fr[year].drop(['Legal_status_date','Date_of_incorporation'],axis=1)
    # drop every row where GUO Name is empty
    fr[year]=fr[year].dropna(axis=0,how='all',subset=[['GUO_Name']])
    fr[year]=fr[year].set_index(['GUO_Name','Date_of_incorporation'])

It happens that in my DataFrames, called for example fr['1961'] the values of Date_of_incorporation can be anything (strings, integer, and so on), so maybe it would be best to completely erase this column and then attach another column with only the year to the DataFrames?

Strings and numbers depending on whether there is only the year (like for Madrid in the example), or whether there is also the month and day (like for Pekin and Paris). — brodrigues, Sep 26 '12 at 15:21

score 169 · Answer 1 · edited Nov 26 '20 at 11:55

169

As @DSM points out, you can do this more directly using the vectorised string methods:

df['Date'].str[-4:].astype(int)

Or using extract (assuming there is only one set of digits of length 4 somewhere in each string):

df['Date'].str.extract('(?P<year>\d{4})').astype(int)

An alternative slightly more flexible way, might be to use apply (or equivalently map) to do this:

df['Date'] = df['Date'].apply(lambda x: int(str(x)[-4:]))
             #  converts the last 4 characters of the string to an integer

The lambda function, is taking the input from the Date and converting it to a year.
You could (and perhaps should) write this more verbosely as:

def convert_to_year(date_in_some_format):
    date_as_string = str(date_in_some_format)  # cast to string
    year_as_string = date_in_some_format[-4:] # last four characters
    return int(year_as_string)

df['Date'] = df['Date'].apply(convert_to_year)

Perhaps 'Year' is a better name for this column...

edited Nov 26 '20 at 11:55

Tommaso Di Noto

1,208
1
13
24

answered Sep 26 '12 at 15:20

Andy Hayden

359,921
101
625
535

1

Thanks for the answer, but it is more complicated than that: sometimes the values are something else entirely (like characters). I think it wight be simpler to completely drop this column, and then add a new one with the year, or completely replace the values by the year. – brodrigues Sep 26 '12 at 15:30
1

@cbrunos Please could you provide an example where this doesn't work? (But you can adjust the `convert_to_year` to deal with it) ...I agree that a more appropriate name would be `df['Year']`. – Andy Hayden Sep 26 '12 at 15:35
1

@cbrunos This should work fine for you: `for year in fr: df=fr[year]; df['Year_of_incorporation']=df['Date_of_incorporation'].map(convert_to_year)`. – Andy Hayden Sep 26 '12 at 18:29
These days I often do things like `df["Date"].str[-4:].astype(int)`. – DSM Jan 15 '14 at 19:05
@DSM which is super neat... ! – Andy Hayden Jan 15 '14 at 19:10
This is a fine solution if you are sure the year will always come last, and in 4 digits. If you can't assume that, you would have to use regular expressions `df["Date"].str.findall(r'\d{4}').str[0]` or a set of conditional expressions so you find the abbreviated 2 digit year within the date string. – dmvianna Jan 15 '14 at 22:48
1

@dmvianna or perhaps `s.str.extract('(?P\d{4})')` – Andy Hayden Jan 16 '14 at 00:06
Oh, wow. I didn't know `str.extract` yet. Pleased to meet it! – dmvianna Jan 16 '14 at 00:21
new to python, but shouldn't `def convert_to_year(date_in_some_format);` be `def convert_to_year(date_in_some_format):` ? I.e., a colon instead of a semi-colon. – user3076252 Aug 09 '16 at 17:43
@AndyHayden `.apply()` throwing the error: `SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy` – 5ervant - techintel.github.io Feb 04 '20 at 21:44
Perhaps the 2nd line in the function should be `year_as_string = date_as_string[-4:]` instead? – ccying Feb 16 '21 at 18:50

score 32 · Answer 2 · answered Feb 11 '18 at 21:52

You can do a column transformation by using apply

Define a clean function to remove the dollar and commas and convert your data to float.

def clean(x):
    x = x.replace("$", "").replace(",", "").replace(" ", "")
    return float(x)

Next, call it on your column like this.

data['Revenue'] = data['Revenue'].apply(clean)

score 8 · Answer 3 · edited Oct 21 '18 at 07:46

8

Or if one want to use lambda function in the apply function:

data['Revenue']=data['Revenue'].apply(lambda x:float(x.replace("$","").replace(",", "").replace(" ", "")))

edited Oct 21 '18 at 07:46

Unheilig

16,196
193
68
98

answered Oct 21 '18 at 07:25

Tanmay

111
1
6

Pandas: how to change all the values of a column?

3 Answers3

Linked