How to Read Text file as input and write into excel columns using panda?

Question

I am using permission.txt file as intput and want to write data into columns of excel -2007(I am using panda XlsxWriter becasue I want more than 256 columns). I want to write like this into excel file. I have tried following code up to this which writes data in rows instead I want to write data into columns(column 1,column 2......column 400).

import pandas as pd
from pandas import ExcelWriter
from pandas import ExcelFile
import numpy as np


data = pd.read_csv('F:/Research_Work/python/Permission.txt', sep=" ", header=None)
writer = ExcelWriter('Example2.xlsx')
data.to_excel(writer,'Sheet1',index=False)

jmcnamara · Accepted Answer · 2017-11-30 13:57:36Z

1

You could just transpose the dataframe data, like this:

import pandas as pd

# Create a Pandas dataframe from some data.
df1 = pd.DataFrame({'Data': [10, 20, 30, 40]})
df2 = df1.T

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('pandas_simple.xlsx', engine='xlsxwriter')

# Write the data in column and transposed (row) directions.
df1.to_excel(writer, sheet_name='Sheet1', 
             startrow=1, startcol=1, header=False, index=False)

df2.to_excel(writer, sheet_name='Sheet1', 
             startrow=1, startcol=3, header=False, index=False)

# Close the Pandas Excel writer and output the Excel file.
writer.save()

Output:

answered Nov 30, 2017 at 13:57

jmcnamara

42.1k7 gold badges98 silver badges114 bronze badges

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

1 Comment

Suman Tiwari Over a year ago

Thanks this work for me. I have added followingto read text file. df1 = pd.read_table('F:/Research_Work/python/Permission.txt', delim_whitespace=True) . Please add in your answer.

Collectives™ on Stack Overflow

How to Read Text file as input and write into excel columns using panda?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related