Rearrange CSV Rows to Columns using Python

If you’re creating directories or other list-type pages by using programmatic SEO, you may need to rearrange rows to columns in your CSV data files.

For example, have a look at the before-after images below to better understand what I’m trying to explain.

In the above example, earlier, the “Gym” was the primary column but as I was trying to build programmatic pages targeting gyms in {city} topics, I made “City” as the primary column.

For all this, I wrote a simple Python script that auto-arranges everything within seconds. Just provide the CSV file, specify the column that you want to make as the primary column, and boom. 💥

To learn more, you can check out this quick video:

But if you prefer reading, keep scrolling.

Converting rows to columns

I have tried breaking down the process into small steps to make it easier to follow. Let’s have a look…

Please note that you need to have Python installed on your machine. If it’s not, you can install by visiting its official website. Also, while code editors like VS Code are not required, but using one makes it convenient to edit your code.

1. Prepare the local folder

First of all, create a folder anywhere on your computer and put the CSV file that you want to work on in the folder.

Now, use your code editor like VS Code or any text editor to create a app.py file in the same folder, and copy-paste the below code into the file.

In the last line of the code, replace sample.csv with the filename of your CSV file and the City with the column name inside that CSV file that you want to make the primary column.

For example, if your CSV filename is coffee-shops.csv and the new primary column should be country then the last line in the app.py file would be:

rearrange_data('coffee-shops.csv', 'country')

Now, let’s move forward to the next steps.

2. Install the pandas library

For manipulating CSV files, we are using Python’s pandas library and you will have to install the library by using the following terminal command.

pip install pandas

Sometimes, if this is not working, you may need to use pip3 in place of pip in the above command.

Before running the command, make sure that the terminal is open in the same directory. If not, use the cd command to navigate between directories.

After pandas gets installed successfully, let’s move to the next and final step.

3. Execute the Python code

Now that everything is ready, it’s time to execute the Python code written in the app.py file. And for that, you will have to run the following command in your terminal:

python app.py

Again, in the above command, you may need to use python3 in place of python depending upon your configuration.

The time required to execute the command will vary depending upon how big your input CSV file is and also on how powerful your computer is.

After the code executes successfully, you will see a new CSV file named rearranged_{input_file} and that’s the file.

Open the file and verify whether everything is correct or not.

And that’s it! You’re done!!

To make it easier for you, I have also put all the code and sample files in this GitHub repository. From here, you can either clone or download the repository and start working.

And if you get stuck anywhere in between, kindly feel free to let me know in the comments below.