Inserting a list into csv with proper format using python

Question

I have a list with string items in it. The list is given below:

mylines = ['Status Name               DisplayName', 'Running MSSQL$DEV          SQL Server (DEV)', 'Running MSSQL$TEST         SQL Server (TEST)', 'Running MSSQLLaunchpad$DEV SQL Server Launchpad (DEV)', 'Running SQLAgent$DEV       SQL Server Agent (DEV)', 'Running SQLAgent$TEST      SQL Server Agent (TEST)', 'Running SQLBrowser         SQL Server Browser', 'Running SQLTELEMETRY$DEV   SQL Server CEIP service (DEV)', 'Running SQLTELEMETRY$TEST  SQL Server CEIP service (TEST)', 'Running SQLWriter          SQL Server VSS Writer']

I want to insert this list into a csv file in such a way that Status Name and DisplayName becomes the column headers and the whole format becomes as below:

I used the following code:

Import pandas as pd
df = pd.DataFrame(mylist)
df.head()

and the following comes up:

which is incorrect as it is only creating a single column instead of the two separate Status Name and DisplayName Column. How can I properly create the dataframe?

Ynjxsjmh · Accepted Answer · 2021-04-19 14:15:02Z

1

You can try to split the item in list with multiple spaces. And export that dataframe to csv without header and index so that the first row is as like header.

import pandas as pd

df = pd.DataFrame([filter(len, item.split('  ')) for item in mylist])

df.to_csv('your_csv', header=None, index=False)

If all your DisplayName has heading SQL Server, you can do

df = pd.DataFrame([item.split('SQL Server') for item in mylist[1:]], columns=['Status Name', 'DisplayName'])
df['DisplayName'] = 'SQL Server' + df['DisplayName']

# Remove heading and tailing spaces in column `Status Name`
df['Status Name'] = df['Status Name'].str.strip()

edited Apr 19, 2021 at 14:15

answered Apr 19, 2021 at 13:44

Ynjxsjmh

30.3k7 gold badges43 silver badges64 bronze badges

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

2 Comments

mangupt Over a year ago

Split should be on Tab character. Running MSSQLLaunchpad$DEV SQL Server Launchpad (DEV)

Ynjxsjmh Over a year ago

@mangupt The string in OP's list doesn't contains tab character.

Tomerikoo · Accepted Answer · 2021-04-19 16:40:03Z

0

Try with:

df[['A', 'B']] = df['AB'].str.split('    ', 1, expand=True)

edited Apr 19, 2021 at 16:40

Tomerikoo

19.6k16 gold badges57 silver badges68 bronze badges

answered Apr 19, 2021 at 13:40

Sala

4804 silver badges21 bronze badges

Collectives™ on Stack Overflow

Inserting a list into csv with proper format using python

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related