Python - Transpose Dataframe Columns into Rows

Today, I was working with Python where I have to transpose some columns into rows to avoid a lot of calculations. As we know that Python has a lot of libraries and very strong communities support. That means, you can solve any problems with your dataset.

Here, I’m using a small dataset to show you that how can we use pandas library to transpose your dataframe.

In this example, I’m using class student’s dataset where each student has their subject in the columns with their obtained marks.

Now, we have to transpose subject columns into rows in the ‘Subject’ column and marks will be display in Marks column next to Subject within dataset-2.

Pandas melt() function is used to change the DataFrame format from wide to long. It’s used to create a specific format of the DataFrame object where one or more columns work as identifiers. All the remaining columns are treated as values and unpivoted to the row axis and only two columns — variable and value.

Here, we can see that with the help of Pandas library, we can transpose our dataset into the desired results.

 

#import Libraries import pandas as pd # Creating DataFrame from dict of narray/lists. intialise data of lists list={'Name':['Ryan','Arjun','john','Rosy'],      'Class':['IV','III','III','V'],          'English':[90,85,90,95],      'Math':[95,90,85,80],      'Science':[95,90,90,90],      'Computer':[98,95,90,85],      'Year':[2020,2020,2020,2020]}   # Create DataFrame from list/narray df=pd.DataFrame(list)   #show data in the dataframe df ====================================================== Name | Class | Year| English | Math |Science |Computer ------------------------------------------------------ Ryan |IV              | 2020 |               90           | 95          | 95     |98 Arjun|III              | 2020 |               85           | 90          | 90     |95 John |III               | 2020 |               90           | 85          | 90     |90 Rosy |V                | 2020 |               95           | 80          | 90     |85 ======================================================     # function to unpivot the dataframe df3=df.melt(['Name','Class','Year'], var_name='Subject')   #show data in the dataframe df3 =======================================   |Name | Class | Year|Subject  |value --------------------------------------- 0 |Ryan |  IV   |2020 |Computer| 98 1 |Arjun|  III  |2020 |Computer| 95 2 |john |  III  |2020 |Computer| 90 3 |Rosy |  V    |2020 |Computer| 85 4 |Ryan |  IV   |2020 |English | 90 5 |Arjun|  III  |2020 |English | 85 6 |john |  III  |2020 |English | 90 7 |Rosy |  V    |2020 |English | 95 8 |Ryan |  IV   |2020 |Math    | 95 9 |Arjun|  III  |2020 |Math    | 90 10|john |  III  |2020 |Math    | 85 11|Rosy |  V    |2020 |Math    | 80 12|Ryan |  IV   |2020 |Science | 95 13|Arjun|  III  |2020 |Science | 90 14|john |  III  |2020 |Science | 90 15|Rosy |  V    |2020 |Science | 90 =======================================     #rename value columns to Marks df3=df3.rename(columns = {'value': 'Marks'}, inplace = False)   #show data in the dataframe df3 =======================================   |Name | Class | Year|Subject  |Marks --------------------------------------- 0 |Ryan |  IV   |2020 |Computer| 98 1 |Arjun|  III  |2020 |Computer| 95 2 |john |  III  |2020 |Computer| 90 3 |Rosy |  V    |2020 |Computer| 85 4 |Ryan |  IV   |2020 |English | 90 5 |Arjun|  III  |2020 |English | 85 6 |john |  III  |2020 |English | 90 7 |Rosy |  V    |2020 |English | 95 8 |Ryan |  IV   |2020 |Math    | 95 9 |Arjun|  III  |2020 |Math    | 90 10|john |  III  |2020 |Math    | 85 11|Rosy |  V    |2020 |Math    | 80 12|Ryan |  IV   |2020 |Science | 95 13|Arjun|  III  |2020 |Science | 90 14|john |  III  |2020 |Science | 90 15|Rosy |  V    |2020 |Science | 90 =======================================

 To learn more, please follow us -

http://www.sql-datatools.com

To Learn more, please visit our YouTube channel at - 

http://www.youtube.com/c/Sql-datatools

To Learn more, please visit our Instagram account at -

https://www.instagram.com/asp.mukesh/

To Learn more, please visit our twitter account at -

https://twitter.com/macxima

To Learn more, please visit our Medium account at -

https://medium.com/@macxima

What is Engineering data

Engineering data is the foundation for all of the recent, current, and future data hypes: machine learning, deep learning, big data, data science, etc. The success and adoption of these hypes is predicated on data being structured properly and available. However, when customers (internal and external) are not clear on what their expectations are and/or the big picture of what they are trying to use the data for, data engineers are often blamed. 

Machine learning is a method used to devise complex models and algorithms that lend themselves to prediction; These analytical models allow researchers, data scientists, engineers, and analysts to produce reliable, repeatable decisions and results.

Communication is key! Couple of points, "big data" has always been there, just ask Statisticians, as for the data science hype, now every one calls themselves data scientist, somehow knowing SQL makes a person data scientist now. Data Science graduates on a daily basis and many are lacking basic analytical skills and believe Data Science and Data Analysis are all about having basic end-user level knowledge of a new fancy software. It is a hype for a fact.

We can have the best people using the data for analytics or modeling, but if we don’t have people that know how to build the systems to make our data available in a consistent reliable manner then you will just be part of the hype. Having multiple data science teams leads to friction between the teams. While they, mainly concerned about product delivery, has an open door policy across the Enterprise, the other, more concerned about, well we don't know what, treats everyone else with a sense of superiority and thinks that their work is super secret somehow.

The reason for it's crashing is most likely because the software you are using is not a sever software/service, eg. if you use excel everything is processed in memory(like MS products is doing it all the time) if you had a db then it had been paging to disk when then the assign memory get full (short cut explanation).  

My understanding of Data scientist is they try to hard code/program different scenarios to get an answer.  If you know some about philosophy you cans tart there. You say what if condition 1 =1, then they add different condition with different variables.  It's not actually that complicated from my understanding. 

The data science hype now is focused on AI. After the dust settles they will realize that not all big data and data science is what's going on in Google or Facebook. Corporate needs another big data and data science. They need to make sense of their own data and solve their own problems using whatever techniques. I believe Optimization and statistical models are more important than AI for most of corp-orates. They can buy the hard stuff like speech and text analytics from Google or Amazon or anyone of this scale and capacity. Yet they have to retain a team to solve their specific problems using science.

That the industry does not want to reveal the pressing questions they want to get answered - obviously due to competition and conflict of interest. There are some good concepts behind data science and modeling (both mathematical and statistical) lies at the heart of obtaining directed insights. But yes, academia-industry partnership is very much lacking, which has the potential to make data science a hope or a hype.