I have a pandas dataframe "df". In this dataframe I have multiple columns, one of which I have to substring.Lets say the column name is "col".I can run a "for" loop like below and substring the column:
for i in range(0,len(df)):df.iloc[i].col = df.iloc[i].col[:9]But I wanted to know, if there is an option where I don't have to use a "for" loop, and do it directly using an attribute.I have huge amount of data, and if I do this, the data will take a very long time process.
Best Answer
Use the str accessor with square brackets:
df['col'] = df['col'].str[:9]Or str.slice:
df['col'] = df['col'].str.slice(0, 9)In case the column isn't a string, use astype to convert it:
df['col'] = df['col'].astype(str).str[:9]As one doesn't know exactly OP's dataframe, one can create one to be used as test.
df = pd.DataFrame({'col': {0: '2020-12-08', 1: '2020-12-08', 2: '2020-12-08', 3: '2020-12-08', 4: '2020-12-08', 5: '2020-12-08', 6: '2020-12-08', 7: '2020-12-08', 8: '2020-12-08', 9: '2020-12-08'}})[Out]:col0 2020-12-081 2020-12-082 2020-12-083 2020-12-084 2020-12-085 2020-12-086 2020-12-087 2020-12-088 2020-12-089 2020-12-08Assuming one wants to store the column in the same dataframe df, and that we want to keep only 4 characters, on a column called col_substring, there are various options one can do.
Option 1
Using pandas.Series.str
df['col_substring'] = df['col'].str[:4][Out]:col col_substring0 2020-12-08 20201 2020-12-08 20202 2020-12-08 20203 2020-12-08 20204 2020-12-08 20205 2020-12-08 20206 2020-12-08 20207 2020-12-08 20208 2020-12-08 20209 2020-12-08 2020Option 2
Using pandas.Series.str.slice as follows
df['col_substring'] = df['col'].str.slice(0, 4)[Out]:col col_substring0 2020-12-08 20201 2020-12-08 20202 2020-12-08 20203 2020-12-08 20204 2020-12-08 20205 2020-12-08 20206 2020-12-08 20207 2020-12-08 20208 2020-12-08 20209 2020-12-08 2020or like this
df['col_substring'] = df['col'].str.slice(stop=4)Option 3
Using a custom lambda function
df['col_substring'] = df['col'].apply(lambda x: x[:4])[Out]:col col_substring0 2020-12-08 20201 2020-12-08 20202 2020-12-08 20203 2020-12-08 20204 2020-12-08 20205 2020-12-08 20206 2020-12-08 20207 2020-12-08 20208 2020-12-08 20209 2020-12-08 2020Option 4
Using a custom lambda function with a regular expression (with re)
import redf['col_substring'] = df['col'].apply(lambda x: re.findall(r'^.{4}', x)[0])[Out]:col col_substring0 2020-12-08 20201 2020-12-08 20202 2020-12-08 20203 2020-12-08 20204 2020-12-08 20205 2020-12-08 20206 2020-12-08 20207 2020-12-08 20208 2020-12-08 20209 2020-12-08 2020Option 5
Using numpy.vectorize
df['col_substring'] = np.vectorize(lambda x: x[:4])(df['col'])[Out]:col col_substring0 2020-12-08 20201 2020-12-08 20202 2020-12-08 20203 2020-12-08 20204 2020-12-08 20205 2020-12-08 20206 2020-12-08 20207 2020-12-08 20208 2020-12-08 20209 2020-12-08 2020Note:
- The ideal solution would depend on the use case, constraints, and the dataframe.