Interesting article how to use Pandas with Excel sheets

Click For Summary
SUMMARY

This discussion highlights the use of Pandas in Python for reading and writing multi-tab Excel spreadsheets, as detailed in an article shared within an edX/Georgia Tech online course. The article outlines a seven-step process for reshaping data using Pandas, which is particularly useful for data analysis tasks that exceed Excel's limitations. Participants express curiosity about potential row limits when pivoting data and the feasibility of handling mixed data types, as well as the possibility of pivoting data across multiple sheets.

PREREQUISITES
  • Familiarity with Python programming
  • Understanding of Pandas library (version 1.0 or higher)
  • Basic knowledge of Excel spreadsheet functionalities
  • Awareness of data types and structures in data analysis
NEXT STEPS
  • Explore the Pandas documentation for reading and writing Excel files
  • Learn about data manipulation techniques in Pandas, such as pivot tables
  • Investigate the limitations of Excel regarding row and column counts
  • Research methods for handling mixed data types in Pandas
USEFUL FOR

This discussion is beneficial for data analysts, Python developers, and anyone interested in integrating Python with Excel for advanced data manipulation and analysis.

scottdave
Science Advisor
Homework Helper
Insights Author
Messages
2,009
Reaction score
974
I came across this article about using Pandas in Python to read in a multi-tab spreadsheet to Python, and work with the data then write back to an Excel spreadsheet:
https://towardsdatascience.com/seve...w-i-use-python-where-excel-fails-62061f86ef9c
I have not yet tried it, but it looks pretty cool. The link was posted on a Slack channel for an online course I'm taking through edX/Georgia Tech. (Introduction to Computing for Data Analysis). It is part of their MicroMasters program in Analytics.
 
  • Like
Likes   Reactions: Asymptotic and QuantumQuest
Technology news on Phys.org
Yes, that's pretty cool. The steps are somewhat involved and you'd have to check to make sure something didn't get lost.

Also is there some limit as to how many rows can get pivoted in this way? I'm guessing the max cols of the spreadsheet program which is 16,384:

https://support.office.com/en-us/ar...d-limits-1672b34d-7043-467e-8e27-269d656771c3

Normally for the data we work with, we'd use MATLAB functions to transpose the matrix of data but I'm not sure how that would work in this case with mixed datatypes of strings and numbers.

I also wonder if you could pivot across sheets (ala 3D matrix of data) as it seems that its possible.
 
  • Like
Likes   Reactions: scottdave and QuantumQuest

Similar threads

Python Insert data into excel, calculate+retrieve data using Python
  • · Replies 5 ·
Replies
5
Views
4K
Python What is the Intel Distribution for Python and how can it improve data analysis?
  • · Replies 5 ·
Replies
5
Views
3K
Requesting suggestions for languages, libraries, and architectures for parallel (and sometimes non parallel) numerical and scientific computations
  • · Replies 8 ·
Replies
8
Views
4K
Useful Tutorials for Organic Chemistry Software
  • · Replies 1 ·
Replies
1
Views
2K
Current and Future AI Threats to Humanity and the Human Response
  • · Replies 10 ·
Replies
10
Views
5K