1

I am working on django project.where user can upload a csv file and stored into database.Most of the csv file i saw 1st row contain header and then under the values but my case my header presents on column.like this(my csv data) I did not understand how to save this type of data on my django model.

Rakibul bashar
  • 25
  • 6
  • so you need to construct suitable object first or you can write your own parser – Gahan Apr 05 '18 at 05:17
  • That's a screenshot of a spreadsheet but once you export it you won't have a traditional csv where each column is one data type. You can use the csv module to break out the cells but you'll need to write your own parser above that. It will need to read in multiple lines and build data columns from multiple rows. Unless that's an ad=hoc spreadsheet that is not following any hard-and-fast rules. Then you'll have to fix it by hand. – tdelaney Apr 05 '18 at 05:22
  • 2
    I recommend taking a look at pandas library for transforming csv data. You can for example read csv, transform it and save to db. All with pandas – Quba Apr 05 '18 at 05:23
  • SKip every row's `0` the element, that's it. – Anup Yadav Apr 05 '18 at 05:25
  • How to skip every row's 0 element.please give some instruction. Anup Yadav – Rakibul bashar Apr 05 '18 at 06:44
  • Please either post your loop of csv code, so that I can exactly help you or just understand that you are doing loop for file and you get row with array so you can simply `continue` for that `0` th index. – Anup Yadav Apr 05 '18 at 07:13

3 Answers3

2

You can transpose your data. I think it is more appropriate for your dataset in order to do real analysis. Usually things such as id values would be the row index and the names such company_id, company_name, etc would be the columns. This will allow you to do further analysis (mean, std, variances, ptc_change, group_by) and use pandas at its fullest. Thus said:

import pandas as pd
df = pd.read_csv('yourcsvfile.csv')
df2 = df.T

Also, as @H.E. Lee pointed out. In order to save your model to your database, you can either use the method to_sql in your dataframe to save in mysql (e.g. your connection), if you're using mongodb you can use to_json and then import the data, or you can manually set your function transformation to your database.

silgon
  • 6,890
  • 7
  • 46
  • 67
  • I am with silgon on organizing your csv data by loading the data into pandas as dataframe and swap the rows for columns, vice versa (transpose). And then I would add on to actually answer your question (silgon you may actually want to edit this in and i can upvote your ans) on how to save into django models : use pandas df.to_sql() with the right db connectors. to do this you can refer to stefan's excellent answer here https://stackoverflow.com/questions/37688054/saving-a-pandas-dataframe-to-a-django-model – aaronlhe Apr 05 '18 at 14:28
  • Thanks silgon and H.E.Lee. – Rakibul bashar Apr 08 '18 at 11:23
  • You're welcome. And thanks @H.E.Lee, I complemented the answered based on your comment. – silgon Apr 08 '18 at 19:15
0

You can flip it with the built-in CSV module quite easily, no need for cumbersome modules like pandas (which in turn requires NumPy...)... Since you didn't define the Python version you're using, and this procedure differs slightly between the versions, I'll assume Python 3.x:

import csv

# open("file.csv", "rb") in Python 2.x
with open("file.csv", "r", newline="") as f:  # open the file for reading
    data = list(map(list, zip(*csv.reader(f))))  # read the CSV and flip it

If you're using Python 2.x you should also use itertools.izip() instead of zip() and you don't have to turn the map() output into a list (it already is).

Also, if the rows are uneven in your CSV you might want to use itertools.zip_longest() (itertools.izip_longest() in Python 2.x) instead.

Either way, this will give you a 2D list data where the first element is your header and the rest of them are the related data. What you plan to do from there depends purely on your DB... If you want to deal with the data only, just skip the first element of data when iterating and you're done.

zwer
  • 24,943
  • 3
  • 48
  • 66
  • Thanks @zwer i already done your instruction.It's work fine.I need some suggestion on my_csv_data.when i flip it multiple date fields treated as a column.My data has indicator column which has different indicator name,under the indicator name you can see date wise values on it.In that case how to handle this. – Rakibul bashar Apr 09 '18 at 07:24
-1

Given your data it may be best to store each row as a string entry using TextField. That way you can be sure not to lose any structure going forward.

Alper
  • 3,424
  • 4
  • 39
  • 45