Using Python's pandas framework to manipulate data tutorials in Excel files

Introduction

The purpose of this article is to show you how to use pandas to perform some common Excel tasks. Some examples are trivial, but I think showing these simple things is just as important as the complex features you can find elsewhere. As an extra benefit, I'm going to do some fuzzy string matching to show some of the tricks and show how pandas is using the complete Python module system to do something that is simple in Python, but complex in Excel.

Does it make sense? Let's get started.
Add a sum to a row

The first task I'm going to cover is to add a few columns and a sum column.

First we import the Excel data into the pandas data frame.

Import pandas as Pdimport numpy as Npdf = Pd.read_excel ("excel-comp-data.xlsx") Df.head ()

We want to add a sum bar to show the total sales of Jan, Feb, and Mar for three months.

In Excel and pandas, this is straightforward. For Excel, I added the formula sum (G2:I2) in column J. It looks like this in Excel:

Here's how we do it in pandas:

df["Total"] = df["Jan"] + df["Feb"] + df["Mar"]df.head ()

Next, let's calculate some summary information and other values for each column. As shown in the Excel table below, we do these things:

As you can see, we add sum (G2:G16) to the 17th row of the column that represents the month to get the sum of the month.
It is easy to perform column-level analysis in pandas. Here are some examples:

df["Jan"].sum (), df["Jan"].mean (), df["Jan"].min (), df["Jan"].max () (1462000, 97466.666666666672, 10000, 162000)

Now we're going to add the sum of each month together to get them. Here Pandas and Excel are a little different. Adding the sum of each month to a cell in Excel is simple. Because pandas needs to maintain the integrity of the entire dataframe, some additional steps are required.

First, create a sum column for all columns

sum_row=df[["Jan", "Feb", "Mar", "Total"]].sum () sum_row Jan   1462000Feb   1507000Mar    717000total  3686000dtype:int64

This is very intuitive, but if you want to show the sum value as a separate row in the table, you'll need to do some tweaking.

We need to transform the data to convert this series of numbers into Dataframe, so that it is easier to merge it into existing data. The T function lets us transform rows-by-row data into column-arranged.

DF_SUM=PD. DataFrame (Data=sum_row). Tdf_sum

The last thing we do before we calculate the sum is to add the missing columns. We use Reindex to help us finish. The trick is to add all the columns and then let pandas add all the missing data.

Df_sum=df_sum.reindex (columns=df.columns) df_sum

Now that we have a well-formed dataframe, we can use append to add it to the existing content.

Df_final=df.append (df_sum,ignore_index=true) Df_final.tail ()

Additional Data transformations

Another example, let's try to add a state abbreviation to the dataset.

For Excel, the simplest way is to add a new column, use the VLOOKUP function for the state name, and populate the abbreviation bar.

I did this, and here's the result:

You can notice that after the VLOOKUP, there are some values that have not been properly obtained. This is because we misspelled the names of some states. Dealing with this problem in Excel is a huge challenge (for large datasets)

Fortunately, using pandas we can take advantage of the powerful Python ecosystem. Considering how to solve this kind of troublesome data problem, I consider doing some fuzzy text matching to determine the correct value.

Fortunately, others have done a lot of work in this area. The Fuzzy Wuzzy library contains some very useful functions to solve this kind of problem. First make sure you install him.

Another piece of code that we need is the state name and its abbreviated mapping table. Instead of entering them yourself, Google can find this code.

First, import the appropriate Fuzzywuzzy function and define our state name Mapping table.

From Fuzzywuzzy import fuzzfrom fuzzywuzzy Import Processstate_to_code = {"VERMONT": "VT", "GEORGIA": "GA", "IOWA": "IA", "Armed Forces Pacific": "AP", "GUAM": "GU", "KANSAS": "KS", "FLORIDA": "FL", "AMERICAN Samoa": "as", "North Caroli  NA ":" NC "," HAWAII ":" HI "," NEW YORK ":" NY "," CALIFORNIA ":" CA "," ALABAMA ":" AL "," IDAHO ":" ID "," Federated states         of Micronesia ":" FM "," Armed Forces Americas ":" AA "," DELAWARE ":" DE "," ALASKA ":" AK "," ILLINOIS ":" IL ", "Armed Forces Africa": "AE", "South DAKOTA": "SD", "Connecticut": "CT", "MONTANA": "MT", "Massachusetts": "MA", "P Uerto RICO ":" PR "," Armed Forces Canada ":" AE "," New HAMPSHIRE ":" NH "," MARYLAND ":" MD "," New MEXICO ":" NM "," MISS Issippi ":" MS "," Tennessee ":" TN "," PALAU ":" PW "," COLORADO ":" CO "," Armed Forces Middle East ":" AE "," NEW JERSEY " : "NJ", "UTAH": "UT", "MICHIGAN": "MI", "WEST VIRGINIA": "WV", "WASHINGTON": "WA", "Minnesota": "MN", "OREGON": "O R "," VIRGINIA ":" VA "," VIrgin ISLANDS ":" VI "," MARSHALL ISLANDS ":" MH "," WYOMING ":" WY "," OHIO ":" OH "," South CAROLINA ":" SC "," INDIANA ": "In", "NEVADA": "NV", "Louisiana": "LA", "NORTHERN MARIANA ISLANDS": "MP", "Nebraska": "NE", "ARIZONA": "AZ", "WIS Consin ":" WI "," North DAKOTA ":" ND "," Armed Forces Europe ":" AE "," Pennsylvania ":" PA "," Oklahoma ":" OK "," Kentuck Y ":" KY "," RHODE Island ":" RI "," DISTRICT of COLUMBIA ":" DC "," ARKANSAS ":" AR "," Missouri ":" MO "," TEXAS ":" TX "," MAINE ":" ME "}

Here are some examples of how fuzzy text matching functions work.

Process.extractone ("Minnesotta", Choices=state_to_code.keys ()) Process.extractone (' Minnesota ', "AlaBAMMazzz", Choices=state_to_code.keys (), score_cutoff=80)

Now that I know how it works, we create our own function to accept the state Name column of data and convert it to a valid abbreviation. Here we use a value of Score_cutoff of 80. You can make some adjustments to see which value is better for your data. You will notice that the return value is either a valid abbreviation or a Np.nan, so there are some valid values in the domain.

def convert_state (Row):  abbrev = Process.extractone (row["state"],choices=state_to_code.keys (), score_cutoff=80 )  if abbrev:    return state_to_code[abbrev[0]]  return Np.nan

Add this column to the cell that we want to fill, and then fill it with Nan

Df_final.insert (6, "abbrev", Np.nan) Df_final.head ()

We use apply to add abbreviations to the appropriate columns.

df_final[' abbrev ' = df_final.apply (convert_state, Axis=1) Df_final.tail ()

I think it's cool. We have developed a very simple process to intelligently clean up data. Obviously, when you have only 15 rows or so of data, it's nothing. But what if it's 15000 lines? In Excel you have to do some manual cleanup.
Category Summary

In the last section of this article, let's do some subtotals (subtotal) by state.

In Excel, we use the Subtotal tool to do this.

The output is as follows:

Creating subtotals in Pandas is done using GroupBy.

df_sub=df_final[["Abbrev", "Jan", "Feb", "Mar", "Total"]].groupby (' abbrev '). SUM () df_sub

Then, we want to format the data units as currency by using Applymap to all the values in the database frame.

def money (x):  return ' ${:,.0f} '. Format (x) FORMATTED_DF = Df_sub.applymap (Money) FORMATTED_DF

Formatting seems to go well, and now we can get the sum as we did before.

sum_row=df_sub[["Jan", "Feb", "Mar", "Total"]].sum () Sum_row

Jan   1462000Feb   1507000Mar    717000total  3686000dtype:int64

Transform the values into columns and then format them.

DF_SUB_SUM=PD. DataFrame (Data=sum_row). Tdf_sub_sum=df_sub_sum.applymap (Money) df_sub_sum

Finally, add the sum to the dataframe.

final_table = Formatted_df.append (df_sub_sum) final_table

You can notice that the index number of the sum row is ' 0 '. We want to use rename to rename it.

final_table = Final_table.rename (index={0: "Total"}) final_table

Conclusion

So far, most people already know that using pandas can do a lot of complicated things with data-just like Excel. Because I've been learning pandas, I've found that I still try to remember how I did it in Excel rather than in pandas. I realized that it was not fair to compare the two-they were completely different tools. However, I would like to be exposed to those who know about Excel and want to learn some other alternative tools that can meet the needs of analyzing their data. I hope that these examples will help others and give them the confidence that they can use pandas to replace their fragmented and complex excel for data manipulation.

Related articles:

How Python writes to MySQL using pandas read CSV files

Python Data analysis Real IP request pandas detailed

Analysis of CDN logs through the Pandas library in Python

