Use string formatting in pandas DataFrame columns

Question

I have following data frame (both columns str type):

+------+-----------------+
| year | indicator_short |
+------+-----------------+
| 2020 | ind_1           |
| 2019 | ind_2           |
| 2019 | ind_3           |
| N/A  | ind_4           |
+------+-----------------+

I would like to add new column which will contain concatenation of two existing columns, but I would like them to be formatted like:

+------+-----------------+--------------------+
| year | indicator_short |   indicator_full   |
+------+-----------------+--------------------+
| 2020 | ind_1           | Indicator_1 (2020) |
| 2019 | ind_2           | Indicator_2 (2019) |
| 2019 | ind_3           | Indicator_3 (2019) |
| N/A  | ind_4           | Indicator_4 (N/A)  |
+------+-----------------+--------------------+

One thing is coming to my mind is use formatting, something like':

df['indicator_full'][df['indicator_short']=='ind_1'] = 'Indicator_1 ({})'.format(df['year'])

but it gives wrong result.

yatu · Accepted Answer · 2020-09-21 09:23:55Z

3

I'd go with string concatenation and formatting the string column as:

years = '('+df['year'].astype(str).str.replace(r'.0$','')+')' 
# years =  '('+df['year']+')' if the year col is a string
df['indicator_full   '] = ('Indicator_'+df.indicator_short.str.rsplit('_').str[-1]) \
                                          .str.cat(years, sep=' ')

print(df)
     year indicator_short   indicator_full   
0  2020.0           ind_1  Indicator_1 (2020)
1  2019.0           ind_2  Indicator_2 (2019)
2  2019.0           ind_3  Indicator_3 (2019)
3     NaN           ind_4   Indicator_4 (nan)

answered Sep 21, 2020 at 9:23

yatu

88.6k12 gold badges93 silver badges148 bronze badges

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

Comments

jezrael · Accepted Answer · 2020-09-21 09:30:13Z

2

Use Series.str.extract for get integers from indicator_short, get integers from floats in year column and last join together:

i = df['indicator_short'].str.extract('(\d+)', expand=False)
y = df['year'].astype('Int64').astype(str).replace('<NA>','N/A')

df['indicator_full'] = 'Indicator_' + i + ' (' + y + ')'
print (df)
0  2020.0           ind_1  Indicator_1 (2020)
1  2019.0           ind_2  Indicator_2 (2019)
2  2019.0           ind_3  Indicator_3 (2019)
3     NaN           ind_4   Indicator_4 (N/A)

edited Sep 21, 2020 at 9:30

answered Sep 21, 2020 at 9:23

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Comments

wwnde · Accepted Answer · 2020-09-21 09:35:39Z

2

Use .str.cat() to concat the two columns after replacing ind with Indicator using .str.replace.

df['indicator_full']=(df.indicator_short.str.replace('ind','Indicator')).str.cat("("+df['year']+ ")", sep=(" ") )

edited Sep 21, 2020 at 9:35

answered Sep 21, 2020 at 9:29

wwnde

26.7k6 gold badges22 silver badges38 bronze badges

Collectives™ on Stack Overflow

Use string formatting in pandas DataFrame columns

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related