Chapter 4 importing exporting Data between CSV files

Chapter- 4 importing/ exporting Data between CSV files /My. SQL and Pandas we have seen Python Pandas library and learn about two data structures of Pandas library i. e series and Dataframes, that are capable of storing any types of data in 2 D tabular format. we can also use spreadsheet files (Ms excel) or database tables (Mysql/Access) to store the data in 2 D table formats. Dataframes can also hold data in similar way, you can transfer data from Data frame to Such data files (or tables) or from files in to a data frame. Two types of transfer of files we have to learn is: 1. To transfer a data to/from a. CSV file from / in to Dataframe. 2. To transfer to/from Database table from/ in to Dataframe. Made by: Gayatri Ghadiali 1

importing/ exporting Data between CSV files /My. SQL and Pandas About CSV file: The acronym CSV is short form of comma separated values. The csv format refers to a tabular data that has been saved as plain text where data is separated by commas. e. g. consider a table having some data as shown below if you store this tables data in csv format then the data of the table will be stored in csv format as shown below on the right. Name Email Phone number Address Bob Smith bob@example. com 123 -456 -7809 123 Fake street Mike Jones mike@example. com 098 -765 -4321 Fake avenue Made by: Gayatri Ghadiali 2

importing/ exporting Data between CSV files /My. SQL and Pandas As you can see that in CSV format ØEach row of the table is stored in one row i. e. the number of rows in a csv file are equal to number of rows in the table( or sheet or database table etc ) ØThe field values of a row are stored together with commas after every field value; but after the last field’s value in a line /row, no comma is given, just the end of line. The csv format is popular as it offers following advantages: § simple compact and indifferent format for data storage. § A common format for data interchange § it can be opened in popular spreadsheet packages like MS Excel , calc etc. §Nearly all spreadsheets and databases support Import / export to csv format Made by: Gayatri Ghadiali 3

importing/ exporting Data between CSV files /My. SQL and Pandas Create CSV file: You can create a csv file yourself by saving data of an MS Excel file in csv format using Save As command from file menu and selecting Save As type as csv format. Eg. csv file sample. csv, is shown in notepad as well as in MS Excel. Made by: Gayatri Ghadiali 4

importing/ exporting Data between CSV files /My. SQL and Pandas Reading from a CSV file to Dataframe: You can use read_csv()function to read data from a CSV file in your dataframe. <DF> =pandas. read_csv(filepath) OUTPUT Saved path is E: sample. csv Note: If you get file path error by single slash then change it to double slashes i. e. “E: \sample. csv” Made by: Gayatri Ghadiali 5

importing/ exporting Data between CSV files /My. SQL and Pandas If we don’t want the first row to be considered as column headings and want default column heading be added. <DF> =pandas. read_csv(filepath, header=None) OUTPUT Made by: Gayatri Ghadiali 6

importing/ exporting Data between CSV files /My. SQL and Pandas If we have first row having some default column values, that we don’t want as columns value than use skiprows argument. OUTPUT Saved path is E: student. csv Made by: Gayatri Ghadiali 7

importing/ exporting Data between CSV files /My. SQL and Pandas skiprow argument if given as 1 first row is skipped. OUTPUT Saved path is E: student. csv Made by: Gayatri Ghadiali 8

importing/ exporting Data between CSV files /My. SQL and Pandas <DF> =pandas. read_csv(filepath, names=<sequence containing column names>, skiprows=<n>) skiprow argument can also take list of row nos. or a row number to skipped rows from the beginning. OUTPUT Made by: Gayatri Ghadiali 9

importing/ exporting Data between CSV files /My. SQL and Pandas Get Dataframe index labels from CSV: <DF> =pandas. read_csv(filepath, index_col=<column name>) OUTPUT Made by: Gayatri Ghadiali 10

importing/ exporting Data between CSV files /My. SQL and Pandas Get Dataframe index labels from CSV: <DF> =pandas. read_csv(filepath, index_col=<column name>) OUTPUT Made by: Gayatri Ghadiali 11