Reading a Csv File in Python Loses the First Data

Python Pandas Reading a CSV

Learn how to read a CSV file and create a Pandas DataFrame

Photo past Mika Baumeister on Unsplash

Introduction

Equally a Data Analyst or Data Scientist, you lot volition frequently have to combine and analyse information from various data sources. A information type I normally get requested to analyse is CSV files. CSV files are popular within the corporate globe equally they tin can handle powerful calculations, are like shooting fish in a barrel to use and are often the output blazon for corporate systems. Today we will demonstrate how to use Python and Pandas to open and read a CSV file on your local automobile.

Getting Started

You can install Pand a via pip from PyPI. If this is your showtime fourth dimension installing Python packages, delight refer to Pandas Series & DataFrame Explained or Python Pandas Iterating a DataFrame. Both of these articles will provide you with the installation instructions and background cognition for today's commodity.

Syntax

The virtually challenging part for me when learning Pandas was the number of tutorials at that place was for Pandas functions such as .read_csv(). However, the tutorials tended to miss the intricacies you needed when dealing with real-world data. In the beginning, I oft found myself having to post questions on StackOverflow to acquire how to employ specific parameters. Below we accept included all the parameters forth with examples for the more conceptually complex.

The above python snippet shows the syntax for Pandas read csv function.

The above syntax might seem complicated at start; however, we would only set a handful of parameters as the majority are assigned default values. Nevertheless, the number of parameters bode to the powerful and flexible nature of Pandas .read_csv().

Parameters

• filepath_or_buffer: You tin pass in a string or path object that references the CSV file yous would like to read. The parameter also accepts URLs that point to a location on a remote server.

The in a higher place Python snippet shows how to read a CSV by providing a file path to the filepath_or_buffer parameter.

• sep & delimiter: The delimiter parameter is an alias for sep. You can utilise sep to tell Pandas what to utilise as a delimiter, by default this is ,. Even so, you can laissez passer in regex such as \t for tab spaced information.
• header: This parameter allows y'all to pass an integer which captures which line the CSVs header names are on. By default, header is ready to infer which means Pandas will have the headers from row 0. If you intend on renaming the default headers, then set header to 0.
• name: Here y'all have the opportunity to override the default column headers. To do this, first set header=0, so pass in an array which contains the new cavalcade names you would like to employ.

The above Python snippet renames the headers from the original CSV. A copy of the CSV that we used for the higher up case can be found here.

• index_col: For those of you that are new to the DataFrame object, DataFrame rows each have a label known as an index. You can pass a column proper name or integer if the CSV file contains a column representing an alphabetize. Alternatively, yous can pass False to tell Pandas not to utilize an index from your file. If Fake is passed, Pandas will create an index using a sequence of incrementing integers.
• usecols: You can use this parameter to render a subset of all the columns in the file. By default, usecols is prepare to None which volition issue in Pandas returning all columns in the DataFrame. This comes in handy when you are only interested in processing certain columns.

Inside the to a higher place Python snippet, we take told Pandas that we only wish to read columns 1 & 2. You can test out the in a higher place snippet using this CSV.

• squeeze: When dealing with a single cavalcade CSV file, you can set this parameter to True which will tell Pandas to return a Series equally opposed to a DataFrame. If you are unfamiliar with Pandas Series, you can refer to Pandas Series & DataFrame Explained for an overview.
• prefix: Here you can set column label prefixes if you haven't specified whatsoever to use. The default behaviour, when columns aren't specified, is to use an integer sequence to characterization them. Using this parameter, you could set columns 0, i, and 2 to column_0, column_1 and column_2.

In the above Python snippet, nosotros have attempted to read a CSV file without headers. By default, Pandas will add numeric column labels. Above we have prefixed the columns with 'column_'. The CSV we take used for this case can be found here.

• mangle_dupe_cols: If the CSV file you are reading contains columns with identical names Pandas will add an integer suffix to each duplicate. In the hereafter mangle_dupe_cols will accept Fake which will cause the duplicate columns to overwrite each other.
• dtype: You can utilize this parameter to laissez passer a dictionary that will take column names equally the keys and data types as their values. I find this handy when yous take a CSV with leading zippo-padded integers. Setting the correct data type for each column will besides amend the overall efficiency when manipulating a DataFrame.

Above nosotros have ensured that the employee_no cavalcade will be cast to a string. This also demonstrates the ability to retain leading zeros as yous'll discover job_no inside the dataset has non been cast and therefore has lost its leading zippo. The dataset tin be plant here.

• engine: Currently, Pandas accepts c or python equally the parsing engine.
• converters: This follows like logic to dtype, withal, instead of passing information types, you can laissez passer functions that will manipulate values within particular columns on read.

The higher up Python snippet is going to utilise the double_number() function we accept defined equally a converter to column i. Our example CSV file can be establish here.

• true_values & false_values: This parameter is quite nifty. Say for instance within your CSV you had a column that independent yes and no, y'all could map these values to Truthful and False. Doing this will allow you to clean some of your data when reading the file into Pandas.

The above Python snippet demonstrates how to define True & False values. Hither we have set both yes & mayhap to Truthful and no to Simulated. An example CSV tin can exist found here.

• skipinitialspace: Y'all tin set this parameter to True, to tell Pandas that in that location may be rows with leading spaces after the delimiter. Pandas volition then driblet any leading spaces subsequently a delimiter and before any not-delimiter character.
• skiprows: When dealing with organisation generated CSV files, sometimes the file can incorporate parameter lines at the start of the file. Frequently we volition non want to process these lines, instead, skip them. Y'all tin can fix skiprows to an integer which will indicate the number of lines to skip earlier first reading. Alternatively, you lot tin supply a callable which will cause Pandas to skip a row when the function evaluates to Truthful.
• skipfooter: Similiar to skiprows this parameter allows you to tell Pandas how many rows to skip at the end of the file. Again, this is handy if report parameters are at the end of the CSV file.
• nrows: You can utilise this to ready a limit to the number of rows collected from the CSV file. I find this handy during the exploratory phase when trying to get a feel for the data. Information technology means that you can test your logic without having to load big files into memory.
• na_values: By default, Pandas has an extensive collection of values that go mapped to NaN (Not a Number). If you have application-specific values that y'all need to clean and map, you tin can pass them to this parameter. Using this parameter means that you lot tin capture all values that are NaN which can all be mapped to a default preprocessing.
• keep_default_na: This parameter can either be set up to True or Fake. If False and the CSV contains default NaN values so Pandas will retain the original NaN value. If True Pandas volition parse the NaN value and mask with NaN in the DataFrame.
• na_filter: Y'all tin fix this to True when you would similar Pandas to interpret your data for missing values. Every bit a tip, prepare this parameter to False when reading large files that you know doesn't have whatever missing values.
• verbose: By default, this is set to False. Setting verbose to True volition output additional data to the console, such as the number of NaN values or how long specific processes took.
• skip_blank_lines: Sometimes, the data nosotros receive may contain blank lines. By setting skip_blank_lines to Truthful, Pandas volition skip these rows as opposed to counting them as NaN values.
• parse_dates: Use this parameter to tell Pandas how you would like dates within the CSV file to be interpreted. You can laissez passer True, which will cause Pandas to parse the index every bit a date. Alternatively, you can pass a cavalcade name or a listing of columns which Pandas will use to create a appointment.

The in a higher place Python snippet is going to turn column 0 into the alphabetize and parse it every bit a Appointment. The CSV file used for this example can be found here. When running the in a higher place script take notation of the changes fabricated to cavalcade 0 with regards to the DateTime format.

• infer_datetime_format: You tin set this parameter to True which will tell Pandas to infer the date-fourth dimension format. Doing this will lead to greater processing speed when combined with parse_dates.
• keep_date_col: If you have set a value for parse_dates, you can use this parameter to retain the columns that created the data. The default behaviour is to drop these columns in place. If you don't wish for this behaviour to occur set keep_date_col to True.

The above Python script is going to create a date past attempting to parse columns 0, ane & 2. Additionally, keep_date_col has been set to Truthful which volition consequence in columns 0, 1 & 2 being retained. Our instance CSV tin can be found hither.

• date_parser: If you already know the format for the date within your CSV yous can pass a office to date_parser to format the appointment-time efficiently instead of inferring the format.
• dayfirst: Pass True if your appointment-time format is DD/MM.
• cache_dates: By default, this is set to Truthful. Pandas will create a unique set of engagement-time string conversion to speed up the transformation of duplicate strings.
• iterator: Setting this parameter to True will allow you to call the Pandas function .get_chunk(), which volition render the number of records to process.
• chunksize: This will allow you to set the size of the chunks within a DataFrame. Doing this comes in handy as you tin can loop with a portion of the DataFrame instead of lazy loading the entire DataFrame in memory.
• compression: If the information that you are reading is compressed on deejay, and then y'all tin can set the blazon of compression for on the fly decompression.
• thousands: This is the separator character for the thousands unit. In CSV files you tin can sometimes see one meg represented as 1_000_000 as , is used equally the delimiter. Setting thousands to _ will upshot in 1_000_000 reflecting as 1000000.
• decimal: You can provide the character that represents decimals inside the CSV file if it deviates from ..
• lineterminator: If you have set engine to c you lot can utilise this parameter to tell Pandas what grapheme y'all expect the lines to end using.
• quotechar: This is the grapheme used throughout your CSV file that signifies the start and end of a quoted chemical element.
• quoting: Here you lot tin can prepare the level of quoting you would like applied to your elements if whatsoever. Past default, this is 0 which set quoting to minimal; you can also fix this to one — quote all, two — quote non-numeric or iii — quote none.
• doublequote: Y'all can use this parameter to tell Pandas what to do when ii quote characters appear within a quote element. When True is passed, the double-quote characters volition get single quote characters.
• escapechar: String of length one, which Pandas volition use to escape other characters.
• comment: You can apply this parameter to bespeak that you don't want the remainder of the line candy. For instance, if comment was ready to # and # appeared inside the current line, Pandas would move to the next line later on reaching #.
• encoding: If y'all are consuming data other than English, set this value to the specific character encoding so the data tin can be correctly read.
• dialect: A CSV dialect is a set of parameters that tell a CSV parser how to read a CSV file. Common dialects include excel, excel-tab and unix additionally, you can create your own and pass it to Pandas.
• error_bad_lines: If Pandas encounters a line with two many attributes typically an exception is raised and Python halts the execution. If you pass False to error_bad_lines then any lines that would generally raise this type of exception will be dropped from the DataFrame.
• warn_bad_lines: If you have set error_bad_lines to False, yous can set warn_bad_lines to Truthful which will output each line that would accept raised an exception.
• delim_whitespace: This parameter is similar to delimiter notwithstanding it is only whitespace specific. If you would like spaces as the delimiter, then you tin can either set delimiter to \s+ or delim_whitespace to True.
• low_memory: Past default, Pandas had this ready to Truthful which results in chunked processing, however, runs the risk of mismatched type inferencing. You tin avoid possible type mismatching by ensuring you set the dtype parameter.
• memory_map: If you accept passed a file to filepath_or_buffer Pandas maps the file object in retentivity to better its efficiency when processing larger files.
• float_precision: Here yous can fix the appropriate converter for the c engine for float elements.
• storage_options: You tin use this parameter to pass specific options when reading a CSV file from a remote location.

Where to Next

At present that you have wrapped your head effectually how to use Pandas .read_csv() our recommendation would exist to learn more than about the Pandas information structures through Pandas Serial & DataFrame Explained or learn how to navigate a DataFrame in Python Pandas Iterating a DataFrame. If you have a grasp of those concepts already, your next pace should exist to read either Pivoting a Pandas DataFrame or How to Combine Python, Pandas & XlsxWriter.

Summary

Learning how to apply Pandas .read_csv() is a crucial skill you should have as a Information Analyst to combine various information sources. As you have seen to a higher place .read_csv() is an extremely powerful and flexible tool that you tin can suit to various existent-world situations to begin your data collection and analysis.

Thank you for taking the fourth dimension to read our story — we hope y'all take found information technology valuable.

hagansleations.blogspot.com

Source: https://towardsdatascience.com/python-pandas-reading-a-csv-7865a11939fd

Share this post