Python 实用宝典

Question 1

I want to set the dtypes of multiple columns in pd.Dataframe (I have a file that I’ve had to manually parse into a list of lists, as the file was not amenable for pd.read_csv)

import pandas as pd
print pd.DataFrame([['a','1'],['b','2']],
                   dtype={'x':'object','y':'int'},
                   columns=['x','y'])

I get

ValueError: entry not a 2- or 3- tuple

The only way I can set them is by looping through each column variable and recasting with astype.

dtypes = {'x':'object','y':'int'}
mydata = pd.DataFrame([['a','1'],['b','2']],
                      columns=['x','y'])
for c in mydata.columns:
    mydata[c] = mydata[c].astype(dtypes[c])
print mydata['y'].dtype   #=> int64

Is there a better way?

Question 2

Since 0.17, you have to use the explicit conversions:

pd.to_datetime, pd.to_timedelta and pd.to_numeric

(As mentioned below, no more “magic”, convert_objects has been deprecated in 0.17)

df = pd.DataFrame({'x': {0: 'a', 1: 'b'}, 'y': {0: '1', 1: '2'}, 'z': {0: '2018-05-01', 1: '2018-05-02'}})

df.dtypes

x    object
y    object
z    object
dtype: object

df

   x  y           z
0  a  1  2018-05-01
1  b  2  2018-05-02

You can apply these to each column you want to convert:

df["y"] = pd.to_numeric(df["y"])
df["z"] = pd.to_datetime(df["z"])    
df

   x  y          z
0  a  1 2018-05-01
1  b  2 2018-05-02

df.dtypes

x            object
y             int64
z    datetime64[ns]
dtype: object

and confirm the dtype is updated.

OLD/DEPRECATED ANSWER for pandas 0.12 – 0.16: You can use convert_objects to infer better dtypes:

In [21]: df
Out[21]: 
   x  y
0  a  1
1  b  2

In [22]: df.dtypes
Out[22]: 
x    object
y    object
dtype: object

In [23]: df.convert_objects(convert_numeric=True)
Out[23]: 
   x  y
0  a  1
1  b  2

In [24]: df.convert_objects(convert_numeric=True).dtypes
Out[24]: 
x    object
y     int64
dtype: object

Magic! (Sad to see it deprecated.)

Question 3

For those coming from Google (etc.) such as myself:

convert_objects has been deprecated since 0.17 – if you use it, you get a warning like this one:

FutureWarning: convert_objects is deprecated.  Use the data-type specific converters 
pd.to_datetime, pd.to_timedelta and pd.to_numeric.

You should do something like the following:

df =df.astype(np.float)
df["A"] =pd.to_numeric(df["A"])

Question 4

you can set the types explicitly with pandas DataFrame.astype(dtype, copy=True, raise_on_error=True, **kwargs) and pass in a dictionary with the dtypes you want to dtype

here’s an example:

import pandas as pd
wheel_number = 5
car_name = 'jeep'
minutes_spent = 4.5

# set the columns
data_columns = ['wheel_number', 'car_name', 'minutes_spent']

# create an empty dataframe
data_df = pd.DataFrame(columns = data_columns)
df_temp = pd.DataFrame([[wheel_number, car_name, minutes_spent]],columns = data_columns)
data_df = data_df.append(df_temp, ignore_index=True) 

In [11]: data_df.dtypes
Out[11]:
wheel_number     float64
car_name          object
minutes_spent    float64
dtype: object

data_df = data_df.astype(dtype= {"wheel_number":"int64",
        "car_name":"object","minutes_spent":"float64"})

now you can see that it’s changed

In [18]: data_df.dtypes
Out[18]:
wheel_number       int64
car_name          object
minutes_spent    float64

Question 5

Another way to set the column types is to first construct a numpy record array with your desired types, fill it out and then pass it to a DataFrame constructor.

import pandas as pd
import numpy as np    

x = np.empty((10,), dtype=[('x', np.uint8), ('y', np.float64)])
df = pd.DataFrame(x)

df.dtypes ->

x      uint8
y    float64

Question 6

facing similar problem to you. In my case I have 1000’s of files from cisco logs that I need to parse manually.

In order to be flexible with fields and types I have successfully tested using StringIO + read_cvs which indeed does accept a dict for the dtype specification.

I usually get each of the files ( 5k-20k lines) into a buffer and create the dtype dictionaries dynamically.

Eventually I concatenate ( with categorical… thanks to 0.19) these dataframes into a large data frame that I dump into hdf5.

Something along these lines

import pandas as pd
import io 

output = io.StringIO()
output.write('A,1,20,31\n')
output.write('B,2,21,32\n')
output.write('C,3,22,33\n')
output.write('D,4,23,34\n')

output.seek(0)


df=pd.read_csv(output, header=None,
        names=["A","B","C","D"],
        dtype={"A":"category","B":"float32","C":"int32","D":"float64"},
        sep=","
       )

df.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 5 entries, 0 to 4
Data columns (total 4 columns):
A    5 non-null category
B    5 non-null float32
C    5 non-null int32
D    5 non-null float64
dtypes: category(1), float32(1), float64(1), int32(1)
memory usage: 205.0 bytes
None

Not very pythonic…. but does the job

Hope it helps.

JC

Question 7

You’re better off using typed np.arrays, and then pass the data and column names as a dictionary.

import numpy as np
import pandas as pd
# Feature: np arrays are 1: efficient, 2: can be pre-sized
x = np.array(['a', 'b'], dtype=object)
y = np.array([ 1 ,  2 ], dtype=np.int32)
df = pd.DataFrame({
   'x' : x,    # Feature: column name is near data array
   'y' : y,
   }
 )

Question 8

I have seen many answers posted to questions on Stack Overflow involving the use of the Pandas method apply. I have also seen users commenting under them saying that “apply is slow, and should be avoided”.

I have read many articles on the topic of performance that explain apply is slow. I have also seen a disclaimer in the docs about how apply is simply a convenience function for passing UDFs (can’t seem to find that now). So, the general consensus is that apply should be avoided if possible. However, this raises the following questions:

If apply is so bad, then why is it in the API?
How and when should I make my code apply-free?
Are there ever any situations where apply is good (better than other possible solutions)?

Question 9

`apply`, the Convenience Function you Never Needed

We start by addressing the questions in the OP, one by one.

“If apply is so bad, then why is it in the API?”

DataFrame.apply and Series.apply are convenience functions defined on DataFrame and Series object respectively. apply accepts any user defined function that applies a transformation/aggregation on a DataFrame. apply is effectively a silver bullet that does whatever any existing pandas function cannot do.

Some of the things apply can do:

Run any user-defined function on a DataFrame or Series
Apply a function either row-wise (axis=1) or column-wise (axis=0) on a DataFrame
Perform index alignment while applying the function
Perform aggregation with user-defined functions (however, we usually prefer agg or transform in these cases)
Perform element-wise transformations
Broadcast aggregated results to original rows (see the result_type argument).
Accept positional/keyword arguments to pass to the user-defined functions.

…Among others. For more information, see Row or Column-wise Function Application in the documentation.

So, with all these features, why is apply bad? It is because apply is slow. Pandas makes no assumptions about the nature of your function, and so iteratively applies your function to each row/column as necessary. Additionally, handling all of the situations above means apply incurs some major overhead at each iteration. Further, apply consumes a lot more memory, which is a challenge for memory bounded applications.

There are very few situations where apply is appropriate to use (more on that below). If you’re not sure whether you should be using apply, you probably shouldn’t.

Let’s address the next question.

“How and when should I make my code apply-free?”

To rephrase, here are some common situations where you will want to get rid of any calls to apply.

Numeric Data

If you’re working with numeric data, there is likely already a vectorized cython function that does exactly what you’re trying to do (if not, please either ask a question on Stack Overflow or open a feature request on GitHub).

Contrast the performance of apply for a simple addition operation.

df = pd.DataFrame({"A": [9, 4, 2, 1], "B": [12, 7, 5, 4]})
df

   A   B
0  9  12
1  4   7
2  2   5
3  1   4

<!- ->

df.apply(np.sum)

A    16
B    28
dtype: int64

df.sum()

A    16
B    28
dtype: int64

Performance wise, there’s no comparison, the cythonized equivalent is much faster. There’s no need for a graph, because the difference is obvious even for toy data.

%timeit df.apply(np.sum)
%timeit df.sum()
2.22 ms ± 41.2 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)
471 µs ± 8.16 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)

Even if you enable passing raw arrays with the raw argument, it’s still twice as slow.

%timeit df.apply(np.sum, raw=True)
840 µs ± 691 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

Another example:

df.apply(lambda x: x.max() - x.min())

A    8
B    8
dtype: int64

df.max() - df.min()

A    8
B    8
dtype: int64

%timeit df.apply(lambda x: x.max() - x.min())
%timeit df.max() - df.min()

2.43 ms ± 450 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)
1.23 ms ± 14.7 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)

In general, seek out vectorized alternatives if possible.

String/Regex

Pandas provides “vectorized” string functions in most situations, but there are rare cases where those functions do not… “apply”, so to speak.

A common problem is to check whether a value in a column is present in another column of the same row.

df = pd.DataFrame({
    'Name': ['mickey', 'donald', 'minnie'],
    'Title': ['wonderland', "welcome to donald's castle", 'Minnie mouse clubhouse'],
    'Value': [20, 10, 86]})
df

     Name  Value                       Title
0  mickey     20                  wonderland
1  donald     10  welcome to donald's castle
2  minnie     86      Minnie mouse clubhouse

This should return the row second and third row, since “donald” and “minnie” are present in their respective “Title” columns.

Using apply, this would be done using

df.apply(lambda x: x['Name'].lower() in x['Title'].lower(), axis=1)

0    False
1     True
2     True
dtype: bool
 
df[df.apply(lambda x: x['Name'].lower() in x['Title'].lower(), axis=1)]

     Name                       Title  Value
1  donald  welcome to donald's castle     10
2  minnie      Minnie mouse clubhouse     86

However, a better solution exists using list comprehensions.

df[[y.lower() in x.lower() for x, y in zip(df['Title'], df['Name'])]]

     Name                       Title  Value
1  donald  welcome to donald's castle     10
2  minnie      Minnie mouse clubhouse     86

<!- ->

%timeit df[df.apply(lambda x: x['Name'].lower() in x['Title'].lower(), axis=1)]
%timeit df[[y.lower() in x.lower() for x, y in zip(df['Title'], df['Name'])]]

2.85 ms ± 38.4 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)
788 µs ± 16.4 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)

The thing to note here is that iterative routines happen to be faster than apply, because of the lower overhead. If you need to handle NaNs and invalid dtypes, you can build on this using a custom function you can then call with arguments inside the list comprehension.

For more information on when list comprehensions should be considered a good option, see my writeup: Are for-loops in pandas really bad? When should I care?.

Note
Date and datetime operations also have vectorized versions. So, for example, you should prefer pd.to_datetime(df['date']), over, say, df['date'].apply(pd.to_datetime).

Read more at the docs.

A Common Pitfall: Exploding Columns of Lists

s = pd.Series([[1, 2]] * 3)
s

0    [1, 2]
1    [1, 2]
2    [1, 2]
dtype: object

People are tempted to use apply(pd.Series). This is horrible in terms of performance.

s.apply(pd.Series)

   0  1
0  1  2
1  1  2
2  1  2

A better option is to listify the column and pass it to pd.DataFrame.

pd.DataFrame(s.tolist())

   0  1
0  1  2
1  1  2
2  1  2

<!- ->

%timeit s.apply(pd.Series)
%timeit pd.DataFrame(s.tolist())

2.65 ms ± 294 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)
816 µs ± 40.5 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)

Lastly,

“Are there any situations where apply is good?”

Apply is a convenience function, so there are situations where the overhead is negligible enough to forgive. It really depends on how many times the function is called.

Functions that are Vectorized for Series, but not DataFrames
What if you want to apply a string operation on multiple columns? What if you want to convert multiple columns to datetime? These functions are vectorized for Series only, so they must be applied over each column that you want to convert/operate on.

df = pd.DataFrame(
         pd.date_range('2018-12-31','2019-01-31', freq='2D').date.astype(str).reshape(-1, 2), 
         columns=['date1', 'date2'])
df

       date1      date2
0 2018-12-31 2019-01-02
1 2019-01-04 2019-01-06
2 2019-01-08 2019-01-10
3 2019-01-12 2019-01-14
4 2019-01-16 2019-01-18
5 2019-01-20 2019-01-22
6 2019-01-24 2019-01-26
7 2019-01-28 2019-01-30

df.dtypes

date1    object
date2    object
dtype: object

This is an admissible case for apply:

df.apply(pd.to_datetime, errors='coerce').dtypes

date1    datetime64[ns]
date2    datetime64[ns]
dtype: object

Note that it would also make sense to stack, or just use an explicit loop. All these options are slightly faster than using apply, but the difference is small enough to forgive.

%timeit df.apply(pd.to_datetime, errors='coerce')
%timeit pd.to_datetime(df.stack(), errors='coerce').unstack()
%timeit pd.concat([pd.to_datetime(df[c], errors='coerce') for c in df], axis=1)
%timeit for c in df.columns: df[c] = pd.to_datetime(df[c], errors='coerce')

5.49 ms ± 247 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)
3.94 ms ± 48.1 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)
3.16 ms ± 216 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)
2.41 ms ± 1.71 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

You can make a similar case for other operations such as string operations, or conversion to category.

u = df.apply(lambda x: x.str.contains(...))
v = df.apply(lambda x: x.astype(category))

v/s

u = pd.concat([df[c].str.contains(...) for c in df], axis=1)
v = df.copy()
for c in df:
    v[c] = df[c].astype(category)

And so on…

Converting Series to `str`: `astype` versus `apply`

This seems like an idiosyncrasy of the API. Using apply to convert integers in a Series to string is comparable (and sometimes faster) than using astype.

The graph was plotted using the perfplot library.

import perfplot

perfplot.show(
    setup=lambda n: pd.Series(np.random.randint(0, n, n)),
    kernels=[
        lambda s: s.astype(str),
        lambda s: s.apply(str)
    ],
    labels=['astype', 'apply'],
    n_range=[2**k for k in range(1, 20)],
    xlabel='N',
    logx=True,
    logy=True,
    equality_check=lambda x, y: (x == y).all())

With floats, I see the astype is consistently as fast as, or slightly faster than apply. So this has to do with the fact that the data in the test is integer type.

`GroupBy` operations with chained transformations

GroupBy.apply has not been discussed until now, but GroupBy.apply is also an iterative convenience function to handle anything that the existing GroupBy functions do not.

One common requirement is to perform a GroupBy and then two prime operations such as a “lagged cumsum”:

df = pd.DataFrame({"A": list('aabcccddee'), "B": [12, 7, 5, 4, 5, 4, 3, 2, 1, 10]})
df

   A   B
0  a  12
1  a   7
2  b   5
3  c   4
4  c   5
5  c   4
6  d   3
7  d   2
8  e   1
9  e  10

<!- ->

You’d need two successive groupby calls here:

df.groupby('A').B.cumsum().groupby(df.A).shift()
 
0     NaN
1    12.0
2     NaN
3     NaN
4     4.0
5     9.0
6     NaN
7     3.0
8     NaN
9     1.0
Name: B, dtype: float64

Using apply, you can shorten this to a a single call.

df.groupby('A').B.apply(lambda x: x.cumsum().shift())

0     NaN
1    12.0
2     NaN
3     NaN
4     4.0
5     9.0
6     NaN
7     3.0
8     NaN
9     1.0
Name: B, dtype: float64

It is very hard to quantify the performance because it depends on the data. But in general, apply is an acceptable solution if the goal is to reduce a groupby call (because groupby is also quite expensive).

Other Caveats

Aside from the caveats mentioned above, it is also worth mentioning that apply operates on the first row (or column) twice. This is done to determine whether the function has any side effects. If not, apply may be able to use a fast-path for evaluating the result, else it falls back to a slow implementation.

df = pd.DataFrame({
    'A': [1, 2],
    'B': ['x', 'y']
})

def func(x):
    print(x['A'])
    return x

df.apply(func, axis=1)

# 1
# 1
# 2
   A  B
0  1  x
1  2  y

This behaviour is also seen in GroupBy.apply on pandas versions <0.25 (it was fixed for 0.25, see here for more information.)

Question 10

Not all `apply`s are alike

The below chart suggests when to consider apply¹. Green means possibly efficient; red avoid.

Some of this is intuitive: pd.Series.apply is a Python-level row-wise loop, ditto pd.DataFrame.apply row-wise (axis=1). The misuses of these are many and wide-ranging. The other post deals with them in more depth. Popular solutions are to use vectorised methods, list comprehensions (assumes clean data), or efficient tools such as the pd.DataFrame constructor (e.g. to avoid apply(pd.Series)).

If you are using pd.DataFrame.apply row-wise, specifying raw=True (where possible) is often beneficial. At this stage, numba is usually a better choice.

`GroupBy.apply`: generally favoured

Repeating groupby operations to avoid apply will hurt performance. GroupBy.apply is usually fine here, provided the methods you use in your custom function are themselves vectorised. Sometimes there is no native Pandas method for a groupwise aggregation you wish to apply. In this case, for a small number of groups apply with a custom function may still offer reasonable performance.

`pd.DataFrame.apply` column-wise: a mixed bag

pd.DataFrame.apply column-wise (axis=0) is an interesting case. For a small number of rows versus a large number of columns, it’s almost always expensive. For a large number of rows relative to columns, the more common case, you may sometimes see significant performance improvements using apply:

# Python 3.7, Pandas 0.23.4
np.random.seed(0)
df = pd.DataFrame(np.random.random((10**7, 3)))     # Scenario_1, many rows
df = pd.DataFrame(np.random.random((10**4, 10**3))) # Scenario_2, many columns

                                               # Scenario_1  | Scenario_2
%timeit df.sum()                               # 800 ms      | 109 ms
%timeit df.apply(pd.Series.sum)                # 568 ms      | 325 ms

%timeit df.max() - df.min()                    # 1.63 s      | 314 ms
%timeit df.apply(lambda x: x.max() - x.min())  # 838 ms      | 473 ms

%timeit df.mean()                              # 108 ms      | 94.4 ms
%timeit df.apply(pd.Series.mean)               # 276 ms      | 233 ms

¹ There are exceptions, but these are usually marginal or uncommon. A couple of examples:

df['col'].apply(str) may slightly outperform df['col'].astype(str).
df.apply(pd.to_datetime) working on strings doesn’t scale well with rows versus a regular for loop.

Question 11

For axis=1 (i.e. row-wise functions) then you can just use the following function in lieu of apply. I wonder why this isn’t the pandas behavior. (Untested with compound indexes, but it does appear to be much faster than apply)

def faster_df_apply(df, func):
    cols = list(df.columns)
    data, index = [], []
    for row in df.itertuples(index=True):
        row_dict = {f:v for f,v in zip(cols, row[1:])}
        data.append(func(row_dict))
        index.append(row[0])
    return pd.Series(data, index=index)

Question 12

Are there ever any situations where apply is good? Yes, sometimes.

Task: decode Unicode strings.

import numpy as np
import pandas as pd
import unidecode

s = pd.Series(['mañana','Ceñía'])
s.head()
0    mañana
1     Ceñía


s.apply(unidecode.unidecode)
0    manana
1     Cenia

Update
I was by no means advocating for the use of apply, just thinking since the NumPy cannot deal with the above situation, it could have been a good candidate for pandas apply. But I was forgetting the plain ol list comprehension thanks to the reminder by @jpp.

Question 13

Currently, pandas I/O tools does not maintain a read_xml() method and the counterpart to_xml(). However, read_json proves tree-like structures can be implemented for dataframe import and read_html for markup formats.

If the pandas team does consider such a read_xml method for a future pandas version, what implementation would they pursue: parsing with built-in xml.etree.ElementTree with its iterfind() or iterparse() functions or the third-party module, lxml with its XPath 1.0 and XSLT 1.0 methods?

Below are my test runs for four method types on a simple, flat, element-centric XML input. All are set up for generalized parsing for any second level children of root and each method should yield exact same pandas dataframe. All but the last calls pd.Dataframe() on list of dictionaries. The XSLT method transforms XML to CSV for casted StringIO() in pd.read_csv().

Question (multi-part)

PERFORMANCE: How do you explain the slower iterparse often recommended for larger files as file is iteratively parsed? Is it partly due to the if logic checks?
MEMORY: Do CPU memory correlate with timings in I/O calls? XSLT and XPath 1.0 tend not to scale well with larger XML documents as entire file must be read in memory to be parsed.
STRATEGY: Is list of dictionaries an optimal strategy for Dataframe() call? See these interesting answers: generator version and a iterwalk user-defined version. Both upcast lists to dataframe.

Input Data (Stack Overflow’s current top users by year of which our pandas friends are included)

<?xml version="1.0" encoding="utf-8"?>
<stackoverflow>
  <topusers>
    <user>Gordon Linoff</user>
    <link>http://www.stackoverflow.com//users/1144035/gordon-linoff</link>
    <location>New York, United States</location>
    <year_rep>5,985</year_rep>
    <total_rep>499,408</total_rep>
    <tag1>sql</tag1>
    <tag2>sql-server</tag2>
    <tag3>mysql</tag3>
  </topusers>
  <topusers>
    <user>Günter Zöchbauer</user>
    <link>http://www.stackoverflow.com//users/217408/g%c3%bcnter-z%c3%b6chbauer</link>
    <location>Linz, Austria</location>
    <year_rep>5,835</year_rep>
    <total_rep>154,439</total_rep>
    <tag1>angular2</tag1>
    <tag2>typescript</tag2>
    <tag3>javascript</tag3>
  </topusers>
  <topusers>
    <user>jezrael</user>
    <link>http://www.stackoverflow.com//users/2901002/jezrael</link>
    <location>Bratislava, Slovakia</location>
    <year_rep>5,740</year_rep>
    <total_rep>83,237</total_rep>
    <tag1>pandas</tag1>
    <tag2>python</tag2>
    <tag3>dataframe</tag3>
  </topusers>
  <topusers>
    <user>VonC</user>
    <link>http://www.stackoverflow.com//users/6309/vonc</link>
    <location>France</location>
    <year_rep>5,577</year_rep>
    <total_rep>651,397</total_rep>
    <tag1>git</tag1>
    <tag2>github</tag2>
    <tag3>docker</tag3>
  </topusers>
  <topusers>
    <user>Martijn Pieters</user>
    <link>http://www.stackoverflow.com//users/100297/martijn-pieters</link>
    <location>Cambridge, United Kingdom</location>
    <year_rep>5,337</year_rep>
    <total_rep>525,176</total_rep>
    <tag1>python</tag1>
    <tag2>python-3.x</tag2>
    <tag3>python-2.7</tag3>
  </topusers>
  <topusers>
    <user>T.J. Crowder</user>
    <link>http://www.stackoverflow.com//users/157247/t-j-crowder</link>
    <location>United Kingdom</location>
    <year_rep>5,258</year_rep>
    <total_rep>508,310</total_rep>
    <tag1>javascript</tag1>
    <tag2>jquery</tag2>
    <tag3>java</tag3>
  </topusers>
  <topusers>
    <user>akrun</user>
    <link>http://www.stackoverflow.com//users/3732271/akrun</link>
    <location></location>
    <year_rep>5,188</year_rep>
    <total_rep>229,553</total_rep>
    <tag1>r</tag1>
    <tag2>dplyr</tag2>
    <tag3>dataframe</tag3>
  </topusers>
  <topusers>
    <user>Wiktor Stribi?ew</user>
    <link>http://www.stackoverflow.com//users/3832970/wiktor-stribi%c5%bcew</link>
    <location>Warsaw, Poland</location>
    <year_rep>4,948</year_rep>
    <total_rep>158,134</total_rep>
    <tag1>regex</tag1>
    <tag2>javascript</tag2>
    <tag3>c#</tag3>
  </topusers>
  <topusers>
    <user>Darin Dimitrov</user>
    <link>http://www.stackoverflow.com//users/29407/darin-dimitrov</link>
    <location>Sofia, Bulgaria</location>
    <year_rep>4,936</year_rep>
    <total_rep>709,683</total_rep>
    <tag1>c#</tag1>
    <tag2>asp.net-mvc</tag2>
    <tag3>asp.net-mvc-3</tag3>
  </topusers>
  <topusers>
    <user>Eric Duminil</user>
    <link>http://www.stackoverflow.com//users/6419007/eric-duminil</link>
    <location></location>
    <year_rep>4,854</year_rep>
    <total_rep>12,557</total_rep>
    <tag1>ruby</tag1>
    <tag2>ruby-on-rails</tag2>
    <tag3>arrays</tag3>
  </topusers>
  <topusers>
    <user>alecxe</user>
    <link>http://www.stackoverflow.com//users/771848/alecxe</link>
    <location>New York, United States</location>
    <year_rep>4,723</year_rep>
    <total_rep>233,368</total_rep>
    <tag1>python</tag1>
    <tag2>selenium</tag2>
    <tag3>protractor</tag3>
  </topusers>
  <topusers>
    <user>Jean-François Fabre</user>
    <link>http://www.stackoverflow.com//users/6451573/jean-fran%c3%a7ois-fabre</link>
    <location>Toulouse, France</location>
    <year_rep>4,526</year_rep>
    <total_rep>30,027</total_rep>
    <tag1>python</tag1>
    <tag2>python-3.x</tag2>
    <tag3>python-2.7</tag3>
  </topusers>
  <topusers>
    <user>piRSquared</user>
    <link>http://www.stackoverflow.com//users/2336654/pirsquared</link>
    <location>Bellevue, WA, United States</location>
    <year_rep>4,482</year_rep>
    <total_rep>41,183</total_rep>
    <tag1>pandas</tag1>
    <tag2>python</tag2>
    <tag3>dataframe</tag3>
  </topusers>
  <topusers>
    <user>CommonsWare</user>
    <link>http://www.stackoverflow.com//users/115145/commonsware</link>
    <location>Who Wants to Know?</location>
    <year_rep>4,475</year_rep>
    <total_rep>616,135</total_rep>
    <tag1>android</tag1>
    <tag2>java</tag2>
    <tag3>android-intent</tag3>
  </topusers>
  <topusers>
    <user>Quentin</user>
    <link>http://www.stackoverflow.com//users/19068/quentin</link>
    <location>United Kingdom</location>
    <year_rep>4,464</year_rep>
    <total_rep>509,365</total_rep>
    <tag1>javascript</tag1>
    <tag2>html</tag2>
    <tag3>css</tag3>
  </topusers>
  <topusers>
    <user>Jon Skeet</user>
    <link>http://www.stackoverflow.com//users/22656/jon-skeet</link>
    <location>Reading, United Kingdom</location>
    <year_rep>4,348</year_rep>
    <total_rep>921,690</total_rep>
    <tag1>c#</tag1>
    <tag2>java</tag2>
    <tag3>.net</tag3>
  </topusers>
  <topusers>
    <user>Felix Kling</user>
    <link>http://www.stackoverflow.com//users/218196/felix-kling</link>
    <location>Sunnyvale, CA</location>
    <year_rep>4,324</year_rep>
    <total_rep>411,535</total_rep>
    <tag1>javascript</tag1>
    <tag2>jquery</tag2>
    <tag3>asynchronous</tag3>
  </topusers>
  <topusers>
    <user>matt</user>
    <link>http://www.stackoverflow.com//users/341994/matt</link>
    <location></location>
    <year_rep>4,313</year_rep>
    <total_rep>220,515</total_rep>
    <tag1>swift</tag1>
    <tag2>ios</tag2>
    <tag3>xcode</tag3>
  </topusers>
  <topusers>
    <user>Psidom</user>
    <link>http://www.stackoverflow.com//users/4983450/psidom</link>
    <location>Atlanta, GA, United States</location>
    <year_rep>4,236</year_rep>
    <total_rep>36,950</total_rep>
    <tag1>python</tag1>
    <tag2>pandas</tag2>
    <tag3>r</tag3>
  </topusers>
  <topusers>
    <user>Martin R</user>
    <link>http://www.stackoverflow.com//users/1187415/martin-r</link>
    <location>Germany</location>
    <year_rep>4,195</year_rep>
    <total_rep>269,380</total_rep>
    <tag1>swift</tag1>
    <tag2>ios</tag2>
    <tag3>swift3</tag3>
  </topusers>
  <topusers>
    <user>Barmar</user>
    <link>http://www.stackoverflow.com//users/1491895/barmar</link>
    <location>Arlington, MA</location>
    <year_rep>4,179</year_rep>
    <total_rep>289,989</total_rep>
    <tag1>javascript</tag1>
    <tag2>php</tag2>
    <tag3>jquery</tag3>
  </topusers>
  <topusers>
    <user>Alexey Mezenin</user>
    <link>http://www.stackoverflow.com//users/1227923/alexey-mezenin</link>
    <location>??????</location>
    <year_rep>4,142</year_rep>
    <total_rep>31,602</total_rep>
    <tag1>laravel</tag1>
    <tag2>php</tag2>
    <tag3>laravel-5.3</tag3>
  </topusers>
  <topusers>
    <user>BalusC</user>
    <link>http://www.stackoverflow.com//users/157882/balusc</link>
    <location>Amsterdam, Netherlands</location>
    <year_rep>4,046</year_rep>
    <total_rep>703,046</total_rep>
    <tag1>java</tag1>
    <tag2>jsf</tag2>
    <tag3>servlets</tag3>
  </topusers>
  <topusers>
    <user>GurV</user>
    <link>http://www.stackoverflow.com//users/6348498/gurv</link>
    <location></location>
    <year_rep>4,016</year_rep>
    <total_rep>7,932</total_rep>
    <tag1>sql</tag1>
    <tag2>mysql</tag2>
    <tag3>sql-server</tag3>
  </topusers>
  <topusers>
    <user>Nina Scholz</user>
    <link>http://www.stackoverflow.com//users/1447675/nina-scholz</link>
    <location>Berlin, Deutschland</location>
    <year_rep>3,950</year_rep>
    <total_rep>61,135</total_rep>
    <tag1>javascript</tag1>
    <tag2>arrays</tag2>
    <tag3>object</tag3>
  </topusers>
  <topusers>
    <user>JB Nizet</user>
    <link>http://www.stackoverflow.com//users/571407/jb-nizet</link>
    <location>Saint-Etienne, France</location>
    <year_rep>3,923</year_rep>
    <total_rep>418,780</total_rep>
    <tag1>java</tag1>
    <tag2>hibernate</tag2>
    <tag3>java-8</tag3>
  </topusers>
  <topusers>
    <user>Frank van Puffelen</user>
    <link>http://www.stackoverflow.com//users/209103/frank-van-puffelen</link>
    <location>San Francisco, CA</location>
    <year_rep>3,920</year_rep>
    <total_rep>86,520</total_rep>
    <tag1>firebase</tag1>
    <tag2>firebase-database</tag2>
    <tag3>android</tag3>
  </topusers>
  <topusers>
    <user>dasblinkenlight</user>
    <link>http://www.stackoverflow.com//users/335858/dasblinkenlight</link>
    <location>United States</location>
    <year_rep>3,886</year_rep>
    <total_rep>475,813</total_rep>
    <tag1>c#</tag1>
    <tag2>java</tag2>
    <tag3>c++</tag3>
  </topusers>
  <topusers>
    <user>Tim Biegeleisen</user>
    <link>http://www.stackoverflow.com//users/1863229/tim-biegeleisen</link>
    <location>Singapore</location>
    <year_rep>3,814</year_rep>
    <total_rep>77,211</total_rep>
    <tag1>sql</tag1>
    <tag2>mysql</tag2>
    <tag3>java</tag3>
  </topusers>
  <topusers>
    <user>Greg Hewgill</user>
    <link>http://www.stackoverflow.com//users/893/greg-hewgill</link>
    <location>Christchurch, New Zealand</location>
    <year_rep>3,796</year_rep>
    <total_rep>529,137</total_rep>
    <tag1>git</tag1>
    <tag2>python</tag2>
    <tag3>git-pull</tag3>
  </topusers>
  <topusers>
    <user>unutbu</user>
    <link>http://www.stackoverflow.com//users/190597/unutbu</link>
    <location></location>
    <year_rep>3,735</year_rep>
    <total_rep>401,595</total_rep>
    <tag1>python</tag1>
    <tag2>pandas</tag2>
    <tag3>numpy</tag3>
  </topusers>
  <topusers>
    <user>Hans Passant</user>
    <link>http://www.stackoverflow.com//users/17034/hans-passant</link>
    <location>Madison, WI</location>
    <year_rep>3,688</year_rep>
    <total_rep>672,118</total_rep>
    <tag1>c#</tag1>
    <tag2>.net</tag2>
    <tag3>winforms</tag3>
  </topusers>
  <topusers>
    <user>Jonathan Leffler</user>
    <link>http://www.stackoverflow.com//users/15168/jonathan-leffler</link>
    <location>California, USA</location>
    <year_rep>3,649</year_rep>
    <total_rep>455,157</total_rep>
    <tag1>c</tag1>
    <tag2>bash</tag2>
    <tag3>unix</tag3>
  </topusers>
  <topusers>
    <user>paxdiablo</user>
    <link>http://www.stackoverflow.com//users/14860/paxdiablo</link>
    <location></location>
    <year_rep>3,636</year_rep>
    <total_rep>507,043</total_rep>
    <tag1>c</tag1>
    <tag2>c++</tag2>
    <tag3>bash</tag3>
  </topusers>
  <topusers>
    <user>Pranav C Balan</user>
    <link>http://www.stackoverflow.com//users/3037257/pranav-c-balan</link>
    <location>Ramanthali, Kannur, Kerala, India</location>
    <year_rep>3,604</year_rep>
    <total_rep>64,476</total_rep>
    <tag1>javascript</tag1>
    <tag2>jquery</tag2>
    <tag3>html</tag3>
  </topusers>
  <topusers>
    <user>Suragch</user>
    <link>http://www.stackoverflow.com//users/3681880/suragch</link>
    <location>Hohhot, China</location>
    <year_rep>3,580</year_rep>
    <total_rep>71,032</total_rep>
    <tag1>swift</tag1>
    <tag2>ios</tag2>
    <tag3>android</tag3>
  </topusers>
</stackoverflow>

Python Methods

import xml.etree.ElementTree as et
import pandas as pd
from io import StringIO
from lxml import etree as lxet

def read_xml_iterfind():
    tree = et.parse('Input.xml')

    data = []
    inner = {}
    for el in tree.iterfind('./*'):
        for i in el.iterfind('*'):
            inner[i.tag] = i.text
        data.append(inner)
        inner = {}

    df = pd.DataFrame(data)

def read_xml_iterparse():
    data = []
    inner = {}
    i = 1
    for (ev, el) in et.iterparse(path):
        if i <= 2:
           first_tag = el.tag

        if el.tag == first_tag and len(inner) != 0:
            data.append(inner)            
            inner = {}

        if el.text is not None and len(el.text.strip()) > 0:
            inner[el.tag] = el.text
    i += 1

    df = pd.DataFrame(data)    

def read_xml_lxml_xpath():     
    tree = lxet.parse('Input.xml')

    data = []
    inner = {}
    for el in tree.xpath('/*/*'):
        for i in el:
            inner[i.tag] = i.text
        data.append(inner)
        inner = {}

    df = pd.DataFrame(data)

def read_xml_lxml_xsl():     
    xml = lxet.parse('Input.xml')

    xslstr = '''
    <xsl:transform xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
        <xsl:output version="1.0" encoding="UTF-8" indent="yes"  method="text"/>
        <xsl:strip-space elements="*"/>

        <!-- HEADERS -->
        <xsl:template match = "/*">
            <xsl:for-each select="*[1]/*">
              <xsl:value-of select="local-name()" />
                <xsl:choose>
                   <xsl:when test="position() != last()">
                      <xsl:text>,</xsl:text>
                   </xsl:when>
                   <xsl:otherwise>
                      <xsl:text>&#xa;</xsl:text>
                   </xsl:otherwise>                              
                </xsl:choose>   
            </xsl:for-each>
            <xsl:apply-templates/>
        </xsl:template>

        <!-- DATA ROWS (COMMA-SEPARATED) -->
        <xsl:template match="/*/*" priority="2">    
            <xsl:for-each select="*">
              <xsl:if test="position() = 1">
                   <xsl:text>&quot;</xsl:text>
              </xsl:if>
              <xsl:value-of select="." />
                <xsl:choose>
                   <xsl:when test="position() != last()">
                      <xsl:text>&quot;,&quot;</xsl:text>
                   </xsl:when>
                   <xsl:otherwise>
                      <xsl:text>&quot;&#xa;</xsl:text>
                   </xsl:otherwise>                              
                </xsl:choose>
            </xsl:for-each>
        </xsl:template>

    </xsl:transform>
    '''
    xsl = lxet.fromstring(xslstr)

    transform = lxet.XSLT(xsl)
    newdom = transform(xml)

    df = pd.read_csv(StringIO(str(newdom)))

Timings (with current XML and XML with 25 times the children (i.e., 900 StackOverflow user records)

# SHORTER FILE
python -mtimeit -s'import readxml_test_runs as test' 'test.read_xml_iterfind()'
100 loops, best of 3: 3.87 msec per loop

python -mtimeit -s'import readxml_test_runs as test' 'test.read_xml_iterparse()'
100 loops, best of 3: 5.5 msec per loop

python -mtimeit -s'import readxml_test_runs as test' 'test.read_xml_lxml_xpath()'
100 loops, best of 3: 3.86 msec per loop

python -mtimeit -s'import readxml_test_runs as test' 'test.read_xml_lxml_xsl()'
100 loops, best of 3: 5.68 msec per loop

# LARGER FILE
python -mtimeit -n'100' -s'import readxml_test_runs as test' 'test.read_xml_iterfind()'
100 loops, best of 3: 36 msec per loop

python -mtimeit -n'100' -s'import readxml_test_runs as test' 'test.read_xml_iterparse()'
100 loops, best of 3: 78.9 msec per loop

python -mtimeit -n'100' -s'import readxml_test_runs as test' 'test.read_xml_lxml_xpath()'
100 loops, best of 3: 32.7 msec per loop

python -mtimeit -n'100' -s'import readxml_test_runs as test' 'test.read_xml_lxml_xsl()'
100 loops, best of 3: 51.4 msec per loop

Question 14

PERFORMANCE: How do you explain the slower iterparse often recommended for larger files as file is iteratively parsed? Is it partly due to the if logic checks?

I would assume that more python code would make it slower, as the python code is evaluated every time. Have you tried a JIT compiler like pypy?

If I remove i and use first_tag only, it seems to be quite a bit faster, so yes it is partly due to the if logic checks:

def read_xml_iterparse2(path):
    data = []
    inner = {}
    first_tag = None
    for (ev, el) in et.iterparse(path):
        if not first_tag:
           first_tag = el.tag

        if el.tag == first_tag and len(inner) != 0:
            data.append(inner)            
            inner = {}

        if el.text is not None and len(el.text.strip()) > 0:
            inner[el.tag] = el.text

    df = pd.DataFrame(data)    

%timeit read_xml_iterparse(path)
# 10 loops, best of 5: 33 ms per loop
%timeit read_xml_iterparse2(path)
# 10 loops, best of 5: 23 ms per loop

I wasn’t sure I understood the purpose of the last if check, but I’m also not sure why you would want to lose whitespace-only elements. Removing the last if consistently shaves off a little bit of time:

def read_xml_iterparse3(path):
    data = []
    inner = {}
    first_tag = None
    for (ev, el) in et.iterparse(path):
        if not first_tag:
           first_tag = el.tag

        if el.tag == first_tag and len(inner) != 0:
            data.append(inner)            
            inner = {}

        inner[el.tag] = el.text

    df = pd.DataFrame(data)    

%timeit read_xml_iterparse(path)
# 10 loops, best of 5: 34.4 ms per loop
%timeit read_xml_iterparse2(path)
# 10 loops, best of 5: 24.5 ms per loop
%timeit read_xml_iterparse3(path)
# 10 loops, best of 5: 20.9 ms per loop

Now, with or without those performance improvements, your iterparse version seems to produce an extra-large dataframe. Here seems to be a working, fast version:

def read_xml_iterparse5(path):
    data = []
    inner = {}
    for (ev, el) in et.iterparse(path):
        # /ending parents trigger a new row, and in our case .text is \n followed by spaces.  it would be more reliable to pass 'topusers' to our read_xml_iterparse5 as the .tag to check
        if el.text and el.text[0] == '\n':
            # ignore /stackoverflow
            if inner:
                data.append(inner)
                inner = {}
        else:
            inner[el.tag] = el.text

    return pd.DataFrame(data)    

print(read_xml_iterfind(path).shape)
# (900, 8)
print(read_xml_iterparse(path).shape)
# (7050, 8)
print(read_xml_lxml_xpath(path).shape)
# (900, 8)
print(read_xml_lxml_xsl(path).shape)
# (900, 8)
print(read_xml_iterparse5(path).shape)
# (900, 8)
%timeit read_xml_iterparse5(path)
# 10 loops, best of 5: 20.6 ms per loop

MEMORY: Do CPU memory correlate with timings in I/O calls? XSLT and XPath 1.0 tend not to scale well with larger XML documents as entire file must be read in memory to be parsed.

I’m not totally sure what you mean by “I/O calls” but if your document is small enough to fit in cache, then everything will be much faster as it won’t evict many other items from the cache.

STRATEGY: Is list of dictionaries an optimal strategy for Dataframe() call? See these interesting answers: generator version and a iterwalk user-defined version. Both upcast lists to dataframe.

The lists use less memory, so depending on how many columns you have, it could make a noticeable difference. Of course, this then requires your XML tags to be in a consistent order, which they do appear to be. The DataFrame() call would also need to do less work, as it doesn’t have to lookup keys in the dict on every row, to figure out what column if for what value.

Question 15

I am kind of getting stuck on extracting value of one variable conditioning on another variable. For example, the following dataframe:

A  B
p1 1
p1 2
p3 3
p2 4

How can I get the value of A when B=3? Every time when I extracted the value of A, I got an object, not a string.

Question 16

You could use loc to get series which satisfying your condition and then iloc to get first element:

In [2]: df
Out[2]:
    A  B
0  p1  1
1  p1  2
2  p3  3
3  p2  4

In [3]: df.loc[df['B'] == 3, 'A']
Out[3]:
2    p3
Name: A, dtype: object

In [4]: df.loc[df['B'] == 3, 'A'].iloc[0]
Out[4]: 'p3'

Question 17

You can try query, which is less typing:

df.query('B==3')['A']

Question 18

df[df['B']==3]['A'], assuming df is your pandas.DataFrame.

Question 19

Use df[df['B']==3]['A'].values if you just want item itself without the brackets

Question 20

male_avgtip=(tips_data.loc[tips_data['sex'] == 'Male', 'tip']).mean()

I have also worked on this clausing and extraction operations for my assignment.

Question 21

Is it possible to only merge some columns? I have a DataFrame df1 with columns x, y, z, and df2 with columns x, a ,b, c, d, e, f, etc.

I want to merge the two DataFrames on x, but I only want to merge columns df2.a, df2.b – not the entire DataFrame.

The result would be a DataFrame with x, y, z, a, b.

I could merge then delete the unwanted columns, but it seems like there is a better method.

Question 22

You could merge the sub-DataFrame (with just those columns):

df2[list('xab')]  # df2 but only with columns x, a, and b

df1.merge(df2[list('xab')])

Question 23

You want to use TWO brackets, so if you are doing a VLOOKUP sort of action:

df = pd.merge(df,df2[['Key_Column','Target_Column']],on='Key_Column', how='left')

This will give you everything in the original df + add that one corresponding column in df2 that you want to join.

Question 24

If you want to drop column(s) from the target data frame, but the column(s) are required for the join, you can do the following:

df1 = df1.merge(df2[['a', 'b', 'key1']], how = 'left',
                left_on = 'key2', right_on = 'key1').drop('key1')

The .drop('key1') part will prevent ‘key1’ from being kept in the resulting data frame, despite it being required to join in the first place.

Question 25

You can use .loc to select the specific columns with all rows and then pull that. An example is below:

pandas.merge(dataframe1, dataframe2.iloc[:, [0:5]], how='left', on='key')

In this example, you are merging dataframe1 and dataframe2. You have chosen to do an outer left join on ‘key’. However, for dataframe2 you have specified .iloc which allows you to specific the rows and columns you want in a numerical format. Using :, your selecting all rows, but [0:5] selects the first 5 columns. You could use .loc to specify by name, but if your dealing with long column names, then .iloc may be better.

Question 26

This is to merge selected columns from two tables.

If table_1 contains t1_a,t1_b,t1_c..,id,..t1_z columns, and table_2 contains t2_a, t2_b, t2_c..., id,..t2_z columns, and only t1_a, id, t2_a are required in the final table, then

mergedCSV = table_1[['t1_a','id']].merge(table_2[['t2_a','id']], on = 'id',how = 'left')
# save resulting output file    
mergedCSV.to_csv('output.csv',index = False)

Question 27

My dataframe has a DOB column (example format 1/1/2016) which by default gets converted to pandas dtype ‘object’: DOB object

Converting this to date format with df['DOB'] = pd.to_datetime(df['DOB']), the date gets converted to: 2016-01-26 and its dtype is: DOB datetime64[ns].

Now I want to convert this date format to 01/26/2016 or in any other general date formats. How do I do it?

Whatever the method I try, it always shows the date in 2016-01-26 format.

Question 28

You can use dt.strftime if you need to convert datetime to other formats (but note that then dtype of column will be object (string)):

import pandas as pd

df = pd.DataFrame({'DOB': {0: '26/1/2016', 1: '26/1/2016'}})
print (df)
         DOB
0  26/1/2016 
1  26/1/2016

df['DOB'] = pd.to_datetime(df.DOB)
print (df)
         DOB
0 2016-01-26
1 2016-01-26

df['DOB1'] = df['DOB'].dt.strftime('%m/%d/%Y')
print (df)
         DOB        DOB1
0 2016-01-26  01/26/2016
1 2016-01-26  01/26/2016

Question 29

Changing the format but not changing the type:

df['date'] = pd.to_datetime(df["date"].dt.strftime('%Y-%m'))

Question 30

The below code worked for me instead of the previous one – try it out !

df['DOB']=pd.to_datetime(df['DOB'].astype(str), format='%m/%d/%Y')

Question 31

Compared to the first answer, I will recommend to use dt.strftime() first, then pd.to_datetime(). In this way, it will still result in the datetime data type.

For example,

import pandas as pd

df = pd.DataFrame({'DOB': {0: '26/1/2016 ', 1: '26/1/2016 '})
print(df.dtypes)

df['DOB1'] = df['DOB'].dt.strftime('%m/%d/%Y')
print(df.dtypes)

df['DOB1'] = pd.to_datetime(df['DOB1'])
print(df.dtypes)

Question 32

There is a difference between

the content of a dataframe cell (a binary value) and
its presentation (displaying it) for us, humans.

So the question is: How to reach the appropriate presentation of my datas without changing the data / data types themselves?

Here is the answer:

If you use the Jupyter notebook for displaying your dataframe, or
if you want to reach a presentation in the form of an HTML file (even with many prepared superfluous id and class attributes for further CSS styling — you may or you may not use them),

use styling. Styling don’t change data / data types of columns of your dataframe.

Now I show you how to reach it in the Jupyter notebook — for a presentation in the form of HTML file see the note near the end of the question.

I will suppose that your column DOB already has the type datetime64 (you shown that you know how to reach it). I prepared a simple dataframe (with only one column) to show you some basic styling:

Not styled:
```
   df
```

          DOB
0  2019-07-03
1  2019-08-03
2  2019-09-03
3  2019-10-03

Styling it as mm/dd/yyyy:

   df.style.format({"DOB": lambda t: t.strftime("%m/%d/%Y")})

          DOB
0  07/03/2019
1  08/03/2019
2  09/03/2019
3  10/03/2019

Styling it as dd-mm-yyyy:

   df.style.format({"DOB": lambda t: t.strftime("%d-%m-%Y")})

          DOB
0  03-07-2019
1  03-08-2019
2  03-09-2019
3  03-10-2019

Be careful!
The returning object is NOT a dataframe — it is an object of the class Styler, so don’t assign it back to df:

Don´t do this:

df = df.style.format({"DOB": lambda t: t.strftime("%m/%d/%Y")})    # Don´t do this!

(Every dataframe has its Styler object accessible by its .style property, and we changed this df.style object, not the dataframe itself.)

Questions and Answers:

Q: Why your Styler object (or an expression returning it) used as the last command in a Jupyter notebook cell displays your (styled) table, and not the Styler object itself?
A: Because every Styler object has a callback method ._repr_html_() which returns an HTML code for rendering your dataframe (as a nice HTML table).

Jupyter Notebook IDE calls this method automatically to render objects which have it.

Note:

You don’t need the Jupyter notebook for styling (i.e. for nice outputting a dataframe without changing its data / data types).

A Styler object has a method render(), too, if you want to obtain a string with the HTML code (e.g. for publishing your formatted dataframe to the Web, or simply present your table in the HTML format):

df_styler = df.style.format({"DOB": lambda t: t.strftime("%m/%d/%Y")})
HTML_string = df_styler.render()

Question 33

Below code changes to ‘datetime’ type and also formats in the given format string. Works well!

df['DOB']=pd.to_datetime(df['DOB'].dt.strftime('%m/%d/%Y'))

Question 34

You can try this it’ll convert the date format to DD-MM-YYYY:

df['DOB'] = pd.to_datetime(df['DOB'], dayfirst = True)

Question 35

在pandas库中多次出现改变就地等物体的方式与下面的语句一个选项…

df.dropna(axis='index', how='all', inplace=True)

我很好奇返回的内容以及inplace=True传递时与传递对象时如何处理该对象inplace=False。

所有操作self何时都在修改inplace=True？何时inplace=False立即创建一个新对象，例如new_df = self然后new_df返回？

Question 36

In the pandas library many times there is an option to change the object inplace such as with the following statement…

df.dropna(axis='index', how='all', inplace=True)

I am curious what is being returned as well as how the object is handled when inplace=True is passed vs. when inplace=False.

Are all operations modifying self when inplace=True? And when inplace=False is a new object created immediately such as new_df = self and then new_df is returned?

Question 37

如果inplace=True通过，该数据被重命名到位（它没有返回值），所以你会使用：

df.an_operation(inplace=True)

当inplace=False传递（这是默认值，所以没有必要），执行操作，并返回该对象的副本，所以你会使用：

df = df.an_operation(inplace=False)

Question 38

When inplace=True is passed, the data is renamed in place (it returns nothing), so you’d use:

df.an_operation(inplace=True)

When inplace=False is passed (this is the default value, so isn’t necessary), performs the operation and returns a copy of the object, so you’d use:

df = df.an_operation(inplace=False)

Question 39

在大熊猫中，inplace = True是否有害？

TLDR；是的，是的。

inplace，顾名思义，通常不会阻止创建副本，并且（几乎）从不提供任何性能优势
inplace 不适用于方法链接
inplace 对于初学者来说是一个常见的陷阱，因此删除此选项将简化API

我不建议设置此参数，因为它的作用很小。请参阅此GitHub问题，其中建议在inplaceapi范围内弃用该参数。

一个普遍的误解是，使用inplace=True会导致更高效或更优化的代码。实际上，使用绝对没有性能上的好处inplace=True。无论是就地和外的地方版本创建数据的副本无论如何，与就地版本自动分配拷贝回来。

inplace=True对于初学者来说是一个常见的陷阱。例如，它可以触发SettingWithCopyWarning：

df = pd.DataFrame({'a': [3, 2, 1], 'b': ['x', 'y', 'z']})

df2 = df[df['a'] > 1]
df2['b'].replace({'x': 'abc'}, inplace=True)
# SettingWithCopyWarning: 
# A value is trying to be set on a copy of a slice from a DataFrame

使用inplace=True 可能会或可能不会在DataFrame列上调用函数。当涉及链式索引时，尤其如此。

似乎上述问题还不够，inplace=True也阻碍了方法链接。对比一下

result = df.some_function1().reset_index().some_function2()

相对于

temp = df.some_function1()
temp.reset_index(inplace=True)
result = temp.some_function2()

前者有助于更好的代码组织和可读性。

另一个支持的说法是，set_axis最近更改了API，以便将inplace默认值从True切换为False。参见GH27600。出色的开发人员！

Question 40

In pandas, is inplace = True considered harmful, or not?

TLDR; Yes, yes it is.

inplace, contrary to what the name implies, often does not prevent copies from being created, and (almost) never offers any performance benefits
inplace does not work with method chaining
inplace is a common pitfall for beginners, so removing this option will simplify the API

I don’t advise setting this parameter as it serves little purpose. See this GitHub issue which proposes the inplace argument be deprecated api-wide.

It is a common misconception that using inplace=True will lead to more efficient or optimized code. In reality, there are absolutely no performance benefits to using inplace=True. Both the in-place and out-of-place versions create a copy of the data anyway, with the in-place version automatically assigning the copy back.

inplace=True is a common pitfall for beginners. For example, it can trigger the SettingWithCopyWarning:

df = pd.DataFrame({'a': [3, 2, 1], 'b': ['x', 'y', 'z']})

df2 = df[df['a'] > 1]
df2['b'].replace({'x': 'abc'}, inplace=True)
# SettingWithCopyWarning: 
# A value is trying to be set on a copy of a slice from a DataFrame

Calling a function on a DataFrame column with inplace=True may or may not work. This is especially true when chained indexing is involved.

As if the problems described above aren’t enough, inplace=True also hinders method chaining. Contrast the working of

result = df.some_function1().reset_index().some_function2()

As opposed to

temp = df.some_function1()
temp.reset_index(inplace=True)
result = temp.some_function2()

The former lends itself to better code organization and readability.

Another supporting claim is that the API for set_axis was recently changed such that inplace default value was switched from True to False. See GH27600. Great job devs!

Question 41

我使用它的方式是

# Have to assign back to dataframe (because it is a new copy)
df = df.some_operation(inplace=False)

要么

# No need to assign back to dataframe (because it is on the same copy)
df.some_operation(inplace=True)

结论：

 if inplace is False
      Assign to a new variable;
 else
      No need to assign

Question 42

The way I use it is

# Have to assign back to dataframe (because it is a new copy)
df = df.some_operation(inplace=False)

Or

# No need to assign back to dataframe (because it is on the same copy)
df.some_operation(inplace=True)

CONCLUSION:

 if inplace is False
      Assign to a new variable;
 else
      No need to assign

Question 43

该inplace参数：

df.dropna(axis='index', how='all', inplace=True)

在Pandas与一般的手段：

1.熊猫创建原始数据的副本

2. …对它进行一些计算

3. …将结果分配给原始数据。

4. …删除副本。

正如你在我的答案其余阅读下面的进一步，我们还可以有充分的理由来使用此参数即inplace operations，但如果能，我们应该避免，因为它产生更多的问题，如：

1.您的代码将更难调试（实际上，SettingwithCopyWarning表示警告您可能出现的问题）

2.与方法链冲突

因此，甚至在某些情况下我们应该使用它吗？

绝对可以。如果我们使用熊猫或任何工具处理庞大的数据集，我们很容易面对这样的情况，即一些大数据会消耗我们的整个内存。为了避免这种不良影响，我们可以使用诸如方法链接之类的一些技术：

(
    wine.rename(columns={"color_intensity": "ci"})
    .assign(color_filter=lambda x: np.where((x.hue > 1) & (x.ci > 7), 1, 0))
    .query("alcohol > 14 and color_filter == 1")
    .sort_values("alcohol", ascending=False)
    .reset_index(drop=True)
    .loc[:, ["alcohol", "ci", "hue"]]
)

这使我们的代码更紧凑（尽管也更难以解释和调试），并且由于链接的方法与另一种方法的返回值一起使用而占用的内存更少，因此仅产生输入数据的一个副本。我们可以清楚地看到，执行此操作后，我们将有2倍的原始数据内存消耗。

或者我们可以使用inplace参数（尽管也更难解释和调试），我们的内存消耗将是原始数据的2倍，但是此操作后的内存消耗仍然是原始数据的1倍，如果有人每次使用庞大的数据集时都确切知道这可能是一个原始数据，大收益。

定论：

避免使用inplace参数，除非您不使用大量数据，并且在仍然使用它的情况下要注意其可能的问题。

Question 44

The inplace parameter:

df.dropna(axis='index', how='all', inplace=True)

in Pandas and in general means:

1. Pandas creates a copy of the original data

2. … does some computation on it

3. … assigns the results to the original data.

4. … deletes the copy.

As you can read in the rest of my answer’s further below, we still can have good reason to use this parameter i.e. the inplace operations, but we should avoid it if we can, as it generate more issues, as:

1. Your code will be harder to debug (Actually SettingwithCopyWarning stands for warning you to this possible problem)

2. Conflict with method chaining

So there is even case when we should use it yet?

Definitely yes. If we use pandas or any tool for handeling huge dataset, we can easily face the situation, where some big data can consume our entire memory. To avoid this unwanted effect we can use some technics like method chaining:

(
    wine.rename(columns={"color_intensity": "ci"})
    .assign(color_filter=lambda x: np.where((x.hue > 1) & (x.ci > 7), 1, 0))
    .query("alcohol > 14 and color_filter == 1")
    .sort_values("alcohol", ascending=False)
    .reset_index(drop=True)
    .loc[:, ["alcohol", "ci", "hue"]]
)

which make our code more compact (though harder to interpret and debug too) and consumes less memory as the chained methods works with the other method’s returned values, thus resulting in only one copy of the input data. We can see clearly, that we will have 2 x original data memory consumption after this operations.

Or we can use inplace parameter (though harder to interpret and debug too) our memory consumption will be 2 x original data, but our memory consumption after this operation remains 1 x original data, which if somebody whenever worked with huge datasets exactly knows can be a big benefit.

Final conclusion:

Avoid using inplace parameter unless you don’t work with huge data and be aware of its possible issues in case of still using of it.

Question 45

将其保存到相同的变量

data["column01"].where(data["column01"]< 5, inplace=True)

将其保存到单独的变量

data["column02"] = data["column01"].where(data["column1"]< 5)

但是，您始终可以覆盖变量

data["column01"] = data["column01"].where(data["column1"]< 5)

仅供参考：默认 inplace = False

Question 46

Save it to the same variable

data["column01"].where(data["column01"]< 5, inplace=True)

Save it to a separate variable

data["column02"] = data["column01"].where(data["column1"]< 5)

But, you can always overwrite the variable

data["column01"] = data["column01"].where(data["column1"]< 5)

FYI: In default inplace = False

Question 47

当尝试使用函数对Pandas数据框进行更改时，如果要将更改提交到数据框，则使用“ inplace = True”。因此，以下代码中的第一行将“ df”中第一列的名称更改为“ Grades”。如果要查看生成的数据库，我们需要调用数据库。

df.rename(columns={0: 'Grades'}, inplace=True)
df

当我们不想提交更改而只打印结果数据库时，我们使用’inplace = False’（这也是默认值）。因此，实际上是在不更改原始数据库的情况下打印具有已提交更改的原始数据库的副本。

为了更清楚一点，以下代码执行相同的操作：

#Code 1
df.rename(columns={0: 'Grades'}, inplace=True)
#Code 2
df=df.rename(columns={0: 'Grades'}, inplace=False}

Question 48

When trying to make changes to a Pandas dataframe using a function, we use ‘inplace=True’ if we want to commit the changes to the dataframe. Therefore, the first line in the following code changes the name of the first column in ‘df’ to ‘Grades’. We need to call the database if we want to see the resulting database.

df.rename(columns={0: 'Grades'}, inplace=True)
df

We use ‘inplace=False’ (this is also the default value) when we don’t want to commit the changes but just print the resulting database. So, in effect a copy of the original database with the committed changes is printed without altering the original database.

Just to be more clear, the following codes do the same thing:

#Code 1
df.rename(columns={0: 'Grades'}, inplace=True)
#Code 2
df=df.rename(columns={0: 'Grades'}, inplace=False}

Question 49

inplace=True 是否使用取决于您是否要更改原始df。

df.drop_duplicates()

将仅查看丢弃的值，而不会对df进行任何更改

df.drop_duplicates(inplace  = True)

将删除值并更改df。

希望这可以帮助。：）

Question 50

inplace=True is used depending if you want to make changes to the original df or not.

df.drop_duplicates()

will only make a view of dropped values but not make any changes to df

df.drop_duplicates(inplace  = True)

will drop values and make changes to df.

Hope this helps.:)

Question 51

inplace=True使功能不纯。它更改原始数据框并返回无。在这种情况下，您会中断DSL链。由于大多数数据框功能都返回一个新的数据框，因此可以方便地使用DSL。喜欢

df.sort_values().rename().to_csv()

inplace=True返回值为None的函数调用，并且DSL链断开。例如

df.sort_values(inplace=True).rename().to_csv()

会抛出 NoneType object has no attribute 'rename'

与python的内置排序和排序类似。lst.sort()返回None并sorted(lst)返回一个新列表。

通常，inplace=True除非有特殊原因，否则请勿使用。当您必须编写类似的重新分配代码时df = df.sort_values()，请尝试将函数调用附加到DSL链中，例如

df = pd.read_csv().sort_values()...

Question 52

inplace=True makes the function impure. It changes the original dataframe and returns None. In that case, You breaks the DSL chain. Because most of dataframe functions return a new dataframe, you can use the DSL conveniently. Like

df.sort_values().rename().to_csv()

Function call with inplace=True returns None and DSL chain is broken. For example

df.sort_values(inplace=True).rename().to_csv()

will throw NoneType object has no attribute 'rename'

Something similar with python’s build-in sort and sorted. lst.sort() returns None and sorted(lst) returns a new list.

Generally, do not use inplace=True unless you have specific reason of doing so. When you have to write reassignment code like df = df.sort_values(), try attaching the function call in the DSL chain, e.g.

df = pd.read_csv().sort_values()...

Question 53

就我在大熊猫方面的经验而言，我想回答一下。

‘inplace = True’参数代表数据帧必须永久更改，例如。

    df.dropna(axis='index', how='all', inplace=True)

更改相同的数据框（因为这只大熊猫在索引中找到NaN条目并将其删除）。如果我们尝试

    df.dropna(axis='index', how='all')

熊猫显示了我们进行了更改的数据框，但不会修改原始数据框“ df”。

Question 54

As Far my experience in pandas I would like to answer.

The ‘inplace=True’ argument stands for the data frame has to make changes permanent eg.

    df.dropna(axis='index', how='all', inplace=True)

changes the same dataframe (as this pandas find NaN entries in index and drops them). If we try

    df.dropna(axis='index', how='all')

pandas shows the dataframe with changes we make but will not modify the original dataframe ‘df’.

Question 55

如果您不使用inplace = True或使用inplace = False，则基本上可以得到一个副本。

因此，例如：

testdf.sort_values(inplace=True, by='volume', ascending=False)

会改变结构，数据按降序排列。

然后：

testdf2 = testdf.sort_values( by='volume', ascending=True)

将使testdf2成为副本。值将全部相同，但排序将颠倒，您将拥有一个独立的对象。

然后在另一列中，说出LongMA，您可以：

testdf2.LongMA = testdf2.LongMA -1

testdf中的LongMA列将保留原始值，而testdf2列将减少值。

随着计算链的增长以及数据帧的副本具有其自己的生命周期，跟踪差异至关重要。

Question 56

If you don’t use inplace=True or you use inplace=False you basically get back a copy.

So for instance:

testdf.sort_values(inplace=True, by='volume', ascending=False)

will alter the structure with the data sorted in descending order.

then:

testdf2 = testdf.sort_values( by='volume', ascending=True)

will make testdf2 a copy. the values will all be the same but the sort will be reversed and you will have an independent object.

then given another column, say LongMA and you do:

testdf2.LongMA = testdf2.LongMA -1

the LongMA column in testdf will have the original values and testdf2 will have the decrimented values.

It is important to keep track of the difference as the chain of calculations grows and the copies of dataframes have their own lifecycle.

Question 57

是的，在Pandas中，我们有很多具有参数的函数，inplace但默认情况下将其分配给False。

因此，当您执行df.dropna(axis='index', how='all', inplace=False)此操作时，它认为您不想更改orignial DataFrame，因此它会为您创建具有所需更改的新副本。

但是，当您将inplace参数更改为True

然后，这等效于明确地说，我不需要新的副本，DataFrame而是对给定的内容进行更改DataFrame

这迫使Python解释器不要创建新的DataFrame

但是您也可以inplace通过将结果重新分配给原始DataFrame来避免使用参数

df = df.dropna(axis='index', how='all')

Question 58

Yes, in Pandas we have many functions has the parameter inplace but by default it is assigned to False.

So, when you do df.dropna(axis='index', how='all', inplace=False) it thinks that you do not want to change the orignial DataFrame, therefore it instead creates a new copy for you with the required changes.

But, when you change the inplace parameter to True

Then it is equivalent to explicitly say that I do not want a new copy of the DataFrame instead do the changes on the given DataFrame

This forces the Python interpreter to not to create a new DataFrame

But you can also avoid using the inplace parameter by reassigning the result to the orignal DataFrame

df = df.dropna(axis='index', how='all')

Question 59

I’m trying to unzip a csv file and pass it into pandas so I can work on the file.
The code I have tried so far is:

import requests, zipfile, StringIO
r = requests.get('http://data.octo.dc.gov/feeds/crime_incidents/archive/crime_incidents_2013_CSV.zip')
z = zipfile.ZipFile(StringIO.StringIO(r.content))
crime2013 = pandas.read_csv(z.read('crime_incidents_2013_CSV.csv'))

After the last line, although python is able to get the file, I get a “does not exist” at the end of the error.

Can someone tell me what I’m doing incorrectly?

Question 60

If you want to read a zipped or a tar.gz file into pandas dataframe, the read_csv methods includes this particular implementation.

df = pd.read_csv('filename.zip')

Or the long form:

df = pd.read_csv('filename.zip', compression='zip', header=0, sep=',', quotechar='"')

Description of the compression argument from the docs:

compression : {‘infer’, ‘gzip’, ‘bz2’, ‘zip’, ‘xz’, None}, default ‘infer’ For on-the-fly decompression of on-disk data. If ‘infer’ and filepath_or_buffer is path-like, then detect compression from the following extensions: ‘.gz’, ‘.bz2’, ‘.zip’, or ‘.xz’ (otherwise no decompression). If using ‘zip’, the ZIP file must contain only one data file to be read in. Set to None for no decompression.

New in version 0.18.1: support for ‘zip’ and ‘xz’ compression.

Question 61

I think you want to open the ZipFile, which returns a file-like object, rather than read:

In [11]: crime2013 = pd.read_csv(z.open('crime_incidents_2013_CSV.csv'))

In [12]: crime2013
Out[12]:
<class 'pandas.core.frame.DataFrame'>
Int64Index: 24567 entries, 0 to 24566
Data columns (total 15 columns):
CCN                            24567  non-null values
REPORTDATETIME                 24567  non-null values
SHIFT                          24567  non-null values
OFFENSE                        24567  non-null values
METHOD                         24567  non-null values
LASTMODIFIEDDATE               24567  non-null values
BLOCKSITEADDRESS               24567  non-null values
BLOCKXCOORD                    24567  non-null values
BLOCKYCOORD                    24567  non-null values
WARD                           24563  non-null values
ANC                            24567  non-null values
DISTRICT                       24567  non-null values
PSA                            24567  non-null values
NEIGHBORHOODCLUSTER            24263  non-null values
BUSINESSIMPROVEMENTDISTRICT    3613  non-null values
dtypes: float64(4), int64(1), object(10)

Question 62

It seems you don’t even have to specify the compression any more. The following snippet loads the data from filename.zip into df.

import pandas as pd
df = pd.read_csv('filename.zip')

(Of course you will need to specify separator, header, etc. if they are different from the defaults.)

Question 63

For “zip” files, you can use import zipfile and your code will be working simply with these lines:

import zipfile
import pandas as pd
with zipfile.ZipFile("Crime_Incidents_in_2013.zip") as z:
   with z.open("Crime_Incidents_in_2013.csv") as f:
      train = pd.read_csv(f, header=0, delimiter="\t")
      print(train.head())    # print the first 5 rows

And the result will be:

X,Y,CCN,REPORT_DAT,SHIFT,METHOD,OFFENSE,BLOCK,XBLOCK,YBLOCK,WARD,ANC,DISTRICT,PSA,NEIGHBORHOOD_CLUSTER,BLOCK_GROUP,CENSUS_TRACT,VOTING_PRECINCT,XCOORD,YCOORD,LATITUDE,LONGITUDE,BID,START_DATE,END_DATE,OBJECTID
0  -77.054968548763071,38.899775938598317,0925135...                                                                                                                                                               
1  -76.967309569035052,38.872119553647011,1003352...                                                                                                                                                               
2  -76.996184958456539,38.927921847721443,1101010...                                                                                                                                                               
3  -76.943077541353617,38.883686046653935,1104551...                                                                                                                                                               
4  -76.939209158039446,38.892278093281632,1125028...

Question 64

https://www.kaggle.com/jboysen/quick-gz-pandas-tutorial

Please follow this link.

import pandas as pd
traffic_station_df = pd.read_csv('C:\\Folders\\Jupiter_Feed.txt.gz', compression='gzip',
                                 header=1, sep='\t', quotechar='"')

#traffic_station_df['Address'] = 'address'

#traffic_station_df.append(traffic_station_df)
print(traffic_station_df)

问题：分配熊猫数据框列dtypes

回答 0

回答 1

回答 2

回答 3

回答 4

回答 5

问题：我何时应该在代码中使用pandas apply（）？

回答 0

apply，您不需要的便利功能

“ 如果应用是如此糟糕，那么为什么要在API中使用它呢？ ”

“ 如何当我应该让我的代码申请-免费？ ”

数值数据

字符串/正则表达式

一个常见的陷阱：列表的爆炸列

“ 有什么情况 apply 是好的吗？ ”

将Series转换为str：astypevsapply

GroupBy 链式转换操作

其他注意事项

apply, the Convenience Function you Never Needed

“If apply is so bad, then why is it in the API?”

“How and when should I make my code apply-free?”

Numeric Data

String/Regex

A Common Pitfall: Exploding Columns of Lists

“Are there any situations where apply is good?”

Converting Series to str: astype versus apply

GroupBy operations with chained transformations

Other Caveats

回答 1

并非所有人apply都一样

GroupBy.apply：普遍偏爱

pd.DataFrame.apply 专栏式：混合袋

Not all applys are alike

GroupBy.apply: generally favoured

pd.DataFrame.apply column-wise: a mixed bag

回答 2

回答 3

问题：熊猫read_xml（）方法测试策略

回答 0

问题：根据另一个列熊猫数据框提取列值

回答 0

回答 1

回答 2

回答 3

回答 4

问题：Python Pandas仅合并某些列

回答 0

回答 1

回答 2

回答 3

回答 4

问题：如何在熊猫中更改日期时间格式

回答 0

回答 1

回答 2

回答 3

回答 4

回答 5

回答 6

问题：了解inplace = True

回答 0

回答 1

TLDR；是的，是的。

TLDR; Yes, yes it is.

回答 2

回答 3

因此，甚至在某些情况下我们应该使用它吗？

定论：

So there is even case when we should use it yet?

Final conclusion:

回答 4

回答 5

回答 6

回答 7

回答 8

回答 9

回答 10

问题：读取压缩文件作为Pandas DataFrame

回答 0

`apply`，您不需要的便利功能

“ 有什么情况 `apply` 是好的吗？ ”

将Series转换为`str`：`astype`vs`apply`

`GroupBy` 链式转换操作

`apply`, the Convenience Function you Never Needed

“If `apply` is so bad, then why is it in the API?”

“How and when should I make my code `apply`-free?”

“Are there any situations where `apply` is good?”

Converting Series to `str`: `astype` versus `apply`

`GroupBy` operations with chained transformations

并非所有人`apply`都一样

`GroupBy.apply`：普遍偏爱

`pd.DataFrame.apply` 专栏式：混合袋

Not all `apply`s are alike

`GroupBy.apply`: generally favoured

`pd.DataFrame.apply` column-wise: a mixed bag