onlyforbopi
2/14/2017 - 8:23 AM

Python.Modules.Pandas

Python.Modules.Pandas #python #Python #Modules #PythonModules #pandas #Pandas #datascience

PYTHON PANDAS MODULE

#if necessary convert TYPE column to string
df['TYPE'] = df['TYPE'].astype(str)
df = df.groupby(["SCENARIO", "STATUS", "TYPE"])['TYPE'].count()

#aggregate sum by first 2 levels
df1 = df.groupby(["SCENARIO", "STATUS"]).sum()
#add 3 level of MultiIndex 
df1.index = [df1.index.get_level_values(0),
            df1.index.get_level_values(1),
            ['Total'] * len(df1)]
print (df1)
SCENARIO  STATUS       
AAA       51      Total    3
          53      Total    1
BBB       51      Total    1
CCC       51      Total    1
Name: TYPE, dtype: int64

#join together and sorts
df = pd.concat([df, df1]).sort_index(level=[0,1])
print (df)
SCENARIO  STATUS  TYPE 
AAA       51      1        2
                  9        1
                  Total    3
          53      228      1
                  Total    1
BBB       51      43       1
                  Total    1
CCC       51      187      1
                  Total    1
Name: TYPE, dtype: int64


#############################################################################################################

Use GroupBy.count with specify column in [] if dont need count NaNs:

table = df.groupby(["SCENARIO", "STATUS", "TYPE"])['TYPE'].count()
print (table)
SCENARIO  STATUS  TYPE
AAA       51      1       2
                  9       1
          53      228     1
BBB       51      43      1
CCC       51      187     1
Name: TYPE, dtype: int64
Or use GroupBy.size, column specify is not necessary, but difference is it count NaNs also:

table = df.groupby(["SCENARIO", "STATUS", "TYPE"]).size()
print (table)
SCENARIO  STATUS  TYPE
AAA       51      1       2
                  9       1
          53      228     1
BBB       51      43      1
CCC       51      187     1
dtype: int64
If need columns from MultiIndex Series:

table = (df.groupby(["SCENARIO", "STATUS", "TYPE"])['TYPE']
           .count()
           .reset_index(name='COUNT(TYPE)'))
print (table)
  SCENARIO  STATUS  TYPE  COUNT(TYPE)
0      AAA      51     1            2
1      AAA      51     9            1
2      AAA      53   228            1
3      BBB      51    43            1
4      CCC      51   187            1
table = (df.groupby(["SCENARIO", "STATUS", "TYPE"])
           .size()
           .reset_index(name='COUNT(TYPE)') )

print (table)
  SCENARIO  STATUS  TYPE  COUNT(TYPE)
0      AAA      51     1            2
1      AAA      51     9            1
2      AAA      53   228            1
3      BBB      51    43            1
4      CCC      51   187            1
Last if dont need write first column called index to excel:

table.to_excel(writer, sheet_name='Sheet1', index=False)
1. import_pandas.py                             - Import Pandas
2. read_csv.py                                  - Read an csv file
3. fix_import                                   - Fix import by Row Column    
4. print_whole                                  - Print whole table
5. Print column                                 - Print a single column(or more)
6. Print Row                                    - Print a row (or more)
7. extract_df.py                                - Extract dataframe from dataframe
8. extract_mult.py                              - Extract Multiple columns/rows
9. add_col.py                                   - Add columns to dataframe
10. edit_row.py                                 - Edit Row / Column
11. Column_prod.py                              - Column as a product of others
12. manual_dec_df.py                            - Manually declare dataframe
13. condition_index.py                          - Conditional indexing / selecting
14. rename_col.py                               - Rename column
15. handle_missing.py                           - Handle missing values
16. map_apply.py                                - Map / Apply to all elements
17. vector_op.py                                - Vectorized operation on dataframe
18. groupby.py                                  - Perform SQL like groupings
19. newfcolumn.py                               - One/Multipe column as a function of one/multiple columns
20. Dataframe_stats.py                          - Produce stats for each column in dataframe 
21. MergeJoin.py                                - Merge Tables, various types of joins
22. plotpanda.py                                - Plotting with panda
23. Removespecificcolumns                       - Remove specific rows columns
24. closeexcelfile.py                           - How to close excel file
25. copyexcelsheet.py                           - How to copy excel sheet to another workbook
26. renameexcelsheet.py                         - How to rename excel sheeet
27. fillNaNinexcel.py                           - How to fill NaN values in excel
28. subtotalswithgroupby.py                     - Subtotals with group by in excel
I believe DataFrame.fillna() will do this for you.

Link to Docs for a dataframe and for a Series.

Example:

In [7]: df
Out[7]: 
          0         1
0       NaN       NaN
1 -0.494375  0.570994
2       NaN       NaN
3  1.876360 -0.229738
4       NaN       NaN

In [8]: df.fillna(0)
Out[8]: 
          0         1
0  0.000000  0.000000
1 -0.494375  0.570994
2  0.000000  0.000000
3  1.876360 -0.229738
4  0.000000  0.000000
To fill the NaNs in only one column, select just that column. in this case I'm using inplace=True to actually change the contents of df.

In [12]: df[1].fillna(0, inplace=True)
Out[12]: 
0    0.000000
1    0.570994
2    0.000000
3   -0.229738
4    0.000000
Name: 1

In [13]: df
Out[13]: 
          0         1
0       NaN  0.000000
1 -0.494375  0.570994
2       NaN  0.000000
3  1.876360 -0.229738
4       NaN  0.000000
You can do this by doing the following:

import openpyxl
ss=openpyxl.load_workbook("file.xlsx")
#printing the sheet names
ss_sheet = ss.get_sheet_by_name('Sheet')
ss_sheet.title = 'Fruit'
ss.save("file.xlsx")
This works for me. Hope this helps.
Thank you both! After taking your advice and trying for 5 minutes, the following worked!

import win32com.client as win32
import os

excel = win32.gencache.EnsureDispatch('Excel.Application')
wb = excel.Workbooks.Add()

for f in [os.path.join(os.getcwd(), "CM1.xlsx"), os.path.join(os.getcwd(), "CM2.xlsx")]: 
    w = excel.Workbooks.Open(f) 
    w.Sheets(1).Copy(wb.Sheets(1))

wb.SaveAs(os.path.join(os.getcwd(), "CM.xlsx"))
excel.Application.Quit()
shareeditflag
answered Oct 12 '15 at 15:03

Tian He
127213
add a comment
up vote
0
down vote
this will paste sheet 1 ot to_copy to sheet 1 of emptyWorkbook

empty_wb = xl.Workbooks.Open(util.getTestRessourcePath("emptyWorkbook.xlsx"))
tocopy_wb = xl.Workbooks.Open(util.getTestRessourcePath("toCopy.xls"))

tocopy_wb.Sheets(1).Cells.Copy()
empty_wb.Sheets(1).Paste(empty_wb.Sheets(1).Range("A1"))
I think you are forgetting to close the file - Once you are done writing data to the file, you will need to close the file handler using

excel.close()
as mentioned in the docs here.


You can use drop(...) to remove rows, and drop(..., axis=1) to remove columns

data = [
    ['', '', '', ''],
    ['', 1, 2, 3],
    ['', '', '', ''],
    ['', 7, 8, 9],
]

import pandas as pd

df = pd.DataFrame(data)

# drop first column - [0]
df = df.drop(0, axis=1)

# drop first and third row - [0,2]
df = df.drop([0,2])

print(df)
Before:

  0  1  2  3
0           
1    1  2  3
2           
3    7  8  9
After:

   1  2  3
1  1  2  3
3  7  8  9

#####################################################################

You could use dropna:

In [13]: df2[df1 != df2].dropna(how='all')
Out[13]: 
   col1  col2
0     4     6
2     5     5
shareeditflag
edited Dec 20 '14 at 14:52
answered Dec 20 '14 at 10:35

unutbu
464k789461053
  	
 		
what is the difference between dropna() and dropna(how='all')? – aerokite Dec 20 '14 at 10:39
1	
 		
dropna() is the same as dropna(how='any') be default. This will drop any row which has a NaN. dropna(how='all') will drop a row only if all the values in the row are NaN. – unutbu Dec 20 '14 at 10:41
add a comment
up vote
1
down vote
>>> df = df2[~df2.isin(df1).all(1)]
>>> df
    col1  col2
0     4     6
2     5     5

##################################################################################################

Use iloc like you were using ix, but apply a different slice...

df2 = df1.iloc[3:] #edited since .ix is now deprecated.
will give you a new df without the first three rows.

 think a more explicit way of doing this is to use drop.

The syntax is:

df.drop(label)
And as pointed out by @tim and @ChaimG, this can be done in-place:

df.drop(label, inplace=True)
One way of implementing this could be:

df.drop(df.index[:3], inplace=True)
And another "in place" use:

df.drop(df.head(3).index, inplace=True)

df.drop(df.index[[0,2]])
Pandas uses zero based numbering, so 0 is the first row, 1 is the second row and 2 is the third row.




#Vectorized mathematical and string operations
#One can perform vectorized calculations using simple operators and numpy functions.


In [4]: df = pd.DataFrame(data={"A":[1,2], "B":[1.2,1.3]})

In [5]: df["C"] = df["A"]+df["B"]

In [6]: df
Out[6]:
   A    B    C
0  1  1.2  2.2
1  2  1.3  3.3

In [7]: df["D"] = df["A"]*3

In [8]: df
Out[8]:
   A    B    C  D
0  1  1.2  2.2  3
1  2  1.3  3.3  6

In [9]: df["E"] = np.sqrt(df["A"])

In [10]: df
Out[10]:
   A    B    C  D         E
0  1  1.2  2.2  3  1.000000
1  2  1.3  3.3  6  1.414214

#Also, vectorized string operations are easy to use.

In [11]: df = pd.DataFrame(data={"A":[1,2], "B":[1.2,1.3], "Z":["a","b"]})

In [12]: df
Out[12]:
   A    B  Z
0  1  1.2  a
1  2  1.3  b

In [13]: df["F"] = df.Z.str.upper()

In [14]: df
Out[14]:
   A    B  Z  F
0  1  1.2  a  A
1  2  1.3  b  B
# Import pandas as pd
import pandas as pd


# Import the cars.csv data: cars
cars = pd.read_csv("cars.csv")

# Print out cars
print (cars)


# Fix import by including index_col
cars = pd.read_csv('cars.csv', index_col = 0)

# Print out cars
print(cars)

# Print out country column as Pandas Series
print (cars['country'])

# Print out country column as Pandas DataFrame
print (cars[['country']])


cars = pd.read_csv('cars.csv', index_col = 0)

# Print out observation for Japan
print(cars.loc['JAP'])

# Print out observations for Australia and Egypt
print(cars.loc[['AUS', 'EG']])

# Print out drives_right value of Morocco
print (cars.loc['MOR', 'drives_right'])

# Print sub-DataFrame
print (cars.loc[['RU', 'MOR'], [ 'country', 'drives_right' ]])
# We need both the row, and the column to extrapolate a single element
# Just like an actual table, we would need x,y
# ie

bricks.loc["CH","Capital"]
#or
bricks["Capital"].loc["CH"]
#or
bricks.loc["CH"]["Capital"]
#
# To select a row we require the row label
# ie 
brics.loc["RowLabel"]
# Way to call on all data in a given column
bricks["columnlabel"]
#Use the rename method to rename columns. It copies the data to another DataFrame.

In [9]: df2 = df.rename(columns={'int_col' : 'some_other_name'})

In [10]: df2
Out[10]:
   float_col  some_other_name str_col
0        0.1                1       a
1        0.2                2       b
2        0.2                6    None
3       10.1                8       c
4        NaN               -1       a


#Set the inplace = True flag incase you want to modify the existing DataFrame.


In [11]: df2.rename(columns={'some_other_name' : 'int_col'}, inplace = True)
Out[11]:
   float_col  int_col str_col
0        0.1        1       a
1        0.2        2       b
2        0.2        6    None
3       10.1        8       c
4        NaN       -1       a
#Called as tablename["columnlabel"]
a = brics["column"]
print(a)

# Multiple column subsetting using IX
In [6]: df.ix[:,['float_col','int_col']]
Out[6]:
   float_col  int_col
0        0.1        1
1        0.2        2
2        0.2        6
3       10.1        8
4        NaN       -1

# Multiple column subsetting using [[]]

2
3
4
5
6
7
8
In [9]: df[['float_col','int_col']]
Out[9]:
   float_col  int_col
0        0.1        1
1        0.2        2
2        0.2        6
3       10.1        8
4        NaN       -1
#Plot
#I was thoroughly surprised by the plotting capabilities of the pandas library. 
#There are several plotting methods available. I am highlighting a couple of 
#simple plots that I use the most. Let’s start with a simple data frame to plot.

In [3]: plot_df = DataFrame(np.random.randn(1000,2),columns=['x','y'])

In [4]: plot_df['y'] = plot_df['y'].map(lambda x : x + 1)
Plot

A simple plot command goes a long way.

In [5]: plot_df.plot()
Out[5]: <matplotlib.axes.AxesSubplot at 0x10e6cad10>


#Histograms
#I really enjoy histograms to get a quick idea about the distribution of the data.

In [6]: plot_df.hist()
Out[6]: array([[Axes(0.125,0.1;0.336957x0.8), Axes(0.563043,0.1;0.336957x0.8)]], dtype=object)
#New Columns = f(Existing Columns)
#Generating new columns from existing columns in a data frame is an integral part of my workflow. This was one of the hardest parts for me to figure out. I hope these examples will save time and effort for other people.
#I will try to illustrate it in a piecemeal manner – multiple columns as a function of a single column, single column as a function of multiple columns, and finally multiple columns as a function of multiple columns.
#multiple columns as a function of a single column
#I often have to generate multiple columns of a DataFrame as a function of a single columns. Related Stack Overflow question

In [43]: df4 = df.copy()

In [44]: def two_three_strings(x):
   ....:   return x*2, x*3
   ....:

In [45]: df4['twice'],df4['thrice'] = zip(*df4['int_col'].map(two_three_strings))

In [46]: df4
Out[46]:
   float_col  int_col str_col  twice  thrice
0        0.1        1       a      2       3
1        0.2        2       b      4       6
2        0.2        6    None     12      18
3       10.1        8       c     16      24
4        NaN       -1       a     -2      -3

#single column as a function of multiple columns
#It’s sometimes useful to generate multiple DataFrame columns from a single column. It comes in handy especially when methods return tuples. Related Stack Overflow question

In [47]: df5 = df.copy()

In [48]: def sum_two_cols(series):
   ....:   return series['int_col'] + series['float_col']
   ....:

In [49]: df5['sum_col'] = df5.apply(sum_two_cols,axis=1)

In [50]: df5
Out[50]:
   float_col  int_col str_col  sum_col
0        0.1        1       a      1.1
1        0.2        2       b      2.2
2        0.2        6    None      6.2
3       10.1        8       c     18.1
4        NaN       -1       a      NaN


#multiple columns as a function of multiple columns
#Finally, a way to generate a new DataFrame with multiple columns based on multiple columns in an existing DataFrame. Related Stack Overflow question

In [51]: import math

In [52]: def int_float_squares(series):
   ....:   return pd.Series({'int_sq' : series['int_col']**2, 'flt_sq' : series['float_col']**2})
   ....:

In [53]: df.apply(int_float_squares, axis = 1)
Out[53]:
   flt_sq  int_sq
0    0.01       1
1    0.04       4
2    0.04      36
3  102.01      64
4     NaN       1
#Merge and Join
#Pandas supports database-like joins which makes it easy to link data frames.
#I will use the simple example to highlight the joins using the merge command.


In [57]: df
Out[57]:
   float_col  int_col str_col
0        0.1        1       a
1        0.2        2       b
2        0.2        6    None
3       10.1        8       c
4        NaN       -1       a

In [58]: other = DataFrame({'str_col' : ['a','b'], 'some_val' : [1, 2]})

In [59]: other
Out[59]:
   some_val str_col
0         1       a
1         2       b

#The inner, outer, left and right joins are show below. The data frames are joined using the str_col keys.

In [60]: pd.merge(df,other,on='str_col',how='inner')
Out[60]:
   float_col  int_col str_col  some_val
0        0.1        1       a         1
1        NaN       -1       a         1
2        0.2        2       b         2

In [61]: pd.merge(df,other,on='str_col',how='outer')
Out[61]:
   float_col  int_col str_col  some_val
0        0.1        1       a         1
1        NaN       -1       a         1
2        0.2        2       b         2
3        0.2        6    None       NaN
4       10.1        8       c       NaN

In [62]: pd.merge(df,other,on='str_col',how='left')
Out[62]:
   float_col  int_col str_col  some_val
0        0.1        1       a         1
1        NaN       -1       a         1
2        0.2        2       b         2
3        0.2        6    None       NaN
4       10.1        8       c       NaN

In [63]: pd.merge(df,other,on='str_col',how='right')
Out[63]:
   float_col  int_col str_col  some_val
0        0.1        1       a         1
1        NaN       -1       a         1
2        0.2        2       b         2
#Forget writing for loops while using pandas. One can do beautiful vectorized computation by applying function over rows and columns using the map, apply and applymap methods.
#map
#The map operation operates over each element of a Series

In [18]: df['str_col'].dropna().map(lambda x : 'map_' + x)
Out[18]:
0    map_a
1    map_b
3    map_c
4    map_a
Name: str_col

#apply
#The apply is a pretty flexible function which, as the name suggests, applies a function along any axis of the DataFrame. The examples show the application of the sum function over columns. (Thanks to Mindey in the comments below to use np.sum instead of np.sqrt in the example)

In [19]: df.ix[:,['int_col','float_col']].apply(np.sqrt)
Out[19]:
int_col      16.0
float_col    10.6
dtype: float64

#apply to Row
b =  df.ix[:,['int_col','float_col']].apply(sum)
c =  df.ix[['int_col','float_col'],:].apply(sum)

#applymap
#The applymap operation can be used to apply the function to each element of the DataFrame.


In [39]: def some_fn(x):
   ....:   if type(x) is str:
   ....:     return 'applymap_' + x
   ....:   elif x:
   ....:     return 100 * x
   ....:   else:
   ....:     return
   ....:

In [40]: df.applymap(some_fn)
Out[40]:
   float_col  int_col     str_col
0         10      100  applymap_a
1         20      200  applymap_b
2         20      600        None
3       1010      800  applymap_c
4        NaN     -100  applymap_a
# For when we want to manually declare a dataframe

import pandas as pd
df = pd.DataFrame({'int_col' : [1,2,6,8,-1], 'float_col' : [0.1, 0.2,0.2,10.1,None], 'str_col' : ['a','b',None,'c','a']})


#The dropna can used to drop rows or columns with missing data (NaN). By default, it drops all rows with any missing entry.

n [12]: df2
Out[12]:
   float_col  int_col str_col
0        0.1        1       a
1        0.2        2       b
2        0.2        6    None
3       10.1        8       c
4        NaN       -1       a

In [13]: df2.dropna()
Out[13]:
   float_col  int_col str_col
0        0.1        1       a
1        0.2        2       b
3       10.1        8       c
Fill missing values

#The fillna method on the other hand can be used to fill missing data (NaN). The example below shows a simple replacement using the mean of the available values.


In [14]: df3 = df.copy()

In [15]: mean = df3['float_col'].mean()

In [16]: df3
Out[16]:
   float_col  int_col str_col
0        0.1        1       a
1        0.2        2       b
2        0.2        6    None
3       10.1        8       c
4        NaN       -1       a

In [17]: df3['float_col'].fillna(mean)
Out[17]:
0     0.10
1     0.20
2     0.20
3    10.10
4     2.65
Name: float_col
#Stats
#Pandas provides nifty methods to understand your data. I am highlighting the describe, correlation, 
#covariance, and correlation methods that I use to quickly make sense of my data.

#describe

The describe method provides quick stats on all suitable columns.

In [54]: df.describe()
Out[54]:
       float_col   int_col
count    4.00000  5.000000
mean     2.65000  3.200000
std      4.96689  3.701351
min      0.10000 -1.000000
25%      0.17500  1.000000
50%      0.20000  2.000000
75%      2.67500  6.000000
max     10.10000  8.000000

#covariance
#The cov method provides the covariance between suitable columns.

In [55]: df.cov()
Out[55]:
           float_col    int_col
float_col  24.670000  12.483333
int_col    12.483333  13.700000

#correlation
#The corr method provides the correlation between suitable columns.

In [56]: df.corr()
Out[56]:
           float_col   int_col
float_col   1.000000  0.760678
int_col     0.760678  1.000000
#One can index using boolean indexing

In [7]: df[df['float_col'] > 0.15]
Out[7]:
   float_col  int_col str_col
1        0.2        2       b
2        0.2        6    None
3       10.1        8       c

In [8]: df[df['float_col'] == 0.1]
Out[8]:
   float_col  int_col str_col
0        0.1        1       a
EDIT Suggestion by Roby Levy in the comments below. One can select multiple boolean operators (| for or, & for and, and ~ for not) and group them by parenthisis.


In [10]: df[(df['float_col'] > 0.1) & (df['int_col']>2)]
Out[10]:
   float_col  int_col str_col
2        0.2        6    None
3       10.1        8       c

In [11]: df[(df['float_col'] > 0.1) | (df['int_col']>2)]
Out[11]:
   float_col  int_col str_col
1        0.2        2       b
2        0.2        6    None
3       10.1        8       c

In [12]: df[~(df['float_col'] > 0.1)]
Out[12]:
   float_col  int_col str_col
0        0.1        1       a
4        NaN       -1       a


###########################################

import pandas as pd

df = pd.DataFrame({'int_col' : [1,2,6,8,-1], 'float_col' : [0.1, 0.2,0.2,10.1,None], 'str_col' : ['a','b',None,'c','a']})

print (df)
print (df[df['int_col'] > 3])
# Import the cars.csv data: cars
cars = pd.read_csv("cars.csv")
# To extra a column and construct a new dataframe with it
b = bricks["Columnname"]        # normal calling just data
b = bricks[["Columnname"]]
# Import pandas as pd
import pandas as pd


GroupBy

#The groupby method let’s you perform SQL-like grouping operations. The example below shows a 
#grouping operation performed with str_col columns entries as keys. It is used to calculate 
#the mean of the float_col for each key. For more details, please refer to the split-apply-combine description on the pandas website.

In [41]: grouped = df['float_col'].groupby(df['str_col'])

In [42]: grouped.mean()
Out[42]:
str_col
a           0.1
b           0.2
c          10.1
# Construct a new column out of two others
# ie
bricks["density"] = bricks["pop"]/bricks["area"] * 10
# table["newcolumnname"] = [ 1, 2, 3, 4, ]     - list of elements
a = brics["newcolname"] = [ 1, 2, 3, 4, 5 ]