1

When the CSV file is created the columns are not in the right place as I wanted them to be. For example the column ‘'Period’ (the variable for this is ‘RD’) is the second column in the file etc. etc.

Is there a way to set the placement of each column to where I want it?

My Code:

from datetime import datetime
from elasticsearch import Elasticsearch
import csv

es = Elasticsearch(["9200"])


res = es.search(index="search", body=
                {
                    "_source": ["VT","NCR","N","DT","RD"],
                    "query": {

                        "bool": {
                            "must": [{"range": {"VT": {
                                            "gte": "now/d",
                                            "lte": "now+1d/d"}}},

                                {"wildcard": {"user": "mike*"}}]}}},size=10)


csv_file = 'File_' + str(datetime.now().strftime('%Y_%m_%d - %H.%M.%S')) + '.csv'


header_names = { 'VT': 'Date', 'NCR': 'ExTime', 'N': 'Name', 'DT': 'Party', ' RD ': 'Period'}



with open(csv_file, 'w', newline='') as f:
    header_present  = False
    for doc in res['hits']['hits']:
        my_dict = doc['_source']
        if not header_present:
            w = csv.DictWriter(f, my_dict.keys())
            w.writerow(header_names,) 
            header_present = True
             w.writerow(my_dict)
Improve this question
2
  • Does not work like that, i have tried it Commented Oct 18, 2017 at 15:16
  • @RomanPerekhrest That's an Elasticsearch list, not a Python list. The results are JSON, so order is not preserved Commented Oct 18, 2017 at 15:40

4 Answers 4

Reset to default
6

Using pandas its very simple:

import pandas as pd


# Read csv / tab-delimited in this example
df = pd.read_csv('example.csv', sep='\t')

print df

   A  B  C
0  4  5  9
1  4  5  9
2  4  5  9
3  4  5  9

# Reorder columns
df = df[['C', 'A', 'B']]

print df

   C  A  B
0  9  4  5
1  9  4  5
2  9  4  5
3  9  4  5

# Write csv / tab-delimited
df.to_csv('example.csv', sep='\t')
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

keeping in mind that my CSV file name is generated with the current date and time stamp
@Rich Filenames are irrelevant to column reordering - And you can load the CSV into Pandas after writing it.
2

Dictionaries are not ordered, if you want to enforce column ordering, you need to explicitly specify that 

import csv
headers = ['Party', 'Period', 'Date', 'ExTime', 'Name'] # Don't use my_dict.keys()
with open('header.csv', 'w') as f:
    w = csv.DictWriter(f, fieldnames=headers)
    w.writeheader()

See 

$ python sample.py && cat header.csv
Party,Period,Date,ExTime,Name

And when you call w.writerow(my_dict), the dictionary will be ordered according to the header. 

row = {'Period':2, 'Date':3, 'Name':5, 'Party': 1, 'ExTime':4}
w.writerow(row)

Outputs

Party,Period,Date,ExTime,Name
1,2,3,4,5
Improve this answer

7 Comments

I tried your suggestion, it didn't work. i guess you will need to show me how to with my code. If you don't mind
"Didn't work" how? Errors? Not sure this answer deserved a downvote
also your code above is not grabbing the same CSV filename that i have. The file name i have is generated by current date and time.
will this conflict with my header_names and the data behind it?
Regarding the name of the file, you already have csv_file as the string. Just use it rather than 'header.csv'. Are you having issues with that?
|
1

As you are dealing with csv files it is better if use pandas for your application.

import pandas as pd

# Let your file have 4 columns named c1, c2, c3 and c4
# And assume you want to reorder it to c2, c3, c1, c4

data_frame = pd.read_csv('filename.csv', delimiter=',') # reading csv file as data frame with pandas

new_data_frame = data_frame[['c2', 'c3', 'c1', 'c4']] # reordered the dataframe and stored in new_data_frame

# If you want to save the result to new csv file

new_data_frame.to_csv('altered.csv', index=None)

In your case assuming the order of columns and delimiter is ','

import pandas as pd

csv_file_name = 'File_' + str(datetime.now().strftime('%Y_%m_%d - %H.%M.%S')) + '.csv'

data_frame = pd.read_csv(csv_file_name, delimiter=',') # change delimiter to '\t' if needed

new_data_frame = data_frame[['Party', 'Period', 'Date', 'ExTime', 'Name']]

new_data_frame.to_csv('filename.csv', index=None)
Improve this answer

12 Comments

how would i use this with my code above? could you show me please
What all are the column headers in your csv.? And what order you want it.?
they are listed in my code. so in my code you can see the header names so - header_names = { 'VT': 'Date', 'NCR': 'ExTime', 'N': 'Name', 'DT': 'Party', ' RD ': 'Period'}. so the columns are Date, ExTime, Name and Party i have more but if you show me how to do this that will be good. keeping in mind that my CSV file name is a fixed name as it grabs the current date and time.
What order you really want the columns to be changed.?
for example, 'Party', 'Period, 'Date'. 'ExTime', 'Name'
|
0 
import pandas as pd

df  = pd.read_csv('pokemon_data.csv')`enter code here`

'Name', 'Type 1', 'Type 2', 'HP', 'Attack', 'Defense', 'Sp. Atk','Sp. Def', 'Speed', 'Generation', 'Legendary'

(My data frame consists of same column names in the same order)

df['Total']=df.iloc[:,4:10].sum(axis=1)

`'Name', 'Type 1', 'Type 2', 'HP', 'Attack', 'Defense', 'Sp. Atk','Sp. Def', 'Speed', 'Generation', 'Legendary', 'Total'`

(adding a new column Total which is the sum of hp, attack, defence, sp.atk, sp.def,speed)

cols = list(df.columns.values)
df = df[cols[0:4]+[cols[-1]]+cols[4:12]]

'Name', 'Type 1', 'Type 2','Total', 'HP', 'Attack', 'Defense', 'Sp. Atk','Sp. Def', 'Speed', 'Generation', 'Legendary'

(Moving the 'Total' columns)

Improve this answer

1 Comment

Please provide additional details in your answer. As it's currently written, it's hard to understand your solution.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.