1

I'm new to Python and coding, and I'm trying to use Openpyxl to insert new lines in a workbook and then fill them with certain data from my dataframe.

Here's my dataframe:

Country Fact Amount
Algeria Population 43m
Algeria Capital Algiers
Algeria Language Arabic
Algeria Rating 80%
Algeria Crime rate 53.82
Albania Birth Rate 2.9
Spain Language Spanish
Spain Population 47m

The Country_Facts workbook is laid out as follows:

A B
America
Population 329m
Capital Washington DC
Algeria
Belgium
Capital Brussels
Language French

So I need to insert lines after Algeria and then add the section related to Algeria and Albania.

My code:

wb = openpyxl.load_workbook('Country_Facts.xlsx')

for Country in df:
    if df[Country] == "Algeria":
        ws.insert_rows()

# Then I want to fill each inserted row with values from df['Fact', 'Amount]

To give this:

A B
America
Population 329m
Capital Washington DC
Algeria
Population 43m
Capital Algiers
Language Arabic
Rating 80%
Crime Rate 53.82
Albania
Birth Rate 2.9
Belgium
Capital Brussels
Language French
Spain
Language Spanish
Population 47m

But I'm stuck at this point.

I could also iterate over rows in the dataframe, but I've read on other Stack Overflow answers that you shouldn't iterate over rows of dataframes. If I turned the data into strings rather than a data frame I could then iterate over each row. Like below, but I still struggle with inserting data from the Fact and Amount column.

words = "Algeria"

for r_idx, row in enumerate(ws.iter_rows(max_row=ws.max_row, max_col=3)):
    if ws.cell(r_idx+1, 1).value in words:
        for row in wb:
            ws.insert_rows()
user18233539
  • 129
  • 1
  • 2
  • 8
  • Why shouldn't you iterate over dataframe rows? Openpyxl has a function **specifically** for doing this. – Charlie Clark Jul 12 '22 at 11:42
  • Hi Charlie! I read it here https://stackoverflow.com/questions/16476924/how-to-iterate-over-rows-in-a-dataframe-in-pandas and I'd iterate over the dataframe to see country name and add rows to the Country_Facts file. I updated the question to include the output I'm looking for. – user18233539 Jul 12 '22 at 13:41
  • 1
    I think what you want to do can be done using "stacking" in Pandas, but you can do it in openpyxl. However, you probably want to read up on using `ws.iter_rows()` as you shouldn't need an enumerator or call `ws.cell()` inside the loop. – Charlie Clark Jul 12 '22 at 16:53
  • Hi Charlie, thanks for your help! I haven't worked it out yet but I did do a workaround. I created a shell file with the facts column in my example data and then used map to populate the values. (My data is similar to my example, though I just realised I repeated elements in the Fact column, in my data the fact column elements are unique allowing me to map them to the shell file.) – user18233539 Jul 13 '22 at 08:53

0 Answers0