Making a txt file using excel sheet data

Question

I am a biology student and have no experience in coding. I want to make txt files from specific data from excel files. The data in the txt file should be in the format given in the txt file where

center_x = data from center_x and pocket 1 (col 7, row 2)
center_y = data from center_y and pocket 1 (col 8, row 2)
center_z = data from center_z and pocket 1 (col 9, row 2)

and

size_x = 60
size_y = 60
size_z = 60

energy_range = 10

How can I make a python script which can do this for 1000 excel files? Any help will be highly appreciated.

Hi, could you copy paste the header of the text file and the excel file if it's csv, otherwise an image would be great. Because from where I'm connected I can't get access to google drive ;) — ArrowRise, Jul 28 '22 at 09:04
first do it for single file and put code in function -ie. `my_function(filename)` - and later use `for`-loop to execute this function for many files on list. — furas, Jul 28 '22 at 09:15
what did you try? Where is your code. First you have to read data from file using i.e. `pandas.read_excel()` or `pandas.read_csv()`, later create one string with all needed lines and later write it in file. — furas, Jul 28 '22 at 09:17
do you want all values in one file or values from every excel in separated file? — furas, Jul 28 '22 at 09:20
generating only one txt file from one excel file and then do this for every excel file in a folder. — user3765252, Jul 28 '22 at 09:26

score 0 · Accepted Answer · answered Jul 28 '22 at 09:40

First do it for single file and put code in function which get filename as argumetn - ie. function(filename) - and later use for-loop to execute it for many filenames on list.

import pandas as pd

def function(filename):
    df = pd.read_csv(filename, sep='\s*,\s*', engine='python')
    #print(df.columns)
    
    row = df[ df['name'] == 'pocket1' ]
    x = row['center_x'][0]
    y = row['center_y'][0]
    z = row['center_z'][0]
    
    #print(x, y, z)
    
    text = ""
    text += f"center_x = {x}\n"
    text += f"center_y = {y}\n"
    text += f"center_z = {z}\n"
    text += f"\n"    
    text += f"size_x = 60\n"    
    text += f"size_y = 60\n"    
    text += f"size_z = 60\n"
    text += "\n"
    text += "energy_range = 10\n"
    
    print(text)
    
    with open(filename + '.txt', 'w') as fh:
        fh.write(text)
        
# --- main ---

#import os
#all_filenames = os.listdir()

#import glob
#all_filenames = glob.glob('*.csv')

all_filenames = ['structure.pdb_predictions.csv']

for filename in all_filenames:
    function(filename)

I got some problems in windows but it worked in linux! thanks a lot for the help. I am not able to upvote because of new account but thank you for your contribution!! — user3765252, Jul 28 '22 at 10:55

Making a txt file using excel sheet data

1 Answers1