Is there a way to export pandas dataframe info -- df.info() into an excel file?

user_8924989 0
Viewed 5761

I have a .csv file locally. I am reading the file with pandas. I want to move the df.info() result into an excel. Looks like df.info().to_excel does not work as it is not supported. Is there any way to do this?

I tried df.info().to_excel

import pandas as pd
from openpyxl.workbook import Workbook
pd.read_csv("file.csv",sep='|', error_bad_lines=False)
writer = pd.ExcelWriter('output.xlsx')
df.info()
df.info().to_excel(writer,sheet_name='info')

I want to show the dataframe info output in a single tab of the excel.

pandas python-2.7 dataframe
4 Answers

The easiest way for me is to get the same information in dataframes, but separately:

df_datatypes = pd.DataFrame(df.dtypes)
df_null_count = df.count()

Then write to excel as usual.

user_91621930

to_excel is a method of the DataFrame https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.to_excel.html and DataFrame.info() doesn't return a DataFrame

You can write the info to a text file like so:

import io
buffer = io.StringIO()
df.info(buf=buffer)
s = buffer.getvalue()
with open("df_info.txt", "w", encoding="utf-8") as f:
    f.write(s)

You can modify this code by removing last two lines and parsing the s variable and creating a DataFrame out of it (in the way you would like this to appear in the excel file) and then use the to_excel() method.

user_11851380

I agree with @yl_low but you could have a more elegant solution as shown:

def get_dataframe_info(df):
    """
    input
       df -> DataFrame
    output
       df_null_counts -> DataFrame Info (sorted)
    """

    df_types = pd.DataFrame(df.dtypes)
    df_nulls = df.count()
    
    df_null_count = pd.concat([df_types, df_nulls], axis=1)
    df_null_count = df_null_count.reset_index()
    
    # Reassign column names
    col_names = ["features", "types", "non_null_counts"]
    df_null_count.columns = col_names
    
    # Add this to sort
    df_null_count = df_null_count.sort_values(by=["null_counts"], ascending=False)
    
    return df_null_count
user_96967450

You can do this in Python 3.

pd.DataFrame({"name": train.columns, "non-nulls": len(train)-train.isnull().sum().values, "nulls": train.isnull().sum().values, "type": train.dtypes.values}).to_excel("op.xlsx")
user_184634700
Related