pandas · Chapter 10 of 22

Cleaning text with .str

By the end of this chapter you can clean a column of text with the .str accessor: take off spaces, fix capitals, put one name in place of another, find values that contain, start or end with something, cut a code into its parts, and turn text like "Rs. 2,549" into a number. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.

  • Part: Selecting and cleaning
  • 4 tasks
  • pandas
  • In the all labs plan

The first 2 chapters of pandas are free. This one comes with the all labs plan.

Why you need this

People type, and people type differently. The website's city is typed by the customer, so Pune arrives as Pune, pune, PUNE, Pune and Pune . The same column has 86 different spellings of 16 cities, including the old names: Bombay, Calcutta, Bangalore. Phone orders put skus in by hand, some in small letters, some with a space at the end. The marketplace writes its prices as text, ₹1,299.00, and the courier's refunds as Rs. 2,549. The returns' reasons are free text.

None of the earlier chapters can fix this. A filter compares exact text, so it misses every other spelling, silently:

lines = pd.read_csv("/workspace/data/orders_site.csv")
print(lines["city"].nunique())
pune = lines["city"].str.strip().str.lower() == "pune"
print((lines["city"] == "Pune").sum(), pune.sum())
86
752 827

city == "Pune" finds 752 lines; there are 827. Shabnam's pop-up in Pune would be planned on a tenth too few customers, and every city's figure is wrong in its own way. Harpreet cannot add up the marketplace's prices at all, because text does not add up into rupees. The second line of that code, with .str, is this chapter.

What you build: 4 tasks

Each task has tests. You write the code in the lab's editor and press Check.

  1. Task 1

    clean_skus.py: the skus typed by hand

    Write two functions in clean_skus.py. skus is a Series of skus, such as the sku column of the website's lines (orders_site.csv), where phone orders were typed by hand: some in small letters, some with a space at an end. The catalogue writes every sku in capitals with no spaces.

    clean_skus(skus) returns a Series of the skus in capitals with the spaces at both ends taken off, with the same labels and the same name as skus, in the same order: " kg-sar-007 " becomes "KG-SAR-007".

    typed_by_hand(skus) returns how many skus clean_skus changes, as one whole number.

    The Check tries the website's sku column and a small Series of 4 skus named sku, labelled 3 to 6, with spaces at either end and mixed capitals.

  2. Task 2

    market_prices.py: the marketplace's prices as numbers

    Write price_numbers(prices) in market_prices.py. prices is a Series of price text, such as the Selling Price column of the marketplace's orders (the Orders sheet of orders_marketplace.xlsx, read with sheet_name="Orders" and skiprows=2), written like "₹1,299.00".

    Return a Series of the prices as decimal numbers, with the same labels and the same name as prices, in the same order: "₹1,299.00" becomes 1299.0 and "₹1,00,000.00" (over a lakh, two commas the Indian way) becomes 100000.0.

    The Check tries the marketplace's Selling Price column and a small Series of 4 prices named Selling Price, labelled 8 to 11, with prices over a lakh and prices with paise.

  3. Task 3

    return_reasons.py: five plain reasons for a return

    Write plain_reasons(reasons) in return_reasons.py. reasons is a Series of text, such as the reason column of returns.csv, where the courier typed 20 spellings of five reasons. Looking at each reason in small letters: one that contains "size" becomes "size issue"; one that contains "colour" or "color" becomes "colour not as shown"; "damaged" becomes "damaged in transit"; "wrong" becomes "wrong item"; and "mind" becomes "changed mind". Every reason contains exactly one of these words.

    Return a Series of the five plain reasons, with the same labels and the same name as reasons, in the same order: " Size Issue " and "SIZE issue" both become "size issue".

    The Check tries the returns' reason column and a small Series of 6 new spellings named reason, labelled 40 to 45, such as "Colour faded", "Changed my mind" and "Color looks dull".

  4. Task 4 · interview question

    Interview: city_lines.py, the lines a city filter missed

    Harpreet's city_lines(lines, city) returns lines[lines["city"] == city], and for Bengaluru it misses 155 order lines; every other city misses some too. Fix it. lines is the website's lines, as read_csv reads orders_site.csv; city is a city's plain name, such as "Bengaluru" or "Delhi". A line is in city when its city, with the spaces at both ends taken off and a capital at the start of each word, is city, or is an old name for it in the starter's OLD_NAMES (Bangalore, Bombay, Calcutta, Gurgaon, Banaras and New Delhi).

    Return a DataFrame of those lines, with every column as it was (the city column keeps its own spelling), in the table's order, with their own labels.

    The Check tries "Bengaluru", "Delhi" (which takes in New Delhi), "Patna", and a small table of 5 lines labelled 50 to 54 with new spellings, with "Bengaluru" and "Delhi".

    Devika will ask: why did city == "Bengaluru" miss 155 order lines, and what would you check before trusting a filter on text?

What you will learn

  • The .str accessor: string methods that work on every value of a Series at once, skip missing values, and return a Series.
  • strip, lower, upper, title and len: spaces off, capitals fixed, lengths.
  • replace: a whole value for another with a dict, or a piece of text inside each value with .str.replace (plain or a regex).
  • contains, startswith, endswith and match: masks from text.
  • split, str[...] and slice: a code cut into its parts.
  • extract: the piece a regular expression finds, such as the number in "Rs. 2,549".

More in Selecting and cleaning

Build "Cleaning text with .str" yourself

Kargha, a Varanasi handloom brand, sells through a website, a marketplace and a store. You make one true picture of its sales. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.

The first 2 chapters of pandas are free. This one comes with the all labs plan.