Replacing Pandas or Numpy Nan with a None to use with MysqlDB

I am trying to write a Pandas dataframe (or can use a numpy array) to a mysql database using MysqlDB . MysqlDB doesn't seem understand 'nan' and my database throws out an error saying nan is not...

How can I map True/False to 1/0 in a Pandas DataFrame?

I have a column in python pandas DataFrame that has boolean True/False values, but for further calculations I need 1/0 representation. Is there a quick pandas/numpy way to do that?

Pandas: change data type of Series to String

I use Pandas 'ver 0.12.0' with Python 2.7 and have a dataframe as below: df = pd.DataFrame({'id' : [123,512,'zhub1', 12354.3, 129, 753, 295, 610], 'colour': ['black',...

Get data from pandas into a SQL server with PYODBC

I am trying to understand how python could pull data from an FTP server into pandas then move this into SQL server. My code here is very rudimentary to say the least and I am looking for any...

Find the column name which has the maximum value for each row

I have a DataFrame like this one: In [7]: frame.head() Out[7]: Communications and...

Format y axis as percent

I have an existing plot that was created with pandas like this: df['myvar'].plot(kind='bar') The y axis is format as float and I want to change the y axis to percentages. All of the solutions I...

Convert commas decimal separators to dots within a Dataframe

I am importing a CSV file like the one below, using pandas.read_csv: df = pd.read_csv(Input, delimiter=";") Example of CSV file: 10;01.02.2015 16:58;01.02.2015...

Convert categorical data in pandas dataframe

I have a dataframe with this type of data (too many columns): col1 int64 col2 int64 col3 category col4 category col5 category Columns seems like this: Name:...

move column in pandas dataframe

I have the following dataframe: a b x y 0 1 2 3 -1 1 2 4 6 -2 2 3 6 9 -3 3 4 8 12 -4 How can I move columns b and x such that they are the last 2 columns in the dataframe?...

Sort a pandas dataframe based on DateTime field

I am trying to sort a dataframe based on DateTime field which is of datatype datetime64[ns]. My dataframe looks like this: Name DateTime1 P38 NaT P62 2016-07-13 16:03:32.771 P59 ...

pandas 0.18: out of memory error when reading CSV file with categoricals

I am trying to read 3GB file (2.5 million rows, mostly categorical (string) data) into Pandas dataframe with read_csv function and get error: out of memory I am on PC with Pandas 0.18 version and...

How to floor a date to the first date of that month?

I have a pandas DataFrame with index column = date. Input: value date 1986-01-31 22.93 1986-02-28 15.46 I want to floor the date to the first day of that month Output: ...

How to plot CSV data using matplotlib and pandas in python

I have a python code in which I read a csv file using pandas and store date and time in one column Datetime. Now i want to plot Sensor Value on y-axis and datatime on x-axis. How can i achieve...

Python: pandas merge multiple dataframes

I have diferent dataframes and need to merge them together based on the date column. If I only had two dataframes, I could use df1.merge(df2, on='date'), to do it with three dataframes, I use...

How to push data to elasticsearch from dataframe in python

I am trying to use elastic search libraries like pyelasticsearch and elasticsearch I am not getting any method where i can pass dataframe and that method will load data frame data to elastic...

InterfaceError: 2013: Lost connection to MySQL server during query

When I'm trying to connect to the MySql Database server in Python on Google Cloud Platform, I'm getting the error. The following is the actual code. import mysql.connector import pandas as pd cnx...

Python Pandas update a dataframe value from another dataframe

I have two dataframes in python. I want to update rows in first dataframe using matching values from another dataframe. Second dataframe serves as an override. Here is an example with same data...

Unexpected behavior in pandas rolling correlation using offset

This is input pandas DataFrame: df = pd.DataFrame({'x':(1,2), 'y':(2,3)}, index=[pd.Timestamp('20180101'), pd.Timestamp('20180102')]) Why would these two return different...

Issues with extracting substrings of a string in Python Pandas Dataframe

I have an expression like ( one row of a column, say 'old_col' in pandas data frame) ( Shown the top two rows from a column of the dataframe ) abcd_6.9_uuu ghaha_12.8 _sksks abcd_5.2_uuu...

Why does PANDAS only see one column to csv dataset with numerous columns?

I am new to and PANDAS and I am trying to work out why the shape of this csv dataset[https://www.kaggle.com/vfoufikos/airbnb-analysis-lisbon][1] is being shown as: (237, 1)? As it appears that the...

Missing optional dependency 'tables'. In pandas to_hdf

following code is giving me error. import pandas as pd df = pd.DataFrame({'a' : [1,2,3]}) df.to_hdf('temp.h5', key='df', mode='w') This is giving me error. Missing optional dependency...

Merge daily and monthly Timeseries with Pandas

I have two dataframes, "bio" with monthly data, where I only need the column bio['Bio_Avg']. And another one "tc" with daily data. Both with Datetime index. What I need is to merge both keeping...

Python Pandas ExcelWriter append to sheet creates a new sheet

I would I really appreciate some help. I'm trying to use a loop to create sheets, and add data to those sheets for every loop. The position of my data is correct, however Panda ExcelWriter creates...

How to add data labels to seaborn barplot?

I have the following code to produce a bar plot in seaborn import pandas as pd import matplotlib.pyplot as plt import seaborn as sns df = pd.DataFrame(np.random.randint(0,100,size=(100, 4)),...

remove index & headers from dataframe while styling

I am reading an xlsx & creating a html while applying some style using jinja2 import pandas import jinja2 df = pandas.read_excel('C:\\Users...\\2020.xlsx', 'TEST', usecols...

Pandas-Export to CSV as a string (not date format)

Pandas DataFrames - how do I export list 'X' to a CSV so it appears as a string? The problem is when I open the CSV using Excel it appears in date format. X=['1-4', '1-5', '2-3', '4-8'] ie. when...

How to remove a character from some rows in a dataframe column?

I have a large dataframe that I need to clean, as a sample please look at this dataframe import pandas as pd cars = {'Brand': ['Honda Civic','Toyota Corolla','Ford Focus','Audi A4','Suzuki'], ...

Pandas: how to make openpyxl the default engine for all read_excel operations?

Since read_exceldefault engine xlrd has been deprecated in newer pandas releases, how do I make openpyxl the default engine of all my pd.read_excel calls? Now, if I update pandas, I must put the...

How to add a checkbox in pandas dataframe

I have created a dataframe as: import pandas as pd data = [['Ankit'], ['Akshat' ]] df = pd.DataFrame(data, columns = ['Name']) Now, I want to insert a column PPA which has checkbox button as...

Pandas json column store as nested dataframe

I have data-frame which contains json column and is quiet huge and is not very efficient, i would like to store it as nested data frame. So sample data-frame looks like: id ...