Python DataFrame - merging many urls into one cell-CodePudding

I'm trying to merge many urls into one cell and save it as excel file, each row has many urls. This is the code of what I have tried

import pandas as pd
urls1 = ["https://url1.com/","https://url2.com/"]
urls2 = ["https://url3.com/","https://url4.com/"]
df1 = pd.DataFrame([["First url", urls1],["Second url", urls2]], columns=['column 1', 'column 2'])
df1.to_excel('excel.xlsx', index=False, header=True)

The expected output

Excel file

CodePudding user response：

Try this:

import pandas as pd
urls1 = ["https://url1.com/","https://url2.com/"]
urls2 = ["https://url3.com/","https://url4.com/"]
df1 = pd.DataFrame([urls1, urls2], columns=['column 1', 'column 2'], index=["First url", "Second url"])
df1.to_excel('excel.xlsx', index=True, header=True)

CodePudding user response：

Try this:

import pandas as pd
urls1 = ["https://url1.com/","https://url2.com/"]
urls2 = ["https://url3.com/","https://url4.com/"]
df1 = pd.DataFrame(zip(urls1, urls2), columns=["First url", "Second url"])
df1
>First url  Second url
0   https://url1.com/   https://url3.com/
1   https://url2.com/   https://url4.com/

CodePudding user response：

You can try explode

import pandas as pd
urls1 = ["https://url1.com/","https://url2.com/"]
urls2 = ["https://url3.com/","https://url4.com/"]
df1 = pd.DataFrame([["First url", urls1],["Second url", urls2]], columns=['column 1', 'column 2'])
df1 = df1.explode('column 2')
df1.to_excel('excel.xlsx', index=False, header=True)

For cell merge, you can have a look at [https://stackoverflow.com/questions/61217923/merge-rows-based-on-value-pandas-to-excel-xlsxwriter](Merge rows based on value)