英文:
link column doesn't extend to end of dataframe
问题
我正在运行一个处理包含链接列的数据框的代码。根据一些试验和错误，我意识到结果在我到达代码的这部分之前都是正常的：
df1 = df[df['operator'] == 'test']
df1.reset_index(inplace=True, drop=True)
df1[phone_col] = df1['new_phone']
df.to_excel('step2_1.xlsx', index=False)
当我打开step2_1.xlsx时，链接列只显示到索引65531，然后之后就是空白的，如下面的图片所示。
我该如何修复它？这与VSCode的设置有关吗？
英文:
I'm running a code on a dataframe which has a link column where it has a link to a page in it.
based on some trial and error I realized that the result is fine untill I reach this part of the code:
df1 = df[df['operator'] == 'test']
df1.reset_index(inplace=True, drop=True)
df1[phone_col] = df1['new_phone']
df.to_excel(f'step2_1.xlsx', index=False)
when I open the step2_1.xlsx, the link column goes only to index of 65531 and then after that its blank like the picture below.
how can i fix it? is it related to a setting for vscode?
答案1
得分: 1
Excel有一个内置的超链接限制，限制为65530。
你可以通过使用Excel的HYPERLINK函数来绕过这个限制。
所以举个例子：
(pd.DataFrame([['test', '=HYPERLINK("https://www.google.com/")']]*65600, columns=['Name', 'Link'])
.to_excel(f'step2_1.xlsx', index=False))
编辑
这显示了如何轻松将你列中的当前字符串转换为Excel超链接公式：
df = pd.DataFrame([['test', 'https://www.google.com/']]*10, columns=['Name', 'Link'])
df['Link'] = df.Link.apply(lambda x: f'=HYPERLINK("{x}")')
输出：
Name Link
0 test =HYPERLINK("https://www.google.com/")
1 test =HYPERLINK("https://www.google.com/")
2 test =HYPERLINK("https://www.google.com/")
3 test =HYPERLINK("https://www.google.com/")
4 test =HYPERLINK("https://www.google.com/")
5 test =HYPERLINK("https://www.google.com/")
6 test =HYPERLINK("https://www.google.com/")
7 test =HYPERLINK("https://www.google.com/")
8 test =HYPERLINK("https://www.google.com/")
9 test =HYPERLINK("https://www.google.com/")
英文:
Excel has a built in hyperlink limit of 65530.
You can bypass this limit by using the HYPERLINK function of Excel.
So as an example:
(pd.DataFrame([['test', '=HYPERLINK("https://www.google.com/")']]*65600, columns = ['Name', 'Link'])
.to_excel(f'step2_1.xlsx', index=False))
EDIT
This shows how to convert the current string in your column to the Excel hyperlink formula easily:
df = pd.DataFrame([['test', 'https://www.google.com/']]*10, columns = ['Name', 'Link'])
df['Link'] = df.Link.apply(lambda x: f'=HYPERLINK("{x}")')
Output:
Name Link
0 test =HYPERLINK("https://www.google.com/")
1 test =HYPERLINK("https://www.google.com/")
2 test =HYPERLINK("https://www.google.com/")
3 test =HYPERLINK("https://www.google.com/")
4 test =HYPERLINK("https://www.google.com/")
5 test =HYPERLINK("https://www.google.com/")
6 test =HYPERLINK("https://www.google.com/")
7 test =HYPERLINK("https://www.google.com/")
8 test =HYPERLINK("https://www.google.com/")
9 test =HYPERLINK("https://www.google.com/")
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论