Search code examples
pandasappendgoogle-colaboratorymultiple-columnsexport-to-excel

Add new columns to excel file from multiple datasets with Pandas in Google Colab

I'm trying to add some columns to a excel file after some data but I'm not having good results just overwriting what I have. Let me give you some context: I'm reading a csv, for each column I'm using a for to value_counts and then create a frame from this value_counts here the code for just one column:

import pandas as pd

data= pd.read_csv('responses.csv')

datatoexcel = data['Music'].value_counts().to_frame()

datatoexcel.to_excel('savedataframetocolumns.xlsx') #Name of the file

This works like this ...

enter image description here enter image description here

And with that code for only one column I have the format that I actually need for excel.

But the problem is when I try to do it with for to all the columns and then "Append" to excel the following dataframes using this formula:

for columnName in df:
    datasetstoexcel = df.value_counts(columnName).to_frame()
    print(datasetstoexcel)
    # Here is my problem with the following line the .to_excel
    x.to_excel('quickgraph.xlsx') #I tried more code lines but I'll leave this one as base

The result that I want to reach is this one:

enter image description here

I'm really close to finish this code, some help here please!

Solution

  • How about this?

    Sample data

    df = pd.DataFrame({
    
    "col1": [1,2,3,4],
    "col2": [5,6,7,8],
    "col3": [9, 9, 11, 12],
    "col4": [13, 14, 15, 16],
})

    Find value counts and add to a list

    li = []

for i in range(0, len(df)):
    value_counts = df.iloc[:, i].value_counts().to_frame().reset_index()
    li.append(value_counts)

    concat all the dataframes inside li and write to excel

    pd.concat(li, axis=1).to_excel("result.xlsx")

    Sample output:

    enter image description here