Python Pandas wrap text in cell output it to_html

Question

I am trying to wrap text at certain point e.g. | in a cell and export that to html.

An example:

import pandas as pd
df = pd.DataFrame({'EmployeeId': ['157', '292', '643', '124', '355'],
                     'City': ['Mumbai|Bangalore', 'Pune|Mumbai|Delhi', 'Mumbai|Bangalore', 'Mumbai|Pune', 'Bangalore']})

print(df)
df.to_html('test1.html')

Output:

   EmployeeId      City
0  157        Mumbai|Bangalore 
1  292        Pune|Mumbai|Delhi
2  643        Mumbai|Bangalore 
3  124        Mumbai|Pune      
4  355        Bangalore

I would have a html file like this (expected):

Image

Output:

   EmployeeId  City
0  157        Mumbai
              Bangalore 
1  292        Pune
              Mumbai
              Delhi
2  ...         ...

Any help will be much appreciated.

check with str.contains

BENY
– BENY

2020-04-28 13:18:18 +00:00
Commented Apr 28, 2020 at 13:18 — BENY
– BENY, Commented Apr 28, 2020 at 13:18

Amrith Krishna · Accepted Answer · 2020-04-28 13:25:28Z

1

Basically the idea would be to use str.split() followed by explode(). Something like the follwoing code should help.

(df.set_index(['EmployeeId']).apply(lambda x:x.str.split('|').explode()).reset_index())

The output would be like

  EmployeeId       City
0        157     Mumbai
1        157  Bangalore
2        292       Pune
3        292     Mumbai
4        292      Delhi
5        643     Mumbai
6        643  Bangalore
7        124     Mumbai
8        124       Pune
9        355  Bangalore

answered Apr 28, 2020 at 13:25

Amrith Krishna

2,8713 gold badges39 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Amrith Krishna Over a year ago

You may refer to the related question here: stackoverflow.com/questions/50731229/…

GIRISH kuniyal Over a year ago

(df.set_index(['EmployeeId']).apply(lambda x:x.str.split('|').explode()).reset_index()).groupby(['EmployeeId','City']).count().to_html('test1.html') I think little additional code may help with this example

BENY · Accepted Answer · 2020-04-28 13:58:55Z

1

Let us do

yourdf=df.City.str.split('|').explode().to_frame('City').join(df[df.columns.difference(['City'])])
        City EmployeeId
0     Mumbai        157
0  Bangalore        157
1       Pune        292
1     Mumbai        292
1      Delhi        292
2     Mumbai        643
2  Bangalore        643
3     Mumbai        124
3       Pune        124
4  Bangalore        355

answered Apr 28, 2020 at 13:58

BENY

324k22 gold badges176 silver badges250 bronze badges

Comments

taf66 · Accepted Answer · 2020-04-29 07:20:05Z

0

Many thanks for your helps!

I try this way. I am sure there are better codes.

import pandas as pd # Import the data
df = pd.DataFrame({'EmployeeId': ['157', '292', '643', '124', '355'],
                   'City': ['Mumbai|Bangalore', 'Pune|Mumbai|Delhi', 'Mumbai|Bangalore', 'Mumbai|Pune', 'Bangalore']})
#print(df)

df1 = df["City"].str.split("|", expand=True).stack().reset_index(level=1, drop=True)
#print(df1)

df2 = pd.concat([df, df1], axis=1, sort=False)
#print(df2)

df2 = df2.drop(["City"], axis=1)
#print(df2)

df2.loc[(df2["EmployeeId"].duplicated()  ), ["EmployeeId"]] = ''
df2.columns = ['EmployeeId', 'City New']
print(df2)

df2.to_html('test1.html')

Output:

  EmployeeId   City New
0        157     Mumbai
0             Bangalore
1        292       Pune
1                Mumbai
1                 Delhi
2        643     Mumbai
2             Bangalore
3        124     Mumbai
3                  Pune
4        355  Bangalore

When export it to_html i get this:

Image 1: With Grid

Is there anyway to export it like this without grid (maybe with styles)?

Image 2: Without Grid

answered Apr 29, 2020 at 7:20

taf66

13 bronze badges

2 Comments

Amrith Krishna Over a year ago

It would be ideal if you update the relevant portion in the question itself, rather than adding it in the answer.

taf66 Over a year ago

Thanks for your suggestion, in future i will keep in mind

Collectives™ on Stack Overflow

Python Pandas wrap text in cell output it to_html

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related