4 Data Transformations Made Spreadsheet-Easy

In recent years, developers have created sophisticated tools to make the job of analyzing big data easier. Popular open-source tools for Python include Pandas, NumPy and of course, there are math-oriented applications like Matlab and R, as well as SQL for databases and cloud-based data lakes.  Big Data solutions are becoming increasingly complex as data teams get more sophisticated, but this is leaving millions of part-time analysts overwhelmed.

The powerful tools I mentioned above (and countless others) allow users to perform various data analysis operations, but these applications require a high level of technical skill and training to accomplish even the most basic tasks. Often the stakeholders, in the business context, don’t have the skills needed to analyze the data themselves. These users typically lean on an intermediary data team, bogging them down with the most banal of tasks. But what do you do if you don’t have a data team?

It’s no wonder newcomers to the big data world struggle. Without prior coding or database experience, many find these highly technical tools overwhelming. Spreadsheets are widely used by business users, but Excel’s max row limit and reliance on loading the full dataset into the machine’s memory inhibits working on projects involving data analysis at scale.

So, what’s a business analyst to do when working with large volumes of data? I hear the detractors muttering “if you're working with more data than Excel can handle, you should use a database.” To which I respond by reminding them that relatively few people in the world know how to use SQL (maybe 3 million), and there are 750 million Excel users.

Enter Gigasheet

Our no-code, big data spreadsheet, that can be used for analyzing datasets that typically require extensive IT infrastructure and data teams. Even at the (free) Community level, Gigasheet makes it easy to explore and analyze big data, as well as identify trends and anomalies.

In this article, I will walk through 4 common big data transformations, and show you how anyone with basic spreadsheet skills can do them with just a few clicks using Gigasheet.

1. Big Data Exploration In A Spreadsheet

In some cases, data sets can span multiple gigabytes and even terabytes. Exploring these data volumes requires powerful systems, efficient methods of data storage and retrieval, and advanced techniques to analyze the data. Commonly used approaches include file replication and splitting, data sharding, and distributed computing.

But what happens when you want to explore big data without all of this technological firepower? What if you’re not even sure what data a file contains? If only there were any easy way to visualize multi-gigabyte data files online, where complexity could be hidden from view, and the power and scale of the cloud could be leveraged.

Fear not, one of Gigasheet’s many use cases is as a free online CSV file viewer. Data not in CSV format? Not to worry - the system converts most structured data files on the fly. Simply upload your file and you’re on your way.

2. Combining Multiple Large Data Files

   
   

Large data files are often split into multiple parts to make them easier to store, transfer, and process. Splitting a large file into smaller parts also reduces the risk of data corruption and makes it easier to recover lost data. However, when it comes time to analyze the data it’s important to have a comprehensive view, so these pieces must be merged, appended, or otherwise combined.

The process of combining data from multiple sources into a single dataset can be done through process automation, data integration tools, or machine learning algorithms. While these methods are very powerful and capable, they are out of reach for the average business user.

Gigasheet makes it simple to join multiple files together, from CSVs or Excel workbooks to JSON. To do this, simply upload the files as a Zip. Once decompressed, just select two or more files in your library. Then, use the Combine button in the Library to merge the files of the same structure.

For instance, if you have 28 daily logs from the same server, you can easily merge them into one sheet using the Combine feature.

3. Removing Duplicate Data

Cleaning big data files of duplicates, aka de-duping, can be tricky, especially when you want to check for duplicates across multiple fields. Many users are familiar with techniques to remove duplicate rows in excel based on two columns, but few could tackle the task in SQL or Python.

Removing duplicates based on multiple values is easy in Gigasheet, and works similarly to popular spreadsheets. Unlike the typical spreadsheet, Gigasheet scales to billions of records.

Once data is loaded into Gigasheet, you’ll find a variety of Data Cleanup tools including a Delete Duplicates function. Simply select multiple columns when running Delete Duplicates and the cloud application will take care of the rest.

4. Extracting Structured Data From JSON

JSON (JavaScript Object Notation) is a popular data format for exchanging data between systems, applications, and services. It allows for storing and querying data in a structured and efficient manner. This is why most programming languages support reading and writing JSON data, and many APIs use JSON data.

However, if spreadsheets are your go-to analysis tool, analyzing large datasets with JSON records can be tricky. You can of course open moderately sized JSON files in tools like Notepad++, but if you’re working with highly nested JSON structures that are multiple Gigabytes in size, you’ll need to use a database…until now.

Gigasheet converts, or “flattens,” huge JSON files on the fly, and they can easily be pared down, exported to CSV, and opened in typical spreadsheet software. Gigasheet accepts two possible JSON file structures: either an entire file as a JSON object, or JSON where there is one object per line. In the case of the latter, each JSON object becomes a row.

Gigasheet handles each JSON file’s unique structure by creating a column for each value for the various nested and sub-nested objects. This results in a tabular representation of the repeated key data. Common fields have values represented across rows in the same column, and unique fields show values in their own column. It’s quite possibly the easiest way to convert JSON to a CSV.

Wrapping Things Up

We all know big data analysis is an essential part of modern businesses. I hope this article has presented some of the most commonly used solutions and techniques for exploring, combining, and analyzing mega-sized datasets with a free no-code alternative.