0

Like the image shows I have a data frame with different countries and numbers for each year. I want to have the countries in a separate column and get the numbers from STATUS_YR to be in rows following each country by year. My DF right now I'm new to python so I don't really know how to do this.

I've tried to move around the rows or resetting the index but usually this just leads to more complications.

Patrick
  • 1
  • 1
  • 1
    Can you share a proper reproducible snip of your data instead of a picture? – Hillygoose Feb 10 '23 at 14:52
  • Might be worth checking out this answer as it seems to be pretty similar to what you are trying to achieve - https://stackoverflow.com/questions/28654047/convert-columns-into-rows-with-pandas – Hillygoose Feb 10 '23 at 14:52

2 Answers2

0

I am able to make a dirty solution. It works, but I still hope some expert may help you to simplify the code.

The solution is just to create an empty dataframe based on the information we know, then gradually fill in the columns as needed.

import pandas as pd

row1 = ['ISO3', 'ARG', 'ARG', 'ARG', 'ZMB', 'ZMB', 'ZMB']
row2 = ['Status.YR1', 12, 16, 15, 12, 14, 13]

df = pd.DataFrame([row1, row2], columns = ['index', 2001, 2002, 2003, 2001, 2002, 2003])

# Creating list of cols and rows
cols = [x for x in df.loc[0].unique() if x != 'ISO3']
rows = [x for x in set(df.columns) if x != 'index']

# An empty dataframe as placeholder
df_final = pd.DataFrame(index=rows, columns = cols)

# Gradually fill in the dataframe
for i, (idx, row) in enumerate(df.transpose().iterrows()):
    if i == 0:
        continue
    year = idx
    country = row[0]
    value = row[1]
    df_final.loc[year, country] = value

Then df_final is what you need.

Minh-Long Luu
  • 2,393
  • 1
  • 17
  • 39
0

The question was a bit clumsily posed, but what I wanted to do was have the years as columns and one row per country. I did it by transposing back the data into the original mode and wrote the following code to move them around. By using the pivot function you can move rows and columns around a bit more freely.

df1 = df.pivot(index="ISO3", columns="STATUS_YR")
Patrick
  • 1
  • 1