How can I turn a dataframe into a markdown vertical table?

Question

EDIT Answer: There are two types of tables I needed. One was the regular Horizontal table view. For that, I just used the "to_markdown()" with the added code to removed the indeces. For the vertical view I copied and pasted Ehsan's code below and it worked perfectly.

Now, I wanted to paste these in a nice view to my excel sheet. That would have taken a bunch of work and formatting very specifically, and it won't work out with the to_markdown(), since that changes the DataFrame to a string. To get it as an index, you can't use that function. You'd either have to take your existing DataFrame and figure out how to add new rows and columns, or: create an empty DataFrame with the proper amount of columns and rows, and fill it with the necessary "|" formatting. After that, you'd have to fill out the empty/correct cells with data from your DataFrame that contains the data. However, this is too much work, so instead I just copied the string over directly into the proper excel sheet using this code:

#Now we are going to save this loop's iteration into the proper Excel sheet
    file_source ='Source Used.xlsx'

    #load excel file
    workbook = load_workbook(filename=file_source)

    #Pick the sheet "Sheet Used"
    ws4 = workbook["Project X"]

    #modify the desired cell
    ws4.cell(row = 1, column = 1).value = newDataFrameWithStringFormat

    #save the file
    workbook.save(filename=file_source)

From here, anytime I wanted to update my markdown website, I could just pull from the Excel sheet's first cell. It doesn't look pretty, but it works.

END EDIT

I have a table that looks like this: df:

Project 1	Project 2	Project 3
data 1	data 2	data 3
data 4	data 5	data 6
data 7	data 8	data 9

I want to edit this table so that it can work with, say, markdown, and be formatted well. However, to do that, I need to add a bunch of formatting to it (it basically needs to look the same as StackOverflow's table setup when writing this question). What function can I apply to it so that a new table is created that looks like this:

As you can see in this picture, the pipes and hyphens are each a new cell of data. Additionally, the Column HEADERS are at the beginning of the rows, since this is a horizontal format. How can I apply this formatting to dataframes of varying size?

This is what the new one should look like (I bolded the Project names myself):

Now hypothetically, I can transpose the data and use to_markdown(), but that may still run into the issue of the top column being created as a column header. How can I avoid this by making a custom function to add in the pipes ("|") and hyphens?

Thank you!

Answer 1

A potential answer:

import pandas as pd

df=pd.DataFrame({"Project 1":["data 1", "data 4", "data 7"], "Project 2": ["data 2", "data 5", "data 8"], "Project 3": ["data 3", "data 6", "data 9"]})

df = df.transpose()
# remove header
df.columns = ["" for i in range(len(df.columns))]

# make the index bold - ** ** 
df.index = ["**{}**".format(idx) for idx in df.index]
# **Project 1**  data 1  data 4  data 7
# **Project 2**  data 2  data 5  data 8
# **Project 3**  data 3  data 6  data 9

print(df.to_markdown())
#|               |        |        |        |
#|:--------------|:-------|:-------|:-------|
#| **Project 1** | data 1 | data 4 | data 7 |
#| **Project 2** | data 2 | data 5 | data 8 |
#| **Project 3** | data 3 | data 6 | data 9 |

Will be like this:

Project 1	data 1	data 4	data 7
Project 2	data 2	data 5	data 8
Project 3	data 3	data 6	data 9

Updated:

In case you want the csv version of the markdown table in your specified format, you can modify the string and save it as a csv file:

markdown_text = df.to_markdown() # from above code
markdown_csv= markdown_text.replace("|", ",|,")[1:-1].replace(",\n,","\n")
with open("results.csv", "w", encoding="utf-8") as fp:
    fp.write(markdown_csv)

The results will be (you can skip **{}**, markdown bold, in case you don't want it - just comment that part of the code):