Skip to content
academia.sh
tr
Log in

Unit 02 of 04

Dataframes

Making the decisions that labeled data structures take on visible one by one: the number of rows an index alignment produces in an addition, the column that type inference silently misreads, label-based and position-based slicing returning different rows, a missing value giving three different filter results in three comparisons, a missing value in a group key silently dropping, the number of rows and the fan-out produced by inner, outer and cross joins, whether the empty period is counted as zero or skipped in a resample, the behavior of vectorized string operations on a missing value.

Published Updated

Lessons

  1. 01 Series and Dataframe January and February readings are added per subscriber, and the same two series give three different results under three join modes: positional addition produces 1,101 rows and only 4 of them point to the same subscriber under both labels, yet its total of 44,271.89 m³ is within 119.13 m³ of the correct answer. Inner alignment by label also produces 1,101 rows and all of them are correct; outer alignment by label produces 1,260 rows and 159 of them are missing. Manual alignment without a label index makes 781,710 label comparisons, while adding the series reaches the same result in 2,361 passes. Counting the missing side as zero moves the total from 44,391.02 m³ to 47,707.44 m³. 15 min read
  2. 02 Reading and Writing Data A file of 3,199 rows and 7 columns, 139,238 bytes in size, is read with a single call, and type inference returns 3 of the 7 columns with the wrong type: the subscriber number is an integer, so its average can be computed as 10,690.5; the average of the tariff tier comes out as 27.4 m³; the 3,008 numeric values in the meter reading column turn into text because of 80 'n/a' entries, and the largest value of the column becomes 'n/a'. When inference is made from the first 100 rows instead of all rows, the pass count drops from 22,393 to 700, but the meter number slides to integer: 1,123 cells of the lettered codes that start at row 2,077 become missing without producing any warning, and 3,199 cells change in the round trip. The format loss is 422 cells under both inferences. 4 min read
  3. 03 Indexing and Slicing The same two ends, 10,100 and 10,200, return four different row counts in four separate spellings: sliced by label it is 95 rows because a label slice includes the end, sliced by the same two positions it is 94 rows because a position slice leaves the end out, written with the ends mistaken for positions it is 100 rows, and in a long table with a non-unique index it is 230 rows. When the frame is sorted by total consumption the label index stops being sorted and the same slice returns 617 rows; an end that is not in the index gives a slice of 22 rows in a sorted index and raises an error in an unsorted one. A linear scan without an index takes 2,658 passes, while binary search in the label index reaches the same 95 rows in 21 passes. 4 min read
  4. 04 Filtering and Querying Readings with a meter reading above 120,000 are filtered with three separate spellings and three different row counts come out: the numeric comparison gives 1,835 rows and 36,435.83 m³, the negation of the same condition gives 2,026 rows and 40,356.71 m³, and the same comparison with the column read as text gives 1,915 rows and 38,035.93 m³. The difference is in the missing values: the negation takes in all 191 missing rows, the text comparison takes in the 80 'n/a' cells because a letter counts as greater than a digit. When two conditions are combined the difference grows, and two expressions written as equivalent in logic return 446 and 502 rows. A manual filter chain builds an intermediate table of 16,184 bytes in 5,034 passes, while the mask makes 6,398 passes and holds 3,399 bytes. 8 min read
  5. 05 Grouping and Aggregation The split, apply and combine steps fit into a single call, and that call carries two decisions at once: the 160 rows whose group key is empty appear in no group under the default grouping, and the total of the five-row summary comes out as 60,025.56 m³ instead of 63,060.63 m³, that is, 4.81% of the dataset drops out without producing any warning. When the missing key is kept, the summary has 6 rows and the dropped part becomes visible as 3,035.07 m³. The second decision is the denominator of the aggregation: the north group holds 878 rows but the meter reading column has 831 filled values, and the average comes out as 126,247.9 or 119,489.8 depending on the denominator. A separate loop per measure costs 15,995 key lookups, while a single split feeds five measures with 3,199 lookups. 15 min read
  6. 06 Join Operations The reading table of 3,199 rows and the visit table of 818 rows are joined on the same key with four modes and four different row counts come out: inner join 1,965, left outer 3,820, full outer 3,856, cross join 2,616,782 rows. The inner join drops 1,855 readings and opens the remaining 1,344 rows into 1,965 rows; this fan-out of 621 rows moves the total from 26,450.81 m³ to 38,701.46 m³, that is, it produces 12,250.65 m³ of excess counting. Although the relationship of the key is many-to-many, it is verified nowhere: the key of 468 rows on the left side corresponds to more than one row on the right, and these 468 rows silently open into 1,089 rows. A hand-written nested loop makes 2,616,782 comparisons, while an indexed join finishes in 4,017 passes. 14 min read
  7. 07 Time Series Operations 3,199 readings are reduced to seven-day periods and two answers come out for the 5 periods that carry no record: if they are counted as zero the series has 13 rows, if they are skipped it has 8 rows. The total is 63,060.63 m³ in both and does not change, but every calculation after the total does. The weekly average moves between 4,850.82 m³ and 7,882.58 m³; of the 12 period-to-period percent change ratios, 5 are left undefined because they fall on a zero denominator, while none of the 7 ratios of the skipped series is undefined; the three-period moving average splits between 708.03 m³ and 9,387.54 m³ in the period of 2026-02-12. The same dataset gives 3 periods with a thirty-day step and none of them is empty: what creates the empty period is not the data but the step decision. 14 min read
  8. 08 Text Operations Of the 3,199 rows of the address code column, 2,943 are complete, 160 are empty and 96 do not carry the third part. The hand-written loop fails at row 20 and stops, producing 0 rows; the vectorized chain that does the same job produces all 3,199 rows, raises no error at all and silently turns 256 rows into missing. The filter that comes after the chain leaves these 256 rows outside on equality and takes them in on inequality: 1,004 and 2,195 rows, whose sum is exactly the dataset. Because the first step of the chain converts the code to lowercase, the uppercase filter returns 0 rows and that does not raise a warning either. A text column with three intermediate series holds 32.6 bytes of pointers per row, and the same structure reaches 0.61 gibibytes at 20 million rows. 15 min read

Start typing to search.

↑↓ ↵ Esc navigate · open · close