DEV Community: Beryl Chebet

Pandas map()function with example

Beryl Chebet — Thu, 07 Apr 2022 21:11:19 +0000

Map() function allows us to transform data in a DataFrame or series one value at a time.A dataframe is a table with a value corresponding to a row and column entry.An example of creating a dataframe is as shown:

import pandas as pd
table=pd.DataFrame({'Age':[16,18,25,27,28,30,45],'Gender':['Female','Male','Female','Male','Male','Male','Female'],'Marks':[66,70,88,90,95,50,88]})

The output should look like this

In the above example we created a DataFrame table using pd.DataFrame() having the columns age,gender,marks.Entries are assigned to the respective columns as shown in the square brackets.
Now lets jump into using the map()function

Mapping female with 1 and male with 0 then displaying result on a different column.

table['sex_num']=table.Gender.map({'Female':1,'Male':0})
table.loc[:,['Gender','sex_num']]

table['sex_num'] creates a new column sex_num,Gender.map specifies the values to map with in the column Gender. Dataframe.loc is used for accessing multiple columns . In this case we want to access the columns 'Gender' and 'sex_num'.

To find deviation from mean mark

dev_mean=table.Marks.mean()
table['Deviation_From_Mean']=table.Marks.map(lambda p:p-dev_mean)
table.loc[:,['Deviation_From_Mean','Marks']]

table.Marks.mean() tells pandas to calculate the marks mean and assign it to dev_mean. table['Deviation_From_Mean']_creates a new column deviation from mean and maps a lambda function _lambda p:p-dev_mean to each value of the column.A lambda function can take a number of arguments & execute an expression. The lambda function has a keyword, a variable and an expression.The keyword is lambda & must be included whenever you're using lambda function. In lambda p:p-dev_mean p _stands for each of the entries in the marks column. The expression _p-dev_mean subtracts the dev_mean from each of the entries.

Creating an SQLite Database From Excel Workbook.

Beryl Chebet — Mon, 28 Mar 2022 20:49:27 +0000

A database is an organized collection of rows and columns with data that are related to each other. Sometimes you have an excel worksheet and would like to query it using SQL. This post takes you through using Pandas to create an SQLite database from an excel sheet. So let's jump into it .

Step 1:Load the dataset to Pandas

To load the dataset from excel to pandas use the following commands:

   ```python 
      import pandas as pd 
      import sqlite3
      df=pd.read_excel('path to file')```

Import pandas as pd tells python to bring the pandas data analysis library into your current environment. pd portion of the code tells python to give pandas the alias pd. This means you can use pandas by typing pd. Import sqlite3 provides an SQL-like interface to read,query and write SQL databases from python.df=pd.read_excel('path to file') tells pandas to read the excel document in a specific location.

Step 2: Create SQLite database using sqlite3.connect()

The following sample code creates an empty database file called Day.db and assigns it to a variable db_conn

   ```db_conn=sqlite3.connect('Day.db')```

This provides an interface for working with the SQLite database.

Step 3:Create a cursor object to run the SQL code that will create the data tables.

Cursor is an instance using which you can invoke methods that execute SQLite statements from the result sets of the queries i.e makes the connection for executing SQL queries on the SQLite database.

```cur=db_conn.cursor()```

Step 4:Create a table

This is a command used to create a table called DayofWeeek with new columns Day and Sum.The data type for each column is specified to the right of the column name.

```create_table="CREATE TABLE DayofWeek(Day TEXT,Sum INT)```

The column names must match those stored in the dataframe.

Step 6:Moving from pandas dataframe to SQL table

.to_sql command is used to write records stored in a dataframe to a SQL database.DayofWeek specifies the the name of SQL table created.
if_exists='append' inserts new values into the existing table DayofWeek.index=False to avoid bringing the dataframe index as a column.

```df.to_sql('DayofWeek',db_conn,if_exists='append', index=False)```

You can run your sql commands on pandas for example:


pd.read_sql("SELECT * FROM DayofWeek",db_conn)

What is data analytics? Types , careers and importance.

Beryl Chebet — Tue, 15 Feb 2022 15:12:39 +0000

Data analytics is the process of cleaning data to get information. it involves collecting and transferring valuable information from useless data and analyzing it to generate numerical data and patterns that can lead to profitable improvement. Therefore, a data analyst is someone who gets information from raw data and can use the information to determine trends, provide solutions and describe problems and their possible solutions.
Data analytics is important in

Supporting decision making – To deliver critical insights on whether a business is moving in the right direction, data analytics in business is effective in determining market trends and explaining the key drivers of the success of a product or service versus another which may not be faring as well.
Driving productivity, efficiency, and revenue growth- The results from analyzing data sets is going to tell an organization where they can optimize, which processes can be optimized or automated, which processes they can get better efficiencies out of and which processes are unproductive and thus can have resources dedicated away.
Determining more effective marketing strategies- Businesses can use data analytics to better understand their client base in terms of customer trends and behaviors. This information can be used focus on more effective pitching of products and services. The different types of data analysis are Predictive analysis, prescriptive analysis, descriptive analysis and diagnostic analysis.

•Descriptive analysis: describes what happened based on past events.
•Diagnostic analysis: aims to understand what happened and why it happened,
•Predictive analysis: forecasting on what will happen in the future.
•Prescriptive analysis: states what action should be done to achieve a goal.

Various roles that require knowledge of data analytics, some of these roles are business intelligence analyst, data analyst, data scientist, data engineer, quantitative analyst and marketing analyst. Data analysis is providing skills that are very important to various fields, some of these fields are healthcare, cybersecurity, businesses and marketing. Transportation. These are just but a few fields where data analytics is applied, data analytics does not limit you to a particular field. With a relevant set of technical skills, you can grow into any field of interest.