Extract Data from .csv file with Python



  • I am new to Python but need to autofilter the data from the excel sheet according to the Engineer name and Age of the tickets in the excel.I need to filter the data above 15 Days and copy to the another sheet of the excel.Is this possible through Python



  • @lakshmana said in Python with Excel Auto Filter and Extract Data:

    I am new to Python but need to autofilter the data from the excel sheet according to the Engineer name and Age of the tickets in the excel.I need to filter the data above 15 Days and copy to the another sheet of the excel.Is this possible through Python

    Why all these oddball requests? An excel macro can do this easily and takes seconds to create.



  • Need to learn automation from python so trying that .Is that possible to automate through macro ?



  • If you're working within Excel, then Python is not what you're looking for.
    If you're manipulating files, preferably csv files, then Python is your friend.

    I bet you there's a nice module that makes it easy to work with csv files, and then saving into a csv file is pretty easy.



  • Whether macros can be used in the .csv files also ?



  • I once had to work with a database that the only connection we had to it was through Telnet.

    The queries could return xml output, however it was a nested database, any queries utilizing nested relations wouldn't preserve the relationship in xml.

    So I had to capture the xml, then work with in python and create the associates, filter it how i wanted it, and then export to csv.

    The csv part was the easiest.



  • @lakshmana said in Python with Excel Auto Filter and Extract Data:

    Need to learn automation from python so trying that .Is that possible to automate through macro ?

    Is it possible, probably. Is it the correct use of using python to automate things, then no. Try http://www.pythonforbeginners.com/code-snippets-source-code/python-code-examples



  • @lakshmana said in Python with Excel Auto Filter and Extract Data:

    Whether macros can be used in the .csv files also ?

    CSV files are just comma-separated text files, and hold no formatting or macro capabilities.

    I haven't looked at Python about this, but I found this for PHP: https://github.com/eaglewu/phpexcel



  • @lakshmana said in Python with Excel Auto Filter and Extract Data:

    I am new to Python but need to autofilter the data from the excel sheet according to the Engineer name and Age of the tickets in the excel.

    Two things here then are a problem.

    1. It is not an Excel file if it is CSV. So this changes the question completely. It's about CSV files.
    2. You don't do this in Excel, you just use Python.

    So the question should be "how to extract data from text file with Python". Extremely different than the question asked.



  • @scottalanmiller said in Python with Excel Auto Filter and Extract Data:

    @lakshmana said in Python with Excel Auto Filter and Extract Data:

    I am new to Python but need to autofilter the data from the excel sheet according to the Engineer name and Age of the tickets in the excel.

    Two things here then are a problem.

    1. It is not an Excel file if it is CSV. So this changes the question completely. It's about CSV files.
    2. You don't do this in Excel, you just use Python.

    So the question should be "how to extract data from text file with Python". Extremely different than the question asked.

    I will try with .csv file and then convert that file into excel by powershell



  • @lakshmana said in Extract Data from .csv file with Python:

    I am new to Python but need to autofilter the data from the excel sheet according to the Engineer name and Age of the tickets in the excel.I need to filter the data above 15 Days and copy to the another sheet of the excel.Is this possible through Python

    It is indeed possible to do with python. You have great libraries built just for data analysis and manipulation for cases like this.



  • Now that I have some free time, I'll give you a simple example of some of the things you can do using pandas.

    Our dataset will be all the posts in this topic, scraped and saved into an excel file.

    DATASET : pandas-test.xslx
    alt text

    I am going to be using a Jupyter notebook just to make the output clearer.

    $ import pandas
    $ pandas.read_excel('pandas-test.xlsx')
    

    alt text
    That is the whole spreadsheet read and basically printed out, but we can't work with that. We need to read the file into a variable and start working with it.

    # Reading the file to variable df
    $ df = pandas.read_excel('pandas-test.xlsx')
    
    # Printing how many rows and column in the file (rows,columns)
    $ df.shape
    (11,4)
    
    # Printing the column names
    $ df.columns
    Index(['Date', 'Time ', 'User', 'Post'], dtype='object')
    

    Only extracting columns - Date and User

    $ df[['Date', 'User']]
    

    alt text

    Lets check how many posts per day

    $ df.groupby(['Date'])['User'].count()
    

    alt text
    Now lets check by day and time

    $ df.groupby(['Date', 'Time ']).count()
    

    alt text


    Lets filter only your posts and create a new csv file based on the data found.

    $ subset = df.loc[df['User']=='Lakshmana']
    

    alt text

    Create a csv file only containing your posts

    $ subset.to_csv('Lakshmana-posts.csv')
    

    Final results your new csv file with your posts filtered out.

    Lakshmana-posts.csv
    alt text


    EDIT: Just if you need it, an example of filtering by date and user
    alt text



  • @romo said in Extract Data from .csv file with Python:

    Now that I have some free time, I'll give you a simple example of some of the things you can do using pandas.

    Our dataset will be all the posts in this topic, scraped and saved into an excel file.

    DATASET : pandas-test.xslx
    alt text

    I am going to be using a Jupyter notebook just to make the output clearer.

    $ import pandas
    $ pandas.read_excel('pandas-test.xlsx')
    

    alt text
    That is the whole spreadsheet read and basically printed out, but we can't work with that. We need to read the file into a variable and start working with it.

    # Reading the file to variable df
    $ df = pandas.read_excel('pandas-test.xlsx')
    
    # Printing how many rows and column in the file (rows,columns)
    $ df.shape
    (11,4)
    
    # Printing the column names
    $ df.columns
    Index(['Date', 'Time ', 'User', 'Post'], dtype='object')
    

    Only extracting columns - Date and User

    $ df[['Date', 'User']]
    

    alt text

    Lets check how many posts per day

    $ df.groupby(['Date'])['User'].count()
    

    alt text
    Now lets check by day and time

    $ df.groupby(['Date', 'Time ']).count()
    

    alt text


    Lets filter only your posts and create a new csv file based on the data found.

    $ subset = df.loc[df['User']=='Lakshmana']
    

    alt text

    Create a csv file only containing your posts

    $ subset.to_csv('Lakshmana-posts.csv')
    

    Final results your new csv file with your posts filtered out.

    Lakshmana-posts.csv
    alt text


    EDIT: Just if you need it, an example of filtering by date and user
    alt text

    Good example thanks to making me understand



  • @romo said in Extract Data from .csv file with Python:

    Printing how many rows and column in the file (rows,columns)

    $ df.shape
    (11,4)

    If i need select the sheet 2 in the Excel means what needs to be done ?



  • You have to read the file differently, and then read the sheet you want passing the file object and then the sheet name.


    DATASET: pandas-test-xlsx, Sheet - Test-Sheet
    alt text

    # Read the whole file
    $ file = pandas.ExcelFile('pandas-test.xlsx')
    
    # Specify the file and then the sheet you want to work with
    $ mysheet = pandas.read_excel(file, 'Test-Sheet')
    
    # Work with the dataframe now contained in the mysheet variable
    $ mysheet
    

    alt text


    EDIT: Just doubled checked an you can pass the file name as a string to the read_excel function so that would be alot easier.

    pandas.read_excel('pandas-test.xlsx', 'Test-Sheet')
    


  • @romo said in Extract Data from .csv file with Python:

    @lakshmana said in Extract Data from .csv file with Python:

    I am new to Python but need to autofilter the data from the excel sheet according to the Engineer name and Age of the tickets in the excel.I need to filter the data above 15 Days and copy to the another sheet of the excel.Is this possible through Python

    It is indeed possible to do with python. You have great libraries built just for data analysis and manipulation for cases like this.

    All the three package installed in Windows only Pandas not installing I am using Python 3.7



  • I usually install Anaconda in windows because I use other things included in it and it is the easiest way of having everything setup for you, but it might really be overkill for you to install it because it is pretty big.

    You can also install miniconda and download the required packages from it.

    The official documentation marks the above two methods as the easiest for beginners but you can also install it from Pypi. pip install pandas



  • @romo said in Extract Data from .csv file with Python:

    pip install pandas

    raise DistutilsError("Setup script exited with %s" % (v.args[0],))
    distutils.errors.DistutilsError: Setup script exited with error: Microsoft Visual C++ 14.0 is required. Get it with "Microsoft Visual C++ Build Tools": http://landinghub.visualstudio.com/visual-cpp-build-tools



  • @lakshmana said in Extract Data from .csv file with Python:

    @romo said in Extract Data from .csv file with Python:

    pip install pandas

    raise DistutilsError("Setup script exited with %s" % (v.args[0],))
    distutils.errors.DistutilsError: Setup script exited with error: Microsoft Visual C++ 14.0 is required. Get it with "Microsoft Visual C++ Build Tools": http://landinghub.visualstudio.com/visual-cpp-build-tools

    Some of the dependencies when installing it with pip are required to be compiled that is why for beginners it is just better to use either Anaconda or miniconda, especially for a Windows install.


Log in to reply