Data questions, answered
40 short answers from 8 guides. Each set links to the full guide for the worked examples and the reasoning behind the answer.
Avro vs Protobuf: Schemas, Encoding and Safe Evolution
What is the difference between Avro and Protobuf encoding?
Avro binary carries no field markers: a record with id 42 and name "Ada" is 5 bytes, and the reader walks the writer's schema in order to decode it. Protobuf puts a tag with the field number and wire type before every field, giving 7 bytes for the same record, so a reader can skip fields it does not know.
How do I add a field without breaking consumers?
In Avro, give the new field a default, otherwise every old record becomes unreadable by the new schema; for an optional field use ["null", T] with "default": null. In Protobuf, adding a field is safe because old readers skip the unknown number, but never reuse an old field number, and reserve deleted numbers and names.
Can I rename a field in Avro or Protobuf?
In Avro, renaming a field breaks schema resolution, which matches fields by name, unless you add an aliases entry. In Protobuf, a rename is safe in binary because names do not travel on the wire, only field numbers, but it breaks the proto3 JSON mapping, which uses field names.
Should I use Avro or Protobuf for Kafka?
Avro leans towards Kafka, Hadoop and data lake files, where the writer's schema sits in a schema registry and each message carries a schema ID, and Spark, Hive and Flink support it first-class. Protobuf leans towards gRPC and service-to-service RPC, with schemas compiled into each service. Both are mature and far smaller than JSON on the wire.
Why is a proto3 zero value the same as an unset field?
In proto3, a missing int32 and an explicit 0 are identical on the wire, so a reader cannot tell whether the value was set. When the difference matters, mark the field optional, which is available again since protobuf 3.15, or use a wrapper type.
Cleaning Up Text Lists: Spaces, Blank Lines, Duplicates and Commas
How do I remove duplicate lines and keep the order?
Keep the first copy of each line and drop later ones, which is what the awk one-liner awk '!seen[$0]++' does. The shell habit of sort -u or sort | uniq also removes duplicates but sorts the list as a side effect, and uniq alone only removes duplicates that sit next to each other. Remove Duplicate Lines keeps the original order and matches lines exactly and case-sensitively.
What order should I clean up a pasted list in?
Clean whitespace first, then remove blank lines, then remove duplicates, then reshape the list. Deduplication compares lines exactly, so a trailing space makes two copies of an email look different. Blank lines are values too, so one survives if you deduplicate first. Once a list is joined with commas, it is much harder to clean anything inside it.
How do I compare two lists to find items in both?
Treat the lists as sets and look for items in both, only in A and only in B. On Unix, comm does this but needs both inputs sorted first, for example comm -12 with two sorted files for items in both. Compare Two Lists needs no sorting, ignores case and surrounding spaces by default, and ignores order and repeats.
Why does splitting on commas break names like "Smith, John"?
A plain split on commas cannot tell a separator from a comma inside a quoted value, so "Smith, John", Jane becomes three items instead of two. Real CSV, as defined in RFC 4180, wraps such values in double quotes and a CSV parser respects them. If a list came from a CSV file, parse it with a CSV converter rather than a text split.
What is the difference between duplicate types and extra duplicates?
Duplicate types counts how many distinct lines repeat, while extra duplicates counts the surplus copies. If one email address appears five times, it adds 1 to duplicate types and 4 to extra duplicates. Extra duplicates always equals total lines minus unique lines, which makes it the number of rows that removing duplicates will delete.
Excel to JSON: Dates, Empty Cells and Other Spreadsheet Traps
What is inside an xlsx file?
An .xlsx file is a zip archive of XML parts defined by the Office Open XML standard, ECMA-376, also ISO/IEC 29500. Each worksheet is an XML file of cells, and each cell has a value, a type and a reference to a style that carries its number format. The older .xls format is a binary BIFF8 file with the same concepts.
What is the Excel 1900 leap year bug?
Excel treats 29 February 1900 as a real day, although 1900 was not a leap year. Lotus 1-2-3 had the bug and Excel kept it for compatibility, so Excel date serial numbers from 61 onward are one higher than a plain day count from 1 January 1900. Anyone converting serials to dates by hand has to allow for it.
What is the Excel 1904 date system?
The 1904 date system counts Excel date serial numbers from 1 January 1904 instead of the start of 1900. It comes from workbooks created by early Mac versions of Excel, and the same serial number means a date four years and a day later than under the 1900 system. Each workbook carries a flag saying which system it uses.
How are merged cells converted to JSON?
Only the top-left cell of a merged range holds a value in Excel, and the other cells in the range are empty, so they come out as null in JSON. If a sheet uses merged cells as group labels, such as a "Region" cell spanning five rows, unmerge the cells and fill the value down in Excel before converting.
How are spreadsheet headers turned into JSON keys?
JSON keys are taken from the first row exactly as written, spaces and capitals included. With the SheetJS library, an empty header cell becomes a key like __EMPTY, and a repeated header gets a suffix, so a second name column becomes name_1. Rename headers such as Start date to startDate first if your code expects camelCase.
TSV Files: The Format Rules, Quoting Traps and TSV vs CSV
What is a TSV file?
A TSV file holds tab-separated values: a header line of field names, then one record per line with fields separated by a single tab. The format is registered with IANA as text/tab-separated-values, from a 1993 registration by the University of Minnesota's Internet Gopher team. Strict TSV has no quoting or escapes, so a field may not contain a tab or line break.
How do I parse a TSV file in Python?
Use the csv module with delimiter="\t", for example csv.DictReader(f, delimiter="\t"), which follows spreadsheet-style quoting by default. For strict TSV, where quote characters are data, add quoting=csv.QUOTE_NONE. Opening the file with encoding="utf-8-sig" strips a byte order mark if one is present, and newline="" lets the module handle line endings.
How does Excel handle quotes and tabs in a tab-delimited export?
Excel's tab-delimited export borrows CSV's quoting rule. A field that contains a tab, a line break or a double quote is wrapped in double quotes, and any quotes inside it are doubled, so fits 1/2" pipe becomes "fits 1/2"" pipe". A parser must apply the same quote handling, or it will leave stray quotes and split multi-line fields into broken rows.
How do PostgreSQL and MySQL write tabs inside TSV values?
PostgreSQL's COPY TO in text format and MySQL's SELECT INTO OUTFILE use backslash escapes instead of quotes. A tab is written as \t, a line break as \n, a backslash as \ and NULL as \N, so every record stays on one physical line. Reading that output with a strict or spreadsheet-style parser leaves literal \n sequences in the data.
Why does my TSV file parse as a single column?
If every row of a TSV file parses as a single field, the separators are probably spaces rather than tabs, often because an editor was configured to expand tabs. Check the separator in a hex view: a tab is byte 09 and a space is 20. Replacing the spaces with real tab characters restores the columns.
What Is a Parquet File? Row Groups, Footers and Types Explained
What is a Parquet file?
A Parquet file is a columnar data file that stores each column's values together, compresses them as a group, and ends with a metadata footer so readers can skip what they do not need. Twitter and Cloudera created Parquet in 2013, building on Google's 2010 Dremel paper, and it became a top-level Apache project in 2015.
What is the difference between row and columnar storage?
Row storage, as in CSV or JSON lines, writes all the fields of one record before the next, which suits applications reading whole records. Columnar storage, as in Parquet, keeps each column together, so an analytics query touching two columns out of forty reads only those two. Grouping similar values also compresses better, through dictionaries and deltas.
What is a row group in Parquet?
A row group is a horizontal slice of the rows in a Parquet file, typically tens of megabytes to a gigabyte, and it is the unit of parallelism, so different workers can read different groups. Inside it, each column is stored as a column chunk split into pages, and the footer keeps min, max and null-count statistics that let queries skip whole groups.
Is Parquet better than CSV?
Parquet is better than CSV for analytics, data lakes and large tables, because it has typed columns, per-column compression, column-only reads and statistics for skipping rows. CSV is still the right choice for small exports, data exchange and anything a person will open in a spreadsheet, since it is human readable and easy to append to.
Can you edit a Parquet file in place?
No, Parquet files are immutable, so updating data means rewriting files or writing new ones. Table formats such as Delta Lake and Apache Iceberg manage those rewrites on top of Parquet. Appending is done by writing a new file or row group, and streaming jobs that write many tiny files should be compacted into larger ones on a schedule.
Which Chart Should I Use? Bar, Line, Scatter, Histogram and Pie
Is a bar chart or a pie chart better for comparing values?
A bar chart is usually better. In their 1984 "Graphical Perception" study, William Cleveland and Robert McGill found that position along a common scale is judged most accurately, while angle and area, which pie charts use, rank near the bottom. A pie works for two or three slices, but with seven similar slices nobody can tell 14% from 16%.
When should I use a line chart?
Use a line chart for a time series, such as revenue over a year. A line says the points are connected in order, which is true for time and false for most categories, so use bars for categories instead. A line chart's axis does not have to start at zero, because the reader judges slope rather than length.
What is the difference between a histogram and a bar chart?
A bar chart compares values across categories, such as sales per product. A histogram shows the distribution of one numeric column: it splits the range into equal-width bins and draws a bar for how many values fall in each. Use a histogram when you need the shape of the data, which a bar chart of the mean alone hides.
Should a bar chart axis start at zero?
Yes, a bar chart's value axis should start at zero, because the bar's length is the encoding, and a truncated axis can make a 5% difference look like 300%. Area charts should also start at zero, since the filled region is read as quantity. Line charts can start elsewhere, but say so in the axis labels.
When should I use a scatter plot?
Use a scatter plot when both columns are numbers and you want to know whether they move together, with one dot per row. Look for direction, shape and outliers: up and to the right means both rise together, a curve is not a straight line, and a lone distant dot is often a data error. Never connect scatter points with lines.
Word Clouds: How Word Frequency Becomes a Picture, and When It Misleads
What are good alternatives to a word cloud?
For anything you will make decisions on, use a table or a bar chart of the top 20 words, which shows exact counts and is easy to compare. For SEO copy, a keyword density analyzer reports each term as a percentage of the text, including two- and three-word phrases. For survey answers, read a sample of the responses behind the biggest words.
Why does a word cloud ignore "not"?
A word cloud counts individual words and throws away the sentence, so "not slow" adds to "slow" and "not helpful" adds to "helpful". A cloud of reviews can therefore make a complaint look like praise. Some stop-word lists even remove "not", which hides negation completely, so always check the source sentences behind the largest words.
Are word sizes in a word cloud accurate?
No, treat word size as a rough rank, never a measurement. People judge a word by the area it covers, which grows with the square of the font size and with the number of letters. A word drawn at 60 px covers about 25 times the area of the same word at 12 px, even when its count is only three times higher.
What is Zipf's law in word frequency?
Zipf's law says a word's frequency in natural language is roughly inversely proportional to its rank. The top handful of English words, such as "the", "of", "and", "to" and "a", make up a large share of any text and say nothing about its topic, so without a stop-word list every English word cloud looks mostly the same.
Why are plurals counted separately in a word cloud?
Most word cloud generators do no stemming, so "crash", "crashed" and "crashes" appear as three small words instead of one big one. Phrases are split too, turning "customer support" into "customer" and "support". Merge variants such as plurals and alternative spellings in the text before generating the cloud if they matter.
XLSX vs XLS vs ODS vs CSV: Choosing and Converting Spreadsheet Formats
What is the difference between XLSX and XLS?
XLSX is the Office Open XML format introduced with Excel 2007: a ZIP archive of XML parts holding up to 1,048,576 rows and 16,384 columns per sheet. XLS is the older binary BIFF8 format used by Excel 97 to 2003, limited to 65,536 rows and 256 columns per sheet. XLS is still common in legacy exports and government data portals.
What is the difference between CSV and TSV?
CSV separates fields with commas and wraps any field containing a comma, double quote or line break in double quotes, as described in RFC 4180. TSV uses tabs instead, and in its strict IANA definition has no quoting at all, so a field cannot contain a tab or newline. That makes TSV easy to parse, though Excel still quotes TSV fields when needed.
Why does Excel turn text into dates when opening a CSV?
A CSV carries no types, so Excel guesses, and text that looks date-like, such as 1-2, 3/4 or MARCH1, is converted to a date using the regional day-month order. This was common enough in genetics that the HUGO Gene Nomenclature Committee renamed genes such as SEPT1 and MARCH1 in 2020. Import through Data > From Text/CSV and set those columns to Text.
Why does Excel show long numbers as 1.23457E+17?
Excel stores numbers as 64-bit floating point and shows only 15 significant digits, so a 16-digit card number or an 18-digit order ID is rounded and displayed in scientific notation. Saving writes the rounded value back, losing the original digits. Keep such columns as text by importing through Data > From Text/CSV, or convert the CSV to XLSX with a tool that does not guess.
How does Excel store dates?
Inside a workbook, Excel stores a date as a number of days counted from the start of 1900, with a date format applied, so 15 January 2026 is stored as 46037. Times are fractions of a day, and workbooks from early Mac Excel count from 1904 instead. Writing a date cell's raw value to CSV therefore gives a meaningless 46037.